想要找看看, 在 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

Parameter In/Out Type Description
p_api_version_number IN NUMBER
API Version
x_return_status OUT VARCHAR2
Return Status
P_org_id
IN
NUMBER
Org Id (MOAC)
P_operating_Unit
IN
NUMBER
Operating Unit (MOAC)
p_header_rec
IN
OE_Blanket_PUB.header_rec_type
Header Rec
p_header_val_rec
IN
OE_Blanket_PUB.header_val_rec_type
Header Value Rec
p_line_tbl
IN
OE_Blanket_PUB line_tbl_Type
Inbound Line table
p_line_val_tbl
IN
OE_Blanket_PUB.line_val _tbl_Type
Inbound Line Values table
p_control_rec
IN
OE_Blanket_PUB.Control_Rec_Type
Inbound Control Record
x_header_rec
OUT OE_Blanket_PUB.header_rec_type
Outbound Header Record
x_line_tbl
OUT
OE_Blanket_PUB line_tbl_Type
Outbound Line table
x_msg_count
OUT
NUMBER
Msg Count
x_msg_data
OUT
VARCHAR2
Msg Data
p_validate_desc_flex
IN VARCHAR2
To Validate FF or not ?


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

Attribute
Type
accounting_rule_id
NUMBER
agreement_id
NUMBER
attribute1 - attribute20
VARCHAR2(240)
context
VARCHAR2(30)
created_by
NUMBER
creation_date
DATE
cust_po_number
VARCHAR2(50)
deliver_to_org_id
NUMBER
freight_terms_code
VARCHAR2(30)
header_id
NUMBER
invoice_to_org_id
NUMBER
invoicing_rule_id
NUMBER
last_updated_by
NUMBER
last_update_date
DATE
last_update_login
NUMBER
order_category_code
VARCHAR2(30)
order_number
NUMBER

order_type_id

NUMBER
org_id
NUMBER
price_list_id
NUMBER
program_application_id
NUMBER
program_id
NUMBER
program_update_date
DATE
request_id
NUMBER
version_number
NUMBER
salesrep_id
NUMBER
shipping_method_code
VARCHAR2(30)
ship_from_org_id
NUMBER
ship_to_org_id
NUMBER
sold_to_contact_id
NUMBER
sold_to_org_id
NUMBER
transactional_curr_code
VARCHAR2(15)
return_status
VARCHAR2(1)
db_flag
VARCHAR2(1)
operation
VARCHAR2(30)
payment_term_id
NUMBER
shipping_instructions
VARCHAR2(2000)
packing_instructions
VARCHAR2(2000)
Price_list_Name
VARCHAR2(240)
Price_list_description
VARCHAR2(2000)
price_list_currency_code
VARCHAR2(30)
conversion_type_code
VARCHAR2(30)
blanket_max_amount
NUMBER
blanket_min_amount
NUMBER
released_amount
NUMBER
fulfilled_amount
NUMBER
returned_amount
NUMBER
enforce_price_list_flag
VARCHAR2(1)
enforce_ship_to_flag
VARCHAR2(1)
enforce_invoice_to_flag
VARCHAR2(1)
enforce_freight_term_flag
VARCHAR2(1)
enforce_shipping_method_flag
VARCHAR2(1)
enforce_payment_term_flag
VARCHAR2(1)
enforce_accounting_rule_flag
VARCHAR2(1)
enforce_invoicing_rule_flag
VARCHAR2(1)
lock_control
NUMBER
on_hold_flag
VARCHAR2(1)
override_amount_flag
VARCHAR2(1)
start_date_active
DATE
end_date_active
DATE
revision_change_comments
VARCHAR2(2000)
revision_change_date
DATE
revision_change_reason_code
VARCHAR2(30)
source_document_type_id
NUMBER
source_document_id
NUMBER
SALES_DOCUMENT_NAME
VARCHAR2(240)
TRANSACTION_PHASE_CODE
VARCHAR2(30)
USER_STATUS_CODE
VARCHAR2(30)
flow_status_code
VARCHAR2(30)
SUPPLIER_SIGNATURE
VARCHAR2(30)
SUPPLIER_SIGNATURE_DATE
DATE
CUSTOMER_SIGNATURE
VARCHAR2(240)
CUSTOMER_SIGNATURE_DATE
DATE
SOLD_TO_SITE_USE_ID
NUMBER
DRAFT_SUBMITTED_FLAG
VARCHAR2(1)
SOURCE_DOCUMENT_VERSION_NUMBER
NUMBER
contract_template_id
NUMBER
new_price_list_id
NUMBER
new_price_list_name
VARCHAR2(240)
new_modifier_list_id
NUMBER
new_modifier_list_name
VARCHAR2(240)
default_discount_percent
NUMBER
default_discount_amount
NUMBER
open_flag
VARCHAR2(1)

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

