api to copy sales order in oracle apps
In this post , We will discuss the option of how we can copy the sale order in oracle application back end. We don't need to go to the application and copy the sales order. Can we do this same from backend ? . Yes , this is possible with the help of Oracle api to copy sales order in oracle apps. This api copy and create the new sale order from the existing sale order. Here below I will share the same script using api to copy sales order in oracle apps.
Example of api to copy sales order in oracle apps
DECLARE
l_hdr_type_id NUMBER := 5675;
l_line_type_id NUMBER := 3455;
l_orj_header_id NUMBER;
l_return_reason_code ont.oe_order_lines_all.return_reason_code%TYPE := 'CANCELLATION';
l_dummy NUMBER;
l_line_count NUMBER;
l_line_list VARCHAR2 (2400);
l_hdr_list VARCHAR2 (2400);
l_message VARCHAR2 (2400);
l_new_order ont.oe_order_headers_all%ROWTYPE;
l_query_header_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
p_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
V_copy_rec oe_order_copy_util.copy_rec_type
:=
oe_order_copy_util.get_copy_rec;
l_copy_f_rec oe_order_copy_util.copy_rec_type
:=
oe_order_copy_util.get_copy_rec;
l_msg VARCHAR2
(2000);
CURSOR crs_order
IS
SELECT
oeh.header_id
FROM
oe_order_headers_all oeh
WHERE
1 = 1
AND oeh.org_id = :P_ORG_ID
AND oeh.order_number
IN
(442230);
BEGIN
fnd_global.apps_initialize
(0, 344953, 660);
FOR rec IN crs_order
LOOP
v_copy_rec :=
l_copy_f_rec;
l_line_count := 0;
l_message := NULL;
l_return_status := NULL;
l_query_header_id := NULL;
l_msg_data := NULL;
l_line_list := NULL;
l_hdr_list := NULL;
l_msg := NULL;
l_hdr_type_id := 0;
l_line_type_id := 0;
l_orj_header_id := rec.header_id;
FOR rec IN (SELECT
*
FROM ont.oe_order_lines_all
WHERE
1 = 1
AND
cancelled_flag = 'N'
AND header_id
=
l_orj_header_id)
LOOP
l_line_count := l_line_count + 1;
l_line_list := l_line_list || ',' || rec.line_id;
END LOOP;
l_line_list := LTRIM
(l_line_list, ',');
l_hdr_list := TO_CHAR
(l_orj_header_id);
v_copy_rec.api_version_number := 1;
v_copy_rec.init_msg_list := 'T';
v_copy_rec.COMMIT := 'F';
v_copy_rec.copy_order := 'T';
v_copy_rec.hdr_count := 1;
v_copy_rec.hdr_list :=
l_hdr_list;
v_copy_rec.expiration_date := NULL;
v_copy_rec.transaction_name := NULL;
v_copy_rec.copy_transaction_name := 'T';
v_copy_rec.copy_expiration_date := 'F';
v_copy_rec.version_number := 0;
v_copy_rec.line_version_number := 0;
v_copy_rec.append_to_header_id := NULL;
v_copy_rec.hdr_info := 'F';
v_copy_rec.hdr_type :=
l_hdr_type_id;
v_copy_rec.hdr_descflex := 'T';
v_copy_rec.hdr_credit_card_details := 'F';
v_copy_rec.hdr_scredits := 'T';
v_copy_rec.hdr_attchmnts := 'T';
v_copy_rec.hdr_holds := 'T';
v_copy_rec.manual_order_number := NULL;
v_copy_rec.manual_quote_number := NULL;
v_copy_rec.hdr_payments := 'T';
v_copy_rec.all_lines := 'F';
v_copy_rec.line_count := l_line_count;
v_copy_rec.line_list :=
l_line_list;
v_copy_rec.line_type :=
l_line_type_id;
v_copy_rec.incl_cancelled := 'F';
v_copy_rec.line_price_mode := 1;
v_copy_rec.line_price_date := NULL;
v_copy_rec.line_discount_id := NULL;
v_copy_rec.line_descflex := 'F';
v_copy_rec.line_holds := 'F';
v_copy_rec.line_scredits := 'F';
v_copy_rec.line_attchmnts := 'T';
v_copy_rec.line_payments := 'F';
v_copy_rec.return_reason_code := l_return_reason_code;
v_copy_rec.default_null_values := 'F';
v_copy_rec.new_phase := 'F';
v_copy_rec.version_reason_code := NULL;
v_copy_rec.comments := NULL;
v_copy_rec.phase_change_flag := 'T';
v_copy_rec.line_phase_change_flag := 'T';
v_copy_rec.phase_change_flag := NULL;
v_copy_rec.version_number := NULL;
v_copy_rec.copy_complete_config := 'T';
v_copy_rec.source_block_type := 'LINE';
oe_order_copy_util.copy_order
(p_copy_rec => v_copy_rec
,x_header_id
=> l_query_header_id
,x_return_status
=> l_return_status
,x_msg_count
=> l_msg_count
,x_msg_data
=> l_msg_data
);
DBMS_OUTPUT.put_line
('Return
Status : ' || l_return_status);
IF
l_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 ..
l_msg_count
LOOP
l_msg := oe_msg_pub.get (i, 'F');
l_message := l_message || CHR (10) || l_msg;
END LOOP;
DBMS_OUTPUT.put_line
('Copy
Order Failed Message : ' || l_message);
ELSE
oe_order_book_util.complete_book_eligible
(p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_header_id => l_query_header_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF
l_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 ..
l_msg_count
LOOP
l_msg := oe_msg_pub.get (i, 'F');
l_message := l_message || CHR (10) || l_msg;
END LOOP;
DBMS_OUTPUT.put_line
( 'Failed
to Booked : New_Header_Id : '
|| l_query_header_id
|| ' Order_Number : '
|| l_new_order.order_number
|| ' Error Message : '
|| l_message
);
ELSE
DBMS_OUTPUT.put_line
( 'Successfully
Booked : New_Header_Id : '
|| l_query_header_id
|| ' Order_Number : '
|| l_new_order.order_number
);
END IF;
SELECT
*
INTO
l_new_order
FROM
oe_order_headers_all
WHERE
1 = 1 AND header_id = l_query_header_id;
DBMS_OUTPUT.put_line
( 'Successfully
Completed : New_Header_Id : '
||
l_query_header_id
|| ' Order_Number : '
||
l_new_order.order_number
);
END IF;
COMMIT;
END LOOP;
END;
0 comments:
Post a Comment