EBS 可拓展的外部信用风险导入

DECLARE

   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);

   g_user_id         CONSTANT NUMBER := fnd_global.user_id;

   g_login_id        CONSTANT NUMBER := fnd_global.conc_login_id;

   g_request_id      CONSTANT NUMBER := fnd_global.conc_request_id;

   g_prog_appl_id    CONSTANT NUMBER := fnd_global.prog_appl_id;

   g_conc_program_id CONSTANT NUMBER := fnd_global.conc_program_id;

   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

   mo_global.init('ONT');

   fnd_global.apps_initialize(user_id => 1370,

                              resp_id => 50717,

                              resp_appl_id => 20005);

   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            := 'UPDATE';

   l_rec.exposure_amount           := 2000;

   l_rec.currency_code             := 'CNY';

   l_rec.bill_to_site_use_id       := 2181;

   l_rec.bill_to_customer_name     := NULL;

   l_rec.bill_to_customer_number   := NULL;

   l_rec.bill_to_customer_id       := 39041;

   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                    := 81;

   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               := 81;

   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

      dbms_output.put_line('导入信用风险出错');

   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

      dbms_output.put_line('导入信用风险出错 - ' || l_message);

   END IF;

END;

DELETE FROM oe_exposure_interface;

SELECT * FROM oe_exposure_interface;

select *from oe_credit_summaries l WHERE l.PARTY_ID = 62042;

SELECT * FROM oe_credit_summaries l WHERE l.balance_type = 18;