VIEW: APPS.OPM_CUSTOMERS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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