Oracle

***********************
Customer Import Tables
***********************
select * from HZ_IMP_ERRORS where batch_id = 100000071311236
select * from HZ_IMP_PARTIES_T where batch_id = 100000071311236
select * from AR_UPD_CUSTOMERS where batch_id = 100000071486444
select * from AR_UPD_CUST_ERRORS where BATCH_ID = 100000071486444 and ORIGINAL_ERROR_MESSAGE = 'HZ_DUPLICATE_COMBINATION'

***************************
Customer TCA Registration
***************************
SELECT HP.ORIG_SYSTEM_REFERENCE,
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HP.PARTY_TYPE,
HP.PARTY_UNIQUE_NAME,
TO_CHAR(HP.CREATION_DATE,'MM/DD/YYYY') AS CREATION_DT,
HP.COUNTRY,
HP.ADDRESS1,
HP.ADDRESS2,
HP.ADDRESS3,
HP.ADDRESS4,
HP.CITY,
HP.POSTAL_CODE,
HP.STATE,
HP.PROVINCE,
HP.STATUS,
HP.COUNTY
FROM
HZ_PARTIES HP
WHERE PARTY_NUMBER LIKE 'CONVD1%'

********************************
Customer Site TCA Registration
********************************
SELECT
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HPS.CURRENCY_CODE,
HPS.ORIG_SYSTEM_REFERENCE,
HPS.PARTY_SITE_NUMBER,
HPS.IDENTIFYING_ADDRESS_FLAG,
HPS.STATUS,
HPS.PARTY_SITE_NAME,
HPS.ADDRESSEE,
HPS.ATTRIBUTE_NUMBER1,
HPS.ATTRIBUTE_NUMBER2,
HPS.ATTRIBUTE_NUMBER3,
HPS.ATTRIBUTE_NUMBER4,
HPS.ATTRIBUTE_NUMBER5,
HPS.ATTRIBUTE_NUMBER6,
HPS.ATTRIBUTE_NUMBER7,
HPS.ATTRIBUTE_NUMBER8,
HPS.ATTRIBUTE_NUMBER9,
HPS.ATTRIBUTE_NUMBER10,
HPS.ATTRIBUTE_NUMBER11,
HPS.ATTRIBUTE_NUMBER12,
HPS.ATTRIBUTE_DATE1,
HPS.ATTRIBUTE_DATE2,
HPS.ATTRIBUTE_DATE3,
HPS.ATTRIBUTE_DATE4,
HPS.ATTRIBUTE_DATE5,
HPS.ATTRIBUTE_DATE6,
HPS.ATTRIBUTE_DATE7,
HPS.ATTRIBUTE_DATE8,
HPS.ATTRIBUTE_DATE9,
HPS.ATTRIBUTE_DATE10,
HPS.ATTRIBUTE_DATE11,
HPS.ATTRIBUTE_DATE12,
HPS.PARTY_SITE_LANGUAGE,
HPS.OVERALL_PRIMARY_FLAG

FROM
HZ_PARTY_SITES HPS,
HZ_PARTIES HP

WHERE HP.PARTY_NUMBER LIKE 'CONVD1%'
AND HP.PARTY_ID=HPS.PARTY_ID
************************************
Customer Site Uses TCA Registration
************************************
SELECT
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HPS.PARTY_SITE_NUMBER,
HPS.PARTY_SITE_NAME,
HPSU.SITE_USE_TYPE,
HPSU.PRIMARY_PER_TYPE,
HPSU.BEGIN_DATE,
HPSU.STATUS ATTRIBUTE_CATEGORY,
HPSU.ATTRIBUTE1,
HPSU.ATTRIBUTE2,
HPSU.ATTRIBUTE3,
HPSU.ATTRIBUTE4,
HPSU.ATTRIBUTE5,
HPSU.ATTRIBUTE6,
HPSU.ATTRIBUTE7,
HPSU.ATTRIBUTE8,
HPSU.ATTRIBUTE9,
HPSU.ATTRIBUTE10,
HPSU.ATTRIBUTE11,
HPSU.ATTRIBUTE12,
HPSU.ATTRIBUTE13,
HPSU.ATTRIBUTE14,
HPSU.ATTRIBUTE15,
HPSU.ATTRIBUTE16,
HPSU.ATTRIBUTE17,
HPSU.ATTRIBUTE18,
HPSU.ATTRIBUTE19,
HPSU.ATTRIBUTE20,
HPSU.ATTRIBUTE21,
HPSU.ATTRIBUTE22,
HPSU.ATTRIBUTE23,
HPSU.ATTRIBUTE24,
HPSU.ATTRIBUTE25,
HPSU.ATTRIBUTE26,
HPSU.ATTRIBUTE27,
HPSU.ATTRIBUTE28,
HPSU.ATTRIBUTE29,
HPSU.ATTRIBUTE30

FROM
HZ_PARTY_SITE_USES HPSU,
HZ_PARTY_SITES HPS,
HZ_PARTIES HP

