[Home] [Help]
SELECT hca.CUST_ACCOUNT_ID ,hca.PARTY_ID ,hp.PARTY_NAME ,hp.PARTY_NUMBER ,hp.EMAIL_ADDRESS ,hp.PERSON_FIRST_NAME ,hp.PERSON_MIDDLE_NAME ,hp.PERSON_LAST_NAME ,hp.PERSON_NAME_SUFFIX ,hp.SALUTATION ,hp.PERSON_TITLE ,hp.ADDRESS1 ,hp.ADDRESS2 ,hp.ADDRESS3 ,hp.ADDRESS4 ,hp.CITY ,hp.STATE ,hp.POSTAL_CODE ,hp.PROVINCE ,hp.COUNTY ,hp.COUNTRY ,lang. territory_short_name COUNTRY_NAME ,hp.URL ,hps.PARTY_SITE_ID ,hps.PARTY_SITE_NAME ,hps.REGION ,hps.MAILSTOP ,hl.LANGUAGE LANGUAGE ,hps.IDENTIFYING_ADDRESS_FLAG ,hca.ACCOUNT_NUMBER ,hca.ACCOUNT_REPLICATION_KEY ,hca.ORIG_SYSTEM_REFERENCE ,hca.STATUS ,hca.CREATION_DATE ,hca.CREATED_BY ,hca.LAST_UPDATE_DATE ,hca.LAST_UPDATED_BY ,hca.LAST_UPDATE_LOGIN ,hca.REQUEST_ID ,hca.PROGRAM_ID ,hca.PROGRAM_APPLICATION_ID ,hca.PROGRAM_UPDATE_DATE ,hca.WAREHOUSE_ID ,hca.WH_UPDATE_DATE ,hca.CUSTOMER_TYPE ,hca.CUSTOMER_CLASS_CODE ,hcsu.PRIMARY_SALESREP_ID ,hca.SALES_CHANNEL_CODE ,hca.TAX_CODE ,hca.TAX_HEADER_LEVEL_FLAG ,hca.TAX_ROUNDING_RULE ,hca.FOB_POINT ,hca.FREIGHT_TERM ,hca.SHIP_PARTIAL ,hca.SHIP_VIA ,hca.PAYMENT_TERM_ID ,hca.COTERMINATE_DAY_MONTH ,hca.PRIMARY_SPECIALIST_ID ,hca.SECONDARY_SPECIALIST_ID ,NULL ACCOUNT_LIABLE_FLAG ,hca.RESTRICTION_LIMIT_AMOUNT ,NULL CURRENT_BALANCE ,hca.PASSWORD_TEXT ,hca.HIGH_PRIORITY_INDICATOR ,hca.ACCOUNT_ESTABLISHED_DATE ,NULL ACCOUNT_TERMINATION_DATE ,NULL ACCOUNT_ACTIVATION_DATE ,hca.CREDIT_CLASSIFICATION_CODE ,NULL DEPARTMENT ,hca.MAJOR_ACCOUNT_NUMBER ,hca.HOTWATCH_SERVICE_FLAG ,hca.HOTWATCH_SVC_BAL_IND ,hca.HELD_BILL_EXPIRATION_DATE ,hca.HOLD_BILL_FLAG ,hca.HIGH_PRIORITY_REMARKS ,hca.PO_EFFECTIVE_DATE ,hca.PO_EXPIRATION_DATE ,NULL REALTIME_RATE_FLAG ,hca.SINGLE_USER_FLAG ,hca.WATCH_ACCOUNT_FLAG ,hca.WATCH_BALANCE_INDICATOR ,NULL ACCT_LIFE_CYCLE_STATUS ,hca.ACCOUNT_NAME ,hca.DEPOSIT_REFUND_METHOD ,NULL DORMANT_ACCOUNT_FLAG ,hca.NPA_NUMBER ,hca.PIN_NUMBER ,NULL SUSPENSION_DATE ,hca.WRITE_OFF_ADJUSTMENT_AMOUNT ,hca.WRITE_OFF_PAYMENT_AMOUNT ,hca.WRITE_OFF_AMOUNT ,hca.SOURCE_CODE ,NULL COMPETITOR_TYPE ,hca.COMMENTS ,hca.DATES_NEGATIVE_TOLERANCE ,hca.DATES_POSITIVE_TOLERANCE ,hca.DATE_TYPE_PREFERENCE ,hca.OVER_SHIPMENT_TOLERANCE ,hca.UNDER_SHIPMENT_TOLERANCE ,hca.OVER_RETURN_TOLERANCE ,hca.UNDER_RETURN_TOLERANCE ,hca.ITEM_CROSS_REF_PREF ,hca.SHIP_SETS_INCLUDE_LINES_FLAG ,hca.ARRIVALSETS_INCLUDE_LINES_FLAG ,hca.SCHED_DATE_PUSH_FLAG ,hca.INVOICE_QUANTITY_RULE ,hca.PRICING_EVENT ,hca.STATUS_UPDATE_DATE ,hca.AUTOPAY_FLAG ,NULL NOTIFY_FLAG ,hca.ATTRIBUTE_CATEGORY ,hca.ATTRIBUTE1 ,hca.ATTRIBUTE2 ,hca.ATTRIBUTE3 ,hca.ATTRIBUTE4 ,hca.ATTRIBUTE5 ,hca.ATTRIBUTE6 ,hca.ATTRIBUTE7 ,hca.ATTRIBUTE8 ,hca.ATTRIBUTE9 ,hca.ATTRIBUTE10 ,hca.ATTRIBUTE11 ,hca.ATTRIBUTE12 ,hca.ATTRIBUTE13 ,hca.ATTRIBUTE14 ,hca.ATTRIBUTE15 ,hca.ATTRIBUTE16 ,hca.ATTRIBUTE17 ,hca.ATTRIBUTE18 ,hca.ATTRIBUTE19 ,hca.ATTRIBUTE20 ,hcas.CUSTOMER_CATEGORY_CODE ,hca.created_by CREATED_BY_NAME ,hca.last_update_login LAST_UPDATE_LOGIN_NAME ,hca.last_updated_by LAST_UPDATED_BY_NAME FROM HZ_CUST_ACCOUNTS hca, HZ_CUST_SITE_USES_ALL hcsu, HZ_PARTIES hp, HZ_PARTY_SITES hps, HZ_CUST_ACCT_SITES_ALL hcas, fnd_territories_tl lang, HZ_LOCATIONS hl WHERE hca.STATUS = 'A' AND hca.PARTY_ID = hp.PARTY_ID AND hps.LOCATION_ID = HL.LOCATION_ID AND hp.STATUS = 'A' AND hp.PARTY_TYPE = 'ORGANIZATION' AND hcas.CUST_ACCT_SITE_ID = hcsu.CUST_ACCT_SITE_ID AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID AND hcsu.status='A' AND hps.PARTY_ID(+) = hp.PARTY_ID AND hps.IDENTIFYING_ADDRESS_FLAG(+) = 'Y' AND hps.ACTUAL_CONTENT_SOURCE(+) = 'USER_ENTERED' AND hcas.PARTY_SITE_ID(+) = hps.PARTY_SITE_ID and lang. language = userenv('LANG') and lang.territory_code = hp.country AND hca.LAST_UPDATE_DATE IN (SELECT MAX(B.LAST_UPDATE_DATE ) FROM HZ_CUST_ACCOUNTS B WHERE B.PARTY_ID = hca.PARTY_ID)
SELECT HCA.CUST_ACCOUNT_ID
, HCA.PARTY_ID
, HP.PARTY_NAME
, HP.PARTY_NUMBER
, HP.EMAIL_ADDRESS
, HP.PERSON_FIRST_NAME
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_LAST_NAME
, HP.PERSON_NAME_SUFFIX
, HP.SALUTATION
, HP.PERSON_TITLE
, HP.ADDRESS1
, HP.ADDRESS2
, HP.ADDRESS3
, HP.ADDRESS4
, HP.CITY
, HP.STATE
, HP.POSTAL_CODE
, HP.PROVINCE
, HP.COUNTY
, HP.COUNTRY
, LANG. TERRITORY_SHORT_NAME COUNTRY_NAME
, HP.URL
, HPS.PARTY_SITE_ID
, HPS.PARTY_SITE_NAME
, HPS.REGION
, HPS.MAILSTOP
, HL.LANGUAGE LANGUAGE
, HPS.IDENTIFYING_ADDRESS_FLAG
, HCA.ACCOUNT_NUMBER
, HCA.ACCOUNT_REPLICATION_KEY
, HCA.ORIG_SYSTEM_REFERENCE
, HCA.STATUS
, HCA.CREATION_DATE
, HCA.CREATED_BY
, HCA.LAST_UPDATE_DATE
, HCA.LAST_UPDATED_BY
, HCA.LAST_UPDATE_LOGIN
, HCA.REQUEST_ID
, HCA.PROGRAM_ID
, HCA.PROGRAM_APPLICATION_ID
, HCA.PROGRAM_UPDATE_DATE
, HCA.WAREHOUSE_ID
, HCA.WH_UPDATE_DATE
, HCA.CUSTOMER_TYPE
, HCA.CUSTOMER_CLASS_CODE
, HCSU.PRIMARY_SALESREP_ID
, HCA.SALES_CHANNEL_CODE
, HCA.TAX_CODE
, HCA.TAX_HEADER_LEVEL_FLAG
, HCA.TAX_ROUNDING_RULE
, HCA.FOB_POINT
, HCA.FREIGHT_TERM
, HCA.SHIP_PARTIAL
, HCA.SHIP_VIA
, HCA.PAYMENT_TERM_ID
, HCA.COTERMINATE_DAY_MONTH
, HCA.PRIMARY_SPECIALIST_ID
, HCA.SECONDARY_SPECIALIST_ID
, NULL ACCOUNT_LIABLE_FLAG
, HCA.RESTRICTION_LIMIT_AMOUNT
, NULL CURRENT_BALANCE
, HCA.PASSWORD_TEXT
, HCA.HIGH_PRIORITY_INDICATOR
, HCA.ACCOUNT_ESTABLISHED_DATE
, NULL ACCOUNT_TERMINATION_DATE
, NULL ACCOUNT_ACTIVATION_DATE
, HCA.CREDIT_CLASSIFICATION_CODE
, NULL DEPARTMENT
, HCA.MAJOR_ACCOUNT_NUMBER
, HCA.HOTWATCH_SERVICE_FLAG
, HCA.HOTWATCH_SVC_BAL_IND
, HCA.HELD_BILL_EXPIRATION_DATE
, HCA.HOLD_BILL_FLAG
, HCA.HIGH_PRIORITY_REMARKS
, HCA.PO_EFFECTIVE_DATE
, HCA.PO_EXPIRATION_DATE
, NULL REALTIME_RATE_FLAG
, HCA.SINGLE_USER_FLAG
, HCA.WATCH_ACCOUNT_FLAG
, HCA.WATCH_BALANCE_INDICATOR
, NULL ACCT_LIFE_CYCLE_STATUS
, HCA.ACCOUNT_NAME
, HCA.DEPOSIT_REFUND_METHOD
, NULL DORMANT_ACCOUNT_FLAG
, HCA.NPA_NUMBER
, HCA.PIN_NUMBER
, NULL SUSPENSION_DATE
, HCA.WRITE_OFF_ADJUSTMENT_AMOUNT
, HCA.WRITE_OFF_PAYMENT_AMOUNT
, HCA.WRITE_OFF_AMOUNT
, HCA.SOURCE_CODE
, NULL COMPETITOR_TYPE
, HCA.COMMENTS
, HCA.DATES_NEGATIVE_TOLERANCE
, HCA.DATES_POSITIVE_TOLERANCE
, HCA.DATE_TYPE_PREFERENCE
, HCA.OVER_SHIPMENT_TOLERANCE
, HCA.UNDER_SHIPMENT_TOLERANCE
, HCA.OVER_RETURN_TOLERANCE
, HCA.UNDER_RETURN_TOLERANCE
, HCA.ITEM_CROSS_REF_PREF
, HCA.SHIP_SETS_INCLUDE_LINES_FLAG
, HCA.ARRIVALSETS_INCLUDE_LINES_FLAG
, HCA.SCHED_DATE_PUSH_FLAG
, HCA.INVOICE_QUANTITY_RULE
, HCA.PRICING_EVENT
, HCA.STATUS_UPDATE_DATE
, HCA.AUTOPAY_FLAG
, NULL NOTIFY_FLAG
, HCA.ATTRIBUTE_CATEGORY
, HCA.ATTRIBUTE1
, HCA.ATTRIBUTE2
, HCA.ATTRIBUTE3
, HCA.ATTRIBUTE4
, HCA.ATTRIBUTE5
, HCA.ATTRIBUTE6
, HCA.ATTRIBUTE7
, HCA.ATTRIBUTE8
, HCA.ATTRIBUTE9
, HCA.ATTRIBUTE10
, HCA.ATTRIBUTE11
, HCA.ATTRIBUTE12
, HCA.ATTRIBUTE13
, HCA.ATTRIBUTE14
, HCA.ATTRIBUTE15
, HCA.ATTRIBUTE16
, HCA.ATTRIBUTE17
, HCA.ATTRIBUTE18
, HCA.ATTRIBUTE19
, HCA.ATTRIBUTE20
, HCAS.CUSTOMER_CATEGORY_CODE
, HCA.CREATED_BY CREATED_BY_NAME
, HCA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, HCA.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
FROM HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_PARTIES HP
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCAS
, FND_TERRITORIES_TL LANG
, HZ_LOCATIONS HL
WHERE HCA.STATUS = 'A'
AND HCA.PARTY_ID = HP.PARTY_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HP.STATUS = 'A'
AND HP.PARTY_TYPE = 'ORGANIZATION'
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.STATUS='A'
AND HPS.PARTY_ID(+) = HP.PARTY_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND HPS.ACTUAL_CONTENT_SOURCE(+) = 'USER_ENTERED'
AND HCAS.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
AND LANG. LANGUAGE = USERENV('LANG')
AND LANG.TERRITORY_CODE = HP.COUNTRY
AND HCA.LAST_UPDATE_DATE IN (SELECT MAX(B.LAST_UPDATE_DATE )
FROM HZ_CUST_ACCOUNTS B
WHERE B.PARTY_ID = HCA.PARTY_ID)
|
|
|
|