VIEW: APPS.RLM_SHIPTO_ITEM_TERMS_V

Source

View Text - Preformatted

SELECT PARTY.PARTY_NAME customer_name, CUST_ACCT.ACCOUNT_NUMBER customer_number, ACCT_SITE.ECE_TP_LOCATION_CODE ECE_LOCATION_CODE, loc.CITY, loc.postal_code, loc.province, loc.county, loc.ADDRESS1 || loc.ADDRESS2 || loc.ADDRESS3 || loc.ADDRESS4 address, mci.customer_item_number, mci.customer_item_desc, mcc.commodity_code, terms.customer_id, terms.CUST_ITEM_TERMS_ID, terms.CUSTOMER_ITEM_ID, terms.SHIP_FROM_ORG_ID, terms.address_id, terms.HEADER_ID, terms.AGREEMENT_ID, terms.AGREEMENT_NAME, terms.FUTURE_AGREEMENT_ID, terms.FUTURE_AGREEMENT_NAME, terms.CALC_CUM_FLAG, terms.CUM_CURRENT_START_DATE, terms.CUM_PREVIOUS_START_DATE, terms.CUST_ITEM_STATUS_CODE, terms.ROUND_TO_STD_PACK_FLAG, terms.SHIP_DELIVERY_RULE_NAME, terms.SHIP_METHOD, terms.INTRANSIT_TIME, terms.TIME_UOM_CODE, terms.STD_PACK_QTY, terms.PRICE_LIST_ID, terms.DEMAND_TOLERANCE_ABOVE, terms.USE_EDI_SDP_CODE_FLAG, terms.LAST_UPDATE_DATE, terms.LAST_UPDATED_BY, terms.CREATION_DATE, terms.CREATED_BY, terms.PLN_FIRM_DAY_TO, terms.ATTRIBUTE_CATEGORY, terms.PLN_FORECAST_DAY_FROM, terms.PLN_FORECAST_DAY_TO, terms.PLN_FROZEN_DAY_TO, terms.PLN_FROZEN_DAY_FROM, terms.ATTRIBUTE1, terms.SEQ_FIRM_DAY_FROM, terms.SEQ_FIRM_DAY_TO, terms.ATTRIBUTE2, terms.SEQ_FORECAST_DAY_TO, terms.SEQ_FORECAST_DAY_FROM, terms.ATTRIBUTE3, terms.SEQ_FROZEN_DAY_FROM, terms.SEQ_FROZEN_DAY_TO, terms.ATTRIBUTE4, terms.SHP_FIRM_DAY_FROM, terms.SHP_FIRM_DAY_TO, terms.ATTRIBUTE5, terms.SHP_FROZEN_DAY_TO, terms.SHP_FROZEN_DAY_FROM, terms.ATTRIBUTE6, terms.SHP_FORECAST_DAY_TO, terms.SHP_FORECAST_DAY_FROM, terms.ATTRIBUTE7, terms.ATTRIBUTE8, terms.ATTRIBUTE9, terms.ATTRIBUTE10, terms.ATTRIBUTE11, terms.ATTRIBUTE12, terms.ATTRIBUTE13, terms.ATTRIBUTE14, terms.ATTRIBUTE15, terms.LAST_UPDATE_LOGIN, terms.REQUEST_ID, terms.PROGRAM_APPLICATION_ID, terms.PROGRAM_ID, terms.PROGRAM_UPDATE_DATE, terms.DEMAND_TOLERANCE_BELOW, terms.CUSTOMER_CONTACT_ID, terms.FREIGHT_CODE, terms.PLN_FIRM_DAY_FROM, terms.SUPPLIER_CONTACT_ID, terms.INACTIVE_DATE, terms.COMMENTS, terms.DEFAULT_SHIP_FROM, 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.TP_ATTRIBUTE_CATEGORY, terms.PLN_MRP_FORECAST_DAY_FROM, terms. PLN_MRP_FORECAST_DAY_TO, terms.SHP_MRP_FORECAST_DAY_FROM, terms.SHP_MRP_FORECAST_DAY_TO, terms.SEQ_MRP_FORECAST_DAY_FROM, terms.SEQ_MRP_FORECAST_DAY_TO, terms.PLN_FROZEN_FLAG, terms.SHP_FROZEN_FLAG, terms.SEQ_FROZEN_FLAG, terms.ORG_ID, terms.BLANKET_NUMBER, terms.RELEASE_RULE, terms.RELEASE_TIME_FRAME, terms.RELEASE_TIME_FRAME_UOM, terms.EXCLUDE_NON_WORKDAYS_FLAG, mif.ITEM_NUMBER, mif.DESCRIPTION FROM mtl_customer_items mci, mtl_commodity_codes mcc, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE , HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, rlm_cust_item_terms terms, mtl_item_flexfields mif, mtl_customer_item_xrefs mcix WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID and LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID And CUST_ACCT.PARTY_ID = PARTY.PARTY_ID and terms.customer_item_id = mci.customer_item_id and terms.address_id is NOT NULL and cust_acct.cust_account_id = terms.customer_id and acct_site.cust_acct_site_id = terms.address_id and mci.commodity_code_id = mcc.commodity_code_id and mif.inventory_item_id = mcix.inventory_item_id and mif.organization_id = mcix.master_organization_id and mcix.customer_item_id = mci.customer_item_id and terms.ORG_ID = acct_site.ORG_ID and mcix.preference_number = ( SELECT MIN(preference_number) FROM mtl_customer_item_xrefs WHERE customer_item_id = mci.customer_item_id AND inactive_flag<>'Y' )

