VIEW: APPS.JL_ZZ_AR_TX_LGL_MSG_V

Source

View Text - Preformatted

SELECT m1.rowid row_id, r1.rule_id rule_id, fc.fsc_cls_id rule_data_id, m1.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m1.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'), 1,80) , fc.FISCAL_CLASSIFICATION_CODE fiscal_classification_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(fc.fiscal_classification_code, 'JLZZ_AR_TX_FISCAL_CLASS_CODE'),1,80) , 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.org_id org_id, 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_all r1 , jl_zz_ar_tx_lgl_msg m1 , jl_zz_ar_tx_fsc_cls_all fc , ar_standard_text_vl st1 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 nvl(m1.org_id,-99) = nvl(r1.org_id,-99) AND nvl(m1.org_id,-99) = nvl(fc.org_id,-99) UNION SELECT m2.rowid row_id, r2.rule_id rule_id, lc2.locn_id rule_data_id, m2.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m2.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) exception_desc, NULL fiscal_classification_code, NULL fiscal_classification_desc, lc2.ship_from_code ship_from_code, lv1.geography_name ship_from_desc, lc2.ship_to_segment_id ship_to_segment_id, lv2.Geography_code ship_to_code, lv2.Geography_name 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.org_id org_id, 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_all r2, jl_zz_ar_tx_lgl_msg m2, jl_zz_ar_tx_locn_all lc2, hz_geographies lv1 , hz_geographies lv2, ar_system_parameters_all sys, ar_standard_text_vl st2 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_from_code = lv1.geography_code AND lv1.country_code = sys.default_country AND lv1.geography_type = sys.global_attribute9 AND lc2.ship_to_segment_id = lv2.geography_id AND m2.message_id = st2.standard_text_id AND nvl(m2.org_id,-99) = nvl(r2.org_id,-99) AND nvl(m2.org_id,-99) = nvl(lc2.org_id,-99) AND nvl(m2.org_id, -99) = nvl(sys.org_id, -99) UNION SELECT m3.rowid row_id, r3.rule_id rule_id, ex.exc_fsc_id rule_data_id, m3.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m3.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) exception_desc, ex.fiscal_classification_code fiscal_classification_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(ex.fiscal_classification_code, 'JLZZ_AR_TX_FISCAL_CLASS_CODE'),1,80) fiscal_classification_desc, ex.ship_from_code ship_from_code, lv31.geography_name ship_from_desc, ex.ship_to_segment_id ship_to_segment_id, lv3.Geography_code ship_to_code, lv3.Geography_name 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.org_id org_id, 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_all r3, jl_zz_ar_tx_lgl_msg m3, jl_zz_ar_tx_exc_fsc_all ex, hz_geographies lv31 , hz_geographies lv3, ar_system_parameters_all sys, ar_standard_text_vl st3 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_from_code = lv31.geography_code AND lv31.country_code = sys.default_country AND lv31.geography_type = sys.global_attribute9 AND ex.ship_to_segment_id||'' = lv3.geography_id AND m3.message_id = st3.standard_text_id AND nvl(m3.org_id,-99) = nvl(r3.org_id,-99) AND nvl(m3.org_id,-99) = nvl(ex.org_id,-99) AND nvl(m3.org_id, -99) = nvl(sys.org_id, -99) UNION SELECT m4.rowid row_id, r4.rule_id rule_id, xi.exc_itm_id rule_data_id, m4.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m4.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) exception_desc, to_char(NULL) fiscal_classification_code, to_char(NULL) fiscal_classification_desc, xi.ship_from_code ship_from_code, lv41.geography_name ship_from_desc, xi.ship_to_segment_id ship_to_segment_id, lv4.geography_code ship_to_code, lv4.geography_name 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.org_id org_id, 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_all r4, jl_zz_ar_tx_lgl_msg m4, jl_zz_ar_tx_exc_itm_all xi, mtl_system_items_b it, hz_geographies lv41 , hz_geographies lv4, ar_system_parameters_all sys, ar_standard_text_vl st4 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_from_code = lv41.geography_code AND lv41.country_code = sys.default_country AND lv41.geography_type = sys.global_attribute9 AND xi.ship_to_segment_id = lv4.geography_id AND m4.message_id = st4.standard_text_id AND nvl(m4.org_id,-99) = nvl(r4.org_id,-99) AND nvl(m4.org_id,-99) = nvl(xi.org_id,-99) AND nvl(m4.org_id, -99) = nvl(sys.org_id, -99) UNION SELECT m5.rowid row_id, r5.rule_id rule_id, m5.rule_data_id rule_data_id, m5.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m5.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) 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.org_id org_id, 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_all r5, jl_zz_ar_tx_lgl_msg m5, hz_parties c, hz_cust_accounts custacct, ar_standard_text_vl st5 WHERE r5.rule_id = m5.rule_id AND r5.rule = 'GET_CUSTOMER_TX_CODE' AND m5.rule_data_id = custacct.cust_account_id AND c.party_id=custacct.party_id and m5.message_id = st5.standard_text_id AND nvl(m5.org_id,-99) = nvl(r5.org_id,-99) AND nvl(m5.org_id,-99) = nvl(custacct.org_id,-99) UNION SELECT m6.rowid row_id, r6.rule_id rule_id, m6.rule_data_id rule_data_id , m6.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m6.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) 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.org_id org_id, 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_all r6, jl_zz_ar_tx_lgl_msg m6, mtl_system_items_b mt, ar_standard_text_vl st6 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 nvl(m6.org_id,-99) = nvl(r6.org_id,-99) UNION SELECT m7.rowid row_id, r7.rule_id rule_id, m7.rule_data_id rule_data_id, m7.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m7.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) 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.org_id org_id, m7.last_updated_by last_updated_by, m7.last_update_date last_update_date, m7.last_update_login last_update_login, m7.creation_date creation_date, m7.created_by created_by FROM jl_zz_ar_tx_rules_all r7, jl_zz_ar_tx_lgl_msg m7, hr_organization_units og, ar_standard_text_vl st7 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 nvl(m7.org_id,-99) = nvl(r7.org_id,-99) UNION SELECT m8.rowid row_id, r8.rule_id rule_id, m8.rule_data_id rule_data_id, m8.exception_code exception_code, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(m8.exception_code, 'JLZZ_AR_TX_LEGAL_MESSAGE'),1,80) 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.org_id org_id, 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_all r8, jl_zz_ar_tx_lgl_msg m8, ar_memo_lines ml, ar_standard_text_vl st8 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 nvl(m8.org_id,-99) = nvl(r8.org_id,-99) AND nvl(m8.org_id,-99) = nvl(ml.org_id,-99)