WHERE HP.PARTY_NUMBER LIKE 'CONVD1%'
AND HP.PARTY_ID=HPS.PARTY_ID
AND HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID

*******************************
Customer Account Information
*******************************
SELECT
HCA.ACCOUNT_NUMBER,
HCA.ACCOUNT_NAME,
HCA.ACCOUNT_ESTABLISHED_DATE,
HCA.ACCOUNT_TERMINATION_DATE,
HCA.STATUS,
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,
HCA.ORIG_SYSTEM_REFERENCE,
HCA.CUSTOMER_TYPE,
HCA.CUSTOMER_CLASS_CODE,
HCA.TAX_CODE,
HCA.TAX_HEADER_LEVEL_FLAG,
HCA.TAX_ROUNDING_RULE,
HCA.COTERMINATE_DAY_MONTH,
HCA.HOLD_BILL_FLAG

FROM
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP

WHERE HP.PARTY_NUMBER LIKE 'CONVD1%'
AND HP.PARTY_ID=HCA.PARTY_ID

HZ_CUST_ACCOUNT_RELATE_ALL ---Not Loaded
****************
Customer Sites
****************
select
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HPS.PARTY_SITE_NUMBER,
HPS.PARTY_SITE_NAME,
HCASA.START_DATE,
HCASA.END_DATE,
HCASA.CUSTOMER_CATEGORY_CODE,
HCASA.KEY_ACCOUNT_FLAG,
HCASA.ATTRIBUTE_CATEGORY,
HCASA.ATTRIBUTE1,
HCASA.ATTRIBUTE2,
HCASA.ATTRIBUTE3,
HCASA.ATTRIBUTE4,
HCASA.ATTRIBUTE5,
HCASA.ATTRIBUTE6,
HCASA.ATTRIBUTE7,
HCASA.ATTRIBUTE8,
HCASA.ATTRIBUTE9,
HCASA.ATTRIBUTE10,
HCASA.ATTRIBUTE11,
HCASA.ATTRIBUTE12,
HCASA.ATTRIBUTE13,
HCASA.ATTRIBUTE14,
HCASA.ATTRIBUTE15,
HCASA.ATTRIBUTE16,
HCASA.ATTRIBUTE17,
HCASA.ATTRIBUTE18,
HCASA.ATTRIBUTE19,
HCASA.ATTRIBUTE20,
HCASA.ORIG_SYSTEM_REFERENCE,
HCASA.STATUS,
HCASA.SET_ID,
HCASA.BILL_TO_FLAG,
HCASA.MARKET_FLAG,
HCASA.SHIP_TO_FLAG

from
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
HZ_PARTIES HP

where

HP.PARTY_NUMBER LIKE 'CONVD1%'
AND HP.PARTY_ID=HCA.PARTY_ID
AND HP.PARTY_ID=HPS.PARTY_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
AND HP.PARTY_ID=HPS.PARTY_ID
********************
Customer Site Uses
********************

SELECT
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HPS.PARTY_SITE_NUMBER,
HPS.PARTY_SITE_NAME,
HCSUA.SITE_USE_CODE,
HCSUA.PRIMARY_FLAG,
HCSUA.STATUS,
HCSUA.LOCATION,
HCSUA.ORIG_SYSTEM_REFERENCE,
HCSUA.PAYMENT_TERM_ID,
HCSUA.ATTRIBUTE_CATEGORY,
HCSUA.ATTRIBUTE1,
HCSUA.ATTRIBUTE2,
HCSUA.ATTRIBUTE3,
HCSUA.ATTRIBUTE4,
HCSUA.ATTRIBUTE5,
HCSUA.ATTRIBUTE6,
HCSUA.ATTRIBUTE7,
HCSUA.ATTRIBUTE8,
HCSUA.ATTRIBUTE9,
HCSUA.ATTRIBUTE10,
HCSUA.SET_ID,
HCSUA.START_DATE,
HCSUA.END_DATE

FROM
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
HZ_PARTIES HP

WHERE
HP.PARTY_NUMBER LIKE 'CONVD1%'
AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID
AND HP.PARTY_ID=HCA.PARTY_ID
AND HP.PARTY_ID=HPS.PARTY_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
AND HP.PARTY_ID=HPS.PARTY_ID
*******************
Customer Contacts
*******************
SELECT
HP1.PARTY_NAME,
HP1.ADDRESS1,
HP1.CITY,
HP1.STATE,
HP1.COUNTRY,
HOC.JOB_TITLE,
HOC.JOB_TITLE_CODE
FROM FUSION.HZ_RELATIONSHIPS HR,
FUSION.HZ_PARTIES HP,
FUSION.HZ_CUST_ACCOUNTS HCA,
FUSION.HZ_PARTIES HP1,
FUSION.HZ_ORG_CONTACTS HOC
WHERE HR.SUBJECT_ID = HP.PARTY_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCA.ACCOUNT_NUMBER = '&ACCOUNT_NUMBER'
AND HR.OBJECT_TYPE = 'PERSON'
AND HR.OBJECT_ID = HP1.PARTY_ID
AND HR.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID;

