CREATE OR REPLACE PROCEDURE APPS.xx_iit_mtr_to_trxfr
--(errbuf OUT NOCOPY VARCHAR2,
-- retcode OUT NOCOPY VARCHAR2)
IS
--declare
/* For Populating Data from xxcs.XX_IIT_STOCK_ISSUE_REQUESTS to mtl_txn_request_headers (MOVE ORDER)
1. Validation Note:= FROM & TO ORGANIZATION IS HARDCODED "XXX ORG"
2. FROM & TO SUBINVENTORY 'SUBINV1' ,'SUBINV2' IS HARDCODED
3. CURSOR WILL GET ALL DATA FROM CUSTOMIZE TABLE WHERE FROM & TO LOCATION IS SAME OR TO LOCACTION IS BLANK.
*/
-- Common Declarations
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_return_values VARCHAR2 (2) := fnd_api.g_true;
l_commit VARCHAR2 (2) := fnd_api.g_FALSE;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_transaction_type_id NUMBER := NULL;
l_header_id NUMBER := NULL;
l_master_organization_id NUMBER := NULL;
l_pos VARCHAR2 (100) := NULL;
l_error VARCHAR2 (100) := NULL;
l_from_organization_id NUMBER := NULL;
cntr_for_error NUMBER := 0;
-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2 (30) := 'XXX';
l_resp_name VARCHAR2 (30) := 'INVENTORY';
l_inventory_item_id NUMBER := NULL;
-- API specific declarations
l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_trohdr_rec inv_move_order_pub.trohdr_rec_type;
x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
l_validation_flag VARCHAR2 (2)
:= inv_move_order_pub.g_validation_yes;
-- API specific declarations
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
-- Cursor to load Move Order Headers
CURSOR c_mo_hdrs
IS
SELECT sir_no, date_required,HEADER_ID
FROM xxcs.xx_iit_stock_issue_requests
WHERE (leg_from_location = leg_to_location OR leg_to_location IS NULL
)
and status_flag in ('A', 'E')
AND source_module = 'XXMOD'
GROUP BY sir_no, date_required,HEADER_ID;
CURSOR c_mo_lines (p_header_id IN NUMBER)
IS
SELECT *
FROM xxcs.xx_iit_stock_issue_requests
WHERE header_id = p_header_id
AND ( leg_from_location = leg_to_location
OR leg_to_location IS NULL
)
AND source_module = 'XXMOD'
AND status_flag IN ('A', 'E');
PROCEDURE mark_error (p_sir_no VARCHAR2, p_error_description VARCHAR2)
IS
BEGIN
UPDATE xxcs.xx_iit_stock_issue_requests
SET error_message = p_error_description,
status_flag = 'E',
last_updated_by = l_user_id,
last_update_date = SYSDATE
WHERE sir_no = p_sir_no;
END;
BEGIN
BEGIN
--
-- Retrieving Move Order Transfer Type ID
--
l_pos := 'Move Order Transfer Type ID';
SELECT transaction_type_id
INTO l_transaction_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'Move Order Transfer';
--
-- Retrieving Organization ID
--
l_pos := 'XX Organization ID';
SELECT organization_id
INTO l_from_organization_id
FROM org_organization_definitions
WHERE organization_name = 'XXX ORG';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error := l_pos || ': Parameter not found';
cntr_for_error := 1;
FOR r1 IN c_mo_hdrs
LOOP
mark_error (r1.sir_no, l_error);
fnd_file.put_line (fnd_file.LOG,
'==============================');
fnd_file.put_line (fnd_file.LOG,
'SIR ' || r1.sir_no || '-' || l_error
);
END LOOP;
WHEN OTHERS
THEN
l_error := l_pos || ':' || SQLERRM;
cntr_for_error := 1;
FOR r1 IN c_mo_hdrs
LOOP
mark_error (r1.sir_no, l_error);
fnd_file.put_line (fnd_file.LOG,
'==============================');
fnd_file.put_line (fnd_file.LOG, SQLCODE || '-' || SQLERRM);
END LOOP;
END;
IF cntr_for_error = 0
THEN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
fnd_file.put_line (fnd_file.LOG,
'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id
);
FOR ord IN c_mo_hdrs
LOOP
-- Initialize the variables
l_trohdr_rec.date_required := ord.date_required;
l_trohdr_rec.organization_id := l_from_organization_id;
l_trohdr_rec.from_subinventory_code := 'SUBINV1';
l_trohdr_rec.to_subinventory_code := 'SUBINV2';
l_trohdr_rec.status_date := SYSDATE;
l_trohdr_rec.header_status := inv_globals.g_to_status_preapproved;
l_trohdr_rec.transaction_type_id := l_transaction_type_id;
l_trohdr_rec.move_order_type := inv_globals.g_move_order_requisition;
l_trohdr_rec.db_flag := fnd_api.g_true;
l_trohdr_rec.operation := inv_globals.g_opr_create;
-- Who columns
l_trohdr_rec.created_by := l_user_id;
l_trohdr_rec.creation_date := SYSDATE;
l_trohdr_rec.last_updated_by := l_user_id;
l_trohdr_rec.last_update_date := SYSDATE;
l_trohdr_rec.last_update_login := fnd_global.login_id;
-- call API to create move order header
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
fnd_file.put_line
(fnd_file.LOG,
'Calling INV_MOVE_ORDER_PUB.Create_Move_Order_Header API'
);
inv_move_order_pub.create_move_order_header
(p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_return_values => l_return_values,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trohdr_rec => l_trohdr_rec,
p_trohdr_val_rec => l_trohdr_val_rec,
x_trohdr_rec => x_trohdr_rec,
x_trohdr_val_rec => x_trohdr_val_rec,
p_validation_flag => l_validation_flag
);
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
fnd_file.put_line (fnd_file.LOG,
'Return Status: ' || x_return_status);
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
mark_error (ord.sir_no, x_msg_data);
fnd_file.put_line (fnd_file.LOG, 'Error Message :' || x_msg_data);
END IF;
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
fnd_file.put_line (fnd_file.LOG,
'Move Order Created Successfully'
);
fnd_file.put_line (fnd_file.LOG,
'Move Order Header ID : '
|| x_trohdr_rec.header_id
);
UPDATE xxcs.xx_iit_stock_issue_requests
SET header_id = x_trohdr_rec.header_id,
error_message = 'Header Created Successfull'
WHERE sir_no = ord.sir_no;
END IF;
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
l_row_cnt := l_row_cnt + 1;
FOR ordlines IN c_mo_lines (ord.header_id)
LOOP
x_msg_data := NULL;
x_return_status := NULL;
SELECT inventory_item_id
INTO l_inventory_item_id
FROM inv.mtl_system_items_b
WHERE organization_id = 102
AND attribute11 = ordlines.leg_cotton_category_code;
-- Initialize the variables
l_trolin_tbl (l_row_cnt).header_id := ordlines.header_id;
l_trolin_tbl (l_row_cnt).date_required :=
ordlines.date_required;
l_trolin_tbl (l_row_cnt).organization_id :=
l_from_organization_id;
l_trolin_tbl (l_row_cnt).inventory_item_id :=
l_inventory_item_id;
l_trolin_tbl (l_row_cnt).from_subinventory_code := 'SUBINV1';
l_trolin_tbl (l_row_cnt).to_subinventory_code := 'SUBINV2';
l_trolin_tbl (l_row_cnt).quantity := ordlines.qty_primary_uom;
l_trolin_tbl (l_row_cnt).status_date := SYSDATE;
l_trolin_tbl (l_row_cnt).uom_code := ordlines.primary_uom;
l_trolin_tbl (l_row_cnt).line_number := l_row_cnt;
l_trolin_tbl (l_row_cnt).line_status :=
inv_globals.g_to_status_preapproved;
l_trolin_tbl (l_row_cnt).db_flag := fnd_api.g_true;
l_trolin_tbl (l_row_cnt).operation := inv_globals.g_opr_create;
-- Who columns
l_trolin_tbl (l_row_cnt).created_by := l_user_id;
l_trolin_tbl (l_row_cnt).creation_date := SYSDATE;
l_trolin_tbl (l_row_cnt).last_updated_by := l_user_id;
l_trolin_tbl (l_row_cnt).last_update_date := SYSDATE;
l_trolin_tbl (l_row_cnt).last_update_login :=
fnd_global.login_id;
l_row_cnt := l_row_cnt + 1;
END LOOP;
-- call API to create move order lines
fnd_file.put_line
(fnd_file.LOG,
'=========================================================='
);
fnd_file.put_line
(fnd_file.LOG,
'Calling INV_MOVE_ORDER_PUB.Create_Move_Order_Lines API'
);
inv_move_order_pub.create_move_order_lines
(p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_return_values => l_return_values,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trolin_tbl => l_trolin_tbl,
p_trolin_val_tbl => l_trolin_val_tbl,
x_trolin_tbl => x_trolin_tbl,
x_trolin_val_tbl => x_trolin_val_tbl,
p_validation_flag => l_validation_flag
);
fnd_file.put_line
(fnd_file.LOG,
'=========================================================='
);
fnd_file.put_line (fnd_file.LOG,
'Return Status: ' || x_return_status
);
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
fnd_file.put_line (fnd_file.LOG,
'Error Message :' || x_msg_data
);
END IF;
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
fnd_file.put_line
(fnd_file.LOG,
'Move Order Lines Created Successfully for '
|| ord.header_id
);
UPDATE xxcs.xx_iit_stock_issue_requests
SET error_message = 'SIR Created',
status_flag = 'P'
WHERE sir_no = ord.sir_no;
END IF;
fnd_file.put_line
(fnd_file.LOG,
'==========================================================SIR CREATED'
);
END LOOP;
-----------------------------MOVE ORDER LINES--------------------------------------------------------
l_row_cnt := 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Exception Occured :');
fnd_file.put_line (fnd_file.LOG, SQLCODE || ':' || SQLERRM);
fnd_file.put_line
(fnd_file.LOG,
'======================================================='
);
END;
/
can't copy to test , so : baaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaad API
ReplyDelete