View Text - HTML Formatted

SELECT M1.ROWID ROW_ID
, R1.RULE_ID RULE_ID
, FC.FSC_CLS_ID RULE_DATA_ID
, M1.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M1.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80)
, FC.FISCAL_CLASSIFICATION_CODE FISCAL_CLASSIFICATION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(FC.FISCAL_CLASSIFICATION_CODE
, 'JLZZ_AR_TX_FISCAL_CLASS_CODE')
, 1
, 80)
, 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.ORG_ID ORG_ID
, 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_ALL R1
, JL_ZZ_AR_TX_LGL_MSG M1
, JL_ZZ_AR_TX_FSC_CLS_ALL FC
, AR_STANDARD_TEXT_VL ST1
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 NVL(M1.ORG_ID
, -99) = NVL(R1.ORG_ID
, -99)
AND NVL(M1.ORG_ID
, -99) = NVL(FC.ORG_ID
, -99) UNION SELECT M2.ROWID ROW_ID
, R2.RULE_ID RULE_ID
, LC2.LOCN_ID RULE_DATA_ID
, M2.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M2.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) EXCEPTION_DESC
, NULL FISCAL_CLASSIFICATION_CODE
, NULL FISCAL_CLASSIFICATION_DESC
, LC2.SHIP_FROM_CODE SHIP_FROM_CODE
, LV1.GEOGRAPHY_NAME SHIP_FROM_DESC
, LC2.SHIP_TO_SEGMENT_ID SHIP_TO_SEGMENT_ID
, LV2.GEOGRAPHY_CODE SHIP_TO_CODE
, LV2.GEOGRAPHY_NAME 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.ORG_ID ORG_ID
, 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_ALL R2
, JL_ZZ_AR_TX_LGL_MSG M2
, JL_ZZ_AR_TX_LOCN_ALL LC2
, HZ_GEOGRAPHIES LV1
, HZ_GEOGRAPHIES LV2
, AR_SYSTEM_PARAMETERS_ALL SYS
, AR_STANDARD_TEXT_VL ST2
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_FROM_CODE = LV1.GEOGRAPHY_CODE
AND LV1.COUNTRY_CODE = SYS.DEFAULT_COUNTRY
AND LV1.GEOGRAPHY_TYPE = SYS.GLOBAL_ATTRIBUTE9
AND LC2.SHIP_TO_SEGMENT_ID = LV2.GEOGRAPHY_ID
AND M2.MESSAGE_ID = ST2.STANDARD_TEXT_ID
AND NVL(M2.ORG_ID
, -99) = NVL(R2.ORG_ID
, -99)
AND NVL(M2.ORG_ID
, -99) = NVL(LC2.ORG_ID
, -99)
AND NVL(M2.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99) UNION SELECT M3.ROWID ROW_ID
, R3.RULE_ID RULE_ID
, EX.EXC_FSC_ID RULE_DATA_ID
, M3.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M3.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) EXCEPTION_DESC
, EX.FISCAL_CLASSIFICATION_CODE FISCAL_CLASSIFICATION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(EX.FISCAL_CLASSIFICATION_CODE
, 'JLZZ_AR_TX_FISCAL_CLASS_CODE')
, 1
, 80) FISCAL_CLASSIFICATION_DESC
, EX.SHIP_FROM_CODE SHIP_FROM_CODE
, LV31.GEOGRAPHY_NAME SHIP_FROM_DESC
, EX.SHIP_TO_SEGMENT_ID SHIP_TO_SEGMENT_ID
, LV3.GEOGRAPHY_CODE SHIP_TO_CODE
, LV3.GEOGRAPHY_NAME 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.ORG_ID ORG_ID
, 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_ALL R3
, JL_ZZ_AR_TX_LGL_MSG M3
, JL_ZZ_AR_TX_EXC_FSC_ALL EX
, HZ_GEOGRAPHIES LV31
, HZ_GEOGRAPHIES LV3
, AR_SYSTEM_PARAMETERS_ALL SYS
, AR_STANDARD_TEXT_VL ST3
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_FROM_CODE = LV31.GEOGRAPHY_CODE
AND LV31.COUNTRY_CODE = SYS.DEFAULT_COUNTRY
AND LV31.GEOGRAPHY_TYPE = SYS.GLOBAL_ATTRIBUTE9
AND EX.SHIP_TO_SEGMENT_ID||'' = LV3.GEOGRAPHY_ID
AND M3.MESSAGE_ID = ST3.STANDARD_TEXT_ID
AND NVL(M3.ORG_ID
, -99) = NVL(R3.ORG_ID
, -99)
AND NVL(M3.ORG_ID
, -99) = NVL(EX.ORG_ID
, -99)
AND NVL(M3.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99) UNION SELECT M4.ROWID ROW_ID
, R4.RULE_ID RULE_ID
, XI.EXC_ITM_ID RULE_DATA_ID
, M4.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M4.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) EXCEPTION_DESC
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_CODE
, TO_CHAR(NULL) FISCAL_CLASSIFICATION_DESC
, XI.SHIP_FROM_CODE SHIP_FROM_CODE
, LV41.GEOGRAPHY_NAME SHIP_FROM_DESC
, XI.SHIP_TO_SEGMENT_ID SHIP_TO_SEGMENT_ID
, LV4.GEOGRAPHY_CODE SHIP_TO_CODE
, LV4.GEOGRAPHY_NAME 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.ORG_ID ORG_ID
, 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_ALL R4
, JL_ZZ_AR_TX_LGL_MSG M4
, JL_ZZ_AR_TX_EXC_ITM_ALL XI
, MTL_SYSTEM_ITEMS_B IT
, HZ_GEOGRAPHIES LV41
, HZ_GEOGRAPHIES LV4
, AR_SYSTEM_PARAMETERS_ALL SYS
, AR_STANDARD_TEXT_VL ST4
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_FROM_CODE = LV41.GEOGRAPHY_CODE
AND LV41.COUNTRY_CODE = SYS.DEFAULT_COUNTRY
AND LV41.GEOGRAPHY_TYPE = SYS.GLOBAL_ATTRIBUTE9
AND XI.SHIP_TO_SEGMENT_ID = LV4.GEOGRAPHY_ID
AND M4.MESSAGE_ID = ST4.STANDARD_TEXT_ID
AND NVL(M4.ORG_ID
, -99) = NVL(R4.ORG_ID
, -99)
AND NVL(M4.ORG_ID
, -99) = NVL(XI.ORG_ID
, -99)
AND NVL(M4.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99) UNION SELECT M5.ROWID ROW_ID
, R5.RULE_ID RULE_ID
, M5.RULE_DATA_ID RULE_DATA_ID
, M5.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M5.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) 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.ORG_ID ORG_ID
, 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_ALL R5
, JL_ZZ_AR_TX_LGL_MSG M5
, HZ_PARTIES C
, HZ_CUST_ACCOUNTS CUSTACCT
, AR_STANDARD_TEXT_VL ST5
WHERE R5.RULE_ID = M5.RULE_ID
AND R5.RULE = 'GET_CUSTOMER_TX_CODE'
AND M5.RULE_DATA_ID = CUSTACCT.CUST_ACCOUNT_ID
AND C.PARTY_ID=CUSTACCT.PARTY_ID
AND M5.MESSAGE_ID = ST5.STANDARD_TEXT_ID
AND NVL(M5.ORG_ID
, -99) = NVL(R5.ORG_ID
, -99)
AND NVL(M5.ORG_ID
, -99) = NVL(CUSTACCT.ORG_ID
, -99) UNION SELECT M6.ROWID ROW_ID
, R6.RULE_ID RULE_ID
, M6.RULE_DATA_ID RULE_DATA_ID
, M6.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M6.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) 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.ORG_ID ORG_ID
, 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_ALL R6
, JL_ZZ_AR_TX_LGL_MSG M6
, MTL_SYSTEM_ITEMS_B MT
, AR_STANDARD_TEXT_VL ST6
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 NVL(M6.ORG_ID
, -99) = NVL(R6.ORG_ID
, -99) UNION SELECT M7.ROWID ROW_ID
, R7.RULE_ID RULE_ID
, M7.RULE_DATA_ID RULE_DATA_ID
, M7.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M7.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) 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.ORG_ID ORG_ID
, M7.LAST_UPDATED_BY LAST_UPDATED_BY
, M7.LAST_UPDATE_DATE LAST_UPDATE_DATE
, M7.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, M7.CREATION_DATE CREATION_DATE
, M7.CREATED_BY CREATED_BY
FROM JL_ZZ_AR_TX_RULES_ALL R7
, JL_ZZ_AR_TX_LGL_MSG M7
, HR_ORGANIZATION_UNITS OG
, AR_STANDARD_TEXT_VL ST7
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 NVL(M7.ORG_ID
, -99) = NVL(R7.ORG_ID
, -99) UNION SELECT M8.ROWID ROW_ID
, R8.RULE_ID RULE_ID
, M8.RULE_DATA_ID RULE_DATA_ID
, M8.EXCEPTION_CODE EXCEPTION_CODE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(M8.EXCEPTION_CODE
, 'JLZZ_AR_TX_LEGAL_MESSAGE')
, 1
, 80) 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.ORG_ID ORG_ID
, 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_ALL R8
, JL_ZZ_AR_TX_LGL_MSG M8
, AR_MEMO_LINES ML
, AR_STANDARD_TEXT_VL ST8
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 NVL(M8.ORG_ID
, -99) = NVL(R8.ORG_ID
, -99)
AND NVL(M8.ORG_ID
, -99) = NVL(ML.ORG_ID
, -99)