AR Customer Credit Profile Listings (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Customer Credit Profile Listing Report query retrieves comprehensive customer profile details, including account, credit, payment, and statement preferences.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves comprehensive customer profile details
  HP.PARTY_NAME CUSTOMER_NAME,
  HP.ORIG_SYSTEM_REFERENCE CUSTOMER_IDENTIFIER,
  HP.PARTY_NUMBER CUSTOMER_NUMBER,
  HP.PARTY_TYPE CUSTOMER_TYPE,
  HP.DUNS_NUMBER_C DUNS_NUMBER,
  HP.CATEGORY_CODE CUSTOMER_CATEGORY,
  HCA.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
  HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
  HCP.CREDIT_CHECKING,
  HCP.TOLERANCE,
  DECODE(
    HCP.CREDIT_HOLD,
    'N',
    'No',
    'Y',
    'Yes',
    HCP.CREDIT_HOLD
  ) CREDIT_HOLD,
  HCP.CREDIT_BALANCE_STATEMENTS,
  HCP.SEND_STATEMENTS,
  HCP.INTEREST_CHARGES LATE_CHARGES,
  HCP.DUNNING_LETTERS,
  HCP.DISCOUNT_TERMS,
  HCP.INTEREST_PERIOD_DAYS,
  HCP.PAYMENT_GRACE_DAYS,
  HCP.DISCOUNT_GRACE_DAYS,
  HCP.AUTO_REC_INCL_DISPUTED_FLAG,
  HCP.ATTRIBUTE_CATEGORY,
  HCPC.NAME PROFILE_CLASS_NAME,
  AC.NAME COLLECTOR_NAME,
  ARSC.NAME STATEMENT_CYCLE_NAME,
  RT.NAME PAYMENT_TERM_NAME,
  RGR.NAME GROUPING_RULES_NAME,
  HCSUA.ORIG_SYSTEM_REFERENCE SITE_USE_IDENTIFIER,
  HCPCA.TRX_CREDIT_LIMIT TRANSACTION_CREDIT_LIMIT,
  HCPCA.OVERALL_CREDIT_LIMIT CUSTOMER_CREDIT_LIMIT,
  HCPCA.MIN_DUNNING_AMOUNT MINIMUM_DUNNING_AMOUNT,
  HCPCA.MIN_DUNNING_INVOICE_AMOUNT MINIMUM_DUNNING_INVOICE_AMOUNT,
  HCPCA.MAX_INTEREST_CHARGE MAXIMUM_INTEREST_CHARGE,
  HCPCA.MIN_STATEMENT_AMOUNT MINIMUM_STATEMENT_AMOUNT,
  HCPCA.AUTO_REC_MIN_RECEIPT_AMOUNT MINIMUM_RECEIPT_AMOUNT,
  HCPCA.INTEREST_RATE,
  HCPCA.MIN_FC_BALANCE_AMOUNT CUSTOMER_THRESHOLD_BALANCE_AMOUNT,
  HCPCA.MIN_FC_INVOICE_AMOUNT CUSTOMER_THRESHOLD_INVOICE_AMOUNT
FROM
  HZ_PARTIES HP,
  HZ_CUST_ACCOUNTS HCA,
  HZ_CUSTOMER_PROFILES_F HCP,
  HZ_CUST_PROFILE_CLASSES HCPC,
  AR_COLLECTORS AC,
  AR_STATEMENT_CYCLES ARSC,
  RA_TERMS RT,
  RA_GROUPING_RULES RGR,
  HZ_CUST_SITE_USES_ALL HCSUA,
  HZ_CUST_PROF_CLASS_AMTS HCPCA
WHERE
  HP.PARTY_ID = HCA.PARTY_ID
  AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID
  AND HCP.PROFILE_CLASS_ID = HCPC.PROFILE_CLASS_ID
  AND HCP.COLLECTOR_ID = AC.COLLECTOR_ID
  AND HCP.STATEMENT_CYCLE_ID = ARSC.STATEMENT_CYCLE_ID
  AND HCP.STANDARD_TERMS = RT.TERM_ID
  AND HCP.GROUPING_RULE_ID = RGR.GROUPING_RULE_ID
  AND HCP.SITE_USE_ID = HCSUA.SITE_USE_ID
  AND HCP.PROFILE_CLASS_ID = HCPCA.PROFILE_CLASS_ID