信用承受風險報表資料來源 Credit Exposure Report data source－Chuck Oracle Engineer

R12.2.9 標準報表: 信用承受風險報 Credit Exposure Report 資料來源

執行信用承受風險報 Credit Exposure Report (OEXCEXP.rdf)
OEXCEXP.rdf 報表中 Before Report 會呼叫 OE_CREDIT_CHECK_RPT.Credit_exposure_report_utils (註1)
其中的 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure 為主要回傳值的地方 (註2)
資料會寫入 OE_CREDIT_EXPOSURE_TEMP (此為 Global temporary table) (註3)

其中第三點 (請參閱註2程式碼)

若 p_need_exposure_details = 'Y' 則可顯示後四個參數值，就可以得知每個模組所占額度是多少；若為 N則僅顯示加總 l_party_total_exposure

l_party_total_exposure
l_order_hold_amount
l_order_amount
l_ar_amount
l_external_amount

後續再研究四個參數值是怎麼抓的

註1

OE_CREDIT_CHECK_RPT.Credit_exposure_report_utils
(
   p_report_by_option   => :P_REPORT_BY_OPTION,
   p_specific_party_id => :P_SPECIFIC_PARTY_ID,
   p_spec_party_num_id => :P_SPEC_PARTY_NUM_ID,
   p_party_name_low     => :P_PARTY_NAME_LOW,
   p_party_name_high    => :P_PARTY_NAME_HIGH,
   p_party_number_low   => :P_PARTY_NUMBER_LOW,
   p_party_number_high => :P_PARTY_NUMBER_HIGH,
   p_prof_class_low     => :p_prof_class_low,
   p_prof_class_high    => :p_prof_class_high,
   p_customer_name_low => :P_CUSTOMER_NAME_LOW,
   p_customer_name_high => :P_CUSTOMER_NAME_HIGH,
   p_cust_number_low    => :P_CUST_NUMBER_LOW,
   p_cust_number_high   => :P_CUST_NUMBER_HIGH,
   p_cr_check_rule_id   => :P_CR_CHECK_RULE_ID,
   p_org_id             => :P_ORG_ID,
   x_return_status      => :P_RETURN_STATUS
);

註2

OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
(
   p_party_id                => party_csr_rec.party_id,
   p_customer_id             => NULL,
   p_site_id                 => NULL,
   p_limit_curr_code         => party_prof_csr_rec.party_currency_code,
   p_credit_check_rule_id    => p_cr_check_rule_id,
   p_need_exposure_details   => 'N',
   x_total_exposure          => l_party_total_exposure,
   x_order_hold_amount       => l_order_hold_amount,
   x_order_amount            => l_order_amount,
   x_ar_amount               => l_ar_amount,
   x_external_amount         => l_external_amount,
   x_return_status           => l_return_status
);

註3

SELECT a.customer_name,
       a.customer_number,
       a.customer_id,
       a.bill_to_site_id,
       a.base_currency,
       a.unchecked_exposure,
       a.credit_limit_currency,
       ROUND(a.cr_cur_overall_limit, c.PRECISION) cr_cur_overall_limit,
       ROUND(a.cr_cur_exposure, c.PRECISION) cr_cur_exposure,
       ROUND(a.cr_cur_available, c.PRECISION) cr_cur_available,
       ROUND(DECODE(a.base_cur_overall_limit,
                    -1, TO_NUMBER(NULL),
                    -2, TO_NUMBER(NULL),
                    a.base_cur_overall_limit), b.PRECISION) base_cur_overall_limit,
       ROUND(DECODE(a.base_cur_exposure,
                    -1, TO_NUMBER(NULL),
                    -2, TO_NUMBER(NULL),
                    a.base_cur_exposure), b.PRECISION) base_cur_exposure,
       ROUND(DECODE(a.base_cur_available,
                    -1, TO_NUMBER(NULL),
                    -2, TO_NUMBER(NULL),
                    a.base_cur_available), b.PRECISION) base_cur_available,
       DECODE(a.global_exposure_flag, 'Y', '*', NULL) global_exposure_flag,
       ROUND(NVL2(a.cr_cur_overall_limit, DECODE(a.base_cur_exposure,
                                                 -1, TO_NUMBER (NULL),
                                                 -2, TO_NUMBER (NULL),
                                                 a.base_cur_exposure), 0), b.PRECISION) base_cur_exposure_temp --Bug15964514
FROM oe_credit_exposure_temp a, fnd_currencies b, fnd_currencies c
WHERE a.bill_to_site_id IS NULL
   AND a.base_currency = b.currency_code
   AND NVL (a.credit_limit_currency, a.base_currency) = c.currency_code