View: OEFV_CUSTOMER_AGREEMENTS

Product:	ONT - Order Management
Description:
Implementation/DBA Data:	APPS.OEFV_CUSTOMER_AGREEMENTS

View Text

SELECT AGREEMENT.AGREEMENT_NUM
, T.NAME
, AGREEMENT.REVISION
, '_LA:AGREEMENT_TYPE_CODE:QP_LOOKUPS:QP_AGREEMENT_TYPE:MEANING'
, '_LA:OVERRIDE_ARULE_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, '_LA:OVERRIDE_IRULE_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, AGREEMENT.START_DATE_ACTIVE
, AGREEMENT.END_DATE_ACTIVE
, AGREEMENT.SIGNATURE_DATE
, AGREEMENT.PURCHASE_ORDER_NUM
, '_DF:QP:OE_AGREEMENTS:AGREEMENT'
, PARTY.PARTY_NAME CUSTOMER_NAME
, SUBSTRB(INVCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(INVCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(INVCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(INV_ARL.MEANING
, NULL
, NULL
, '
, '|| INV_ARL.MEANING ) INVOICE_CONTACT_NAME
, INVCONT2.PERSON_FIRST_NAME INVOICE_CONTACT_FIRST_NAME
, INVCONT2.PERSON_LAST_NAME INVOICE_CONTACT_LAST_NAME
, SUBSTRB(AGREECONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(AGREECONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(AGREECONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(AGREE_ARL.MEANING
, NULL
, NULL
, '
, '|| AGREE_ARL.MEANING ) AGREEMENT_CONTACT_NAME
, AGREECONT2.PERSON_FIRST_NAME AGREEMENT_CONTACT_FIRST_NAME
, AGREECONT2.PERSON_LAST_NAME AGREEMENT_CONTACT_LAST_NAME
, RULE1.NAME
, RULE2.NAME
, INVOICE_SITE_USE.LOCATION
, INVOICE_TO_ADDR2.ADDRESS1
, INVOICE_TO_ADDR2.ADDRESS2
, INVOICE_TO_ADDR2.ADDRESS3
, INVOICE_TO_ADDR2.ADDRESS4
, INVOICE_TO_ADDR2.CITY
, INVOICE_TO_ADDR2.STATE
, INVOICE_TO_ADDR2.POSTAL_CODE
, INVOICE_TO_ADDR2.PROVINCE
, INVOICE_TO_ADDR2.COUNTY
, INVOICE_TO_ADDR2.COUNTRY
, OE_BIS_SALESPERSON.GET_SALESPERSON_NAME(AGREEMENT.SALESREP_ID) SALESREP_NAME
, TERM.NAME
, PRICE_LIST.NAME
, AGREEMENT.AGREEMENT_ID
, AGREEMENT.INVOICE_CONTACT_ID
, AGREEMENT.AGREEMENT_CONTACT_ID
, AGREEMENT.SOLD_TO_ORG_ID
, AGREEMENT.INVOICING_RULE_ID
, AGREEMENT.ACCOUNTING_RULE_ID
, AGREEMENT.SALESREP_ID
, AGREEMENT.INVOICE_TO_ORG_ID
, INVOICE_SITE_USE.CUST_ACCT_SITE_ID
, AGREEMENT.TERM_ID
, AGREEMENT.PRICE_LIST_ID
, AGREEMENT.LAST_UPDATE_DATE
, AGREEMENT.LAST_UPDATED_BY
, AGREEMENT.CREATION_DATE
, AGREEMENT.CREATED_BY
FROM OE_AGREEMENTS_B AGREEMENT
, OE_AGREEMENTS_TL T
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_ACCOUNT_ROLES INVCONT1
, HZ_PARTIES INVCONT2
, AR_LOOKUPS INV_ARL
, HZ_RELATIONSHIPS INVCONT4
, HZ_CUST_ACCOUNTS INVACCT
, HZ_CUST_ACCOUNT_ROLES AGREECONT1
, HZ_PARTIES AGREECONT2
, AR_LOOKUPS AGREE_ARL
, HZ_RELATIONSHIPS AGREECONT4
, HZ_CUST_ACCOUNTS AGREEACCT
, RA_RULES RULE1
, RA_RULES RULE2
, HZ_CUST_SITE_USES_ALL INVOICE_SITE_USE
, HZ_PARTY_SITES INVOICE_TO_ADDR1
, HZ_LOCATIONS INVOICE_TO_ADDR2
, HZ_CUST_ACCT_SITES_ALL INVOICE_TO_ADDR3
, RA_TERMS_TL TERM
, QP_LIST_HEADERS_TL PRICE_LIST
WHERE AGREEMENT.AGREEMENT_ID = T.AGREEMENT_ID
AND T.LANGUAGE = USERENV('LANG')
AND AGREEMENT.INVOICE_CONTACT_ID = INVCONT1.CUST_ACCOUNT_ROLE_ID
AND AGREEMENT.INVOICE_CONTACT_ID IS NOT NULL
AND INVCONT1.PARTY_ID = INVCONT4.PARTY_ID
AND INVCONT1.ROLE_TYPE = 'CONTACT'
AND INV_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND INV_ARL.LOOKUP_CODE(+) = INVCONT2.PERSON_PRE_NAME_ADJUNCT
AND INVCONT1.CUST_ACCOUNT_ID = INVACCT.CUST_ACCOUNT_ID(+)
AND NVL(INVCONT4.OBJECT_ID
, -1) = NVL(INVACCT.PARTY_ID
, -1)
AND INVCONT4.SUBJECT_ID = INVCONT2.PARTY_ID
AND AGREEMENT.AGREEMENT_CONTACT_ID = AGREECONT1.CUST_ACCOUNT_ROLE_ID
AND AGREEMENT.AGREEMENT_CONTACT_ID IS NOT NULL
AND AGREECONT1.PARTY_ID = AGREECONT4.PARTY_ID
AND AGREECONT1.ROLE_TYPE = 'CONTACT'
AND AGREE_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND AGREE_ARL.LOOKUP_CODE(+) = AGREECONT2.PERSON_PRE_NAME_ADJUNCT
AND AGREECONT1.CUST_ACCOUNT_ID = AGREEACCT.CUST_ACCOUNT_ID(+)
AND NVL(AGREECONT4.OBJECT_ID
, -1) = NVL(AGREEACCT.PARTY_ID
, -1)
AND AGREECONT4.SUBJECT_ID = AGREECONT2.PARTY_ID
AND AGREEMENT.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
AND AGREEMENT.INVOICING_RULE_ID = RULE1.RULE_ID(+)
AND AGREEMENT.ACCOUNTING_RULE_ID = RULE2.RULE_ID(+)
AND AGREEMENT.INVOICE_TO_ORG_ID = INVOICE_SITE_USE.SITE_USE_ID(+)
AND INVOICE_SITE_USE.CUST_ACCT_SITE_ID = INVOICE_TO_ADDR3.CUST_ACCT_SITE_ID(+)
AND INVOICE_TO_ADDR3.PARTY_SITE_ID = INVOICE_TO_ADDR1.PARTY_SITE_ID(+)
AND INVOICE_TO_ADDR2.LOCATION_ID(+) = INVOICE_TO_ADDR1.LOCATION_ID
AND AGREEMENT.TERM_ID = TERM.TERM_ID(+)
AND AGREEMENT.PRICE_LIST_ID = PRICE_LIST.LIST_HEADER_ID
AND TERM.LANGUAGE(+) = USERENV('LANG')
AND PRICE_LIST.LANGUAGE = USERENV('LANG') WITH READ ONLY

Columns