VIEW: APPS.RLM_CUM_SHIPFR_SHIPTO_V

Source

View Text - Preformatted

SELECT terms.rowid row_id, cust_site.CUST_ACCT_SITE_ID , cust_site.location, terms.customer_id, cust_acct.cust_account_id, PARTY.PARTY_NAME, CUST_ACCT.ACCOUNT_NUMBER, acct_site.ece_tp_location_code, ACCT_SITE.CUSTOMER_CATEGORY_CODE, org.organization_code , cust_site.site_use_id, cust_site.site_use_code, terms.ship_from_org_id, terms.cum_control_code, terms.cum_control_code cum_control_code_mn, terms.cum_org_level_code, terms.cum_org_level_code cum_org_level_code_mn, terms.cum_current_start_date, terms.cum_current_record_year, terms.cum_shipment_rule_code, terms.cum_shipment_rule_code cum_shipment_rule_code_mn, terms.cum_yesterd_time_cutoff, terms.LAST_UPDATE_DATE, terms.LAST_UPDATED_BY, terms.CREATION_DATE, terms.CREATED_BY, terms.ATTRIBUTE_CATEGORY, terms.ATTRIBUTE1, terms.ATTRIBUTE2, terms.ATTRIBUTE3, terms.ATTRIBUTE4, terms.ATTRIBUTE5, terms.ATTRIBUTE6, terms.ATTRIBUTE7, terms.ATTRIBUTE8, terms.ATTRIBUTE9, terms.ATTRIBUTE10, terms.ATTRIBUTE11, terms.ATTRIBUTE12, terms.ATTRIBUTE13, terms.ATTRIBUTE14, terms.ATTRIBUTE15, terms.TP_ATTRIBUTE_CATEGORY, terms.TP_ATTRIBUTE1, terms.TP_ATTRIBUTE2, terms.TP_ATTRIBUTE3, terms.TP_ATTRIBUTE4, terms.TP_ATTRIBUTE5, terms.TP_ATTRIBUTE6, terms.TP_ATTRIBUTE7, terms.TP_ATTRIBUTE8, terms.TP_ATTRIBUTE9, terms.TP_ATTRIBUTE10, terms.TP_ATTRIBUTE11, terms.TP_ATTRIBUTE12, terms.TP_ATTRIBUTE13, terms.TP_ATTRIBUTE14, terms.TP_ATTRIBUTE15, terms.LAST_UPDATE_LOGIN, terms.REQUEST_ID, terms.PROGRAM_APPLICATION_ID, terms.PROGRAM_ID, terms.PROGRAM_UPDATE_DATE, terms.ORG_ID FROM rlm_cust_shipto_terms terms, org_organization_definitions org, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_CUST_ACCT_SITES_ALL ACCT_SITE, HZ_CUST_SITE_USES_ALL CUST_SITE WHERE CUST_SITE.site_use_code = 'SHIP_TO' And acct_site.cust_acct_site_id = cust_site.CUST_ACCT_SITE_ID And CUST_ACCT.PARTY_ID = PARTY.PARTY_ID and ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID and ( acct_site.cust_acct_site_id = terms.address_id or ( terms.address_id is NULL and acct_site.cust_account_id in ( Select to_number(terms.customer_id) from dual UNION Select cust_account_id from hz_cust_acct_relate_all where related_cust_account_id = terms.customer_id and org_id = terms.ORG_ID) and NOT EXISTS ( select tt.address_id from rlm_cust_shipto_terms_all tt where tt.customer_id = terms.customer_id and tt.ship_from_org_id = terms.ship_from_org_id and cust_site.CUST_ACCT_SITE_ID = tt.address_id and tt.ORG_ID = terms.ORG_ID ) ) ) AND org.organization_id = terms.ship_from_org_id and terms.cum_control_code <> 'NO_CUM' AND terms.ORG_ID = acct_site.ORG_ID AND terms.ORG_ID = cust_site.ORG_ID

View Text - HTML Formatted

