--To Extract Customer header and details
SELECT distinct hca.orig_system_reference "Orig System Customer Ref" ,
hcs.ORIG_SYSTEM_REFERENCE "Orig System address Ref" ,
HP.PARTY_NAME CUSTOMER_NAME ,
DECODE(hca.customer_type,'I','Internal','R','Extrenal',hca.customer_type) Customer_Type,
hca.status,
hp.ORGANIZATION_NAME_PHONETIC customer_alt_name,
hl.country ,
hl.address1 ,
hl.address2 ,
hl.address3 ,
hl.address4 ,
hl.ADDRESS_LINES_PHONETIC Alternate_Name ,
hl.city ,
hl.state ,
HL.POSTAL_CODE,
HCSU.SITE_USE_CODE USAGE ,
HCS.LANGUAGE ,
HCSU.LOCATION ,
HCSU.PRIMARY_FLAG ,
HCSU.TAX_CODE ,
HCSU.TAX_REFERENCE "Tax Registration Number" ,
(SELECT SEGMENT1
||'-'
||SEGMENT2
||'-'
||SEGMENT3
||'-'
||SEGMENT4
||'-'
||SEGMENT5
||'-'
||SEGMENT6
||'-'
||SEGMENT7
||'-'
||SEGMENT8
||'-'
||SEGMENT9
||'-'
||SEGMENT10
FROM APPS.GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID=HCSU.GL_ID_REC
)"Accounts Receivable" ,
(SELECT SEGMENT1
||'-'
||SEGMENT2
||'-'
||SEGMENT3
||'-'
||SEGMENT4
||'-'
||SEGMENT5
||'-'
||SEGMENT6
||'-'
||SEGMENT7
||'-'
||SEGMENT8
||'-'
||SEGMENT9
||'-'
||SEGMENT10
FROM APPS.GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID=HCSU.GL_ID_REV
) "Accounts Revenue" ,
(SELECT SEGMENT1
||'-'
||SEGMENT2
||'-'
||SEGMENT3
||'-'
||SEGMENT4
||'-'
||SEGMENT5
||'-'
||SEGMENT6
||'-'
||SEGMENT7
||'-'
||SEGMENT8
||'-'
||SEGMENT9
||'-'
||SEGMENT10
FROM APPS.GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID=HCSU.GL_ID_FREIGHT
)"Accounts Freight" ,
HCSU.SHIP_VIA SHIPPING_METHOD ,
hca.account_name,
(SELECT DISTINCT NAME
FROM APPS.RA_SALESREPS_ALL
WHERE SALESREP_ID=HCSU.PRIMARY_SALESREP_ID
) "Sales Person" ,
HCSU.FREIGHT_TERM ,
(SELECT DISTINCT NAME
FROM APPS.SO_PRICE_LISTS
WHERE PRICE_LIST_ID=HCSU.PRICE_LIST_ID
) "Price List" ,
HCSU.FOB_POINT ,
(SELECT NAME FROM APPS.RA_TERMS WHERE TERM_ID=HCSU.PAYMENT_TERM_ID
) "Payment Term" ,
(SELECT DISTINCT NAME
FROM APPS.OE_TRANSACTION_TYPES_TL
WHERE UPPER(TRANSACTION_TYPE_ID)=HCSU.ORDER_TYPE_ID
) "Order Type",
hps.addressee,
hcsu.tax_classification,
HCSU.global_attribute_category Context_value,
hcsu.attribute1 recipt_Ack,
hca.account_number Customer_number,
hp.party_id,
hps.party_site_id,
hps.location_id,
hp.party_number ,
HCS.org_id,
hca.creation_date,
hca.created_by
FROM apps.HZ_PARTIES hp ,
apps.HZ_LOCATIONS hl ,
apps.hz_party_sites hps ,
apps.hz_cust_accounts_all hca,
APPS.HZ_CUST_ACCT_SITES_ALL HCS ,
APPS.HZ_CUST_SITE_USES_ALL HCSU
WHERE 1 =1
AND hps.party_id =hp.party_id
AND hca.party_id =hp.party_id
AND hcs.party_site_id=hps.party_site_id
AND HCS.CUST_ACCOUNT_ID =HCA.CUST_ACCOUNT_ID
AND HCS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID
AND hl.location_id=hps.location_id
AND HCS.org_id=2661
--AND hca.orig_system_reference like 'SP%'
ORDER BY hca.ORIG_SYSTEM_REFERENCE ;
--- To Extract Customer profiles
SELECT DISTINCT hca.orig_system_reference Orig_system_customer_ref,
hcsa.orig_system_reference Orig_system_address_ref,
hca.account_number Customer_Number,
(SELECT NAME
FROM APPS.HZ_CUST_PROFILE_CLASSES
WHERE profile_class_id=hpc.profile_class_id
) customer_profile_class_name ,
(SELECT name FROM apps.AR_COLLECTORS WHERE collector_id=hpc.COLLECTOR_ID
) Collector_Name ,
CREDIT_HOLD ,
CREDIT_CHECKING ,
(SELECT name FROM apps.RA_TERMS WHERE term_id=STANDARD_TERMS
) Term_Name ,
hpc.SEND_STATEMENTS "Send Statement" ,
(SELECT name
FROM apps.AR_STATEMENT_CYCLES as1
WHERE as1.STATEMENT_CYCLE_ID=hpc.STATEMENT_CYCLE_ID
) Cycle ,
DUNNING_LETTERS Send_Letters,
(SELECT name
FROM apps.ar_dunning_letter_sets adl
WHERE adl.dunning_letter_set_id=hpc.dunning_letter_set_id
) dunning_letter_set_name ,
(SELECT name
FROM apps.RA_GROUPING_RULES
WHERE GROUPING_RULE_ID=hpc.GROUPING_RULE_ID
) "Grouping Rule" ,
cpa.OVERALL_CREDIT_LIMIT ,
cpa.trx_credit_limit ,
cpa.CURRENCY_CODE,
credit_balance_statements,
hpc.cust_account_id,
hpc.site_use_id,
hcsa.org_id,
hpc.creation_date,
hpc.created_by
FROM apps.HZ_CUSTOMER_PROFILES hpc ,
apps.hz_cust_accounts_all hca,
apps.HZ_CUST_ACCT_SITES_ALL hcsa,
apps.hz_cust_site_uses_all sit_use,
APPS.HZ_CUST_PROFILE_AMTS CPA
WHERE 1 =1
AND hca.cust_account_id =hpc.cust_account_id
AND sit_use.cust_acct_site_id=hcsa.cust_acct_site_id(+)
AND CPA.cust_account_profile_id=hpc.cust_account_profile_id
AND sit_use.site_use_id(+)=hpc.site_use_id
AND hcsa.org_id=2661
--AND hca.orig_system_reference LIKE'SP%'
ORDER BY hca.orig_system_reference;
--To Extract Customer Contact Phones
select *from APPS.HZ_CONTACT_POINTS;SELECT DISTINCT hcp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_TELEPHONE_REF ,
acar.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_CONTACT_REF ,
hca.orig_system_reference Orig_system_Customer_ref ,
hcsa.orig_system_reference Orig_system_address_ref ,
hca.account_number Customer_Number,
hcp.PHONE_NUMBER Telephone_Numbe ,
hcp.PHONE_EXTENSION Extension ,
hcp.EMAIL_ADDRESS Mail ,
hcp.PHONE_LINE_TYPE Type ,
hcp.PHONE_AREA_CODE Telephone_Area_Code ,
hcp.PHONE_COUNTRY_CODE Phone_Country_Code ,
hcp.CONTACT_POINT_TYPE Contact_Point_Type,
hp.person_first_name First_Name,
hp.person_last_name Last_Name,
acar.cust_account_id ,
acar.cust_acct_site_id ,
hcsa.org_id,
hcp.creation_date,
hcp.created_by
FROM apps.HZ_CUST_ACCOUNT_ROLES acar ,
apps.hz_cust_accounts hca,
apps.HZ_CUST_ACCT_SITES_ALL hcsa,
apps.hz_parties hp,
apps.hz_relationships hr,
APPS.HZ_CONTACT_POINTS hcp
WHERE 1 =1
AND hca.cust_account_id =acar.cust_account_id
AND hcsa.cust_acct_site_id(+)=acar.Cust_acct_site_id
AND hp.party_id = hr.object_id
and acar.party_id = hr.party_id
and hr.party_id=hcp.owner_table_id
and hcp.owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES')
and hr.relationship_code = 'CONTACT'
--AND hca.ORIG_SYSTEM_REFERENCE like 'SP%'
and hcsa.org_id=2661;
-- To Extract Customers Only Telephone references which are not having contact reference
SELECT DISTINCT hcp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_TELEPHONE_REF ,
hcp.PHONE_NUMBER Telephone_Numbe ,
hcp.PHONE_EXTENSION Extension ,
hcp.EMAIL_ADDRESS Mail ,
hcp.PHONE_LINE_TYPE Type ,
hcp.PHONE_AREA_CODE Telephone_Area_Code ,
hcp.PHONE_COUNTRY_CODE Phone_Country_Code ,
hcp.CONTACT_POINT_TYPE Contact_Point_Type ,
hcp.creation_date,
hcp.created_by
FROM
APPS.HZ_CONTACT_POINTS hcp
WHERE 1 =1
AND hcp.ORIG_SYSTEM_REFERENCE like 'SP%%'
and not exists (select 1 from hz_relationships hr where hr.party_id=hcp.owner_table_id);
---Customer relationships
SELECT CUST_ACCOUNT_ID ,
(SELECT ORIG_SYSTEM_REFERENCE
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.CUST_ACCOUNT_ID
) ORIG_SYSTEM_REFERENCE,
(SELECT ACCOUNT_NUMBER
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.CUST_ACCOUNT_ID
) CUSTOMER_NUMBER,
RELATED_CUST_ACCOUNT_ID,
(SELECT ORIG_SYSTEM_REFERENCE
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.RELATED_CUST_ACCOUNT_ID
) RELATED_ORIG_SYSTEM_REFERENCE,
(SELECT ACCOUNT_NUMBER
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.RELATED_CUST_ACCOUNT_ID
) RELATED_CUSTOMER_NUMBER,
RELATIONSHIP_TYPE,
comments,
CUSTOMER_RECIPROCAL_FLAG,
BILL_TO_FLAG,
SHIP_TO_FLAG,
hcar.creation_date,
hcar.created_by
FROM apps.hz_cust_acct_relate_all HCAR
where org_id=2661;
SELECT distinct hca.orig_system_reference "Orig System Customer Ref" ,
hcs.ORIG_SYSTEM_REFERENCE "Orig System address Ref" ,
HP.PARTY_NAME CUSTOMER_NAME ,
DECODE(hca.customer_type,'I','Internal','R','Extrenal',hca.customer_type) Customer_Type,
hca.status,
hp.ORGANIZATION_NAME_PHONETIC customer_alt_name,
hl.country ,
hl.address1 ,
hl.address2 ,
hl.address3 ,
hl.address4 ,
hl.ADDRESS_LINES_PHONETIC Alternate_Name ,
hl.city ,
hl.state ,
HL.POSTAL_CODE,
HCSU.SITE_USE_CODE USAGE ,
HCS.LANGUAGE ,
HCSU.LOCATION ,
HCSU.PRIMARY_FLAG ,
HCSU.TAX_CODE ,
HCSU.TAX_REFERENCE "Tax Registration Number" ,
(SELECT SEGMENT1
||'-'
||SEGMENT2
||'-'
||SEGMENT3
||'-'
||SEGMENT4
||'-'
||SEGMENT5
||'-'
||SEGMENT6
||'-'
||SEGMENT7
||'-'
||SEGMENT8
||'-'
||SEGMENT9
||'-'
||SEGMENT10
FROM APPS.GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID=HCSU.GL_ID_REC
)"Accounts Receivable" ,
(SELECT SEGMENT1
||'-'
||SEGMENT2
||'-'
||SEGMENT3
||'-'
||SEGMENT4
||'-'
||SEGMENT5
||'-'
||SEGMENT6
||'-'
||SEGMENT7
||'-'
||SEGMENT8
||'-'
||SEGMENT9
||'-'
||SEGMENT10
FROM APPS.GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID=HCSU.GL_ID_REV
) "Accounts Revenue" ,
(SELECT SEGMENT1
||'-'
||SEGMENT2
||'-'
||SEGMENT3
||'-'
||SEGMENT4
||'-'
||SEGMENT5
||'-'
||SEGMENT6
||'-'
||SEGMENT7
||'-'
||SEGMENT8
||'-'
||SEGMENT9
||'-'
||SEGMENT10
FROM APPS.GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID=HCSU.GL_ID_FREIGHT
)"Accounts Freight" ,
HCSU.SHIP_VIA SHIPPING_METHOD ,
hca.account_name,
(SELECT DISTINCT NAME
FROM APPS.RA_SALESREPS_ALL
WHERE SALESREP_ID=HCSU.PRIMARY_SALESREP_ID
) "Sales Person" ,
HCSU.FREIGHT_TERM ,
(SELECT DISTINCT NAME
FROM APPS.SO_PRICE_LISTS
WHERE PRICE_LIST_ID=HCSU.PRICE_LIST_ID
) "Price List" ,
HCSU.FOB_POINT ,
(SELECT NAME FROM APPS.RA_TERMS WHERE TERM_ID=HCSU.PAYMENT_TERM_ID
) "Payment Term" ,
(SELECT DISTINCT NAME
FROM APPS.OE_TRANSACTION_TYPES_TL
WHERE UPPER(TRANSACTION_TYPE_ID)=HCSU.ORDER_TYPE_ID
) "Order Type",
hps.addressee,
hcsu.tax_classification,
HCSU.global_attribute_category Context_value,
hcsu.attribute1 recipt_Ack,
hca.account_number Customer_number,
hp.party_id,
hps.party_site_id,
hps.location_id,
hp.party_number ,
HCS.org_id,
hca.creation_date,
hca.created_by
FROM apps.HZ_PARTIES hp ,
apps.HZ_LOCATIONS hl ,
apps.hz_party_sites hps ,
apps.hz_cust_accounts_all hca,
APPS.HZ_CUST_ACCT_SITES_ALL HCS ,
APPS.HZ_CUST_SITE_USES_ALL HCSU
WHERE 1 =1
AND hps.party_id =hp.party_id
AND hca.party_id =hp.party_id
AND hcs.party_site_id=hps.party_site_id
AND HCS.CUST_ACCOUNT_ID =HCA.CUST_ACCOUNT_ID
AND HCS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID
AND hl.location_id=hps.location_id
AND HCS.org_id=2661
--AND hca.orig_system_reference like 'SP%'
ORDER BY hca.ORIG_SYSTEM_REFERENCE ;
--- To Extract Customer profiles
SELECT DISTINCT hca.orig_system_reference Orig_system_customer_ref,
hcsa.orig_system_reference Orig_system_address_ref,
hca.account_number Customer_Number,
(SELECT NAME
FROM APPS.HZ_CUST_PROFILE_CLASSES
WHERE profile_class_id=hpc.profile_class_id
) customer_profile_class_name ,
(SELECT name FROM apps.AR_COLLECTORS WHERE collector_id=hpc.COLLECTOR_ID
) Collector_Name ,
CREDIT_HOLD ,
CREDIT_CHECKING ,
(SELECT name FROM apps.RA_TERMS WHERE term_id=STANDARD_TERMS
) Term_Name ,
hpc.SEND_STATEMENTS "Send Statement" ,
(SELECT name
FROM apps.AR_STATEMENT_CYCLES as1
WHERE as1.STATEMENT_CYCLE_ID=hpc.STATEMENT_CYCLE_ID
) Cycle ,
DUNNING_LETTERS Send_Letters,
(SELECT name
FROM apps.ar_dunning_letter_sets adl
WHERE adl.dunning_letter_set_id=hpc.dunning_letter_set_id
) dunning_letter_set_name ,
(SELECT name
FROM apps.RA_GROUPING_RULES
WHERE GROUPING_RULE_ID=hpc.GROUPING_RULE_ID
) "Grouping Rule" ,
cpa.OVERALL_CREDIT_LIMIT ,
cpa.trx_credit_limit ,
cpa.CURRENCY_CODE,
credit_balance_statements,
hpc.cust_account_id,
hpc.site_use_id,
hcsa.org_id,
hpc.creation_date,
hpc.created_by
FROM apps.HZ_CUSTOMER_PROFILES hpc ,
apps.hz_cust_accounts_all hca,
apps.HZ_CUST_ACCT_SITES_ALL hcsa,
apps.hz_cust_site_uses_all sit_use,
APPS.HZ_CUST_PROFILE_AMTS CPA
WHERE 1 =1
AND hca.cust_account_id =hpc.cust_account_id
AND sit_use.cust_acct_site_id=hcsa.cust_acct_site_id(+)
AND CPA.cust_account_profile_id=hpc.cust_account_profile_id
AND sit_use.site_use_id(+)=hpc.site_use_id
AND hcsa.org_id=2661
--AND hca.orig_system_reference LIKE'SP%'
ORDER BY hca.orig_system_reference;
--To Extract Customer Contact Phones
select *from APPS.HZ_CONTACT_POINTS;SELECT DISTINCT hcp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_TELEPHONE_REF ,
acar.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_CONTACT_REF ,
hca.orig_system_reference Orig_system_Customer_ref ,
hcsa.orig_system_reference Orig_system_address_ref ,
hca.account_number Customer_Number,
hcp.PHONE_NUMBER Telephone_Numbe ,
hcp.PHONE_EXTENSION Extension ,
hcp.EMAIL_ADDRESS Mail ,
hcp.PHONE_LINE_TYPE Type ,
hcp.PHONE_AREA_CODE Telephone_Area_Code ,
hcp.PHONE_COUNTRY_CODE Phone_Country_Code ,
hcp.CONTACT_POINT_TYPE Contact_Point_Type,
hp.person_first_name First_Name,
hp.person_last_name Last_Name,
acar.cust_account_id ,
acar.cust_acct_site_id ,
hcsa.org_id,
hcp.creation_date,
hcp.created_by
FROM apps.HZ_CUST_ACCOUNT_ROLES acar ,
apps.hz_cust_accounts hca,
apps.HZ_CUST_ACCT_SITES_ALL hcsa,
apps.hz_parties hp,
apps.hz_relationships hr,
APPS.HZ_CONTACT_POINTS hcp
WHERE 1 =1
AND hca.cust_account_id =acar.cust_account_id
AND hcsa.cust_acct_site_id(+)=acar.Cust_acct_site_id
AND hp.party_id = hr.object_id
and acar.party_id = hr.party_id
and hr.party_id=hcp.owner_table_id
and hcp.owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES')
and hr.relationship_code = 'CONTACT'
--AND hca.ORIG_SYSTEM_REFERENCE like 'SP%'
and hcsa.org_id=2661;
-- To Extract Customers Only Telephone references which are not having contact reference
SELECT DISTINCT hcp.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_TELEPHONE_REF ,
hcp.PHONE_NUMBER Telephone_Numbe ,
hcp.PHONE_EXTENSION Extension ,
hcp.EMAIL_ADDRESS Mail ,
hcp.PHONE_LINE_TYPE Type ,
hcp.PHONE_AREA_CODE Telephone_Area_Code ,
hcp.PHONE_COUNTRY_CODE Phone_Country_Code ,
hcp.CONTACT_POINT_TYPE Contact_Point_Type ,
hcp.creation_date,
hcp.created_by
FROM
APPS.HZ_CONTACT_POINTS hcp
WHERE 1 =1
AND hcp.ORIG_SYSTEM_REFERENCE like 'SP%%'
and not exists (select 1 from hz_relationships hr where hr.party_id=hcp.owner_table_id);
---Customer relationships
SELECT CUST_ACCOUNT_ID ,
(SELECT ORIG_SYSTEM_REFERENCE
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.CUST_ACCOUNT_ID
) ORIG_SYSTEM_REFERENCE,
(SELECT ACCOUNT_NUMBER
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.CUST_ACCOUNT_ID
) CUSTOMER_NUMBER,
RELATED_CUST_ACCOUNT_ID,
(SELECT ORIG_SYSTEM_REFERENCE
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.RELATED_CUST_ACCOUNT_ID
) RELATED_ORIG_SYSTEM_REFERENCE,
(SELECT ACCOUNT_NUMBER
FROM hz_cust_accounts_all
WHERE CUST_ACCOUNT_ID=HCAR.RELATED_CUST_ACCOUNT_ID
) RELATED_CUSTOMER_NUMBER,
RELATIONSHIP_TYPE,
comments,
CUSTOMER_RECIPROCAL_FLAG,
BILL_TO_FLAG,
SHIP_TO_FLAG,
hcar.creation_date,
hcar.created_by
FROM apps.hz_cust_acct_relate_all HCAR
where org_id=2661;
No comments:
Post a Comment