Aloz 的 Oracle ERP 天地

想要找看看, 在 11i 的版本中, 是不是 API 給 Blanket Sales Agreement (BSA) 使用, 結果很遺憾的沒有, 只有 R12 限定 (囧)

Doc ID : 790223.1

Purpose

This note is intended to show how the creation of Blanket Sales Agreement action can be simulated using the newly available Public API in R12.1.1. .
Scope and Application
The intended audience is for those with technical knowledge of Oracle Applications Code, the usage of public APIs, and those familiar with creating custom solutions for unique business requirements.
How to create Blanket Sales Agreement using public API OE_BLANKET_PUB.Process_Blanket in Oracle Order Management


Assumption: 
=========== 
The following setups related to the Blanket Sales Agreement are not in the scope of this document  
and we assume that these setup steps are already in place. 

1. Setup of order transaction type 
2. Setup of document sequence 
3. Setup of document assignment 

The Blanket Sales Agreement feature has been around from Release 11.5.9 and  
its creation in Order Management has been supported from Blanket Sales Agreement Form. 
From Release 12.1.1, support has been provided to create BSA from backend using the newly  
available public API OE_BLANKET_PUB.Process_Blanket(). 

The sales Agreement functionality supports standard, ATO items and Kits 
and also includes the ability to create releases by order import and process order API.  

Sales Agreement header created usually includes the following information 
   - Customer, ship to , Bill to , Version e.t.c 
   - Effective Dates  
   - Payment and freight terms between the customer and supplier 
   - Sales Agreement Min and Max quantity 
   - Control flag to determine whether you can exceed the maximum value 
   - Pricing information such as Standard price lists or Sales Agreement price lists. 

Sales Agreement Lines Include the following information 
   - Item categories to store the categories and ALL ITEMS to cover all items. 
   - Min and Max quantity agreed by the customer and supplier 
   - Price including the choice of price list in addition to line level  
     modifiers: Discount % and discount amount. 
   - Effective dates

Procedure Parameter Description: 
******************************** 

OE_BLANKET_PUB.process_Blanket() accepts the following parameters

Record Parameter description for header_rec_type:
=================================================

Record Parameter description for line_Rec_type:
===============================================

Table Description for line_tbl_Type:
====================================

The Scripts provided here can be used as reference in order to create your own wrapper  
files to call the API. Also you can use these scripts to troubleshoot the API specific issues  
by analyzing the generated debug files. 

Scenario 1: Creating Blanket Sales Agreement with Sales Agreement specific Price Lists 
            (inline Pricing)with the following criteria. 
            Customer would need you to supply a min quantity of 499 and max quantity of 999   
            over a period of next 10 months.      

SET SERVEROUTPUT ON SIZE 100000;

prompt -----------------------------------------------------------;
prompt This script creates a Blanket Sales Agreement with one Line;
prompt Output : Sales Agreement Number;
prompt -----------------------------------------------------------;

DECLARE   
   -- Input Variables
   l_hdr_rec                     OE_Blanket_PUB.header_Rec_type;
   l_hdr_val_rec                 OE_Blanket_PUB.header_val_Rec_type; 
   l_line_tbl                    OE_Blanket_PUB.line_tbl_Type;
   l_line_val_tbl                OE_Blanket_PUB.line_Val_tbl_Type;
   l_line_rec                    OE_Blanket_PUB.line_rec_Type; 
   l_line_val_rec                OE_Blanket_PUB.line_val_rec_Type;
   l_control_rec                 OE_Blanket_PUB.Control_rec_type; 
   
   -- Output Variables
   x_line_tbl                    OE_Blanket_PUB.line_tbl_Type;
   x_header_rec                  OE_Blanket_PUB.header_Rec_type;
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2(2000);
   x_return_status               VARCHAR2(30);
   
   -- Incremental variables
   i                             NUMBER;
   j                             NUMBER;
   