***************************
Receipt Class and Methods
***************************
SELECT
ARC.NAME AS RECEIPT_CLASS_NAME,
ARC.CONFIRM_FLAG,
ARC.CREATION_METHOD_CODE AS RC_CREATION_METHOD,
ARC.CLEAR_FLAG,
ARC.CREATION_STATUS,
ARC.REMIT_FLAG,
ARC.REMIT_METHOD_CODE,
ARM.NAME RECEIPT,
ARM.PRINTED_NAME,
ARM.START_DATE RM_START_DATE,
ARM.END_DATE RM_END_DATE,
ARM.LEAD_DAYS,
arm.MATURITY_DATE_RULE_CODE,
arm.RECEIPT_CREATION_RULE_CODE,
ARMA.ORG_ID,
fabu.BU_NAME,
ARMA.BANK_ACCOUNT_ID,
cba.BANK_ACCOUNT_NAME,
cba.BANK_ACCOUNT_NUM,
cba.BANK_BRANCH_ID,
cba.BANK_ID,
cba.CURRENCY_CODE,
cba.START_DATE BK_START_DATE,
cba.END_DATE BK_END_DATE,
arma.CLEARING_DAYS,
ARMA.PRIMARY_FLAG,
ARMA.REMIT_BANK_ACCT_USE_ID,
ARMA.MIN_RECEIPT_AMOUNT,
ARMA.ATTRIBUTE1 remit_email_id,
ARMA.ATTRIBUTE2 remit_address,
ARMA.CASH_CCID,
GCC1.CONCATENATED_SEGMENTS CASH_CC,
ARMA.EARNED_CCID,
GCC2.CONCATENATED_SEGMENTS EARNED_CC,
ARMA.ON_ACCOUNT_CCID,
GCC3.CONCATENATED_SEGMENTS ON_ACC_CC,
ARMA.UNAPPLIED_CCID,
GCC4.CONCATENATED_SEGMENTS UNAPP_CC,
ARMA.UNEARNED_CCID,
GCC5.CONCATENATED_SEGMENTS UNEARN_CC,
ARMA.UNIDENTIFIED_CCID,
GCC6.CONCATENATED_SEGMENTS UNID_CC,
ARMA.BANK_CHARGES_CCID,
GCC6.CONCATENATED_SEGMENTS BC_CC,
arma.REMITTANCE_CCID,
GCC7.CONCATENATED_SEGMENTS REM_CC,
ARMA.EDISC_RECEIVABLES_TRX_ID,
ARMA.UNEDISC_RECEIVABLES_TRX_ID,
ARMA.RECEIPT_CLEARING_CCID,
GCC8.CONCATENATED_SEGMENTS RC_CC

FROM
AR_RECEIPT_CLASSES ARC,
AR_RECEIPT_METHODS ARM,
AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMA,
CE_BANK_ACCOUNTS cba,
FUN_ALL_BUSINESS_UNITS_V FABU,
GL_CODE_COMBINATIONS GCC1,
GL_CODE_COMBINATIONS GCC2,
GL_CODE_COMBINATIONS GCC3,
GL_CODE_COMBINATIONS GCC4,
GL_CODE_COMBINATIONS GCC5,
GL_CODE_COMBINATIONS GCC6,
GL_CODE_COMBINATIONS GCC7,
GL_CODE_COMBINATIONS GCC8

WHERE ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID
AND ARM.RECEIPT_METHOD_ID = ARMA.RECEIPT_METHOD_ID
and arma.BANK_ACCOUNT_ID = cba.BANK_ACCOUNT_ID (+)
AND arma.ORG_ID = FABU.BU_ID
AND ARMA.CASH_CCID = GCC1.CODE_COMBINATION_ID (+)
AND ARMA.EARNED_CCID = GCC2.CODE_COMBINATION_ID (+)
AND ARMA.ON_ACCOUNT_CCID = GCC3.CODE_COMBINATION_ID (+)
AND ARMA.UNAPPLIED_CCID = GCC4.CODE_COMBINATION_ID (+)
AND ARMA.UNIDENTIFIED_CCID = GCC5.CODE_COMBINATION_ID (+)
AND ARMA.BANK_CHARGES_CCID = GCC6.CODE_COMBINATION_ID (+)
AND ARMA.REMITTANCE_CCID = GCC7.CODE_COMBINATION_ID (+)
AND ARMA.RECEIPT_CLEARING_CCID = GCC8.CODE_COMBINATION_ID (+)
and (ARC.NAME = :CLASS_NAME OR :CLASS_NAME IS NULL)
and (ARM.NAME = :METHOD_NAME OR :METHOD_NAME IS NULL)