CREATE OR REPLACE PACKAGE BODY APPS.xxey_itsr_po_req_int_pkg1
AS
PROCEDURE main_proc (errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_header_id IN NUMBER)
AS
-------------cursor declaration-------------
CURSOR c_get_stg_details
IS
SELECT xbh.REQ_HEADER_ID,
xbh.REQUEST_TYPE,
xbh.REQUEST_SUB_TYPE,
xbh.NEED_BY_DATE,
xbh.LAST_UPDATE_DATE,
xbh.LAST_UPDATED_BY,
xbh.CREATION_DATE,
xbh.CREATED_BY,
xbh.LAST_UPDATE_LOGIN,
xbh.REQUESTER_ACCNT_ID,
xbh.PROCESS_FLAG,
xbh.ORG_ID,
xbh.REQUESTER_NAME,
xbh.PREPARER_NAME,
xbh.PREPARER_ID,
xbh.REQUEST_STATUS,
xbh.VENDOR_NAME,
xbh.VENDOR_ID,
xbh.VENDOR_SITE_ID,
xbh.ERROR_MESSAGE,
xbh.REQUEST_NUMBER,
xbh.REQUESTER_ID,
xbh.REQUEST_DATE,
xbl.REQUEST_LINE_ID,
xbl.REQUEST_HEADER_ID,
xbl.LINE_NUM,
xbl.QUANTITY,
xbl.UNIT_PRICE,
xbh.CURRENCY_CODE,
xbl.UOM_CODE,
xbl.ITEM_DESCRIPTION,
xbl.ITEM_ID,
xbl.ITEM_TYPE
FROM xxey_itsr_request_header xbh, xxey_itsr_request_lines xbl
WHERE 1 = 1
AND xbh.REQ_HEADER_ID = xbl.REQUEST_HEADER_ID
AND xbh.REQ_HEADER_ID = p_header_id
AND xbh.REQUEST_SUB_TYPE IN ('STK', 'SPL')
AND NVL (xbh.PROCESS_FLAG, 'N') IN ('N', 'E');
lc_req_msg VARCHAR2 (240);
lc_dev_phase VARCHAR2 (15);
lc_dev_status VARCHAR2 (15);
lb_get_req_status BOOLEAN;
ln_wait_interval NUMBER := 10;
ln_max_wait NUMBER := 1000;
lc_err_msg VARCHAR2 (32766);
lc_err_msg2 VARCHAR2 (32767);
lc_phase VARCHAR2 (100);
lc_status VARCHAR2 (10);
ln_lin_count NUMBER := 0;
ln_vald_cnt NUMBER := 0;
ln_req_id NUMBER;
ln_req_hdr_id NUMBER;
ln_inv_item_id NUMBER;
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_org_id NUMBER;
ln_category_id NUMBER;
ln_ccid NUMBER;
lc_vendor_name VARCHAR2 (240);
ln_vendor_id NUMBER;
ln_req_num NUMBER;
ln_deliver_to_requester NUMBER;
lc_rec_status VARCHAR2 (1);
ln_count NUMBER;
lc_service_type VARCHAR2 (240);
ln_mo_num NUMBER;
ln_retcode NUMBER;
ln_rate NUMBER;
lc_dest_org VARCHAR2 (30) := 'CMS';
lc_dest_loc VARCHAR2 (30) := 'CMS Location';
lc_dest_code VARCHAR2 (30) := 'INVENTORY';
lc_source_org VARCHAR2 (30) := 'CMS';
ln_uom_code VARCHAR2 (30);
---Declaring the cursor for item_id---------------
CURSOR c_get_item_id (
p_item_id IN NUMBER,
p_des_org_code IN VARCHAR2)
IS
SELECT msi.inventory_item_id, INITCAP (PRIMARY_UOM_CODE)
FROM mtl_system_items_b msi, org_organization_definitions ood
WHERE ood.organization_id = msi.organization_id
AND msi.inventory_item_id = p_item_id
AND msi.inventory_item_status_code LIKE 'Active'
AND UPPER (ood.organization_code) = UPPER (p_des_org_code);
----Declaring the cursor for category id with inventory item----------
CURSOR c_get_item_cat ( -- p_category_id IN NUMBER,
p_inv_item_id IN NUMBER,
p_des_org_code IN VARCHAR2)
IS
SELECT mic.category_id
FROM mtl_item_categories mic, org_organization_definitions ood
WHERE mic.organization_id = ood.organization_id
-- AND mic.category_id = p_category_id Commented By Raghu
AND mic.inventory_item_id = p_inv_item_id
AND UPPER (ood.organization_code) = UPPER (p_des_org_code)
AND ROWNUM = 1;
---Declaring the cursor for code_combination id-------------
CURSOR c_get_ccid (p_ccid IN NUMBER)
IS
SELECT code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = p_ccid AND enabled_flag = 'Y';
---Declaring the cursor for to get the requisition number---
CURSOR c_get_req_update (
p_req_id IN NUMBER,
p_org_id IN NUMBER)
IS
SELECT segment1
FROM po_requisition_headers_all
WHERE attribute15 IS NOT NULL
AND attribute15 = TO_CHAR (p_req_id)
AND interface_source_code = 'ITSR'
AND org_id = p_org_id;
--Declaring the Cursor to get the conversion rate
CURSOR c_exchange_rate (
p_currency_code IN VARCHAR2,
p_date IN DATE)
IS
SELECT conversion_rate
FROM gl_daily_rates
WHERE from_currency = p_currency_code
AND conversion_date = p_date
AND to_currency = 'AED'
AND conversion_type = 'Corporate';
---Decalring the cursor for deliver to location-------------
CURSOR c_get_locations (
p_des_loc IN VARCHAR2,
p_deliver_loc IN VARCHAR2)
IS
SELECT COUNT (1)
FROM hr_locations
WHERE inventory_organization_id IN
(SELECT organization_id
FROM org_organization_definitions
WHERE organization_code = p_des_loc)
AND location_code = p_deliver_loc;
BEGIN
FOR lcu_get_details IN c_get_stg_details
LOOP
BEGIN
lc_err_msg := NULL;
ln_inv_item_id := NULL;
ln_category_id := NULL;
ln_count := NULL;
ln_ccid := NULL;
ln_org_id := lcu_get_details.org_id;
lc_rec_status := 'P';
ln_req_hdr_id := lcu_get_details.REQ_HEADER_ID;
ln_lin_count := ln_lin_count + 1;
ln_rate := NULL;
IF lcu_get_details.process_flag = 'E'
THEN
DELETE FROM po_requisitions_interface_all
WHERE header_attribute15 = lcu_get_details.REQ_HEADER_ID
AND interface_source_code = 'ITSR';
END IF;
OPEN c_get_item_id (lcu_get_details.item_id, lc_dest_org); --lcu_get_details.destination_organization_code);
FETCH c_get_item_id
INTO ln_inv_item_id, ln_uom_code;
CLOSE c_get_item_id;
IF ln_inv_item_id IS NULL
THEN
lc_rec_status := 'E';
lc_err_msg := lc_err_msg || ' ' || 'Invalid Inventory Item';
END IF;
OPEN c_get_item_cat ( ---lcu_get_details.category_id, Commented By Raghu
lcu_get_details.item_id, lc_dest_org); --, lcu_get_details.destination_organization_code);
FETCH c_get_item_cat INTO ln_category_id;
CLOSE c_get_item_cat;
IF ln_category_id IS NULL
THEN
lc_rec_status := 'E';
lc_err_msg := lc_err_msg || ' ' || 'Invalid Item Category';
END IF;
OPEN c_get_locations (lc_dest_org --lcu_get_details.destination_organization_code
, lc_dest_loc); --lcu_get_details.deliver_to_location_code);
FETCH c_get_locations INTO ln_count;
CLOSE c_get_locations;
IF ln_count = 0
THEN
lc_rec_status := 'E';
lc_err_msg :=
lc_err_msg || ' ' || 'Invalid Deliver to location code';
END IF;
OPEN c_get_ccid (lcu_get_details.REQUESTER_ACCNT_ID);
FETCH c_get_ccid INTO ln_ccid;
CLOSE c_get_ccid;
IF ln_ccid IS NULL
THEN
lc_rec_status := 'E';
lc_err_msg := lc_err_msg || ' ' || 'Invalid Code Combination';
END IF;
IF lcu_get_details.currency_code <> 'AED'
THEN
OPEN c_exchange_rate (lcu_get_details.currency_code,
lcu_get_details.request_date);
FETCH c_exchange_rate INTO ln_rate;
CLOSE c_exchange_rate;
IF ln_rate IS NULL
THEN
lc_rec_status := 'E';
lc_err_msg :=
lc_err_msg || ' ' || 'Invalid Daily Rate Defined';
END IF;
END IF;
IF (lc_rec_status <> 'E')
THEN
ln_vald_cnt := ln_vald_cnt + 1;
DBMS_OUTPUT.put_line ('Entering to insert into interface');
BEGIN
INSERT
INTO po_requisitions_interface_all (
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
interface_source_code,
destination_type_code,
authorization_status,
preparer_id,
item_id,
quantity,
unit_price,
uom_code,
category_id,
destination_organization_code,
deliver_to_location_code,
deliver_to_requestor_id,
source_type_code,
org_id,
charge_account_id,
budget_account_id,
source_organization_code,
item_description,
header_attribute15,
requisition_type,
currency_code,
gl_date,
reference_num,
-- request_id,
rate_type,
rate_date,
need_by_date)
VALUES (
gn_user_id,
SYSDATE,
gn_user_id,
SYSDATE,
gn_login_id,
'ITSR',
lc_dest_code --lcu_get_details.destination_type_code
,
'APPROVED',
lcu_get_details.preparer_id,
ln_inv_item_id,
lcu_get_details.quantity,
lcu_get_details.unit_price,
TRIM (ln_uom_code),
ln_category_id,
lc_dest_org --lcu_get_details.destination_organization_code
,
lc_dest_loc --lcu_get_details.deliver_to_location_code
,
NVL (lcu_get_details.requester_id,
lcu_get_details.preparer_id),
'INVENTORY',
ln_org_id,
ln_ccid,
ln_ccid,
lc_source_org --lcu_get_details.source_organization_code
,
lcu_get_details.item_description,
lcu_get_details.REQ_HEADER_ID,
'INTERNAL',
lcu_get_details.currency_code,
lcu_get_details.request_date,
lcu_get_details.request_number,
-- lcu_get_details.request_id,
'Corporate',
lcu_get_details.request_date,
SYSDATE + 1);
DBMS_OUTPUT.put_line (
'Entering to after the insert into interface');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Insert Statement Error' || SQLERRM);
END;
END IF;
UPDATE xxey_itsr_request_lines
SET process_flag = lc_rec_status,
error_message = lc_err_msg,
-- request_id = gn_req_id,
last_update_date = SYSDATE,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE REQUEST_LINE_ID = lcu_get_details.REQUEST_LINE_ID;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
lc_err_msg := SUBSTR (SQLERRM, 240);
UPDATE xxey_itsr_request_lines
SET process_flag = 'E',
error_message = lc_err_msg,
-- request_id = gn_req_id,
last_update_date = SYSDATE,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE REQUEST_LINE_ID = lcu_get_details.REQUEST_LINE_ID;
COMMIT;
END;
END LOOP;
IF ln_lin_count = ln_vald_cnt
THEN
lc_rec_status := 'P';
lc_err_msg := NULL;
---Calling the standard concurrent program-------------
fnd_global.apps_initialize (21879, 20707, 201); --Added on NOV-20
BEGIN
ln_req_id :=
fnd_request.submit_request (application => 'PO',
program => 'REQIMPORT',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'ITSR',
argument2 => NULL,
argument3 => 'ALL',
argument4 => NULL,
-- argument5 => 'No',
argument6 => 'Yes');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error Message At Caling Submit Request ' || SQLERRM);
END;
IF (ln_req_id > 0)
THEN
lb_get_req_status :=
fnd_concurrent.wait_for_request (ln_req_id,
ln_wait_interval,
ln_max_wait,
lc_phase,
lc_status,
lc_dev_phase,
lc_dev_status,
lc_req_msg);
IF UPPER (lc_dev_phase) = 'COMPLETE'
AND UPPER (lc_dev_status) = 'NORMAL'
THEN
OPEN c_get_req_update (p_header_id, ln_org_id);
FETCH c_get_req_update INTO ln_req_num;
CLOSE c_get_req_update;
-- fnd_file.put_line(fnd_file.output,'requisition number'||ln_req_num);
IF ln_req_num IS NOT NULL
THEN
---update the staging table with the RequisitionNumber and Rquest_id----
UPDATE xxey_itsr_request_header
SET requisition_number = ln_req_num,
-- request_id = gn_req_id,
last_update_date = SYSDATE,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE REQ_HEADER_ID = p_header_id;
COMMIT;
-----calling procedure for Move Order Issue-----------------
create_move_order (lc_err_msg2, ln_req_num, ln_org_id);
IF lc_err_msg2 IS NULL
THEN
SELECT COUNT (DISTINCT header_id)
INTO ln_mo_num
FROM mtl_txn_request_headers
WHERE request_number = ln_req_num
AND organization_id = 106;
IF (ln_mo_num > 0)
THEN
---update the staging table with the RequisitionNumber and Rquest_id----
UPDATE xxey_itsr_request_header
SET requisition_number = ln_req_num,
-- request_id = gn_req_id,
process_flag = 'P',
request_status = 'PSMI',
error_message = NULL,
last_update_date = SYSDATE,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE REQ_HEADER_ID = p_header_id;
COMMIT;
ELSE
lc_rec_status := 'E';
lc_err_msg := 'Move order not Created';
END IF;
ELSE
lc_rec_status := 'E';
lc_err_msg :=
'Error in Move order procedure: ' || lc_err_msg2;
END IF;
ELSE
lc_rec_status := 'E';
lc_err_msg := 'Requisition not created';
END IF;
ELSE
lc_rec_status := 'E';
lc_err_msg := 'Requisition Import completed with error';
END IF;
ELSE
lc_rec_status := 'E';
lc_err_msg := 'Requisition Import Failed to submit';
END IF;
ELSE
lc_rec_status := 'E';
lc_err_msg := 'Error in lines';
END IF;
IF lc_rec_status = 'E'
THEN
---Updating the staging Table---
UPDATE xxey_itsr_request_header
SET -- request_id = gn_req_id,
REQUEST_STATUS_MEANING = 'Error in IR/MO Interface',
last_update_date = SYSDATE,
process_flag = lc_rec_status,
error_message = lc_err_msg,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE REQ_HEADER_ID = p_header_id;
END IF;
EXECUTE IMMEDIATE 'COMMIT';
EXCEPTION
WHEN OTHERS
THEN
lc_err_msg := SUBSTR (SQLERRM, 1, 100);
---Updating the staging Table---
UPDATE xxey_itsr_request_header
SET process_flag = 'E',
error_message = lc_err_msg,
-- request_id = gn_req_id,
last_update_date = SYSDATE,
last_updated_by = gn_user_id,
last_update_login = gn_login_id
WHERE REQ_HEADER_ID = p_header_id;
EXECUTE IMMEDIATE 'COMMIT';
END main_proc;
PROCEDURE submit_program (p_err_msg OUT VARCHAR2, p_header_id IN NUMBER)
AS
ln_req_id NUMBER;
ln_po_req_id NUMBER;
lc_service_type VARCHAR2 (240);
BEGIN
fnd_global.apps_initialize (21879, 20707, 201);
---submitting the concurrent program in custom application-----
ln_req_id :=
fnd_request.submit_request (application => 'XXCUST',
program => 'XXEYIRC',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => p_header_id);
COMMIT;
IF ln_req_id <= 0
THEN
p_err_msg := 'Requisition Interface Program failed to submit';
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_err_msg := SQLERRM;
END submit_program;
PROCEDURE create_move_order (p_errbuf OUT VARCHAR2,
p_requisition_num IN VARCHAR2,
p_org_id IN NUMBER)
IS
lc_header_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
lc_line_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
ln_msg_count NUMBER;
lc_msg_data VARCHAR2 (4000);
lc_msg VARCHAR2 (4000);
ln_line_num NUMBER := 0;
ln_order_count NUMBER := 1;
ln_count NUMBER;
ln_header_id NUMBER;
ln_line_id NUMBER;
ln_user_id NUMBER;
ln_trx_type_id NUMBER;
lcu_trohdr_rec inv_move_order_pub.trohdr_rec_type;
lcu_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
lcu_trolin_tbl inv_move_order_pub.trolin_tbl_type;
lcu_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
ln_mo_num NUMBER;
lc_rec_status VARCHAR2 (1);
lc_err_msg VARCHAR2 (32766);
--
CURSOR cur_req_header (
p_req_no VARCHAR2,
p_org_id NUMBER)
IS
SELECT DISTINCT prh.segment1,
prh.org_id,
prl.source_organization_id,
prl.need_by_date,
prh.created_by
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prh.segment1 = p_req_no
AND prh.org_id = p_org_id
AND prl.org_id = prh.org_id
AND prl.source_type_code = 'INVENTORY'
AND prh.authorization_status = 'APPROVED'
AND prd.requisition_line_id(+) = prl.requisition_line_id
AND NVL (prh.attribute14, 'N') <> 'Y'
AND NVL (prl.cancel_flag, 'N') <> 'Y';
---------Lines Information for Move Orders for End User
CURSOR cur_req_line (
p_req_no VARCHAR2,
p_org_id NUMBER,
p_src_org NUMBER)
IS
SELECT DISTINCT prl.requisition_line_id,
prl.item_id,
prl.unit_meas_lookup_code,
prl.created_by,
prl.quantity,
prl.source_subinventory,
prl.source_organization_id,
prd.code_combination_id,
prl.need_by_date,
prl.destination_subinventory
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prh.segment1 = p_req_no
AND prl.source_organization_id = p_src_org
AND prh.org_id = p_org_id
AND prl.org_id = prh.org_id
AND prl.source_type_code = 'INVENTORY'
AND prh.authorization_status = 'APPROVED'
AND prd.requisition_line_id(+) = prl.requisition_line_id
AND NVL (prl.attribute14, 'N') <> 'Y'
AND NVL (prl.cancel_flag, 'N') <> 'Y';
BEGIN
SELECT transaction_type_id
INTO ln_trx_type_id
FROM mtl_transaction_types
WHERE attribute10 = 'Y'
AND transaction_type_name LIKE 'EY%'
AND transaction_action_id = 1;
FOR var_cur_req_header IN cur_req_header (p_requisition_num, p_org_id)
LOOP
BEGIN
SELECT COUNT (DISTINCT header_id)
INTO ln_count
FROM mtl_txn_request_headers
WHERE request_number = var_cur_req_header.segment1
AND organization_id =
var_cur_req_header.source_organization_id;
EXCEPTION
WHEN OTHERS
THEN
ln_count := 0;
END;
IF ln_count = 0
THEN
-----
SELECT mtl_txn_request_headers_s.NEXTVAL
INTO ln_header_id
FROM DUAL;
-----
ln_line_num := 0;
lcu_trohdr_rec.created_by := var_cur_req_header.created_by;
lcu_trohdr_rec.creation_date := SYSDATE;
-- l_trohdr_rec.date_required := var_cur_req_header.need_by_date;
--l_trohdr_rec.from_subinventory_code := NULL;
lcu_trohdr_rec.header_status :=
inv_globals.g_to_status_preapproved;
--inv_globals.g_to_status_incomplete;
lcu_trohdr_rec.last_updated_by := var_cur_req_header.created_by;
lcu_trohdr_rec.last_update_date := SYSDATE;
lcu_trohdr_rec.last_update_login := var_cur_req_header.created_by;
lcu_trohdr_rec.request_number := var_cur_req_header.segment1;
lcu_trohdr_rec.organization_id :=
var_cur_req_header.source_organization_id;
lcu_trohdr_rec.status_date := SYSDATE;
lcu_trohdr_rec.header_id := ln_header_id;
lcu_trohdr_rec.to_subinventory_code := NULL;
lcu_trohdr_rec.move_order_type :=
inv_globals.g_move_order_requisition;
lcu_trohdr_rec.transaction_type_id := ln_trx_type_id;
lcu_trohdr_rec.db_flag := fnd_api.g_true;
lcu_trohdr_rec.operation := inv_globals.g_opr_create;
--initialization Parameters.
fnd_global.apps_initialize (user_id => gn_user_id,
resp_id => gn_resp_id,
resp_appl_id => gn_resp_appl_id);
inv_move_order_pub.create_move_order_header (
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => lc_header_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_trohdr_rec => lcu_trohdr_rec,
p_trohdr_val_rec => lcu_trohdr_val_rec,
x_trohdr_rec => lcu_trohdr_rec,
x_trohdr_val_rec => lcu_trohdr_val_rec);
IF (lc_header_status != fnd_api.g_ret_sts_success)
THEN
IF ln_msg_count IS NOT NULL
THEN
FOR i IN 1 .. ln_msg_count
LOOP
p_errbuf :=
SUBSTR (p_errbuf || fnd_msg_pub.get (i, NULL), 100);
END LOOP;
END IF;
END IF;
FOR var_cur_req_line
IN cur_req_line (var_cur_req_header.segment1,
var_cur_req_header.org_id,
var_cur_req_header.source_organization_id)
LOOP
SELECT mtl_txn_request_lines_s.NEXTVAL
INTO ln_line_id
FROM DUAL;
ln_line_num := ln_line_num + 1;
lcu_trolin_tbl (ln_line_num).created_by :=
var_cur_req_line.created_by;
lcu_trolin_tbl (ln_line_num).creation_date := SYSDATE;
lcu_trolin_tbl (ln_line_num).date_required :=
var_cur_req_line.need_by_date;
lcu_trolin_tbl (ln_line_num).from_subinventory_code :=
var_cur_req_line.source_subinventory;
lcu_trolin_tbl (ln_line_num).inventory_item_id :=
var_cur_req_line.item_id;
lcu_trolin_tbl (ln_line_num).last_updated_by :=
var_cur_req_line.created_by;
lcu_trolin_tbl (ln_line_num).last_update_date := SYSDATE;
lcu_trolin_tbl (ln_line_num).last_update_login :=
var_cur_req_line.created_by;
lcu_trolin_tbl (ln_line_num).line_id := ln_line_id;
lcu_trolin_tbl (ln_line_num).line_number := ln_line_num;
lcu_trolin_tbl (ln_line_num).header_id := ln_header_id;
--l_trolin_tbl(l_line_num).MOVE_ORDER_TYPE_NAME := 'Requisition';
lcu_trolin_tbl (ln_line_num).line_status :=
inv_globals.g_to_status_preapproved;
--inv_globals.g_to_status_approved;
lcu_trolin_tbl (ln_line_num).organization_id :=
var_cur_req_line.source_organization_id;
lcu_trolin_tbl (ln_line_num).quantity :=
var_cur_req_line.quantity;
lcu_trolin_tbl (ln_line_num).status_date := SYSDATE;
--l_trolin_tbl(l_line_num).REQUEST_NUMBER:= VAR_CUR_REQ_LINE.SEGMENT1;
--l_trolin_tbl (l_line_num).to_subinventory_code := var_cur_req_line.destination_subinventory;
lcu_trolin_tbl (ln_line_num).to_subinventory_code := NULL;
SELECT uom_code
INTO lcu_trolin_tbl (ln_line_num).uom_code
FROM mtl_units_of_measure_vl
WHERE TRUNC (SYSDATE) <
TRUNC (NVL (disable_date, SYSDATE + 1))
AND unit_of_measure =
var_cur_req_line.unit_meas_lookup_code;
--lcu_trolin_tbl(ln_line_num).uom_code :=var_cur_req_line.unit_meas_lookup_code;
lcu_trolin_tbl (ln_line_num).transaction_type_id :=
ln_trx_type_id;
lcu_trolin_tbl (ln_line_num).to_account_id :=
var_cur_req_line.code_combination_id;
lcu_trolin_tbl (ln_line_num).db_flag := fnd_api.g_true;
lcu_trolin_tbl (ln_line_num).operation :=
inv_globals.g_opr_create;
--initialization Parameters.
fnd_global.apps_initialize (user_id => gn_user_id,
resp_id => gn_resp_id,
resp_appl_id => gn_resp_appl_id);
inv_move_order_pub.create_move_order_lines (
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => lc_line_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_trolin_tbl => lcu_trolin_tbl, --l_trolin_rec
p_trolin_val_tbl => lcu_trolin_val_tbl,
x_trolin_tbl => lcu_trolin_tbl,
x_trolin_val_tbl => lcu_trolin_val_tbl);
---------------
IF (lc_line_status != fnd_api.g_ret_sts_success)
THEN
IF ln_msg_count IS NOT NULL
THEN
FOR i IN 1 .. ln_msg_count
LOOP
p_errbuf :=
SUBSTR (p_errbuf || fnd_msg_pub.get (i, NULL),
100);
END LOOP;
END IF;
END IF;
--Update the Requisition Lines .........
IF lc_line_status = 'S'
THEN
UPDATE po_requisition_lines_all
SET attribute14 = 'Y'
WHERE requisition_header_id =
(SELECT requisition_header_id
FROM po_requisition_headers_all
WHERE segment1 = var_cur_req_header.segment1
AND source_organization_id =
var_cur_req_header.source_organization_id)
AND org_id = var_cur_req_header.org_id;
END IF;
END LOOP;
--Update the Requisition.........
IF lc_header_status = 'S'
THEN
UPDATE po_requisition_headers_all
SET attribute14 = 'Y',
description =
description || ' ' || '(Converted to Move Order)'
WHERE segment1 = var_cur_req_header.segment1
AND org_id = var_cur_req_header.org_id;
END IF;
ELSE
p_errbuf := 'Move order already created';
END IF;
END LOOP;
EXECUTE IMMEDIATE 'COMMIT';
EXCEPTION
WHEN OTHERS
THEN
p_errbuf := SUBSTR (SQLERRM, 1, 100);
END;
END xxey_itsr_po_req_int_pkg1;
/
No comments:
Post a Comment