VIEW: APPS.OE_PRICING_CONTRACTS_V

Source

View Text - Preformatted

SELECT SA.NAME, SA.AGREEMENT_NUM , SA.REVISION , SA.REVISION_REASON_CODE , SA.REVISION_DATE , RC.PARTY_NAME , RC.ACCOUNT_NUMBER , SL.MEANING , DECODE(RA.FIRST_NAME, '', RA.LAST_NAME, RA.LAST_NAME||','||RA.FIRST_NAME) , SA.START_DATE_ACTIVE , SA.END_DATE_ACTIVE , PURCHASE_ORDER_NUM , QP_UTIL.GET_SALESREP(SA.SALESREP_ID) , SA.SIGNATURE_DATE , RT.NAME , SP.LIST_TYPE_CODE, SP.NAME , C.PARTY_NAME , D.ADDRESS1 , D.ADDRESS2 , DECODE(D.STATE||D.POSTAL_CODE, '', D.CITY, D.CITY||', ' || D.STATE || ' '|| D.POSTAL_CODE) , DECODE(RC2.FIRST_NAME, '',RC2.LAST_NAME,RC2.LAST_NAME||', '||RC2.FIRST_NAME) , RR.NAME , RR2.NAME , SA.OVERRIDE_ARULE_FLAG , SA.OVERRIDE_IRULE_FLAG , SA.AGREEMENT_ID , SA.CREATION_DATE , SA.CREATED_BY , SA.LAST_UPDATE_DATE , SA.LAST_UPDATED_BY , SA.LAST_UPDATE_LOGIN , SA.AGREEMENT_TYPE_CODE , SA.PRICE_LIST_ID , SA.TERM_ID , SA.INVOICING_RULE_ID , SA.ACCOUNTING_RULE_ID , SA.SOLD_TO_ORG_ID , SA.INVOICE_CONTACT_ID , SA.AGREEMENT_CONTACT_ID , SA.INVOICE_TO_ORG_ID , C.CUST_ACCOUNT_ID , C.CUST_ACCOUNT_ID , RC.CUST_ACCOUNT_ID , D.CUST_ACCT_SITE_ID , SA.SALESREP_ID , SP.CURRENCY_CODE , SP.ROUNDING_FACTOR , SP.DESCRIPTION , SA.SHIP_METHOD_CODE , SA.FREIGHT_TERMS_CODE , FT.FREIGHT_TERMS , SP.COMMENTS , SP.CURRENCY_HEADER_ID, SA.CONTEXT , SA.ATTRIBUTE1 , SA.ATTRIBUTE2 , SA.ATTRIBUTE3 , SA.ATTRIBUTE4 , SA.ATTRIBUTE5 , SA.ATTRIBUTE6 , SA.ATTRIBUTE7 , SA.ATTRIBUTE8 , SA.ATTRIBUTE9 , SA.ATTRIBUTE10 , SA.ATTRIBUTE11 , SA.ATTRIBUTE12 , SA.ATTRIBUTE13 , SA.ATTRIBUTE14 , SA.ATTRIBUTE15, SA.AGREEMENT_SOURCE_CODE, SA.ORIG_SYSTEM_AGR_ID, SA.DESCRIPTION ORIG_SYSTEM_AGR_DESC FROM OE_AGREEMENTS_VL SA, SO_FREIGHT_TERMS_ACTIVE_V FT, ( SELECT substrb(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME , substrb(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) RA , RA_RULES RR, ( SELECT PARTY.PARTY_ID PARTY_ID, PARTY.PARTY_NAME , CUST_ACCT.CUST_ACCOUNT_ID , CUST_ACCT.ACCOUNT_NUMBER FROM HZ_PARTIES PARTY , HZ_CUST_ACCOUNTS CUST_ACCT WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID ) RC , RA_RULES RR2, QP_LIST_HEADERS_ALL SP, RA_TERMS RT, QP_LOOKUPS SL, ( SELECT substrb(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME , substrb(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) RC2 , hz_cust_site_uses A , ( SELECT PARTY.PARTY_ID PARTY_ID, PARTY.PARTY_NAME , CUST_ACCT.CUST_ACCOUNT_ID , CUST_ACCT.ACCOUNT_NUMBER FROM HZ_PARTIES PARTY , HZ_CUST_ACCOUNTS CUST_ACCT WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) C , ( select LOC.ADDRESS1 ADDRESS1, LOC.ADDRESS2, LOC.STATE , LOC.POSTAL_CODE ,LOC.CITY , D.PARTY_SITE_ID , D.CUST_ACCT_SITE_ID, D.CUST_ACCOUNT_ID FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL D WHERE D.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID and exists (select 'x' FROM hz_locations hzloc , hz_cust_acct_sites_all acct_site , hz_party_sites p_site , hz_geo_struct_map map , hz_geo_name_reference_log log WHERE acct_site.party_site_id = p_site.party_site_id AND p_site.location_id = hzloc.location_id AND hzloc.location_id = log.location_id AND log.location_table_name = 'HZ_LOCATIONS' AND log.usage_code = 'TAX' AND log.map_status = 'S' AND hzloc.country = map.country_code AND map.loc_tbl_name = 'HZ_LOCATIONS' and LOC.LOCATION_ID = hzloc.LOCATION_ID UNION all SELECT 'x' FROM hz_locations hzloc , hz_cust_acct_sites_all acct_site , hz_party_sites p_site , hz_geographies g WHERE acct_site.party_site_id = p_site.party_site_id AND p_site.location_id = hzloc.location_id AND hzloc.country = g.country_code AND g.geography_type = 'COUNTRY' AND NOT EXISTS ( SELECT '1' FROM hz_geo_struct_map map WHERE map.country_code = hzloc.country AND map.loc_tbl_name = 'HZ_LOCATIONS' ) and LOC.LOCATION_ID = hzloc.LOCATION_ID ) AND NVL( D.ORG_ID, -99 ) = NVL ( D.ORG_ID, -99 ) ) D WHERE RA.CUST_ACCOUNT_ROLE_ID(+) = SA.AGREEMENT_CONTACT_ID AND RR.RULE_ID(+) = SA.ACCOUNTING_RULE_ID AND RC.CUST_ACCOUNT_ID(+) = SA.SOLD_TO_ORG_ID AND RR2.RULE_ID(+) = SA.INVOICING_RULE_ID AND SP.LIST_HEADER_ID(+) = SA.PRICE_LIST_ID AND RT.TERM_ID(+) = SA.TERM_ID AND SL.LOOKUP_TYPE(+) = 'QP_AGREEMENT_TYPE' AND SL.LOOKUP_CODE(+) = SA.AGREEMENT_TYPE_CODE AND RC2.CUST_ACCOUNT_ROLE_ID(+) = SA.INVOICE_CONTACT_ID AND C.CUST_ACCOUNT_ID(+) = D.CUST_ACCOUNT_ID AND D.CUST_ACCT_SITE_ID(+) = A.CUST_ACCT_SITE_ID AND A.SITE_USE_ID(+) = SA.INVOICE_TO_ORG_ID AND FT.FREIGHT_TERMS_CODE (+) = SA.FREIGHT_TERMS_CODE

View Text - HTML Formatted

SELECT SA.NAME
, SA.AGREEMENT_NUM
, SA.REVISION
, SA.REVISION_REASON_CODE
, SA.REVISION_DATE
, RC.PARTY_NAME
, RC.ACCOUNT_NUMBER
, SL.MEANING
, DECODE(RA.FIRST_NAME
, ''
, RA.LAST_NAME
, RA.LAST_NAME||'
, '||RA.FIRST_NAME)
, SA.START_DATE_ACTIVE
, SA.END_DATE_ACTIVE
, PURCHASE_ORDER_NUM
, QP_UTIL.GET_SALESREP(SA.SALESREP_ID)
, SA.SIGNATURE_DATE
, RT.NAME
, SP.LIST_TYPE_CODE
, SP.NAME
, C.PARTY_NAME
, D.ADDRESS1
, D.ADDRESS2
, DECODE(D.STATE||D.POSTAL_CODE
, ''
, D.CITY
, D.CITY||'
, ' || D.STATE || ' '|| D.POSTAL_CODE)
, DECODE(RC2.FIRST_NAME
, ''
, RC2.LAST_NAME
, RC2.LAST_NAME||'
, '||RC2.FIRST_NAME)
, RR.NAME
, RR2.NAME
, SA.OVERRIDE_ARULE_FLAG
, SA.OVERRIDE_IRULE_FLAG
, SA.AGREEMENT_ID
, SA.CREATION_DATE
, SA.CREATED_BY
, SA.LAST_UPDATE_DATE
, SA.LAST_UPDATED_BY
, SA.LAST_UPDATE_LOGIN
, SA.AGREEMENT_TYPE_CODE
, SA.PRICE_LIST_ID
, SA.TERM_ID
, SA.INVOICING_RULE_ID
, SA.ACCOUNTING_RULE_ID
, SA.SOLD_TO_ORG_ID
, SA.INVOICE_CONTACT_ID
, SA.AGREEMENT_CONTACT_ID
, SA.INVOICE_TO_ORG_ID
, C.CUST_ACCOUNT_ID
, C.CUST_ACCOUNT_ID
, RC.CUST_ACCOUNT_ID
, D.CUST_ACCT_SITE_ID
, SA.SALESREP_ID
, SP.CURRENCY_CODE
, SP.ROUNDING_FACTOR
, SP.DESCRIPTION
, SA.SHIP_METHOD_CODE
, SA.FREIGHT_TERMS_CODE
, FT.FREIGHT_TERMS
, SP.COMMENTS
, SP.CURRENCY_HEADER_ID
, SA.CONTEXT
, SA.ATTRIBUTE1
, SA.ATTRIBUTE2
, SA.ATTRIBUTE3
, SA.ATTRIBUTE4
, SA.ATTRIBUTE5
, SA.ATTRIBUTE6
, SA.ATTRIBUTE7
, SA.ATTRIBUTE8
, SA.ATTRIBUTE9
, SA.ATTRIBUTE10
, SA.ATTRIBUTE11
, SA.ATTRIBUTE12
, SA.ATTRIBUTE13
, SA.ATTRIBUTE14
, SA.ATTRIBUTE15
, SA.AGREEMENT_SOURCE_CODE
, SA.ORIG_SYSTEM_AGR_ID
, SA.DESCRIPTION ORIG_SYSTEM_AGR_DESC
FROM OE_AGREEMENTS_VL SA
, SO_FREIGHT_TERMS_ACTIVE_V FT
, ( SELECT SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) RA
, RA_RULES RR
, ( SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME
, CUST_ACCT.CUST_ACCOUNT_ID
, CUST_ACCT.ACCOUNT_NUMBER
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID ) RC
, RA_RULES RR2
, QP_LIST_HEADERS_ALL SP
, RA_TERMS RT
, QP_LOOKUPS SL
, ( SELECT SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) RC2
, HZ_CUST_SITE_USES A
, ( SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME
, CUST_ACCT.CUST_ACCOUNT_ID
, CUST_ACCT.ACCOUNT_NUMBER
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) C
, ( SELECT LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2
, LOC.STATE
, LOC.POSTAL_CODE
, LOC.CITY
, D.PARTY_SITE_ID
, D.CUST_ACCT_SITE_ID
, D.CUST_ACCOUNT_ID
FROM HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL D
WHERE D.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND EXISTS (SELECT 'X'
FROM HZ_LOCATIONS HZLOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES P_SITE
, HZ_GEO_STRUCT_MAP MAP
, HZ_GEO_NAME_REFERENCE_LOG LOG
WHERE ACCT_SITE.PARTY_SITE_ID = P_SITE.PARTY_SITE_ID
AND P_SITE.LOCATION_ID = HZLOC.LOCATION_ID
AND HZLOC.LOCATION_ID = LOG.LOCATION_ID
AND LOG.LOCATION_TABLE_NAME = 'HZ_LOCATIONS'
AND LOG.USAGE_CODE = 'TAX'
AND LOG.MAP_STATUS = 'S'
AND HZLOC.COUNTRY = MAP.COUNTRY_CODE
AND MAP.LOC_TBL_NAME = 'HZ_LOCATIONS'
AND LOC.LOCATION_ID = HZLOC.LOCATION_ID UNION ALL SELECT 'X'
FROM HZ_LOCATIONS HZLOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES P_SITE
, HZ_GEOGRAPHIES G
WHERE ACCT_SITE.PARTY_SITE_ID = P_SITE.PARTY_SITE_ID
AND P_SITE.LOCATION_ID = HZLOC.LOCATION_ID
AND HZLOC.COUNTRY = G.COUNTRY_CODE
AND G.GEOGRAPHY_TYPE = 'COUNTRY'
AND NOT EXISTS ( SELECT '1'
FROM HZ_GEO_STRUCT_MAP MAP
WHERE MAP.COUNTRY_CODE = HZLOC.COUNTRY
AND MAP.LOC_TBL_NAME = 'HZ_LOCATIONS' )
AND LOC.LOCATION_ID = HZLOC.LOCATION_ID )
AND NVL( D.ORG_ID
, -99 ) = NVL ( D.ORG_ID
, -99 ) ) D
WHERE RA.CUST_ACCOUNT_ROLE_ID(+) = SA.AGREEMENT_CONTACT_ID
AND RR.RULE_ID(+) = SA.ACCOUNTING_RULE_ID
AND RC.CUST_ACCOUNT_ID(+) = SA.SOLD_TO_ORG_ID
AND RR2.RULE_ID(+) = SA.INVOICING_RULE_ID
AND SP.LIST_HEADER_ID(+) = SA.PRICE_LIST_ID
AND RT.TERM_ID(+) = SA.TERM_ID
AND SL.LOOKUP_TYPE(+) = 'QP_AGREEMENT_TYPE'
AND SL.LOOKUP_CODE(+) = SA.AGREEMENT_TYPE_CODE
AND RC2.CUST_ACCOUNT_ROLE_ID(+) = SA.INVOICE_CONTACT_ID
AND C.CUST_ACCOUNT_ID(+) = D.CUST_ACCOUNT_ID
AND D.CUST_ACCT_SITE_ID(+) = A.CUST_ACCT_SITE_ID
AND A.SITE_USE_ID(+) = SA.INVOICE_TO_ORG_ID
AND FT.FREIGHT_TERMS_CODE (+) = SA.FREIGHT_TERMS_CODE