View: JL_ZZ_AR_TX_LMSG_CREATE_V

View Text

SELECT M1.ROWID ROW_ID
, R1.RULE_ID RULE_ID
, FC.FSC_CLS_ID RULE_DATA_ID
, M1.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, FC.FISCAL_CLASSIFICATION_CODE FISCAL_CLASSIFICATION_CODE
, LKP.MEANING FISCAL_CLASSIFICATION_DESC
, TO_CHAR(NULL) SHIP_FROM_CODE
, TO_CHAR(NULL) SHIP_FROM_DESC
, TO_NUMBER(NULL) SHIP_TO_SEGMENT_ID
, TO_CHAR(NULL) SHIP_TO_CODE
, TO_CHAR(NULL) SHIP_TO_DESC
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_CHAR(NULL) ITEM_NAME
, M1.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M1.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST1.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST1.STANDARD_TEXT_ID MESSAGE_ID
, ST1.TEXT TEXT
, FC.START_DATE_ACTIVE START_DATE_ACTIVE
, FC.END_DATE_ACTIVE END_DATE_ACTIVE
, ST1.START_DATE MSG_EFFECTIVE_DATE
, ST1.END_DATE MSG_END_DATE_ACTIVE
, M1.LAST_UPDATED_BY LAST_UPDATED_BY
, M1.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M1.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M1.CREATION_DATE CREATION_DATE
, M1.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R1
, JL_ZZ_AR_TX_LGL_MSG M1
, JL_ZZ_AR_TX_FSC_CLS FC
, AR_STANDARD_TEXT ST1
, FND_LOOKUPS LKP
, FND_LOOKUPS LKP_EX
WHERE R1.RULE_ID = M1.RULE_ID
AND R1.RULE = 'GET_FISC_CLAS_TX_CODE'
AND FC .FSC_CLS_ID = M1.RULE_DATA_ID
AND FC.ENABLED_FLAG = 'Y'
AND M1.MESSAGE_ID = ST1.STANDARD_TEXT_ID
AND FC.FISCAL_CLASSIFICATION_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
AND LKP.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP.END_DATE_ACTIVE
, SYSDATE)
AND LKP_EX.LOOKUP_CODE = M1.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES'
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT M2.ROWID ROW_ID
, R2.RULE_ID RULE_ID
, LC2.LOCN_ID RULE_DATA_ID
, M2.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, NULL FISCAL_CLASSIFICATION_CODE
, NULL FISCAL_CLASSIFICATION_DESC
, LC2.SHIP_FROM_CODE SHIP_FROM_CODE
, LKP.MEANING SHIP_FROM_DESC
, LC2.SHIP_TO_SEGMENT_ID SHIP_TO_SEGMENT_ID
, LV2.LOCATION_SEGMENT_VALUE SHIP_TO_CODE
, LV2.LOCATION_SEGMENT_USER_VALUE SHIP_TO_DESC
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_CHAR(NULL) ITEM_NAME
, M2.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M2.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST2.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST2.STANDARD_TEXT_ID MESSAGE_ID
, ST2.TEXT TEXT
, LC2.START_DATE_ACTIVE START_DATE_ACTIVE
, LC2.END_DATE_ACTIVE END_DATE_ACTIVE
, ST2.START_DATE MSG_EFFECTIVE_DATE
, ST2.END_DATE MSG_END_DATE_ACTIVE
, M2.LAST_UPDATED_BY LAST_UPDATED_BY
, M2.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M2.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M2.CREATION_DATE CREATION_DATE
, M2.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R2
, JL_ZZ_AR_TX_LGL_MSG M2
, JL_ZZ_AR_TX_LOCN LC2
, AR_LOCATION_VALUES LV2
, AR_STANDARD_TEXT ST2
, FND_LOOKUPS LKP
, FND_LOOKUPS LKP_EX
WHERE R2.RULE_ID = M2.RULE_ID AND R2.RULE = 'GET_LOCATION_TX_CODE' AND LC2.LOCN_ID = M2.RULE_DATA_ID AND LC2.SHIP_TO_SEGMENT_ID = LV2.LOCATION_SEGMENT_ID AND M2.MESSAGE_ID = ST2.STANDARD_TEXT_ID AND LC2.SHIP_FROM_CODE = LKP.LOOKUP_CODE AND LKP.LOOKUP_TYPE = 'JLZZ_STATE_PROVINCE' AND LKP.ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN NVL(LKP.START_DATE_ACTIVE
, SYSDATE) AND NVL(LKP.END_DATE_ACTIVE
, SYSDATE) AND LKP_EX.LOOKUP_CODE = M2.EXCEPTION_CODE AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES' AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT M3.ROWID ROW_ID
, R3.RULE_ID RULE_ID
, EX.EXC_FSC_ID RULE_DATA_ID
, M3.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, EX.FISCAL_CLASSIFICATION_CODE FISCAL_CLASSIFICATION_CODE
, LKP.MEANING FISCAL_CLASSIFICATION_DESC
, EX.SHIP_FROM_CODE SHIP_FROM_CODE
, LKP1.MEANING SHIP_FROM_DESC
, EX.SHIP_TO_SEGMENT_ID SHIP_TO_SEGMENT_ID
, LV3.LOCATION_SEGMENT_VALUE SHIP_TO_CODE
, LV3.LOCATION_SEGMENT_USER_VALUE SHIP_TO_DESC
, TO_NUMBER( NULL) INVENTORY_ITEM_ID
, TO_CHAR(NULL) ITEM_NAME
, M3.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M3.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST3.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST3.STANDARD_TEXT_ID MESSAGE_ID
, ST3.TEXT TEXT
, EX.START_DATE_ACTIVE START_DATE_ACTIVE
, EX.END_DATE_ACTIVE END_DATE_ACTIVE
, ST3.START_DATE MSG_EFFECTIVE_DATE
, ST3.END_DATE MSG_END_DATE_ACTIVE
, M3.LAST_UPDATED_BY LAST_UPDATED_BY
, M3.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M3.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M3.CREATION_DATE CREATION_DATE
, M3.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R3
, JL_ZZ_AR_TX_LGL_MSG M3
, JL_ZZ_AR_TX_EXC_FSC EX
, AR_LOCATION_VALUES LV3
, AR_STANDARD_TEXT ST3
, FND_LOOKUPS LKP
, FND_LOOKUPS LKP1
, FND_LOOKUPS LKP_EX
WHERE R3.RULE_ID = M3.RULE_ID
AND R3.RULE = 'GET_EXC_FISC_CLAS_TX_CODE'
AND EX.EXC_FSC_ID = M3.RULE_DATA_ID
AND EX.SHIP_TO_SEGMENT_ID = LV3.LOCATION_SEGMENT_ID
AND M3.MESSAGE_ID = ST3.STANDARD_TEXT_ID
AND EX.FISCAL_CLASSIFICATION_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
AND LKP.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP.END_DATE_ACTIVE
, SYSDATE)
AND EX.SHIP_FROM_CODE = LKP1.LOOKUP_CODE
AND LKP1.LOOKUP_TYPE = 'JLZZ_STATE_PROVINCE'
AND LKP1.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP1.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP1.END_DATE_ACTIVE
, SYSDATE)
AND LKP_EX.LOOKUP_CODE = M3.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES '
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT R4.ROWID ROW_ID
, R4.RULE_ID RULE_ID
, XI.EXC_ITM_ID RULE_DATA_ID
, M4.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_CODE
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_DESC
, XI.SHIP_FROM_CODE SHIP_FROM_CODE
, LKP.MEANING SHIP_FROM_DESC
, XI.SHIP_TO_SEGMENT_ID SHIP_TO_SEGMENT_ID
, LV4.LOCATION_SEGMENT_VALUE SHIP_TO_CODE
, LV4.LOCATION_SEGMENT_USER_VALUE SHIP_TO_DESC
, XI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, IT.DESCRIPTION ITEM_NAME
, M4.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M4.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST4.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST4.STANDARD_TEXT_ID MESSAGE_ID
, ST4.TEXT TEXT
, XI.START_DATE_ACTIVE START_DATE_ACTIVE
, XI.END_DATE_ACTIVE END_DATE_ACTIVE
, ST4.START_DATE MSG_EFFECTIVE_DATE
, ST4.END_DATE MSG_END_DATE_ACTIVE
, M4.LAST_UPDATED_BY LAST_UPDATED_BY
, M4.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M4.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M4.CREATION_DATE CREATION_DATE
, M4.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R4
, JL_ZZ_AR_TX_LGL_MSG M4
, JL_ZZ_AR_TX_EXC_ITM XI
, MTL_SYSTEM_ITEMS IT
, AR_LOCATION_VALUES LV4
, AR_STANDARD_TEXT ST4
, FND_LOOKUPS LKP
, FND_LOOKUPS LKP_EX
WHERE R4.RULE_ID = M4.RULE_ID
AND R4.RULE = 'GET_EXC_ITEM_TX_CODE'
AND XI.EXC_ITM_ID = M4.RULE_DATA_ID
AND XI.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
AND XI.ORGANIZATION_ID = IT.ORGANIZATION_ID
AND XI.SHIP_TO_SEGMENT_ID = LV4.LOCATION_SEGMENT_ID
AND M4.MESSAGE_ID = ST4.STANDARD_TEXT_ID
AND XI.SHIP_FROM_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'JLZZ_STATE_PROVINCE'
AND LKP.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP.END_DATE_ACTIVE
, SYSDATE)
AND LKP_EX.LOOKUP_CODE = M4.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES'
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT M5.ROWID ROW_ID
, R5.RULE_ID RULE_ID
, M5.RULE_DATA_ID RULE_DATA_ID
, M5.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_CODE
, TO_CHAR( NULL) FISCAL_CLASSIFICATION_DESC
, TO_CHAR(NULL) SHIP_FROM_CODE
, TO_CHAR(NULL) SHIP_FROM_DESC
, TO_NUMBER(NULL) SHIP_TO_SEGMENT_ID
, TO_CHAR(NULL) SHIP_TO_CODE
, TO_CHAR(NULL) SHIP_TO_DESC
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_CHAR(NULL) ITEM_NAME
, M5.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M5.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST5.NAME NAME
, CUSTACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, C.PARTY_NAME CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST5.STANDARD_TEXT_ID MESSAGE_ID
, ST5.TEXT TEXT
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, ST5.START_DATE MSG_EFFECTIVE_DATE
, ST5.END_DATE MSG_END_DATE_ACTIVE
, M5.LAST_UPDATED_BY LAST_UPDATED_BY
, M5.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M5.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M5.CREATION_DATE CREATION_DATE
, M5.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R5
, JL_ZZ_AR_TX_LGL_MSG M5
, HZ_PARTIES C
, HZ_CUST_ACCOUNTS CUSTACCT
, AR_STANDARD_TEXT ST5
, FND_LOOKUPS LKP_EX
WHERE R5.RULE_ID = M5.RULE_ID
AND R5.RULE = 'GET_CUSTOMER_TX_CODE'
AND M5.RULE_DATA_ID = CUSTACCT.CUST_ACCOUNT_ID
AND M5.MESSAGE_ID = ST5.STANDARD_TEXT_ID
AND LKP_EX.LOOKUP_CODE = M5.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES'
AND C.PARTY_ID=CUSTACCT.PARTY_ID
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT M6.ROWID ROW_ID
, R6.RULE_ID RULE_ID
, M6.RULE_DATA_ID RULE_DATA_ID
, M6.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_CODE
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_DESC
, TO_CHAR(NULL) SHIP_FROM_CODE
, TO_CHAR(NULL) SHIP_FROM_DESC
, TO_NUMBER(NULL) SHIP_TO_SEGMENT_ID
, TO_CHAR(NULL) SHIP_TO_CODE
, TO_CHAR(NULL) SHIP_TO_DESC
, MT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MT.DESCRIPTION ITEM_NAME
, M6.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M6.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST6.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST6.STANDARD_TEXT_ID MESSAGE_ID
, ST6.TEXT TEXT
, MT.START_DATE_ACTIVE START_DATE_ACTIVE
, MT.END_DATE_ACTIVE END_DATE_ACTIVE
, ST6.START_DATE MSG_EFFECTIVE_DATE
, ST6.END_DATE MSG_END_DATE_ACTIVE
, M6.LAST_UPDATED_BY LAST_UPDATED_BY
, M6.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M6.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M6.CREATION_DATE CREATION_DATE
, M6.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R6
, JL_ZZ_AR_TX_LGL_MSG M6
, MTL_SYSTEM_ITEMS MT
, AR_STANDARD_TEXT ST6
, FND_LOOKUPS LKP_EX
WHERE R6.RULE_ID = M6.RULE_ID
AND R6.RULE = 'GET_ITEM_TX_CODE'
AND M6.RULE_DATA_ID = MT.INVENTORY_ITEM_ID
AND M6.INVENTORY_ORGANIZATION_ID = MT.ORGANIZATION_ID
AND M6.MESSAGE_ID = ST6.STANDARD_TEXT_ID
AND LKP_EX.LOOKUP_CODE = M6.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES'
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT M7.ROWID ROW_ID
, R7.RULE_ID RULE_ID
, M7.RULE_DATA_ID RULE_DATA_ID
, M7.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_CODE
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_DESC
, TO_CHAR(NULL) SHIP_FROM_CODE
, TO_CHAR(NULL) SHIP_FROM_DESC
, TO_NUMBER(NULL) SHIP_TO_SEGMENT_ID
, TO_CHAR(NULL) SHIP_TO_CODE
, TO_CHAR(NULL) SHIP_TO_DESC
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_CHAR(NULL) ITEM_NAME
, M7.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M7.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST7.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, OG.NAME ORG_NAME
, TO_NUMBER(NULL) MEMO_LINE_ID
, TO_CHAR(NULL) MEMO_LINE
, ST7.STANDARD_TEXT_ID MESSAGE_ID
, ST7.TEXT TEXT
, TO_DATE(NULL) START_DATE_ACTIVE
, TO_DATE(NULL) END_DATE_ACTIVE
, ST7.START_DATE MSG_EFFECTIVE_DATE
, ST7.END_DATE MSG_END_DATE_ACTIVE
, M7.LAST_UPDATED_BY LAST_UPDATED_BY
, M7.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M7.LAST_UPDATE_LOGIN AST_UPDATE_LOGIN
, M7.CREATION_DATE CREATION_DATE
, M7.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R7
, JL_ZZ_AR_TX_LGL_MSG M7
, HR_ORGANIZATION_UNITS OG
, AR_STANDARD_TEXT ST7
, FND_LOOKUPS LKP_EX
WHERE R7.RULE_ID = M7.RULE_ID
AND R7.RULE = 'GET_ORGANIZATION_TX_CODE'
AND M7.RULE_DATA_ID = OG.ORGANIZATION_ID
AND M7.MESSAGE_ID = ST7.STANDARD_TEXT_ID
AND LKP_EX.LOOKUP_CODE = M7.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES'
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE) UNION SELECT M8.ROWID ROW_ID
, R8.RULE_ID RULE_ID
, M8.RULE_DATA_ID RULE_DATA_ID
, M8.EXCEPTION_CODE EXCEPTION_CODE
, LKP_EX.MEANING EXCEPTION_DESC
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_CODE
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_DESC
, TO_CHAR(NULL) SHIP_FROM_CODE
, TO_CHAR(NULL) SHIP_FROM_DESC
, TO_NUMBER(NULL) SHIP_TO_SEGMENT_ID
, TO_CHAR(NULL) SHIP_TO_CODE
, TO_CHAR(NULL) SHIP_TO_DESC
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_CHAR(NULL) ITEM_NAME
, M8.INVENTORY_ITEM_FLAG INVENTORY_ITEM_FLAG
, M8.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, ST8.NAME NAME
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) ORG_NAME
, ML.MEMO_LINE_ID MEMO_LINE_ID
, ML.NAME MEMO_LINE
, ST8.STANDARD_TEXT_ID MESSAGE_ID
, ST8.TEXT TEXT
, ML.START_DATE START_DATE_ACTIVE
, ML.END_DATE END_DATE_ACTIVE
, ST8.START_DATE MSG_EFFECTIVE_DATE
, ST8.END_DATE MSG_END_DATE_ACTIVE
, M8.LAST_UPDATED_BY LAST_UPDATED_BY
, M8.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M8.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M8.CREATION_DATE CREATION_DATE
, M8.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES R8
, JL_ZZ_AR_TX_LGL_MSG M8
, AR_MEMO_LINES ML
, AR_STANDARD_TEXT ST8
, FND_LOOKUPS LKP_EX
WHERE R8.RULE_ID = M8.RULE_ID
AND R8.RULE = 'GET_MEMO_LINE_TX_CODE'
AND M8 .RULE_DATA_ID = ML.MEMO_LINE_ID
AND M8.MESSAGE_ID = ST8.STANDARD_TEXT_ID
AND LKP_EX.LOOKUP_CODE = M8.EXCEPTION_CODE
AND LKP_EX.LOOKUP_TYPE = 'JLZZ_AR_TX_LEGAL_MESSAGES'
AND LKP_EX.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(LKP_EX.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LKP_EX.END_DATE_ACTIVE
, SYSDATE)

Columns