Attribute
Type
accounting_rule_id
NUMBER
agreement_id
NUMBER
attribute1 - attribute20
VARCHAR2(240)
context
VARCHAR2(30)
created_by
NUMBER
creation_date
DATE
cust_po_number
VARCHAR2(50)
deliver_to_org_id
NUMBER
freight_terms_code
VARCHAR2(30)
header_id
NUMBER
inventory_item_id
NUMBER
invoice_to_org_id
NUMBER
invoicing_rule_id
NUMBER
ordered_item
VARCHAR2(2000)
ordered_item_id
NUMBER
last_updated_by
NUMBER
last_update_date
DATE
last_update_login
NUMBER
line_type_id
NUMBER
line_id
NUMBER
line_number NUMBER
order_number
VARCHAR2(240)
order_quantity_uom
VARCHAR2(30)
org_id
NUMBER
payment_term_id
NUMBER
preferred_grade
VARCHAR2(150)
price_list_id
NUMBER
request_id
NUMBER
program_id
NUMBER
program_application_id
NUMBER
program_update_date
DATE
shipping_method_code
NUMBER
ship_from_org_id
NUMBER
ship_to_org_id
NUMBER
sold_to_org_id
NUMBER
return_status
VARCHAR2(1)
db_flag
VARCHAR2(1)
operation
VARCHAR2(30)
item_identifier_type
VARCHAR2(30)
item_type_code
VARCHAR2(30)
shipping_instructions
VARCHAR2(2000)
packing_instructions
VARCHAR2(2000)
salesrep_id
NUMBER
unit_list_price
NUMBER
pricing_uom
VARCHAR2(150)
lock_control
NUMBER
enforce_price_list_flag
VARCHAR2(1)
enforce_ship_to_flag
VARCHAR2(1)
enforce_invoice_to_flag
VARCHAR2(1)
enforce_freight_term_flag
VARCHAR2(1)
enforce_shipping_method_flag
VARCHAR2(1)
enforce_payment_term_flag
VARCHAR2(1)
enforce_accounting_rule_flag
VARCHAR2(1)
enforce_invoicing_rule_flag
VARCHAR2(1)
override_blanket_controls_flag
VARCHAR2(1)
override_release_controls_flag
VARCHAR2(1)
qp_list_line_id
NUMBER
fulfilled_quantity
NUMBER
blanket_min_quantity
NUMBER
blanket_max_quantity
NUMBER
blanket_min_amount
NUMBER
blanket_max_amount
NUMBER
min_release_quantity
NUMBER
max_release_quantity
NUMBER
min_release_amount
NUMBER
max_release_amount
NUMBER
released_amount
NUMBER
fulfilled_amount
NUMBER
released_quantity
NUMBER
returned_amount
NUMBER
returned_quantity
NUMBER
start_date_active
DATE
end_date_active
DATE
source_document_type_id
NUMBER
source_document_id
NUMBER
source_document_line_id
NUMBER
transaction_phase_code
VARCHAR2(30)
source_document_version_number
NUMBER
modifier_list_line_id
NUMBER
discount_percent
NUMBER
discount_amount
NUMBER
revision_change_comments
VARCHAR2(2000)
revision_change_date
DATE
revision_change_reason_code
VARCHAR2(30)

Table Description for line_tbl_Type:
====================================
Parameter
Type
Line_Rec_Type
Record

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 

 

創作者介紹

Aloz 的 Oracle ERP 天地

Aloz 發表在 痞客邦 PIXNET 留言(2) 人氣()


留言列表 (2)

發表留言
  • 訪客
  • Excellent source for the Blanket Sales Agreement import...Thnaks a lot
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