--參考別人用的標準API,自己 改成SQL可呼叫的function .
CREATE OR REPLACE FUNCTION APPS.XXGET_CUSTOMER_EXPOSURE(I_ORG_ID IN NUMBER , I_CUSTOMER_ID IN NUMBER , I_SITE_ID IN NUMBER default null , I_LIMIT_CURR_CODE IN VARCHAR2 , I_CREDIT_CHECK_RULE_ID IN NUMBER DEFAULT 1041 )
RETURN number
AS
/********************************************************************/
/* Function name : APPS.XXGET_CUSTOMER_EXPOSURE */
/* Author : Nolem */
/* Creation : 2015/06/09 */
/* purporse: 透過OE_CREDIT_ENGINE_GRP.GET_CUSTOMER_EXPOSURE, */
/* 抓出標準信用額度承受風險後回傳 */
/* I_ORG_ID =>OU ID */
/* I_CUSTOMER_ID => Customer Account ID */
/* I_SITE_ID => Site Use ID ,default null for accout level */
/* I_LIMIT_CURR_CODE => Currency Code */
/* I_CREDIT_CHECK_RULE_ID => Credit_Check_Rule_ID(REDIT_CHECK_RULES ) Default BOOK id */
/* Retun number => account or site level exposure amount , */
/* if error occured then return -99999999 */
/* 使用範例SQL
/* SELECT org_id,
SITE_USE_ID,
CUST_ACCT_SITE_ID,
CUST_ACCOUNT_ID,
ACCOUNT_NAME,
ACCOUNT_NUMBER,
SALES_DEPARTMENT,
S_CURRENCY_CODE2,
S_OVERALL_CREDIT_LIMIT2,
CASE
WHEN S_OVERALL_CREDIT_LIMIT1 > 0 THEN S_CURRENCY_CODE1
WHEN S_OVERALL_CREDIT_LIMIT2 > 0 THEN S_CURRENCY_CODE2
WHEN S_OVERALL_CREDIT_LIMIT3 > 0 THEN S_CURRENCY_CODE3
WHEN S_OVERALL_CREDIT_LIMIT4 > 0 THEN S_CURRENCY_CODE4
WHEN S_OVERALL_CREDIT_LIMIT5 > 0 THEN S_CURRENCY_CODE5
ELSE 'TWD'
END CURRENCY_CODE,
apps.XXGET_CUSTOMER_EXPOSURE (I_ORG_ID => org_id,
I_CUSTOMER_ID => CUST_ACCOUNT_ID,
I_SITE_ID => SITE_USE_ID,
I_LIMIT_CURR_CODE => CASE
WHEN S_OVERALL_CREDIT_LIMIT1 > 0 THEN S_CURRENCY_CODE1
WHEN S_OVERALL_CREDIT_LIMIT2 > 0 THEN S_CURRENCY_CODE2
WHEN S_OVERALL_CREDIT_LIMIT3 > 0 THEN S_CURRENCY_CODE3
WHEN S_OVERALL_CREDIT_LIMIT4 > 0 THEN S_CURRENCY_CODE4
WHEN S_OVERALL_CREDIT_LIMIT5 > 0 THEN S_CURRENCY_CODE5
ELSE 'TWD'
END)
EXPOSURE_AMT
FROM XX_CUSTOMER_QUICK_VIEW
WHERE ACCOUNT_NUMBER in ('1330','28755')
*/
/***************************************************************/
L_CUST_TOTAL_EXPOSURE NUMBER;
P_CREDIT_CHECK_RULE_ID NUMBER;
V_MSG_DATA VARCHAR2 (4000);
L_RETURN_STATUS VARCHAR2 (30);
l_user_id number:=null;
l_resp_id number:=null;
pragma autonomous_transaction;
BEGIN
apps.MO_GLOBAL.INIT ('ONT');
l_user_id := nvl(fnd_global.user_id,1090); --1090 => nolem
l_resp_id := nvl(fnd_global.resp_id,21623);--21623); --21623 => ont supuer user resp
FND_GLOBAL.APPS_INITIALIZE (USER_ID => l_user_id,
RESP_ID => l_resp_id,
RESP_APPL_ID => 660 --ont appl_id
);
MO_GLOBAL.SET_POLICY_CONTEXT ('S', I_ORG_ID);
OE_CREDIT_ENGINE_GRP.GET_CUSTOMER_EXPOSURE
(P_CUSTOMER_ID => I_CUSTOMER_ID, -- Customer Account ID
P_SITE_ID => I_SITE_ID, -- Site Use ID
P_LIMIT_CURR_CODE => I_LIMIT_CURR_CODE, -- Currency Code
P_CREDIT_CHECK_RULE_ID => I_CREDIT_CHECK_RULE_ID, -- credit_Check_Rule_ID
X_TOTAL_EXPOSURE => L_CUST_TOTAL_EXPOSURE,
X_RETURN_STATUS => L_RETURN_STATUS
);
IF L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS
THEN
-- DBMS_OUTPUT.PUT_LINE ( 'L_CUST_TOTAL_EXPOSURE ' || L_CUST_TOTAL_EXPOSURE);
rollback;
return L_CUST_TOTAL_EXPOSURE;
-- COMMIT;
ELSIF L_RETURN_STATUS IS NULL
THEN
rollback;
return null;
ELSE
DBMS_OUTPUT.PUT_LINE ('Failed: ' || V_MSG_DATA);
FOR I IN 1 .. OE_MSG_PUB.COUNT_MSG
LOOP
V_MSG_DATA := OE_MSG_PUB.GET (P_MSG_INDEX => I, P_ENCODED => 'F');
DBMS_OUTPUT.PUT_LINE (I || ') ' || V_MSG_DATA);
END LOOP;
ROLLBACK;
return -99999999;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error is ' || SQLCODE || '---' || SQLERRM);
rollback;
return -99999999;
END;
- Jun 09 Tue 2015 12:22
EBS SQL function 取客戶承受風險金額
全站熱搜
留言列表