CREATE OR REPLACE PROCEDURE APPS.xx_ora_import_bsa
IS
-- 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;
l_transaction_type_id NUMBER := NULL;
-- 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;
CURSOR c1
IS
SELECT *
FROM xxcs.xx_iit_bsa_headers
WHERE status_flag <> 'P';
CURSOR c2 (header_id IN NUMBER)
IS
SELECT *
FROM xxcs.xx_iit_bsa_lines
WHERE header_transaction_id = header_id;
BEGIN
--Fnd_Global.apps_initialize(&user_Id,&responsibility_Id,&resp_appl_id);
fnd_global.apps_initialize (1253, 50636, 660);
mo_global.init ('ONT'); -- MOAC
FOR hdr IN c1
LOOP
SELECT transaction_type_id
INTO l_transaction_type_id
FROM oe_transaction_types
WHERE NAME = hdr.order_type;
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 := hdr.sold_to_org_id; --3045;--3347;
l_hdr_rec.order_type_id := l_transaction_type_id;
--1027; --hard code "Export BSA" ;--3123;
-- l_hdr_rec.ship_to_org_id := 3730;
l_hdr_rec.transactional_curr_code := hdr.currency; --'USD';
--l_hdr_rec.attribute1 := 'dr test';
l_hdr_rec.start_date_active := hdr.activation_date; --'01-MAR-2009';
l_hdr_rec.end_date_active := hdr.expiration_date; --'01-JAN-2012';
-- Create New Price list and modifier
l_hdr_rec.new_price_list_name :=
'NPL' || '-' || xx_import_bsa_new_price_list.NEXTVAL;
--'dr pricelist for BSA22_5';
l_hdr_rec.new_modifier_list_name :=
'MPL' || '-' || xx_import_bsa_new_price_list.CURRVAL;
--'dr modifier list22_5';
l_hdr_rec.default_discount_percent := 15;
-- populate line rec
FOR lines IN c2 (hdr.header_transaction_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 := 3045;--3347;
l_line_rec.inventory_item_id := lines.inventory_item_id;
--56037;--149;
l_line_rec.blanket_min_quantity := lines.blanket_min_qty; --499;
l_line_rec.blanket_max_quantity := lines.blanket_max_qty; --999;
l_line_rec.blanket_max_quantity := lines.blanket_max_qty; --999;
--l_line_rec.min_release_quantity := 499;
--l_line_rec.max_release_quantity := 999;
-- Item pricing details
l_line_rec.unit_list_price := lines.unit_price; --888;
l_line_rec.item_identifier_type := 'INT';
l_line_rec.pricing_uom := lines.pricing_uom; --'CT';
l_line_rec.order_quantity_uom := lines.pricing_uom; --'CT';
l_line_rec.line_number := lines.line_number;
FOR i IN 1 .. 1
LOOP
l_line_tbl (i) := l_line_rec;
l_line_val_tbl (i) := l_line_val_rec;
END LOOP;
END LOOP;
oe_debug_pub.ADD ('Before calling Process Blanket API', 1);
oe_msg_pub.initialize;
oe_blanket_pub.process_blanket (p_org_id => 101,
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
|| ')'
);
UPDATE xxcs.xx_iit_bsa_headers
SET error_message = NULL,
status_flag = 'P',
last_updated_by = 'EBS',
last_update_date = SYSDATE
WHERE header_transaction_id = hdr.header_transaction_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;
/
Does it create multiple line in BSA.. I am trying the same code but it create only the last line..
ReplyDeletePlease share the procedure to create multiple lines in the BSA.
Regards,
Ajay
Can you please share the code to create multiple lines. The same code is creating only the last line.
ReplyDeleteThis is not creating the new price list. Could you please share what I need to do to create the new price list and assign it to Sales Agreement
ReplyDeleteThis comment has been removed by the author.
ReplyDelete