VIEW: APPS.AR_INTEREST_HEADERS_V

Source

View Text - Preformatted

SELECT h.INTEREST_HEADER_ID, h.INTEREST_BATCH_ID, h.CUSTOMER_ID, h.CUSTOMER_SITE_USE_ID, h.CURRENCY_CODE, h.EXCHANGE_RATE_TYPE, ct.user_conversion_type exchange_rate_type_dsp, h.EXCHANGE_RATE, party.PARTY_NAME, c.ACCOUNT_NUMBER, su.LOCATION, h.ORG_ID, arp_addr_pkg.format_address ( addr.address_style, addr.address1, addr.address2, addr.address3, addr.address4, addr.city, addr.county, addr.state, addr.province, addr.postal_code, terr.territory_short_name) CONCATENATED_ADDRESS, h.COLLECTOR_ID, h.CUSTOMER_PROFILE_ID, ar_interest_headers_pkg.get_header_amount(h.interest_header_id) header_amount, h.HEADER_TYPE , ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_LATE_CHARGE_TYPE',h.HEADER_TYPE) header_type_m, h.LATE_CHARGE_CALCULATION_TRX, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('AR_MANDATORY_LATE_CHARGES',h.LATE_CHARGE_CALCULATION_TRX) LATE_CHARGE_CALCULATION_TRX_m, h.CREDIT_ITEMS_FLAG, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('YES/NO',h.CREDIT_ITEMS_FLAG) credit_items_flag_m, h.DISPUTED_TRANSACTIONS_FLAG, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('YES/NO',h.DISPUTED_TRANSACTIONS_FLAG) DISPUTED_TRANSACTIONS_FLAG_m, h.PAYMENT_GRACE_DAYS, h.LATE_CHARGE_TERM_ID, rt.name late_charge_term_name, h.INTEREST_PERIOD_DAYS, h.INTEREST_CALCULATION_PERIOD, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_CALCULATION_PERIOD',h.INTEREST_CALCULATION_PERIOD) INTEREST_CALCULATION_PERIOD_m, h.CHARGE_ON_FINANCE_CHARGE_FLAG, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('YES/NO',h.CHARGE_ON_FINANCE_CHARGE_FLAG) formula_m, h.HOLD_CHARGED_INVOICES_FLAG, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('YES/NO',h.HOLD_CHARGED_INVOICES_FLAG) HOLD_CHARGED_INVOICES_FLAG_m, h.MESSAGE_TEXT_ID, msg.name message_text_name, h.MULTIPLE_INTEREST_RATES_FLAG, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('YES/NO',h.MULTIPLE_INTEREST_RATES_FLAG) MULTIPLE_INTEREST_RATES_FLAG_m, h.CHARGE_BEGIN_DATE, h.CUST_ACCT_PROFILE_AMT_ID, h.MIN_FC_INVOICE_OVERDUE_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_AMOUNT_PERCENT',h.MIN_FC_INVOICE_OVERDUE_TYPE) MIN_FC_INVOICE_OVERDUE_TYPE_m, h.MIN_FC_INVOICE_AMOUNT, h.MIN_FC_INVOICE_PERCENT, DECODE(h.MIN_FC_INVOICE_OVERDUE_TYPE,'AMOUNT',h.MIN_FC_INVOICE_AMOUNT,h.MIN_FC_INVOICE_PERCENT) MIN_FC_INVOICE_VALUE, h.MIN_FC_BALANCE_OVERDUE_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_AMOUNT_PERCENT',h.MIN_FC_BALANCE_OVERDUE_TYPE) MIN_FC_BALANCE_OVERDUE_TYPE_m, h.MIN_FC_BALANCE_AMOUNT, h.MIN_FC_BALANCE_PERCENT, DECODE(h.MIN_FC_BALANCE_OVERDUE_TYPE,'AMOUNT',h.MIN_FC_BALANCE_AMOUNT,h.MIN_FC_BALANCE_PERCENT) MIN_FC_BALANCE_VALUE, h.MIN_INTEREST_CHARGE, h.MAX_INTEREST_CHARGE, h.INTEREST_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_INTEREST_PENALTY_TYPE',h.INTEREST_TYPE) INTEREST_TYPE_m, h.INTEREST_RATE, h.INTEREST_FIXED_AMOUNT, h.INTEREST_SCHEDULE_ID, intsch.schedule_name INTEREST_SCHEDULE_NAME, DECODE(h.INTEREST_TYPE,'FIXED_AMOUNT',h.INTEREST_FIXED_AMOUNT, 'FIXED_RATE' ,h.INTEREST_RATE,h.INTEREST_SCHEDULE_ID) interest_type_value, h.PENALTY_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_INTEREST_PENALTY_TYPE',h.PENALTY_TYPE) PENALTY_TYPE_m, h.PENALTY_RATE, h.PENALTY_FIXED_AMOUNT, h.PENALTY_SCHEDULE_ID, pensch.schedule_name PENALTY_SCHEDULE_NAME, DECODE(h.PENALTY_TYPE,'FIXED_AMOUNT',h.PENALTY_FIXED_AMOUNT, 'FIXED_RATE' ,h.PENALTY_RATE,h.INTEREST_SCHEDULE_ID) penalty_type_value, h.LAST_ACCRUE_CHARGE_DATE, h.LEGAL_ENTITY_ID, xlep.legal_entity_name legal_entity_name, h.PROCESS_MESSAGE, h.PROCESS_STATUS, ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS',h.PROCESS_STATUS) PROCESS_STATUS_m, h.CUST_TRX_TYPE_ID, tty.name trx_name, h.OBJECT_VERSION_NUMBER, h.LAST_UPDATED_BY, h.LAST_UPDATE_DATE, h.LAST_UPDATE_LOGIN, h.CREATED_BY, h.CREATION_DATE, h.display_flag FROM AR_INTEREST_HEADERS h, HZ_CUST_ACCOUNTS c, HZ_PARTIES party, HZ_CUST_SITE_USES su, HZ_CUST_ACCT_SITES site, HZ_PARTY_SITES ps, HZ_LOCATIONS addr, FND_TERRITORIES_VL terr, GL_DAILY_CONVERSION_TYPES ct , ra_terms rt, ar_standard_text msg, ar_charge_schedules intsch, ar_charge_schedules pensch, ra_cust_trx_types tty, (SELECT x.legal_entity_name legal_entity_name, x.LEGAL_ENTITY_ID LEGAL_ENTITY_ID, sysp.org_id org_id FROM xle_le_ou_ledger_V x, ar_system_parameters sysp WHERE sysp.org_id = x.OPERATING_UNIT_ID AND sysp.set_of_books_id = x.LEDGER_ID) xlep WHERE h.customer_id = c.cust_account_id AND h.customer_site_use_id = su.site_use_id AND su.cust_acct_site_id = site.cust_acct_site_id AND site.party_site_id = ps.party_site_id AND party.party_id =c.party_id(+) AND ps.location_id = addr.location_id AND addr.country = terr.territory_code AND ct.conversion_type(+) = h.exchange_rate_type AND h.LATE_CHARGE_TERM_ID = rt.term_id(+) AND h.MESSAGE_TEXT_ID = msg.standard_text_id(+) AND h.INTEREST_SCHEDULE_ID = intsch.SCHEDULE_ID(+) AND h.PENALTY_SCHEDULE_ID = pensch.SCHEDULE_ID(+) AND h.CUST_TRX_TYPE_ID = tty.cust_trx_type_id(+) AND h.legal_entity_id = xlep.legal_entity_id(+) AND h.org_id = xlep.org_id(+)

