BSA's are used when you have specific feature or quality to a purchasing BSA between a Supplier and a Customer.
These features include the date range of the agreement, the items details included, the price of items, quantity of each item, etc.
Before create BSA we need to create Sales Agreement transaction types, create Document sequence.
Here we will create BSA using API:
Example: PLSQL Script to create BSA with Active status.
SET SERVEROUTPUT ON SIZE 999999;
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;
x NUMBER;
------
-- First cursor to get header data.
-- As per requirement you will change cursor query or use any custom table to provide valid data to API to create BSA.
-- 1st Cursor
--
CURSOR lcur_bsa_header
IS
SELECT *
FROM oe_blanket_headers_all hdr
WHERE hdr.header_id = 1218952; -- I took for testing purpose.
-- This cursor related provide line level records to API
-- 2nd Cursor
--
CURSOR lcur_bsa_line (p_header_id IN NUMBER)
IS
SELECT *
FROM oe_blanket_lines_all HL
WHERE hl.HEADER_ID = p_header_id;
--
BEGIN
dbms_output.put_line('Start to creating New BSA:-->');
x := 1;
fnd_global.apps_initialize (23432, 5456, 254);
-- Replace with valid values
MO_GLOBAL.INIT('ONT'); -- MOAC
FOR lreq_hdr IN lcur_bsa_header
LOOP
l_hdr_rec := oe_blanket_pub.g_miss_header_rec;
l_hdr_val_rec := oe_blanket_pub.g_miss_header_val_rec;
l_hdr_rec.operation := oe_globals.g_opr_create; -- header operation
l_hdr_rec.sold_to_org_id := lreq_hdr.sold_to_org_id;
l_hdr_rec.order_type_id := lreq_hdr.order_type_id;
l_hdr_rec.ship_to_org_id := lreq_hdr.ship_to_org_id;
l_hdr_rec.invoice_to_org_id := lreq_hdr.invoice_to_org_id;
l_hdr_rec.attribute1 := 'TEST007'; -- This is reference field, optional
l_hdr_rec.start_date_active := SYSDATE ;
l_hdr_rec.end_date_active := NULL;
--
FOR lreq_line IN lcur_bsa_line(lreq_hdr.header_id)
LOOP
--
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 := lreq_line.sold_to_org_id;
l_line_rec.inventory_item_id := lreq_line.inventory_item_id;
l_line_rec.order_quantity_uom := lreq_line.order_quantity_uom;
l_line_tbl(x) := l_line_rec;
l_line_val_tbl (x) := l_line_val_rec;
x:=x+1;
--
END LOOP;
DBMS_OUTPUT.put_line('line count=' || l_line_tbl.count);
oe_msg_pub.initialize;
--
-- Call API to create BSA
--
OE_Blanket_PUB.Process_Blanket(
p_org_id => lreq_hdr.org_id
,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
);
--
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);
END LOOP;
--
IF x_return_status <> fnd_api.g_ret_sts_success THEN
dbms_output.put_line('Error in Process blanket.');
Rollback;
ELSE
dbms_output.put_line('New Sales Agreement Number is :'
||x_header_rec.order_number
||'(Header ID : '
||x_header_rec.header_id
||')'
);
--
-- Typical error: Blanket Sales Agreement created in Entered status.
-- For create BSA in Active status we need to call Workflow API of Submit Draft.
--
IF (x_header_rec.order_number IS NOT NULL) THEN
oe_blanket_wf_util.Submit_Draft(p_header_id => x_header_rec.header_id,
p_transaction_phase_code => x_header_rec.transaction_phase_code,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
END IF;
--
END IF;
--
END LOOP;
COMMIT;
END;
/
Once script executed successfully then check records in base table using HEADER_ID or BSA Number:
-- Header Table
SELECT * FROM oe_blanket_headers_all
WHERE header_id = 679963;
-- Line Table
SELECT * FROM oe_blanket_lines_all
WHERE header_id = 679963;
Thanks....
No comments:
Post a Comment