View: OPM_CUSTOMERS

Product:	GMF - Process Manufacturing Financials
Description:	Used for OPM customer synchronization
Implementation/DBA Data:	APPS.OPM_CUSTOMERS

View Text

SELECT 0
, /* CUST_ID */ C.ACCOUNT_NUMBER || NVL(FND_PROFILE.VALUE('GL$CUST_DELIMITER')
, '-') || S.LOCATION
, /* CUST_NO */ 0
, /* ADDR_ID */ C.ACCOUNT_NUMBER
, /* CUSTSORT_NO */ PR.PARTY_NAME
, /* CUST_NAME */ GMF_AR_CUSTOMER.PHONE(C.CUST_ACCOUNT_ID
, A.CUST_ACCT_SITE_ID
, NULL
, PR.PARTY_ID
, CP.PARTY_SITE_ID
, 'GEN')
, /* PHONE_NO */ GMF_AR_CUSTOMER.PHONE(C.CUST_ACCOUNT_ID
, A.CUST_ACCT_SITE_ID
, NULL
, PR.PARTY_ID
, CP.PARTY_SITE_ID
, 'FAX')
, /* FAX_NO */ GMF_AR_CUSTOMER.PHONE(C.CUST_ACCOUNT_ID
, A.CUST_ACCT_SITE_ID
, NULL
, PR.PARTY_ID
, CP.PARTY_SITE_ID
, 'TLX')
, /* TELEX_NO */ C.ACCOUNT_NUMBER || NVL(FND_PROFILE.VALUE('GL$CUST_DELIMITER')
, '-') || S.LOCATION
, /* ALTCUST_NO */ DECODE (S.ORG_ID
, NULL
, NULL
, P.CO_CODE)
, /* CO_CODE */ 0
, /* MAILADDR_ID */ GMF_AR_CUSTOMER.TERMS(NVL(S.PAYMENT_TERM_ID
, NVL(PRS.STANDARD_TERMS
, NVL(PRC.STANDARD_TERMS
, NULL))))
, /* TERMS_CODE */ DECODE(NVL(PRS.OVERRIDE_TERMS
, NVL(PRC.OVERRIDE_TERMS
, 'Y'))
, 'N'
, 0
, 1)
, /* TERMS_VARY */ NVL(S.FOB_POINT
, NVL(C.FOB_POINT
, NULL))
, /* FOB_CODE */ GMF_AR_CUSTOMER.SALESREP(NVL(S.PRIMARY_SALESREP_ID
, NVL(C.PRIMARY_SALESREP_ID
, NULL))
, S.ORG_ID)
, /* SLSREP_CODE */ TO_CHAR(''
, '9')
, /* COMMISSION_CODE */ DECODE(C.SHIP_SETS_INCLUDE_LINES_FLAG
, 'Y'
, 0
, DECODE(S.SHIP_SETS_INCLUDE_LINES_FLAG
, 'Y'
, 0
, 1))
, /* BACKORDER_IND BUG 2937494 REPLLACED SHIP_PARTIAL WITH SHIP_SETS_INCLUDE_LINES_FLAG */ 0
, /* STMT_IND */ 0
, /* SVCCHG_IND */ DECODE(C.STATUS
, 'I'
, 1
, DECODE(A.STATUS
, 'I'
, 1
, DECODE(S.STATUS
, 'I'
, 1
, 0)))
, /* INACTIVE_IND */ DECODE(S.SITE_USE_CODE
, 'BILL_TO'
, DECODE(S.STATUS
, 'A'
, DECODE(A.STATUS
, 'A'
, DECODE(C.STATUS
, 'A'
, 1
, 0)
, 0)
, 0)
, 0)
, /* BILL_IND */ DECODE(S.SITE_USE_CODE
, 'SHIP_TO'
, DECODE(S.STATUS
, 'A'
, DECODE(A.STATUS
, 'A'
, DECODE(C.STATUS
, 'A'
, 1
, 0)
, 0)
, 0)
, 0)
, /* SHIP_IND */ DECODE(C.CUSTOMER_TYPE
, 'I'
, 2
, 0)
, /* CUST_TYPE */ S.SIC_CODE
, /* SIC_CODE */ A.ATTRIBUTE2
, /* FROM_WHSE */ TO_CHAR(''
, '9')
, /* TO_WHSE */ TO_CHAR(''
, '9')
, /* SPLC_CODE */ A.ATTRIBUTE1
, /* FRTBILL_MTHD */ 0
, /* CREDIT_LIMIT */ 0
, /* PRICECUST_ID */ TO_CHAR(''
, '9')
, /* CUST_TERRITORY */ GMF_AR_CUSTOMER.CURRENCY(C.CUST_ACCOUNT_ID
, S.SITE_USE_ID
, S.ORG_ID)
, /* CUST_CURRENCY */ C.CUSTOMER_CLASS_CODE
, /* CUST_CLASS */ TO_CHAR(''
, '9')
, /* CUSTTRADE_CLASS */ NVL(S.ATTRIBUTE1
, NVL(C.ATTRIBUTE1
, NULL))
, /* CUSTGL_CLASS */ NVL(S.ATTRIBUTE2
, NVL(C.ATTRIBUTE2
, NULL))
, /* TAXLOC_CODE */ NVL(S.ATTRIBUTE3
, NVL(C.ATTRIBUTE3
, NULL))
, /* TAXCALC_CODE */ C.CUST_ACCOUNT_ID
, /* OF_CUST_ID */ DECODE(S.SITE_USE_CODE
, 'SHIP_TO'
, A.CUST_ACCT_SITE_ID
, NULL)
, /* OF_SHIP_TO_ADDRESS_ID */ DECODE(S.SITE_USE_CODE
, 'SHIP_TO'
, S.SITE_USE_ID
, NULL)
, /* OF_SHIP_TO_SITE_USE_ID */ DECODE(S.SITE_USE_CODE
, 'BILL_TO'
, A.CUST_ACCT_SITE_ID
, NULL)
, /* OF_BILL_TO_ADDRESS_ID */ DECODE(S.SITE_USE_CODE
, 'BILL_TO'
, S.SITE_USE_ID
, NULL)
, /* OF_BILL_TO_SITE_USE_ID */ C.CREATION_DATE
, /* CREATION_DATE */ C.LAST_UPDATE_LOGIN
, /* LAST_UPDATE_LOGIN */ GREATEST(C.LAST_UPDATE_DATE
, NVL(S.LAST_UPDATE_DATE
, C.LAST_UPDATE_DATE)
, NVL(A.LAST_UPDATE_DATE
, C.LAST_UPDATE_DATE)
, NVL(PRC.LAST_UPDATE_DATE
, C.LAST_UPDATE_DATE)
, NVL(PRS.LAST_UPDATE_DATE
, C.LAST_UPDATE_DATE)
, NVL(GMF_AR_CUSTOMER.CURRENCY_DATE(C.CUST_ACCOUNT_ID
, S.SITE_USE_ID
, S.ORG_ID)
, C.LAST_UPDATE_DATE)
, NVL(GMF_AR_CUSTOMER.PHONE_DATE(C.CUST_ACCOUNT_ID
, A.CUST_ACCT_SITE_ID
, NULL
, PR.PARTY_ID
, CP.PARTY_SITE_ID)
, C.LAST_UPDATE_DATE))
, /* LAST_UPDATE_DATE */ C.CREATED_BY
, /* CREATED_BY */ C.LAST_UPDATED_BY
, /* LAST_UPDATED_BY */ 0
, /* TRANS_CNT */ 0
, /* DELETE_MARK */ TO_NUMBER(NULL)
, /* TEXT_CODE */ 0
, /* OPEN_BALANCE */ NVL(S.ATTRIBUTE4
, NVL(C.ATTRIBUTE4
, NULL))
, /* CUSTPRICE_CLASS */ TO_CHAR(''
, '9')
, /* EMAIL_ADDRESS */ TO_CHAR(''
, '9')
, /* LOCKBOX_CODE */ 0
, /* EDI_FLAG */ DECODE(C.ATTRIBUTE5
, '1'
, 1
, 0)
, /* USE_PROFILE */ DECODE(C.ATTRIBUTE6
, '0'
, 1
, 0)
, /* RESTRICT_TO_CUSTITEMS */ 0
, /* SOCONFIRM_IND */ TO_CHAR(''
, '9')
, /* PROGRAM_APPLICATION_ID */ TO_CHAR(''
, '9')
, /* PROGRAM_ID */ TO_CHAR(''
, '9')
, /* PROGRAM_UPDATE_DATE */ TO_CHAR(''
, '9')
, /* REQUEST_ID */ TO_CHAR(''
, '9')
, /* ATTRIBUTE1 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE2 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE3 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE4 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE5 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE6 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE7 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE8 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE9 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE10 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE11 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE12 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE13 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE14 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE15 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE16 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE17 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE18 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE19 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE20 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE21 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE22 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE23 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE24 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE25 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE26 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE27 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE28 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE29 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE30 */ TO_CHAR(''
, '9')
, /* ATTRIBUTE_CATEGORY */ TO_NUMBER(NULL)
, /* LOWEST_PRICE_IND */ NVL(S.SHIP_VIA
, C.SHIP_VIA)
, L.ADDRESS1
, /* ADDR1 */ L.ADDRESS2
, /* ADDR2 */ L.ADDRESS3
, /* ADDR3 */ L.ADDRESS4
, /* ADDR4 */ L.CITY
, /* ORA_ADDR4 */ L.PROVINCE
, /* PROVINCE */ L.COUNTY
, /* COUNTY */ L.STATE
, /* STATE_CODE */ L.POSTAL_CODE
, /* POSTAL_CODE */ L.COUNTRY
, /* COUNTRY_CODE */ S.BILL_TO_SITE_USE_ID
, /* BILL_TO_SITE_USE_ID */ C.ACCOUNT_NUMBER
, /* CUSTOMER_NUMBER */ DECODE(F.OF_SITE_USE_ID
, NULL
, 0
, 1) INTERFACE_IND
, S.SITE_USE_CODE /* SITE_USE_CODE */
FROM HZ_CUST_ACCOUNTS C
, HZ_PARTIES PR
, HZ_CUST_ACCT_SITES_ALL A
, HZ_CUST_SITE_USES_ALL S
, HZ_PARTY_SITES CP
, HZ_LOCATIONS L
, HZ_CUSTOMER_PROFILES PRC
, HZ_CUSTOMER_PROFILES PRS
, GML_INVALID_CUSTOMERS F
, GL_PLCY_MST P
WHERE C.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND C.PARTY_ID = PR.PARTY_ID
AND A.CUST_ACCT_SITE_ID = S.CUST_ACCT_SITE_ID
AND S.SITE_USE_CODE IN ('BILL_TO'
, 'SHIP_TO')
AND A.PARTY_SITE_ID = CP.PARTY_SITE_ID
AND CP.LOCATION_ID = L.LOCATION_ID
AND C.CUST_ACCOUNT_ID = PRC.CUST_ACCOUNT_ID
AND PRC.SITE_USE_ID IS NULL
AND S.SITE_USE_ID = PRS.SITE_USE_ID (+)
AND S.SITE_USE_ID = F.OF_SITE_USE_ID (+)
AND S.SITE_USE_CODE = F.OF_SITE_USE_CODE (+)
AND P.CO_CODE = NVL(F.CO_CODE
, P.CO_CODE)
AND S.ORG_ID = P.ORG_ID

Columns