View Text - HTML Formatted

SELECT H.INTEREST_HEADER_ID
, H.INTEREST_BATCH_ID
, H.CUSTOMER_ID
, H.CUSTOMER_SITE_USE_ID
, H.CURRENCY_CODE
, H.EXCHANGE_RATE_TYPE
, CT.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP
, H.EXCHANGE_RATE
, PARTY.PARTY_NAME
, C.ACCOUNT_NUMBER
, SU.LOCATION
, H.ORG_ID
, ARP_ADDR_PKG.FORMAT_ADDRESS ( ADDR.ADDRESS_STYLE
, ADDR.ADDRESS1
, ADDR.ADDRESS2
, ADDR.ADDRESS3
, ADDR.ADDRESS4
, ADDR.CITY
, ADDR.COUNTY
, ADDR.STATE
, ADDR.PROVINCE
, ADDR.POSTAL_CODE
, TERR.TERRITORY_SHORT_NAME) CONCATENATED_ADDRESS
, H.COLLECTOR_ID
, H.CUSTOMER_PROFILE_ID
, AR_INTEREST_HEADERS_PKG.GET_HEADER_AMOUNT(H.INTEREST_HEADER_ID) HEADER_AMOUNT
, H.HEADER_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_LATE_CHARGE_TYPE'
, H.HEADER_TYPE) HEADER_TYPE_M
, H.LATE_CHARGE_CALCULATION_TRX
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('AR_MANDATORY_LATE_CHARGES'
, H.LATE_CHARGE_CALCULATION_TRX) LATE_CHARGE_CALCULATION_TRX_M
, H.CREDIT_ITEMS_FLAG
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('YES/NO'
, H.CREDIT_ITEMS_FLAG) CREDIT_ITEMS_FLAG_M
, H.DISPUTED_TRANSACTIONS_FLAG
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('YES/NO'
, H.DISPUTED_TRANSACTIONS_FLAG) DISPUTED_TRANSACTIONS_FLAG_M
, H.PAYMENT_GRACE_DAYS
, H.LATE_CHARGE_TERM_ID
, RT.NAME LATE_CHARGE_TERM_NAME
, H.INTEREST_PERIOD_DAYS
, H.INTEREST_CALCULATION_PERIOD
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_CALCULATION_PERIOD'
, H.INTEREST_CALCULATION_PERIOD) INTEREST_CALCULATION_PERIOD_M
, H.CHARGE_ON_FINANCE_CHARGE_FLAG
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('YES/NO'
, H.CHARGE_ON_FINANCE_CHARGE_FLAG) FORMULA_M
, H.HOLD_CHARGED_INVOICES_FLAG
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('YES/NO'
, H.HOLD_CHARGED_INVOICES_FLAG) HOLD_CHARGED_INVOICES_FLAG_M
, H.MESSAGE_TEXT_ID
, MSG.NAME MESSAGE_TEXT_NAME
, H.MULTIPLE_INTEREST_RATES_FLAG
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('YES/NO'
, H.MULTIPLE_INTEREST_RATES_FLAG) MULTIPLE_INTEREST_RATES_FLAG_M
, H.CHARGE_BEGIN_DATE
, H.CUST_ACCT_PROFILE_AMT_ID
, H.MIN_FC_INVOICE_OVERDUE_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_AMOUNT_PERCENT'
, H.MIN_FC_INVOICE_OVERDUE_TYPE) MIN_FC_INVOICE_OVERDUE_TYPE_M
, H.MIN_FC_INVOICE_AMOUNT
, H.MIN_FC_INVOICE_PERCENT
, DECODE(H.MIN_FC_INVOICE_OVERDUE_TYPE
, 'AMOUNT'
, H.MIN_FC_INVOICE_AMOUNT
, H.MIN_FC_INVOICE_PERCENT) MIN_FC_INVOICE_VALUE
, H.MIN_FC_BALANCE_OVERDUE_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_AMOUNT_PERCENT'
, H.MIN_FC_BALANCE_OVERDUE_TYPE) MIN_FC_BALANCE_OVERDUE_TYPE_M
, H.MIN_FC_BALANCE_AMOUNT
, H.MIN_FC_BALANCE_PERCENT
, DECODE(H.MIN_FC_BALANCE_OVERDUE_TYPE
, 'AMOUNT'
, H.MIN_FC_BALANCE_AMOUNT
, H.MIN_FC_BALANCE_PERCENT) MIN_FC_BALANCE_VALUE
, H.MIN_INTEREST_CHARGE
, H.MAX_INTEREST_CHARGE
, H.INTEREST_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_INTEREST_PENALTY_TYPE'
, H.INTEREST_TYPE) INTEREST_TYPE_M
, H.INTEREST_RATE
, H.INTEREST_FIXED_AMOUNT
, H.INTEREST_SCHEDULE_ID
, INTSCH.SCHEDULE_NAME INTEREST_SCHEDULE_NAME
, DECODE(H.INTEREST_TYPE
, 'FIXED_AMOUNT'
, H.INTEREST_FIXED_AMOUNT
, 'FIXED_RATE'
, H.INTEREST_RATE
, H.INTEREST_SCHEDULE_ID) INTEREST_TYPE_VALUE
, H.PENALTY_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_INTEREST_PENALTY_TYPE'
, H.PENALTY_TYPE) PENALTY_TYPE_M
, H.PENALTY_RATE
, H.PENALTY_FIXED_AMOUNT
, H.PENALTY_SCHEDULE_ID
, PENSCH.SCHEDULE_NAME PENALTY_SCHEDULE_NAME
, DECODE(H.PENALTY_TYPE
, 'FIXED_AMOUNT'
, H.PENALTY_FIXED_AMOUNT
, 'FIXED_RATE'
, H.PENALTY_RATE
, H.INTEREST_SCHEDULE_ID) PENALTY_TYPE_VALUE
, H.LAST_ACCRUE_CHARGE_DATE
, H.LEGAL_ENTITY_ID
, XLEP.LEGAL_ENTITY_NAME LEGAL_ENTITY_NAME
, H.PROCESS_MESSAGE
, H.PROCESS_STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_PROCESS_STATUS'
, H.PROCESS_STATUS) PROCESS_STATUS_M
, H.CUST_TRX_TYPE_ID
, TTY.NAME TRX_NAME
, H.OBJECT_VERSION_NUMBER
, H.LAST_UPDATED_BY
, H.LAST_UPDATE_DATE
, H.LAST_UPDATE_LOGIN
, H.CREATED_BY
, H.CREATION_DATE
, H.DISPLAY_FLAG
FROM AR_INTEREST_HEADERS H
, HZ_CUST_ACCOUNTS C
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES SITE
, HZ_PARTY_SITES PS
, HZ_LOCATIONS ADDR
, FND_TERRITORIES_VL TERR
, GL_DAILY_CONVERSION_TYPES CT
, RA_TERMS RT
, AR_STANDARD_TEXT MSG
, AR_CHARGE_SCHEDULES INTSCH
, AR_CHARGE_SCHEDULES PENSCH
, RA_CUST_TRX_TYPES TTY
, (SELECT X.LEGAL_ENTITY_NAME LEGAL_ENTITY_NAME
, X.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, SYSP.ORG_ID ORG_ID
FROM XLE_LE_OU_LEDGER_V X
, AR_SYSTEM_PARAMETERS SYSP
WHERE SYSP.ORG_ID = X.OPERATING_UNIT_ID
AND SYSP.SET_OF_BOOKS_ID = X.LEDGER_ID) XLEP
WHERE H.CUSTOMER_ID = C.CUST_ACCOUNT_ID
AND H.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND SITE.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND PARTY.PARTY_ID =C.PARTY_ID(+)
AND PS.LOCATION_ID = ADDR.LOCATION_ID
AND ADDR.COUNTRY = TERR.TERRITORY_CODE
AND CT.CONVERSION_TYPE(+) = H.EXCHANGE_RATE_TYPE
AND H.LATE_CHARGE_TERM_ID = RT.TERM_ID(+)
AND H.MESSAGE_TEXT_ID = MSG.STANDARD_TEXT_ID(+)
AND H.INTEREST_SCHEDULE_ID = INTSCH.SCHEDULE_ID(+)
AND H.PENALTY_SCHEDULE_ID = PENSCH.SCHEDULE_ID(+)
AND H.CUST_TRX_TYPE_ID = TTY.CUST_TRX_TYPE_ID(+)
AND H.LEGAL_ENTITY_ID = XLEP.LEGAL_ENTITY_ID(+)
AND H.ORG_ID = XLEP.ORG_ID(+)