View Text - HTML Formatted

SELECT PARTY.PARTY_NAME CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, ACCT_SITE.ECE_TP_LOCATION_CODE ECE_LOCATION_CODE
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.PROVINCE
, LOC.COUNTY
, LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3 || LOC.ADDRESS4 ADDRESS
, MCI.CUSTOMER_ITEM_NUMBER
, MCI.CUSTOMER_ITEM_DESC
, MCC.COMMODITY_CODE
, TERMS.CUSTOMER_ID
, TERMS.CUST_ITEM_TERMS_ID
, TERMS.CUSTOMER_ITEM_ID
, TERMS.SHIP_FROM_ORG_ID
, TERMS.ADDRESS_ID
, TERMS.HEADER_ID
, TERMS.AGREEMENT_ID
, TERMS.AGREEMENT_NAME
, TERMS.FUTURE_AGREEMENT_ID
, TERMS.FUTURE_AGREEMENT_NAME
, TERMS.CALC_CUM_FLAG
, TERMS.CUM_CURRENT_START_DATE
, TERMS.CUM_PREVIOUS_START_DATE
, TERMS.CUST_ITEM_STATUS_CODE
, TERMS.ROUND_TO_STD_PACK_FLAG
, TERMS.SHIP_DELIVERY_RULE_NAME
, TERMS.SHIP_METHOD
, TERMS.INTRANSIT_TIME
, TERMS.TIME_UOM_CODE
, TERMS.STD_PACK_QTY
, TERMS.PRICE_LIST_ID
, TERMS.DEMAND_TOLERANCE_ABOVE
, TERMS.USE_EDI_SDP_CODE_FLAG
, TERMS.LAST_UPDATE_DATE
, TERMS.LAST_UPDATED_BY
, TERMS.CREATION_DATE
, TERMS.CREATED_BY
, TERMS.PLN_FIRM_DAY_TO
, TERMS.ATTRIBUTE_CATEGORY
, TERMS.PLN_FORECAST_DAY_FROM
, TERMS.PLN_FORECAST_DAY_TO
, TERMS.PLN_FROZEN_DAY_TO
, TERMS.PLN_FROZEN_DAY_FROM
, TERMS.ATTRIBUTE1
, TERMS.SEQ_FIRM_DAY_FROM
, TERMS.SEQ_FIRM_DAY_TO
, TERMS.ATTRIBUTE2
, TERMS.SEQ_FORECAST_DAY_TO
, TERMS.SEQ_FORECAST_DAY_FROM
, TERMS.ATTRIBUTE3
, TERMS.SEQ_FROZEN_DAY_FROM
, TERMS.SEQ_FROZEN_DAY_TO
, TERMS.ATTRIBUTE4
, TERMS.SHP_FIRM_DAY_FROM
, TERMS.SHP_FIRM_DAY_TO
, TERMS.ATTRIBUTE5
, TERMS.SHP_FROZEN_DAY_TO
, TERMS.SHP_FROZEN_DAY_FROM
, TERMS.ATTRIBUTE6
, TERMS.SHP_FORECAST_DAY_TO
, TERMS.SHP_FORECAST_DAY_FROM
, TERMS.ATTRIBUTE7
, TERMS.ATTRIBUTE8
, TERMS.ATTRIBUTE9
, TERMS.ATTRIBUTE10
, TERMS.ATTRIBUTE11
, TERMS.ATTRIBUTE12
, TERMS.ATTRIBUTE13
, TERMS.ATTRIBUTE14
, TERMS.ATTRIBUTE15
, TERMS.LAST_UPDATE_LOGIN
, TERMS.REQUEST_ID
, TERMS.PROGRAM_APPLICATION_ID
, TERMS.PROGRAM_ID
, TERMS.PROGRAM_UPDATE_DATE
, TERMS.DEMAND_TOLERANCE_BELOW
, TERMS.CUSTOMER_CONTACT_ID
, TERMS.FREIGHT_CODE
, TERMS.PLN_FIRM_DAY_FROM
, TERMS.SUPPLIER_CONTACT_ID
, TERMS.INACTIVE_DATE
, TERMS.COMMENTS
, TERMS.DEFAULT_SHIP_FROM
, 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.TP_ATTRIBUTE_CATEGORY
, TERMS.PLN_MRP_FORECAST_DAY_FROM
, TERMS. PLN_MRP_FORECAST_DAY_TO
, TERMS.SHP_MRP_FORECAST_DAY_FROM
, TERMS.SHP_MRP_FORECAST_DAY_TO
, TERMS.SEQ_MRP_FORECAST_DAY_FROM
, TERMS.SEQ_MRP_FORECAST_DAY_TO
, TERMS.PLN_FROZEN_FLAG
, TERMS.SHP_FROZEN_FLAG
, TERMS.SEQ_FROZEN_FLAG
, TERMS.ORG_ID
, TERMS.BLANKET_NUMBER
, TERMS.RELEASE_RULE
, TERMS.RELEASE_TIME_FRAME
, TERMS.RELEASE_TIME_FRAME_UOM
, TERMS.EXCLUDE_NON_WORKDAYS_FLAG
, MIF.ITEM_NUMBER
, MIF.DESCRIPTION
FROM MTL_CUSTOMER_ITEMS MCI
, MTL_COMMODITY_CODES MCC
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, RLM_CUST_ITEM_TERMS TERMS
, MTL_ITEM_FLEXFIELDS MIF
, MTL_CUSTOMER_ITEM_XREFS MCIX
WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND TERMS.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID
AND TERMS.ADDRESS_ID IS NOT NULL
AND CUST_ACCT.CUST_ACCOUNT_ID = TERMS.CUSTOMER_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = TERMS.ADDRESS_ID
AND MCI.COMMODITY_CODE_ID = MCC.COMMODITY_CODE_ID
AND MIF.INVENTORY_ITEM_ID = MCIX.INVENTORY_ITEM_ID
AND MIF.ORGANIZATION_ID = MCIX.MASTER_ORGANIZATION_ID
AND MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID
AND TERMS.ORG_ID = ACCT_SITE.ORG_ID
AND MCIX.PREFERENCE_NUMBER = ( SELECT MIN(PREFERENCE_NUMBER)
FROM MTL_CUSTOMER_ITEM_XREFS
WHERE CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID
AND INACTIVE_FLAG<>'Y' )