11i与R12中Customer的一段信息脚本

**************************************11i

SELECT ou.name ou_name,
       hp.creation_date,
       hp.party_name customer_name,
       hp.organization_name_phonetic Alternate_Name,
       hcal.account_number customer_number,
       hp.tax_reference  Tax_Registration_Number,
       hcal.attribute1 Customer_Type,
       (SELECT meaning
         FROM  apps.ar_lookups
         WHERE lookup_type = 'CUSTOMER CLASS'
           AND lookup_code = hcal.customer_class_code) Classification,
       decode(hcal.customer_type, 'I','Internal','R', 'External') External_Type,
       decode(hcal.status,'A', 'Active') CUSTOMER_STATUS,
       bill_loc.country,
       bill_loc.address1,
       bill_loc.address2,
       bill_loc.city city,
       bill_loc.PROVINCE province,
       decode(hcasa.status, 'A', 'Active') Address_Active,
       hcsu.site_use_code usage,
       hcsu.location location,
       decode(hcsu.status, 'A', 'Active') Site_Usage_Active,
       hcsu.tax_code tax_code,
       term.name Payment_Terms,      
       rat.segment2 || DECODE(rat.segment2, NULL, NULL, '.') ||
       rat.segment1 || DECODE(rat.segment1, NULL, NULL, '.') ||
       rat.segment3 Sales_territory,
       sr.name SALES_Person,
       ar_bank.bank_account_num account_number,
       ar_bank.bank_account_name account_name, 
       ar_bank.start_date,
       ar_bank.end_date,
       hcp_ph.phone_country_code || ' ' || hcp_ph.phone_number phone_number,
       hcp_ph.status phone_status
   FROM apps.hz_parties hp,
        apps.hz_cust_accounts_all hcal,
        apps.hz_cust_acct_sites_all hcasa,
        (SELECT organization_id, name
          FROM apps.hr_organization_units
          WHERE TYPE = 'OU') ou,
        apps.hz_cust_site_uses_all hcsu,
        apps.hz_party_sites bill_ps,
        apps.hz_locations bill_loc,
        apps.jtf_rs_salesreps sr,
        apps.RA_TERRITORIES rat,
        apps.RA_TERMS term,
        apps.HZ_CONTACT_POINTS hcp_ph,
        (SELECT DECODE(NVL(FND_PROFILE.VALUE('AR_MASK_BANK_ACCOUNT_NUMBERS'),'F'),'N',
                      BACCT.BANK_ACCOUNT_NUM,'F',
                      RPAD(SUBSTR(BACCT.BANK_ACCOUNT_NUM, 1, 4),
                           LENGTH(BACCT.BANK_ACCOUNT_NUM),
                           '*'), 'L',
                      LPAD(SUBSTR(BACCT.BANK_ACCOUNT_NUM, -4),
                           LENGTH(BACCT.BANK_ACCOUNT_NUM),
                           '*')) BANK_ACCOUNT_NUM,
               BACCT.BANK_ACCOUNT_NAME,
               BAUSES.START_DATE,
               BAUSES.END_DATE,
               BAUSES.PRIMARY_FLAG,
               BAUSES.CUSTOMER_ID,
               BAUSES.CUSTOMER_SITE_USE_ID
          FROM apps.AP_BANK_ACCOUNT_USES_ALL BAUSES,
               apps.AP_BANK_BRANCHES         BBNCH,
               apps.AP_BANK_ACCOUNTS_ALL     BACCT
         WHERE BAUSES.EXTERNAL_BANK_ACCOUNT_ID = BACCT.BANK_ACCOUNT_ID
           AND BACCT.BANK_BRANCH_ID = BBNCH.BANK_BRANCH_ID
           ) ar_bank
 WHERE hp.party_id = hcal.party_id
   AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id
   AND hcal.cust_account_id = hcasa.cust_account_id
   AND hcasa.org_id = ou.organization_id
   AND hcsu.site_use_code in ('BILL_TO', 'SHIP_TO')
   and hcasa.party_site_id = bill_ps.party_site_id(+)
   and bill_ps.location_id = bill_loc.location_id(+)
   and hcsu.territory_id = rat.territory_id(+)
   AND hcsu.PAYMENT_TERM_ID = TERM.TERM_ID(+)
   AND hcsu.primary_salesrep_id = sr.salesrep_id(+)
   AND bill_ps.party_site_id = hcp_ph.owner_table_id(+)
   AND hcp_ph.contact_point_type(+) = 'PHONE'
   AND hcp_ph.phone_line_type(+) = 'GEN'
   and hcsu.site_use_id = ar_bank.CUSTOMER_SITE_USE_ID(+)
   and hcal.status = 'A'
   and hcasa.status = 'A'
   and hcsu.status = 'A'
   and hcp_ph.owner_table_name = 'HZ_PARTY_SITES'

