客户信用控制请求

来源：互联网发布：德雷斯罗萨知乎编辑：程序博客网时间：2024/04/30 07:27

一，导入外部风险

FUNCTION import_credit_exposure(p_org_id               IN NUMBER,                                   p_customer_id          IN NUMBER,                                   p_bill_to_site_uses_id IN NUMBER,                                   p_amount               IN NUMBER,                                   p_import_type          IN VARCHAR2,                                   p_currency_code        IN VARCHAR2)      RETURN VARCHAR2 IS         l_msg_count       NUMBER;      l_msg_data        VARCHAR2(2000);      l_return_status   VARCHAR2(30);      l_cc_hold_comment VARCHAR2(30);      l_result_out      VARCHAR2(30);         l_request_id   NUMBER;      l_p_request_id NUMBER;      l_wait         BOOLEAN;      l_p_wait       BOOLEAN;      l_phase        VARCHAR2(80);      l_status       VARCHAR2(80);      l_dev_phase    VARCHAR2(80);      l_dev_status   VARCHAR2(80);      l_message      VARCHAR2(80);      l_data_access  NUMBER;      l_id           NUMBER;      l_e_wait       BOOLEAN;         -- l_batch_id   NUMBER;      l_ret_status VARCHAR2(1);      l_msg_count  NUMBER;      l_msg_data   VARCHAR2(200);         l_org_id               NUMBER;      l_exposure_source_code VARCHAR2(240);      l_batch_id             NUMBER;      l_validate_only        VARCHAR2(1);         l_rec oe_exposure_interface%ROWTYPE;   BEGIN         l_rec.exposure_source_code      := 'CLE_F147_SOURCE';      l_rec.exposure_interface_id     := oe_exposure_interface_s.nextval;      l_rec.batch_id                  := NULL;      l_rec.operation_code            := p_import_type;      l_rec.exposure_amount           := p_amount;      l_rec.currency_code             := p_currency_code;      l_rec.bill_to_site_use_id       := p_bill_to_site_uses_id;      l_rec.bill_to_customer_name     := NULL;      l_rec.bill_to_customer_number   := NULL;      l_rec.bill_to_customer_id       := p_customer_id;      l_rec.bill_to_address1          := NULL;      l_rec.bill_to_address2          := NULL;      l_rec.bill_to_address3          := NULL;      l_rec.bill_to_address4          := NULL;      l_rec.bill_to_city              := NULL;      l_rec.bill_to_state             := NULL;      l_rec.bill_to_country           := NULL;      l_rec.bill_to_postal_code       := NULL;      l_rec.import_status_code        := NULL;      l_rec.exposure_date             := SYSDATE;      l_rec.org_id                    := p_org_id;      l_rec.original_system_reference := NULL;      l_rec.created_by                := g_user_id;      l_rec.creation_date             := SYSDATE;      l_rec.last_updated_by           := g_user_id;      l_rec.last_update_date          := SYSDATE;      l_rec.last_update_login         := g_login_id;      l_rec.program_application_id    := NULL;      l_rec.program_id                := NULL;      l_rec.program_update_date       := NULL;      l_rec.request_id                := NULL;      l_rec.bill_to_province          := NULL;      l_rec.bill_to_county            := NULL;         INSERT INTO oe_exposure_interface VALUES l_rec;         l_org_id               := p_org_id;      l_exposure_source_code := 'CLE_F147_SOURCE';      l_batch_id             := NULL;      l_validate_only        := 'N';         l_request_id := fnd_request.submit_request('ONT',                                                 'OEXCEIMP',                                                 '',                                                 to_char(SYSDATE,                                                         'YYYY/MM/DD HH24:MI:SS'),                                                 FALSE,                                                 l_org_id,                                                 l_exposure_source_code,                                                 l_batch_id,                                                 l_validate_only,                                                                                                  chr(0));         COMMIT;      -- dbms_output.put_line(l_request_id);         IF l_request_id = 0 THEN         cux_conc_utl.log_msg('导入信用风险出错');         raise_exception('E');      END IF;      -- 导入信用风险完成      -- wait request      l_wait := fnd_concurrent.wait_for_request(request_id => l_request_id,                                                INTERVAL   => 1, -- interval second for check                                                max_wait   => 0, -- max_wait, Max amount of time to wait (in seconds)                                                phase      => l_phase,                                                status     => l_status,                                                dev_phase  => l_dev_phase,                                                dev_status => l_dev_status,                                                message    => l_message);         IF upper(l_status) NOT IN ('NORMAL', '正常') THEN         cux_conc_utl.log_msg('导入信用风险出错 - ' || l_message);         raise_exception('E');      END IF;         RETURN 'S';      END import_credit_exposure;

