ORACLE EBS 暂挂和解挂销售订单API

ORACLE EBS 暂挂和解挂销售订单API

------暂挂SO
DECLARE
  l_file            VARCHAR2(100);
  l_return_status   VARCHAR2(30);
  l_msg_data        VARCHAR2(4000);
  l_msg_count       NUMBER;
  l_msg_index       NUMBER;
  l_data            VARCHAR2(2000);
  l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
  x_debug_file      VARCHAR2(100);
  l_msg_index_out   NUMBER(10);
  l_file_val        VARCHAR2(100);
BEGIN

    fnd_global.apps_initialize(user_id => 1140, resp_id => 21623,
                               resp_appl_id => 660, security_group_id => 0);
  
    dbms_output.enable(1000000);
    oe_debug_pub.initialize;
    oe_debug_pub.setdebuglevel(5);
    oe_debug_pub.debug_on;
    l_file_val := oe_debug_pub.set_debug_mode('FILE');
    mo_global.set_policy_context('S', 81);
  
    l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
    l_hold_source_rec.hold_entity_code := 'O'; -- order level hold
    l_hold_source_rec.hold_entity_id := &header_id; -- header_id of the order
    l_hold_source_rec.header_id := &header_id; -- header_id of the order
    l_hold_source_rec.hold_comment := 'Sales Order hold by 未回款';
    l_hold_source_rec.hold_id := 1; --must give correct value to hold_id
    l_return_status := NULL;
    l_msg_data := NULL;
    l_msg_count := NULL;
    dbms_output.put_line('current org id:' || mo_global.get_current_org_id);
    oe_debug_pub.add('Just Before calling OE_Holds_PUB.Apply_Holds:');
    dbms_output.put_line('Just before calling OE_Holds_PUB.Apply_Holds:');
    oe_holds_pub.apply_holds(p_api_version => 1.0,
                             p_init_msg_list => fnd_api.g_true,
                             p_commit => fnd_api.g_true,
                             p_hold_source_rec => l_hold_source_rec,
                             x_return_status => l_return_status,
                             x_msg_count => l_msg_count,
                             x_msg_data => l_msg_data);
  
    oe_debug_pub.add('Just After calling OE_Holds_PUB.Apply_Holds:');
    dbms_output.put_line('Just after calling OE_Holds_PUB.Apply_Holds:');
    -- Check Return Status
  
    dbms_output.put_line(l_return_status);
    IF l_return_status = fnd_api.g_ret_sts_success THEN
      oe_debug_pub.add('success');
      dbms_output.put_line('success:');
      COMMIT;
    ELSIF l_return_status IS NULL THEN
      dbms_output.put_line('Status is null');
    ELSE
      oe_debug_pub.add('failure：' || l_msg_count);
      dbms_output.put_line('failure:' || l_msg_count ||
                           nvl(l_msg_data, '：nothingkdkd'));
      ROLLBACK;
    END IF;
    oe_debug_pub.add('process ORDER ret status IS: ' || l_return_status);
    oe_debug_pub.add('process ORDER msg data IS: ' || l_msg_data);
    oe_debug_pub.add('process ORDER msg COUNT IS: ' || l_msg_count);
    oe_debug_pub.debug_off;
    dbms_output.put_line('Error is nothing .');

END;
----解挂SO

DECLARE
    l_return_status VARCHAR2(300);
    l_msg_data      VARCHAR2(3000);
    l_msg_count     NUMBER;
    l_order_tbl     oe_holds_pvt.order_tbl_type;
    l_hold_id             NUMBER;
    i                     NUMBER := 0;
    l_release_reason_code oe_hold_releases.release_reason_code%TYPE; --释放原因  
    l_release_comment     oe_hold_releases.release_comment%TYPE; --释放备注  
  
    CURSOR cr IS
      SELECT hdra.header_id
            ,hdra.order_number
            ,hsrc.hold_source_id
            ,hsrc.hold_id
            ,hlda.line_id
            ,hdra.org_id
      FROM   oe_order_headers_all hdra
            , --销售订单题头  
             oe_order_holds_all   hlda
            , --销售订单行暂挂明细  
             oe_hold_sources_all  hsrc
            , --暂挂解挂明细  
             oe_hold_definitions  hdef --暂挂定义表  
      WHERE  1 = 1
      AND    hdra.order_number IN
             (SELECT DISTINCT xal.order_number
               FROM   xxcus_ar_overdue_header_t xah
                     ,xxcus_so_overdue_hold_t   xal
               WHERE  xal.header_id = xal.header_id
               AND    xal.order_header_id = &p_header_id
               AND    xal.hold_flag = '3') --销售订单?码  
      AND    hdra.header_id = hlda.header_id
      AND    hlda.hold_source_id = hsrc.hold_source_id
      AND    hsrc.hold_id = hdef.hold_id
      AND    hlda.released_flag = 'N'
      ORDER  BY hsrc.hold_id;
  
  BEGIN
  
    FOR rs IN cr
    LOOP
      l_release_reason_code := 'MANUAL_RELEASE_MARGIN_HOLD';
      l_release_comment := '审核通过释放暂挂-未回款';
      mo_global.init('ONT');
      fnd_global.apps_initialize(user_id => 1140, resp_id => 21623,
                                 resp_appl_id => 660, security_group_id => 0);
      --fnd_client_info.set_org_context(l_Org_id);
      fnd_client_info.set_org_context(rs.org_id);
      mo_global.set_policy_context('S', rs.org_id);
      IF (nvl(l_hold_id, 0) > 0 AND l_hold_id <> rs.hold_id) THEN
        oe_holds_pub.release_holds(p_order_tbl => l_order_tbl,
                                   p_hold_id => l_hold_id,
                                   p_release_reason_code => l_release_reason_code,
                                   p_release_comment => l_release_comment,
                                   x_return_status => l_return_status,
                                   x_msg_count => l_msg_count,
                                   x_msg_data => l_msg_data);
        IF (l_return_status = 'S') THEN
          dbms_output.put_line('Release Success!');
          COMMIT;
        ELSE
          dbms_output.put_line('l_msg_data:' || l_msg_data);
          dbms_output.put_line('l_msg_count:' || l_msg_count);
          ROLLBACK;
        END IF;
        i := 0;
      END IF;
      i := i + 1;
      l_hold_id := rs.hold_id;
      l_order_tbl(i).header_id := rs.header_id;
      l_order_tbl(i).line_id := rs.line_id;
    END LOOP;
    --  
    IF (nvl(i, 0) > 0) THEN
      oe_holds_pub.release_holds(p_order_tbl => l_order_tbl,
                                 p_hold_id => l_hold_id,
                                 p_release_reason_code => l_release_reason_code,
                                 p_release_comment => l_release_comment,
                                 x_return_status => l_return_status,
                                 x_msg_count => l_msg_count,
                                 x_msg_data => l_msg_data);
      IF (l_return_status = 'S') THEN
        dbms_output.put_line('Release Success!');
        COMMIT;
      ELSE
        dbms_output.put_line('l_msg_data:' || l_msg_data);
        dbms_output.put_line('l_msg_count:' || l_msg_count);
        ROLLBACK;
      END IF;
    END IF;
  END;