**************************************R12

SELECT ieba.BANK_ACCOUNT_NUM,--bank.account_number,
       bank.bank_name,
       ou.name ou_name,
       hp.party_id,
       hcal.cust_account_id customer_id,
       bill_ps.party_site_id ,
       hcsu.cust_acct_site_id,
       hcal.account_number customer_number,
       hp.party_name customer_name,
       hcsu.tax_reference  Tax_Registration_Number,
       hp.creation_date,
       hp.known_as alias,
       hp.organization_name_phonetic Name_Pronunciation,
       (SELECT meaning
       
          FROM apps.ar_lookups
         WHERE lookup_type = 'CUSTOMER CLASS'
           AND lookup_code = hcal.customer_class_code) Classification,
       decode(hcal.customer_type, 'Internal', 'External') customer_type,
       hcal.status STATUS,
       hcal.attribute16 locator_code,
       hcal.attribute1 Customer_Type,
       hcal.attribute2 Internal_Usage_Expense_Account,
      
       hcasa.status Active,
       hcasa.org_id,
       bill_ps.party_site_NUMBER site_numbe,
       hcsu.site_use_code,
       bill_loc.country,
       bill_loc.address1,
       bill_loc.address2,
       bill_loc.address3,
       bill_loc.address4,
       bill_loc.city city,
       bill_loc.PROVINCE province,
       bill_loc.county,
       bill_loc.postal_code,
       hcsu.location location,
       hcp_ph.phone_country_code || ' ' || hcp_ph.phone_number phone_number,
      
       hcsu.tax_code tax_code,
       rat.segment2 || DECODE(rat.segment2, NULL, NULL, '.') ||
       rat.segment1 || DECODE(rat.segment1, NULL, NULL, '.') ||
       rat.segment3 Sales_territory,
       sr.name SALES_Person,
       term.name Payment_Terms,
       (select gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
               gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6
          from apps.gl_code_combinations gcc
         where gcc.code_combination_id = hcsu.gl_id_rec) Receivable,
       (select gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
               gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6
          from apps.gl_code_combinations gcc
         where gcc.code_combination_id = hcsu.gl_id_rev) Revenue,
       (select gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
               gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6
          from apps.gl_code_combinations gcc
         where gcc.code_combination_id = hcsu.gl_id_tax) Tax,
      
       (select gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
               gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6
          from apps.gl_code_combinations gcc
         where gcc.code_combination_id = hcsu.gl_id_freight) Freight,
      
       (select gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
               gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6
          from apps.gl_code_combinations gcc
         where gcc.code_combination_id = hcsu.gl_id_unbilled) Unbilled_Receivable,
       (select gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
               gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6
          from apps.gl_code_combinations gcc
         where gcc.code_combination_id = hcsu.gl_id_unearned) Unearned_Revenue
 
  FROM apps.hz_parties hp,
       apps.hz_cust_accounts_all hcal,
       apps.hz_cust_acct_sites_all hcasa,
       (SELECT organization_id, name
          FROM apps.hr_organization_units
         WHERE TYPE = 'OU') ou,
       apps.hz_cust_site_uses_all hcsu,
       apps.hz_party_sites bill_ps,
       apps.hz_locations bill_loc,
       apps.jtf_rs_salesreps sr,
       apps.RA_TERRITORIES rat,
       apps.RA_TERMS term,
       apps.HZ_CONTACT_POINTS hcp_ph,
       apps.IBY_FNDCPT_PAYER_ASSGN_INSTR_V bank  ,
       APPS.IBY_EXT_BANK_ACCOUNTS ieba
       --apps.IBY_EXT_BANK_ACCOUNTS bank
      
 WHERE hp.party_id = hcal.party_id
   AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id
   AND hcal.cust_account_id = hcasa.cust_account_id
   AND hcasa.org_id = ou.organization_id
   AND hcsu.site_use_code in ('BILL_TO', 'SHIP_TO')
   and hcasa.party_site_id = bill_ps.party_site_id(+)
   and bill_ps.location_id = bill_loc.location_id(+)
   and hcsu.territory_id = rat.territory_id(+)
   AND hcsu.PAYMENT_TERM_ID = TERM.TERM_ID(+)
   AND hcsu.primary_salesrep_id = sr.salesrep_id(+)
   AND bill_ps.party_site_id = hcp_ph.owner_table_id(+)
   AND hcp_ph.contact_point_type(+) = 'PHONE'
   AND hcp_ph.phone_line_type(+) = 'GEN'
   AND ou.name in ('OU:HUTS', 'OU:HSTC', 'OU:UTSC')
   and bank.acct_site_use_id(+)=hcsu.site_use_id
   AND ieba.EXT_BANK_ACCOUNT_ID = bank.instrument_id
   and hcp_ph.owner_table_name = 'HZ_PARTY_SITES'