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;