AR Customer Profile Amount Details (SQL Script)

Enterprise Resource Planning (ERP) Account Receivables (AR)
1

This AR Customer Profile Amount Details query provides a Customer Credit and Profile Settings Report, extracting customer-level financial and risk parameters from Oracle Receivables.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query provides the customer credit and profile settings details
  SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  HCPMF.CURRENCY_CODE ,
  NVL(HCPMF.INTEREST_RATE, 0) INTEREST_RATE,
  NVL(HCPMF.MAX_INTEREST_CHARGE, 0) MAXIMUM_INTEREST_CHARGE,
  NVL(HCPMF.MIN_FC_BALANCE_AMOUNT, 0) MINIMUM_FUNCTIONAL_BALANCE_AMOUNT,
  NVL(HCPMF.MIN_FC_INVOICE_AMOUNT, 0) MINIMUM_FUNCTIONAL_INVOICE_AMOUNT,
  NVL(HCPMF.MIN_STATEMENT_AMOUNT, 0) MINIMUM_STATEMENT_AMOUNT,
  NVL(HCPMF.AUTO_REC_MIN_RECEIPT_AMOUNT,0) MINIMUM_RECEIPT_AMOUNT,
  NVL(HCPMF.MIN_DUNNING_AMOUNT, 0) MINIMUM_DUNNING_AMOUNT,
  NVL(HCPMF.MIN_DUNNING_INVOICE_AMOUNT,0) MINIMUM_DUNNING_INVOICE_AMOUNT,
  HCPMF.OVERALL_CREDIT_LIMIT,
  HCPMF.TRX_CREDIT_LIMIT TRANSACTION_CREDIT_LIMIT,
  HP.CATEGORY_CODE,
  HCPF.CONS_INV_TYPE BILL_TYPE,
  HCPF.CREDIT_RATING,
  HCA.CUSTOMER_TYPE,
  HCPF.LOCKBOX_MATCHING_OPTION,
  HCPF.RISK_CODE,
  HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
  HCPF.TAX_PRINTING_OPTION,
  HCA.TAX_ROUNDING_RULE
FROM
  HZ_CUSTOMER_PROFILES_F HCPF,
  HZ_CUST_PROFILE_AMTS_F HCPMF,  
  HZ_CUST_ACCOUNTS HCA,  
  HZ_PARTIES HP
WHERE
  1 = 1
  AND HCPF.CUST_ACCOUNT_PROFILE_ID =HCPMF.CUST_ACCOUNT_PROFILE_ID(+)
  AND HCPF.CUST_ACCOUNT_ID=HCA.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID = HP.PARTY_ID