How to hold Sales Order programmatically

select *from OE_ORDER_HOLDS_ALL

where header_id = 3541;--SO Header ID

--Hold Resource,Hold comment

select *from OE_HOLD_SOURCES_ALL

where hold_source_id = 1568;

--when release sales order,the following table will keep release information

select *from oe_hold_releases;

--The following Script. work well at R12.1.2

--I copy the script. from Metalink，but It can not apply hold to the sales order.return_status is 'E' but msg_data always null.

--finally, I found that when I gave correct value to l_hold_source_rec.hold_id,ant it worked fine for successful hold

--R12 Sales Order：1011110354，order's Stats is Open,SO Header ID is 3583

/*OE_HOLDS_PUB.APPLY_HOLDS is used to apply hold to an order. A sample code showing how to call the API :

From R12 MO_GLOBAL.set_policy_context needs to be added along with FND_GLOBAL

*/

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_ORG             number := 84; --OU ID

  L_FILE_VAL        varchar2(100);

BEGIN

  -- Source the Environment variables

  --fnd_global.apps_initialize(1090, 50491, 660); -- pass in user_id, responsibility_id, and application_id

  fnd_global.apps_initialize(user_id           => 1110,

                             resp_id           => 50821,

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

  dbms_output.put_line('.');

  dbms_output.put_line(' .... Log File Name and Location :- ' ||

                       OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);

  dbms_output.put_line('.');

  MO_GLOBAL.set_policy_context('S', L_ORG);

  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   := 3602; -- header_id of the order

  l_hold_source_rec.header_id        := 3602; -- header_id of the order

  l_hold_source_rec.HOLD_COMMENT     := 'Sales Order hold by Johnson';

  l_hold_source_rec.HOLD_ID          := 1;--must give correct value to hold_id

  --------------------------------

  --get hold_id from oe_hold_definitions

  --SELECT *

  --FROM oe_hold_definitions

  --Setup:OM->Setup->Sales Order->Holds

  --------------------------------

  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_holds_pvt.apply_Holds(

     p_hold_source_rec     =>  l_hold_source_rec

    ,p_hold_existing_flg   =>  'N'

    ,p_hold_future_flg     =>  'Y'

    ,p_check_authorization_flag => 'N' -- 8477694

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

  -- Display Return Status

  OE_DEBUG_PUB.Add('process ORDER ret status IS: ' || l_return_status);

  --fnd_file.put_line(fnd_file.output,'process ORDER ret status IS: ' || l_return_status);

  OE_DEBUG_PUB.Add('process ORDER msg data IS: ' || l_msg_data);

  --fnd_file.put_line(fnd_file.output,'process ORDER msg data IS: ' || l_msg_data );

  OE_DEBUG_PUB.Add('process ORDER msg COUNT IS: ' || l_msg_count);

  --fnd_file.put_line(fnd_file.output,'process ORDER msg COUNT IS: ' || l_msg_count);

  OE_DEBUG_PUB.DEBUG_OFF;

  dbms_output.put_line('Error is nothing .');

exception

  when others then

    dbms_output.put_line('Error is ' || sqlcode || '---' || sqlerrm);

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/385592/viewspace-695155/，如需转载，请注明出处，否则将追究法律责任。