BEGIN
  -- setting OM debug level and writing debug info to a debug file oe_debug_pub.setdebuglevel(5);
  oe_debug_pub.add('Enter create BSA ',1);

  dbms_output.put_line('The debug file is :'||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

  --Fnd_Global.apps_initialize(&user_Id,&responsibility_Id,&resp_appl_id);
  Fnd_Global.apps_initialize(1318,21623,660);
  MO_GLOBAL.INIT('ONT'); -- MOAC

  For j IN 1..1 LOOP
     l_hdr_rec                   := OE_Blanket_PUB.G_MISS_HEADER_REC; -- header record as missing
     l_hdr_val_rec               := OE_Blanket_PUB.G_MISS_HEADER_VAL_REC; -- header val rec as missing
     l_hdr_rec.operation         := OE_Globals.G_OPR_CREATE; -- Header Operation
     l_hdr_rec.sold_to_org_id    := 3347;
     l_hdr_rec.order_type_id     := 3123; 
     l_hdr_rec.ship_to_org_id    := 3730;
     l_hdr_rec.attribute1        := 'dr test';
     l_hdr_rec.start_date_active := '01-MAR-2009';
     l_hdr_rec.end_date_active   := '01-JAN-2010';
     
     -- Create New Price list and modifier
     l_hdr_rec.new_price_list_name          := 'dr pricelist for BSA22';
     l_hdr_rec.new_modifier_list_name       := 'dr modifier list22';
     l_hdr_rec.default_discount_percent     := 15;
     
     -- populate line rec
     l_line_rec                             := OE_Blanket_PUB.G_MISS_BLANKET_LINE_REC;
     l_line_val_rec                         := OE_Blanket_PUB.G_MISS_BLANKET_LINE_VAL_REC;
     l_line_rec.operation                   := OE_Globals.G_OPR_CREATE;
     l_line_rec.sold_to_org_id              := 3347;
     l_line_rec.inventory_item_id           := 149;
     l_line_rec.blanket_min_quantity        := 499;
     l_line_rec.blanket_max_quantity        := 999; 
     l_line_rec.min_release_quantity        := 499;
     l_line_rec.max_release_quantity        := 999;
     
     -- Item pricing details
     l_line_rec.unit_list_price             := 888;
     l_line_rec.ITEM_IDENTIFIER_TYPE        := 'INT';
     l_line_rec.pricing_uom                 := 'EA';

     for i in 1..1 loop
       l_line_tbl(i)      := l_line_rec;
       l_line_val_tbl (i) := l_line_val_rec;
     end loop;

     oe_debug_pub.add('Before calling Process Blanket API',1);
     oe_msg_pub.initialize;

     OE_Blanket_PUB.Process_Blanket( 
       p_org_id             => 204 
      ,p_operating_unit     => NULL
      ,p_api_version_number => 1.0 
      ,x_return_status      => x_return_status 
      ,x_msg_count          => x_msg_count 
      ,x_msg_data           => x_msg_data 
      ,p_header_rec         => l_hdr_rec  
      ,p_header_val_rec     => l_hdr_val_rec 
      ,p_line_tbl           => l_line_tbl 
      ,p_line_val_tbl       => l_line_val_tbl
      ,p_control_rec        => l_control_rec 
      ,x_header_rec         => x_header_rec 
      ,x_line_tbl           => x_line_tbl 
     );

     oe_debug_pub.add('Number of OE messages :'||x_msg_count,1);
     for k in 1 .. x_msg_count loop
       x_msg_data := oe_msg_pub.get( p_msg_index => k, p_encoded => 'F');
       dbms_Output.put_line('Message :'||x_msg_data);
       oe_debug_pub.add(substr(x_msg_data,1,255));
       oe_debug_pub.add(substr(x_msg_data,255,length(x_msg_data)));
     end loop;

     if x_return_status <> FND_API.G_RET_STS_SUCCESS then
       oe_debug_pub.add('Error in process blanket ',1);
       dbms_output.put_line('Error in Process blanket, Check the debug log file ');
       rollback;
     else
       dbms_output.put_line('New Sales Agreement Number is :'||x_header_rec.order_number||'(Header ID : '||x_header_rec.header_id||')');
       oe_debug_pub.add('Line ID :'     ||x_line_tbl(1).line_id,1);
       oe_debug_pub.add('Header ID :'   ||x_header_rec.header_id,1);
       oe_debug_pub.add('Order number :'||x_header_rec.order_number,1);
       oe_debug_pub.add('Sold To :'     ||x_header_rec.sold_to_org_id,1);
       oe_debug_pub.add('Invoice To :'  ||x_header_rec.invoice_to_org_id,1);
       oe_debug_pub.add('Ship To :'     ||x_header_rec.ship_to_org_id,1);
     end if;

   end loop;

   commit;
End;
/

Scenario2; Creating Blanket Sales Agreement with Standard Price lists.

SET SERVEROUTPUT ON SIZE 100000;
prompt -----------------------------------------------------------;
prompt This script creates a Blanket Sales Agreement with one Line;
prompt Output : Sales Agreement Number;
prompt -----------------------------------------------------------;

DECLARE
   -- Input variables
   l_hdr_rec                     OE_Blanket_PUB.header_Rec_type;
   l_hdr_val_rec                 OE_Blanket_PUB.header_val_Rec_type; 
   l_line_tbl                    OE_Blanket_PUB.line_tbl_Type;
   l_line_val_tbl                OE_Blanket_PUB.line_Val_tbl_Type;
   l_line_rec                    OE_Blanket_PUB.line_rec_Type;
   l_line_val_rec                OE_Blanket_PUB.line_val_rec_Type;
   l_control_rec                 OE_Blanket_PUB.Control_rec_type; 
   
   -- output variables
   x_line_tbl                    OE_Blanket_PUB.line_tbl_Type;
   x_header_rec                  OE_Blanket_PUB.header_Rec_type;
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2(2000);
   x_return_status               VARCHAR2(30);
   
   -- Incremental variables
   i                             NUMBER;
   j                             NUMBER;
   
BEGIN 
  -- setting OM debug level and writing debug info to a debug file
  oe_debug_pub.setdebuglevel(5);
  oe_debug_pub.add('Enter create BSA ',1);
  dbms_output.put_line('The debug file is :'||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

  --Fnd_Global.apps_initialize(&user_Id,&responsibility_Id,&resp_appl_id);
  Fnd_Global.apps_initialize(1318,21623,660);
  MO_GLOBAL.INIT('ONT'); -- MOAC

  For j IN 1..1 LOOP
    l_hdr_rec                              := OE_Blanket_PUB.G_MISS_HEADER_REC; -- Consider header record as missing
    l_hdr_val_rec                          := OE_Blanket_PUB.G_MISS_HEADER_VAL_REC; -- Consider header val record as missing
    l_hdr_rec.operation                    := OE_Globals.G_OPR_CREATE; -- Header Operation
    l_hdr_rec.sold_to_org_id               := 3347;
    l_hdr_rec.order_type_id                := 3123; 
    l_hdr_rec.ship_to_org_id               := 3730;
    l_hdr_rec.attribute1                   := 'dr test';
    l_hdr_rec.start_date_active            := Sysdate;
    l_hdr_rec.end_date_active              := '01-JAN-2010';
    l_hdr_rec.price_list_id                := 1000; -- Pass price_list_id for corporate price list
    l_hdr_rec.enforce_price_list_flag      := 'Y';  -- will enforce price list on the releases.
    l_hdr_rec.payment_term_id              := 4;    -- Payment term NET 30 
    
    -- populate line rec
    l_line_rec                             := OE_Blanket_PUB.G_MISS_BLANKET_LINE_REC;
    l_line_val_rec                         := OE_Blanket_PUB.G_MISS_BLANKET_LINE_VAL_REC;
    l_line_rec.operation                   := OE_Globals.G_OPR_CREATE;
    l_line_rec.sold_to_org_id              := 3347;
    l_line_rec.inventory_item_id           := 149;
    
    -- Item pricing details
    l_line_rec.unit_list_price             := 999;
    l_line_rec.ITEM_IDENTIFIER_TYPE        := 'INT';
    l_line_rec.pricing_uom                 := 'EA';

    for i in 1..1 loop
      l_line_tbl(i)      := l_line_rec;
      l_line_val_tbl (i) := l_line_val_rec;
    end loop;


    oe_debug_pub.add('Before calling Process Blanket API',1);
    oe_msg_pub.initialize;

    OE_Blanket_PUB.Process_Blanket( 
       p_org_id             => 204 
      ,p_operating_unit     => NULL
      ,p_api_version_number => 1.0 
      ,x_return_status      => x_return_status 
      ,x_msg_count          => x_msg_count 
      ,x_msg_data           => x_msg_data 
      ,p_header_rec         => l_hdr_rec  
      ,p_header_val_rec     => l_hdr_val_rec 
      ,p_line_tbl           => l_line_tbl 
      ,p_line_val_tbl       => l_line_val_tbl
      ,p_control_rec        => l_control_rec 
      ,x_header_rec         => x_header_rec 
      ,x_line_tbl           => x_line_tbl 
    );

    oe_debug_pub.add('Number of OE messages :'||x_msg_count,1);
    for k in 1 .. x_msg_count loop
      x_msg_data := oe_msg_pub.get( p_msg_index => k, p_encoded => 'F');
      dbms_Output.put_line('Message :'||x_msg_data);
      oe_debug_pub.add(substr(x_msg_data,1,255));
      oe_debug_pub.add(substr(x_msg_data,255,length(x_msg_data)));
    end loop;

    if x_return_status <> FND_API.G_RET_STS_SUCCESS then
      oe_debug_pub.add('Error in process blanket ',1);
      dbms_output.put_line('Error in Process blanket, Check the debug log file ');
      rollback;
    else
      dbms_output.put_line('New Sales Agreement Number is :'||x_header_rec.order_number||'(Header ID : '||x_header_rec.header_id||')');
      oe_debug_pub.add('Line ID :'     ||x_line_tbl(1).line_id,1);
      oe_debug_pub.add('Header ID :'   ||x_header_rec.header_id,1);
      oe_debug_pub.add('Order number :'||x_header_rec.order_number,1);
      oe_debug_pub.add('Sold To :'     ||x_header_rec.sold_to_org_id,1);
      oe_debug_pub.add('Invoice To :'  ||x_header_rec.invoice_to_org_id,1);
      oe_debug_pub.add('Ship To :'     ||x_header_rec.ship_to_org_id,1);
    end if;
  end loop;

  commit;
End;
/

Conclusion:
=========
Using Process Blanket Public API , users can only create Blanket Sales Agreement
without having entering them through the Sales Agreement forms User Interface.
As of now, Only the "CREATE" operation is supported by the public API for BSA.
This also provides the ability to create releases using order import and process order API.


References:
=========
Order Management Implementation Guide - Release 12
Order Management User's Guide - Release 12