Product: | ONT - Order Management |
---|---|
Description: | |
Implementation/DBA Data: | APPS.OE_AK_SOLD_TO_ORGS_V |
SELECT CUST_ACCT.CUST_ACCOUNT_ID
, CUST_ACCT.PRICE_LIST_ID
, CUST_ACCT.FOB_POINT
, CUST_ACCT.FREIGHT_TERM
, CUST_ACCT.SHIP_PARTIAL
, CUST_ACCT.SHIP_VIA
, INV_TO.SITE_USE_ID
, SHIP_TO.SITE_USE_ID
, NULL
, CUST_PROF.STANDARD_TERMS
, CUST_ACCT.ORDER_TYPE_ID
, CUST_ACCT.PRIMARY_SALESREP_ID
, CUST_ACCT.SALES_CHANNEL_CODE
, TO_NUMBER(TO_CHAR(CUST_ACCT.WAREHOUSE_ID))
, CUST_ACCT.OVER_SHIPMENT_TOLERANCE
, CUST_ACCT.UNDER_SHIPMENT_TOLERANCE
, CUST_ACCT.OVER_RETURN_TOLERANCE
, CUST_ACCT.UNDER_RETURN_TOLERANCE
, CUST_ACCT.ITEM_CROSS_REF_PREF
, CUST_ACCT.DATE_TYPE_PREFERENCE
, CUST_ACCT.DATES_NEGATIVE_TOLERANCE
, CUST_ACCT.DATES_POSITIVE_TOLERANCE
, DECODE (NVL(CUST_ACCT.SHIP_SETS_INCLUDE_LINES_FLAG
, 'N')
, 'Y'
, 'SHIP'
, DECODE(NVL(CUST_ACCT.ARRIVALSETS_INCLUDE_LINES_FLAG
, 'N')
, 'Y'
, 'ARRIVAL'
, NULL)) DEFAULT_LINE_SET
, NVL(SHIP_ADDR.ORG_ID
, INV_ADDR.ORG_ID)
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_SITE_USES_ALL INV_TO
, HZ_CUST_ACCT_SITES INV_ADDR
, HZ_CUST_SITE_USES_ALL SHIP_TO
, HZ_CUST_ACCT_SITES SHIP_ADDR
, HZ_CUSTOMER_PROFILES CUST_PROF
WHERE CUST_ACCT.CUST_ACCOUNT_ID = INV_ADDR.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND INV_ADDR.BILL_TO_FLAG(+) = 'P'
AND INV_ADDR.STATUS(+) = 'A'
AND INV_ADDR.CUST_ACCT_SITE_ID = INV_TO.CUST_ACCT_SITE_ID(+)
AND INV_TO.SITE_USE_CODE(+) = 'BILL_TO'
AND INV_TO.PRIMARY_FLAG(+) = 'Y'
AND INV_TO.STATUS(+) = 'A'
AND CUST_ACCT.CUST_ACCOUNT_ID = SHIP_ADDR.CUST_ACCOUNT_ID(+)
AND INV_ADDR.ORG_ID = INV_TO.ORG_ID(+)
AND SHIP_ADDR.SHIP_TO_FLAG(+) = 'P'
AND SHIP_ADDR.STATUS(+) = 'A'
AND SHIP_ADDR.CUST_ACCT_SITE_ID = SHIP_TO.CUST_ACCT_SITE_ID(+)
AND SHIP_ADDR.ORG_ID = SHIP_TO.ORG_ID(+)
AND SHIP_TO.SITE_USE_CODE(+) = 'SHIP_TO'
AND SHIP_TO.PRIMARY_FLAG(+) = 'Y'
AND SHIP_TO.STATUS(+) = 'A'
AND CUST_ACCT.CUST_ACCOUNT_ID = CUST_PROF.CUST_ACCOUNT_ID(+)
AND CUST_PROF.SITE_USE_ID(+) IS NULL