SELECT NULL null_cbp,
RALU10.MEANING PRIMARY_cbp,
RALU1.MEANING USAGE_cbp,
RALU8.MEANING STATUS_cbp,
RASU1.LOCATION LOCATION_cbp,
RASU2.LOCATION BILL_TO_LOCATION_cbp,
ARCPC.NAME CREDIT_PROFILE_CLASS_cbp,
RASU1.SIC_CODE SIC_CODE_cbp,
RAT.NAME PAYMENT_TERM_cbp,
RALU4.MEANING GSA_INDICATOR_cbp,
RALU5.MEANING SHIP_PARTIAL_cbp,
RALU6.MEANING SHIP_VIA_cbp,
RALU7.MEANING FOB_POINT_cbp,
SOOT.NAME ORDER_TYPE_cbp,
RASU1.FREIGHT_TERM FREIGHT_TERM_cbp,
SOPL.NAME PRICE_LIST_cbp,
RASU1.CUST_ACCT_SITE_ID ADDRESS_ID_cbp,
DECODE(RASU1.CONTACT_ID, NULL, NULL,
SUBSTRB(PARTY.PERSON_LAST_NAME,1,50)||', '|| SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) ) CONTACT_cbp,
RASU1.TAX_REFERENCE TAX_REFERENCE_cbp ,
RASU1.TAX_CODE TAX_CODE_cbp,
ARVT.TAX_RATE TAX_RATE_cbp,
SOLU1.MEANING FREIGHT_TERMS_cbp,
FCLU.MEANING DEMAND_CLASS_cbp,
RASU1.SITE_USE_ID SITE_USE_ID_cbp,
null TERRITORY_cbp,
ORGD.ORGANIZATION_NAME WAREHOUSE_cbp,
ORGF.DESCRIPTION CARRIER_cbp,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_territory', 'AR', 'CT#', 101, NULL, RATT.TERRITORY_ID, 'ALL', 'Y', 'VALUE') D_TERRITORY
FROM HZ_CUST_SITE_USES RASU1,
HZ_CUST_SITE_USES RASU2,
RA_TERMS RAT,
SO_ORDER_TYPES SOOT,
SO_PRICE_LISTS SOPL,
AR_VAT_TAX ARVT,
AR_LOOKUPS RALU1,
AR_LOOKUPS RALU4,
AR_LOOKUPS RALU5,
AR_LOOKUPS RALU6,
AR_LOOKUPS RALU7,
AR_LOOKUPS RALU8,
AR_LOOKUPS RALU10,
SO_LOOKUPS SOLU1,
HZ_CUSTOMER_PROFILES ARCP,
HZ_CUST_PROFILE_CLASSES ARCPC,
HZ_CUST_ACCOUNT_ROLES RACO,
HZ_RELATIONSHIPS REL,
HZ_PARTIES PARTY,
FND_COMMON_LOOKUPS FCLU,
ORG_ORGANIZATION_DEFINITIONS ORGD,
ORG_FREIGHT ORGF,
RA_TERRITORIES RATT
WHERE
RALU1.LOOKUP_CODE = RASU1.SITE_USE_CODE
AND RALU1.LOOKUP_TYPE = 'SITE_USE_CODE'
AND RALU10.LOOKUP_CODE = RASU1.PRIMARY_FLAG
AND RALU10.LOOKUP_TYPE = 'YES/NO'
AND RALU4.LOOKUP_CODE(+) = RASU1.GSA_INDICATOR
AND RALU4.LOOKUP_TYPE(+) = 'YES/NO'
AND RALU5.LOOKUP_CODE(+) = RASU1.SHIP_PARTIAL
AND RALU5.LOOKUP_TYPE(+) = 'YES/NO'
AND RALU6.LOOKUP_CODE(+) = RASU1.SHIP_VIA
AND RALU6.LOOKUP_TYPE(+) = 'SHIP_VIA'
AND RALU7.LOOKUP_CODE(+) = RASU1.FOB_POINT
AND RALU7.LOOKUP_TYPE(+) = 'FOB'
AND RALU8.LOOKUP_CODE = RASU1.STATUS
AND RALU8.LOOKUP_TYPE = 'CODE_STATUS'
AND RASU2.SITE_USE_ID(+) = RASU1.BILL_TO_SITE_USE_ID
AND RAT.TERM_ID(+) = RASU1.PAYMENT_TERM_ID
AND SOOT.ORDER_TYPE_ID(+) = RASU1.ORDER_TYPE_ID
AND SOPL.PRICE_LIST_ID(+) = RASU1.PRICE_LIST_ID
AND ARCP.SITE_USE_ID(+) = RASU1.SITE_USE_ID
AND ARCPC.PROFILE_CLASS_ID(+) = ARCP.PROFILE_CLASS_ID
AND
RACO.CUST_ACCOUNT_ROLE_ID (+) = RASU1.CONTACT_ID
AND RACO.PARTY_ID = REL.PARTY_ID(+)
AND RACO.ROLE_TYPE(+) = 'CONTACT'
AND REL.SUBJECT_ID = PARTY.PARTY_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND ARVT.TAX_CODE(+) = RASU1.TAX_CODE
AND TRUNC(SYSDATE) BETWEEN NVL(ARVT.START_DATE,
TRUNC(SYSDATE)) AND
NVL(ARVT.END_DATE,
TRUNC(SYSDATE))
AND SOLU1.LOOKUP_CODE(+) = RASU1.FREIGHT_TERM
AND SOLU1.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND FCLU.LOOKUP_CODE(+) = RASU1.DEMAND_CLASS_CODE
AND FCLU.LOOKUP_TYPE(+) = 'DEMAND_CLASS'
AND ORGD.ORGANIZATION_ID(+) = RASU1.WAREHOUSE_ID
AND ORGF.FREIGHT_CODE(+) = RASU1.SHIP_VIA
AND ORGF.ORGANIZATION_ID(+) = :ITEM_FLEX_STRUCTURE
AND RATT.TERRITORY_ID(+) = RASU1.TERRITORY_ID
and RASU1.CUST_ACCT_SITE_ID=:ADDRESS_ID_ca
ORDER BY
RALU10.MEANING,
RALU1.MEANING,
RALU8.MEANING,
RASU1.LOCATION |