二，初始化信用汇总表

FUNCTION check_credit(p_org_id               IN NUMBER,                         p_customer_id          IN NUMBER,                         p_bill_to_site_uses_id IN NUMBER,                         p_amount               IN NUMBER,                         p_import_type          IN VARCHAR2,                         p_currency_code        IN VARCHAR2) RETURN VARCHAR2 IS      -- l_return_status VARCHAR2(30);      l_msg_count       NUMBER;      l_msg_data        VARCHAR2(2000);      l_return_status   VARCHAR2(30);      l_cc_hold_comment VARCHAR2(30);      l_result_out      VARCHAR2(30);         l_request_id   NUMBER;      l_p_request_id NUMBER;      l_wait         BOOLEAN;      l_p_wait       BOOLEAN;      l_phase        VARCHAR2(80);      l_status       VARCHAR2(80);      l_dev_phase    VARCHAR2(80);      l_dev_status   VARCHAR2(80);      l_message      VARCHAR2(80);      l_data_access  NUMBER;      l_id           NUMBER;      l_e_wait       BOOLEAN;         -- l_batch_id   NUMBER;      l_ret_status VARCHAR2(1);      l_msg_count  NUMBER;      l_msg_data   VARCHAR2(200);   BEGIN         --先调用 import_credit_exposure 导入可拓展类型为18 的信用风险      mo_global.init('ONT');      fnd_global.apps_initialize(user_id => g_user_id,                                                                  resp_id => fnd_global.resp_id,                                                                  resp_appl_id => fnd_global.resp_appl_id);         l_return_status := import_credit_exposure(p_org_id => p_org_id,                                                                                                p_customer_id          => p_customer_id,                                                p_bill_to_site_uses_id => p_bill_to_site_uses_id,                                                p_amount               => p_amount,                                                p_import_type          => p_import_type,                                                p_currency_code        => p_currency_code);         IF l_return_status = 'S' THEN         --调用标准请求  初始化信用汇总表          l_request_id := fnd_request.submit_request('ONT',                                                    'OEXINICS',                                                    '',                                                    to_char(SYSDATE,                                                            'YYYY/MM/DD HH24:MI:SS'),                                                    FALSE,                                                    'N',                                                    chr(0));               COMMIT;         -- dbms_output.put_line(l_request_id);               IF l_request_id = 0 THEN            cux_conc_utl.log_msg('调用标准请求  初始化信用汇总表 出错');            raise_exception('E');         END IF;         -- 导入信用风险完成         -- wait request         l_wait := fnd_concurrent.wait_for_request(request_id => l_request_id,                                                   INTERVAL   => 1, -- interval second for check                                                   max_wait   => 0, -- max_wait, Max amount of time to wait (in seconds)                                                   phase      => l_phase,                                                   status     => l_status,                                                   dev_phase  => l_dev_phase,                                                   dev_status => l_dev_status,                                                   message    => l_message);               IF upper(l_status) NOT IN ('NORMAL', '正常') THEN            cux_conc_utl.log_msg('调用标准请求  初始化信用汇总表  - ' || l_message);            raise_exception('E');         END IF;      ELSE         cux_conc_utl.log_msg('调用 import_credit_exposure 导入可拓展类型为18 的信用风险出错 - ');         raise_exception('E');      END IF; -- IF l_return_status = 'S' THNE         RETURN 'S';   END check_credit;

0 0