SELECT TERMS.ROWID ROW_ID
, CUST_SITE.CUST_ACCT_SITE_ID
, CUST_SITE.LOCATION
, TERMS.CUSTOMER_ID
, CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, ACCT_SITE.ECE_TP_LOCATION_CODE
, ACCT_SITE.CUSTOMER_CATEGORY_CODE
, ORG.ORGANIZATION_CODE
, CUST_SITE.SITE_USE_ID
, CUST_SITE.SITE_USE_CODE
, TERMS.SHIP_FROM_ORG_ID
, TERMS.CUM_CONTROL_CODE
, TERMS.CUM_CONTROL_CODE CUM_CONTROL_CODE_MN
, TERMS.CUM_ORG_LEVEL_CODE
, TERMS.CUM_ORG_LEVEL_CODE CUM_ORG_LEVEL_CODE_MN
, TERMS.CUM_CURRENT_START_DATE
, TERMS.CUM_CURRENT_RECORD_YEAR
, TERMS.CUM_SHIPMENT_RULE_CODE
, TERMS.CUM_SHIPMENT_RULE_CODE CUM_SHIPMENT_RULE_CODE_MN
, TERMS.CUM_YESTERD_TIME_CUTOFF
, TERMS.LAST_UPDATE_DATE
, TERMS.LAST_UPDATED_BY
, TERMS.CREATION_DATE
, TERMS.CREATED_BY
, TERMS.ATTRIBUTE_CATEGORY
, TERMS.ATTRIBUTE1
, TERMS.ATTRIBUTE2
, TERMS.ATTRIBUTE3
, TERMS.ATTRIBUTE4
, TERMS.ATTRIBUTE5
, TERMS.ATTRIBUTE6
, TERMS.ATTRIBUTE7
, TERMS.ATTRIBUTE8
, TERMS.ATTRIBUTE9
, TERMS.ATTRIBUTE10
, TERMS.ATTRIBUTE11
, TERMS.ATTRIBUTE12
, TERMS.ATTRIBUTE13
, TERMS.ATTRIBUTE14
, TERMS.ATTRIBUTE15
, TERMS.TP_ATTRIBUTE_CATEGORY
, TERMS.TP_ATTRIBUTE1
, TERMS.TP_ATTRIBUTE2
, TERMS.TP_ATTRIBUTE3
, TERMS.TP_ATTRIBUTE4
, TERMS.TP_ATTRIBUTE5
, TERMS.TP_ATTRIBUTE6
, TERMS.TP_ATTRIBUTE7
, TERMS.TP_ATTRIBUTE8
, TERMS.TP_ATTRIBUTE9
, TERMS.TP_ATTRIBUTE10
, TERMS.TP_ATTRIBUTE11
, TERMS.TP_ATTRIBUTE12
, TERMS.TP_ATTRIBUTE13
, TERMS.TP_ATTRIBUTE14
, TERMS.TP_ATTRIBUTE15
, TERMS.LAST_UPDATE_LOGIN
, TERMS.REQUEST_ID
, TERMS.PROGRAM_APPLICATION_ID
, TERMS.PROGRAM_ID
, TERMS.PROGRAM_UPDATE_DATE
, TERMS.ORG_ID
FROM RLM_CUST_SHIPTO_TERMS TERMS
, ORG_ORGANIZATION_DEFINITIONS ORG
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_CUST_SITE_USES_ALL CUST_SITE
WHERE CUST_SITE.SITE_USE_CODE = 'SHIP_TO'
AND ACCT_SITE.CUST_ACCT_SITE_ID = CUST_SITE.CUST_ACCT_SITE_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND ( ACCT_SITE.CUST_ACCT_SITE_ID = TERMS.ADDRESS_ID OR ( TERMS.ADDRESS_ID IS NULL
AND ACCT_SITE.CUST_ACCOUNT_ID IN ( SELECT TO_NUMBER(TERMS.CUSTOMER_ID)
FROM DUAL UNION SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCT_RELATE_ALL
WHERE RELATED_CUST_ACCOUNT_ID = TERMS.CUSTOMER_ID
AND ORG_ID = TERMS.ORG_ID)
AND NOT EXISTS ( SELECT TT.ADDRESS_ID
FROM RLM_CUST_SHIPTO_TERMS_ALL TT
WHERE TT.CUSTOMER_ID = TERMS.CUSTOMER_ID
AND TT.SHIP_FROM_ORG_ID = TERMS.SHIP_FROM_ORG_ID
AND CUST_SITE.CUST_ACCT_SITE_ID = TT.ADDRESS_ID
AND TT.ORG_ID = TERMS.ORG_ID ) ) )
AND ORG.ORGANIZATION_ID = TERMS.SHIP_FROM_ORG_ID
AND TERMS.CUM_CONTROL_CODE <> 'NO_CUM'
AND TERMS.ORG_ID = ACCT_SITE.ORG_ID
AND TERMS.ORG_ID = CUST_SITE.ORG_ID