[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_OL_PO_DELETED_STATUS CONSTANT VARCHAR2(30) := 'PO_DELETED';
G_TXN_TYPE_PO_UPDATE CONSTANT VARCHAR2(30) := 'PO Update';
PROCEDURE Insert_PO_Header(
p_po_header_rec IN PO_Header_Rec_Type,
x_intf_header_id OUT NOCOPY NUMBER,
x_batch_id OUT NOCOPY NUMBER);
PROCEDURE Insert_PO_Lines(
p_po_line_tbl IN PO_Line_Tbl_Type,
p_intf_header_id IN NUMBER);
PROCEDURE Handle_Deleted_PO_Lines(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE Update_OSP_Order(
p_osp_order_id IN NUMBER,
p_batch_id IN NUMBER := NULL,
p_request_id IN NUMBER := NULL,
p_status_code IN VARCHAR2 := NULL,
p_po_header_id IN NUMBER := NULL,
p_intf_hdr_id IN NUMBER := NULL);
/** The following two procedures Handle_Deleted_PO_Headers and Handle_Deleted_Sales_Orders
* were added by jaramana on March 31, 2006 to implement the ER 5074660
***/
-- This Procedure handles deleted PO Headers and is Part of PO Synchronization.
-- This procedure commits its work if p_commit is set to true and
-- if there were no errors during the execution of this procedure.
-- It does not check the message list for performing the commit action
PROCEDURE Handle_Deleted_PO_Headers(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE Handle_Deleted_Sales_Orders(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
SELECT OSP_ORDER_ID
FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_NUMBER = p_osp_order_number;
SELECT VENDOR_ID, VENDOR_SITE_ID, PO_AGENT_ID, PO_BATCH_ID, PO_INTERFACE_HEADER_ID, VENDOR_CONTACT_ID
, STATUS_CODE -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id;
SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
OL.SERVICE_ITEM_DESCRIPTION, WO.QUANTITY, OL.NEED_BY_DATE,
OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
BOM.ORGANIZATION_ID, BOM.LOCATION_ID
FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_V WO, BOM_DEPARTMENTS BOM
WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
WO.WORKORDER_ID = OL.WORKORDER_ID AND
BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
OL.SERVICE_ITEM_DESCRIPTION, OL.QUANTITY, OL.NEED_BY_DATE,
OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
/**
OL.INVENTORY_ORG_ID, BOM.LOCATION_ID
FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
**/
OL.INVENTORY_ORG_ID, DECODE(OL.WORKORDER_ID, NULL, HAOU.LOCATION_ID, BOM.LOCATION_ID)
-- Added by mpothuku on 10-oct-2007 to fix bug 6431740
, WO.WIP_ENTITY_ID
, WDJ.PROJECT_ID
, WDJ.TASK_ID
/*
FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU
*/
-- Changes made by jaramana on December 19, 2005
-- to improve the performace of this SQL.
-- Removed reference to AHL_WORKORDERS_OSP_V and instead joined directly with
-- WIP_DISCRETE_JOBS to get the work order department
FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU, WIP_DISCRETE_JOBS WDJ
WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
WO.WORKORDER_ID (+) = OL.WORKORDER_ID AND
/**
BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
SELECT oola.ship_from_org_id, HAOU.LOCATION_ID
FROM oe_order_lines_all oola, ahl_osp_order_lines aool, HR_ALL_ORGANIZATION_UNITS HAOU
WHERE oola.line_id = aool.oe_return_line_id and
HAOU.ORGANIZATION_ID = oola.ship_from_org_id and
aool.osp_order_line_id = p_osp_line_id;
l_po_line_tbl.DELETE(l_temp_num); -- Delete the last (null) record
Insert_PO_Header(p_po_header_rec => l_po_header,
x_intf_header_id => l_intf_hdr_id,
x_batch_id => l_batch_id);
Insert_PO_Lines(p_po_line_tbl => l_po_line_tbl,
p_intf_header_id => l_intf_hdr_id);
DELETE FROM PO_INTERFACE_ERRORS WHERE
INTERFACE_HEADER_ID = l_old_intf_header_id;
DELETE FROM PO_HEADERS_INTERFACE WHERE
INTERFACE_HEADER_ID = l_old_intf_header_id;
DELETE FROM PO_LINES_INTERFACE WHERE
INTERFACE_HEADER_ID = l_old_intf_header_id;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE WHERE
INTERFACE_HEADER_ID = l_old_intf_header_id;
DELETE FROM po_interface_errors
WHERE INTERFACE_TRANSACTION_ID in
(SELECT transaction_id
FROM po_requisitions_interface_all
WHERE INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
AND INTERFACE_SOURCE_LINE_ID = l_po_header.OSP_ORDER_ID);
DELETE FROM po_requisitions_interface_all
WHERE INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
AND INTERFACE_SOURCE_LINE_ID = l_po_header.OSP_ORDER_ID;
SELECT PO_HEADER_ID FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id;
SELECT OSP_ORDER_ID FROM AHL_OSP_ORDERS_B
WHERE STATUS_CODE = G_OSP_SUBMITTED_STATUS
AND PO_HEADER_ID IS NULL
AND PO_BATCH_ID IS NOT NULL
-- Added by jaramana on April 7, 2008 for bug 6609988
AND OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id();
/** The following calls to the Handle_Deleted_PO_Headers and Handle_Deleted_Sales_Orders
* procedures were added by jaramana on March 31, 2006 to implement the ER 5074660
***/
-- Handle Deleted PO Headers
HANDLE_DELETED_PO_HEADERS(p_commit => p_commit, --Commit this independent of other operations
x_return_status => x_return_status);
fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted PO Headers');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Headers, Return Status = ' || x_return_status);
HANDLE_DELETED_SALES_ORDERS(p_commit => p_commit, --Commit this independent of other operations
x_return_status => x_return_status);
fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted Sales Orders');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted Sales Orders, Return Status = ' || x_return_status);
HANDLE_DELETED_PO_LINES(p_commit => p_commit, --Commit this independent of other operations
x_return_status => x_return_status);
fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted PO Lines');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Lines, Return Status = ' || x_return_status);
SELECT NVL(CLOSED_CODE, G_PO_OPEN), NVL(CANCEL_FLAG, 'N') FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_po_header_id;
SELECT PL.PO_LINE_ID
FROM PO_LINES_ALL PL, AHL_OSP_ORDERS_B OSP
WHERE PL.PO_HEADER_ID = OSP.PO_HEADER_ID AND
OSP.OSP_ORDER_ID = p_osp_order_id AND
NVL(PL.CANCEL_FLAG, 'N') <> 'Y' AND
PL.PO_LINE_ID NOT IN (SELECT PO_LINE_ID from AHL_OSP_ORDER_LINES
WHERE OSP_ORDER_ID = p_osp_order_id);
SELECT 'x' FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id
-- The following condition commented out by jaramana on request of jeli
-- on May 27, 2005 so that status update can be done later.
-- AND STATUS_CODE = G_OSP_SUBMITTED_STATUS
FOR UPDATE OF PO_BATCH_ID, PO_REQUEST_ID; -- Lock Row
SELECT 'x' FROM PO_VENDORS_VIEW
WHERE VENDOR_ID = p_supplier_id
AND ENABLED_FLAG = G_YES_FLAG
AND NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
SELECT 'x' FROM PO_VENDOR_SITES
WHERE VENDOR_SITE_ID = p_supp_site_id
AND VENDOR_ID = p_supp_id
AND NVL(INACTIVE_DATE, SYSDATE + 1) > SYSDATE
AND NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) = G_NO_FLAG
AND PURCHASING_SITE_FLAG = G_YES_FLAG;
SELECT 'x' FROM PO_VENDOR_CONTACTS
WHERE VENDOR_CONTACT_ID = p_vendor_contact_id
AND VENDOR_SITE_ID = p_supp_site_id;
SELECT 'x' FROM PO_AGENTS_NAME_V
WHERE BUYER_ID = p_buyer_id;
SELECT 'x' FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = p_item_id
AND ENABLED_FLAG = G_YES_FLAG
AND PURCHASING_ENABLED_FLAG = G_YES_FLAG
AND INVENTORY_ITEM_FLAG = G_NO_FLAG -- No Physical Items
AND NVL(START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
AND ORGANIZATION_ID = p_org_id
AND NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG;
SELECT 'x' FROM PO_LINE_TYPES
WHERE ORDER_TYPE_LOOKUP_CODE = G_PO_LINE_TYPE_QUANTITY
AND NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG
AND LINE_TYPE_ID = p_line_type_id;
PROCEDURE Insert_PO_Header(
p_po_header_rec IN PO_Header_Rec_Type,
x_intf_header_id OUT NOCOPY NUMBER,
x_batch_id OUT NOCOPY NUMBER) IS
CURSOR l_get_osp_order_dtls_csr(p_osp_order_id IN NUMBER) IS
SELECT OSP_ORDER_NUMBER, DESCRIPTION FROM AHL_OSP_ORDERS_VL
WHERE OSP_ORDER_ID = p_osp_order_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Insert_PO_Header';
SELECT NVL(MAX(batch_id), 0) + 1 INTO l_batch_id FROM po_headers_interface;
SELECT PO_HEADERS_INTERFACE_S.NEXTVAL INTO l_intf_hdr_id FROM sys.dual;
SELECT invoice_currency_code INTO l_temp_v FROM po_vendor_sites
WHERE vendor_site_id = p_po_header_rec.vendor_site_id AND
vendor_id = p_po_header_rec.vendor_id;
SELECT invoice_currency_code INTO l_temp_v FROM po_vendors
WHERE vendor_id = p_po_header_rec.vendor_id;
SELECT GSB.currency_code INTO l_currency_code
FROM FINANCIALS_SYSTEM_PARAMETERS FSP, GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id;
INSERT INTO PO_HEADERS_INTERFACE (
INTERFACE_HEADER_ID,
BATCH_ID,
INTERFACE_SOURCE_CODE,
PROCESS_CODE,
ACTION,
DOCUMENT_TYPE_CODE,
CURRENCY_CODE,
AGENT_ID,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID, -- Added by jaramana on May 27, 2005 for Inventory Service Orders
COMMENTS,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
REFERENCE_NUM,
DOCUMENT_NUM, -- SATHAPLI::Bug 8583364, 21-Aug-2009, l_manual_po_number to be passed - either NULL or created
ORG_ID -- Added by jaramana on Sep 9, 2005 for MOAC Uptake
) VALUES (
l_intf_hdr_id,
l_batch_id,
AHL_GLOBAL.AHL_APP_SHORT_NAME, -- INTERFACE_SOURCE_CODE = 'AHL'
G_PROCESS_CODE, -- 'PENDING'
G_ACTION_CODE, -- 'ORIGINAL'
G_DOC_TYPE_CODE, -- 'STANDARD'
l_currency_code,
p_po_header_rec.BUYER_ID,
p_po_header_rec.VENDOR_ID,
p_po_header_rec.VENDOR_SITE_ID,
p_po_header_rec.VENDOR_CONTACT_ID, -- Added by jaramana on May 27, 2005 for Inventory Service Orders
l_description,
AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
AHL_GLOBAL.AHL_APPLICATION_ID,
p_po_header_rec.OSP_ORDER_ID,
l_manual_po_number, -- SATHAPLI::Bug 8583364, 21-Aug-2009, l_manual_po_number to be passed - either NULL or created
l_curr_org_id -- Added by jaramana on Sep 9, 2005 for MOAC Uptake
);
END Insert_PO_Header;
PROCEDURE Insert_PO_Lines(
p_po_line_tbl IN PO_Line_Tbl_Type,
p_intf_header_id IN NUMBER) IS
-- Commented out by jaramana on June 22, 2005
-- Calling the new utility method Get_Item_Price instead
/*
CURSOR l_chk_price_csr(p_item_id IN NUMBER,
p_org_id IN NUMBER) IS
SELECT 'x' FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_org_id
AND LIST_PRICE_PER_UNIT IS NOT NULL;
SELECT COMPLETION_DATE from pa_tasks
where task_id = c_task_id;
SELECT vtsk.end_date_time
from ahl_visit_tasks_b vtsk,
ahl_osp_order_lines ospl,
ahl_workorders wo
where ospl.osp_order_line_id = c_osp_line_id
and ospl.workorder_id = wo.workorder_id
and wo.visit_task_id = vtsk.visit_task_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Insert_PO_Lines';
SELECT PO_LINES_INTERFACE_S.NEXTVAL INTO l_intf_line_id FROM sys.dual;
INSERT INTO PO_LINES_INTERFACE (
INTERFACE_LINE_ID,
INTERFACE_HEADER_ID,
LINE_NUM,
LINE_TYPE_ID,
ITEM_ID,
ITEM_DESCRIPTION,
CATEGORY,
UOM_CODE,
UNIT_PRICE,
QUANTITY,
NEED_BY_DATE,
LINE_REFERENCE_NUM,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID
) VALUES (
l_intf_line_id,
p_intf_header_id,
p_po_line_tbl(i).LINE_NUMBER,
p_po_line_tbl(i).PO_LINE_TYPE_ID,
p_po_line_tbl(i).ITEM_ID,
p_po_line_tbl(i).ITEM_DESCRIPTION,
l_category,
p_po_line_tbl(i).UOM_CODE,
l_price,
p_po_line_tbl(i).QUANTITY,
p_po_line_tbl(i).NEED_BY_DATE,
p_po_line_tbl(i).OSP_LINE_ID,
p_po_line_tbl(i).SHIP_TO_ORG_ID,
p_po_line_tbl(i).SHIP_TO_LOC_ID,
AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
AHL_GLOBAL.AHL_APPLICATION_ID
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id before inserting: '|| l_charge_acct_id);
INSERT INTO PO_DISTRIBUTIONS_INTERFACE (
INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
QUANTITY_ORDERED,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
CREATION_DATE,
CREATED_BY
-- Added by mpothuku on 10-oct-2007 to fix bug 6431740
,WIP_ENTITY_ID
,PROJECT_RELEATED_FLAG
,PROJECT_ACCOUNTING_CONTEXT
,PROJECT_ID
,TASK_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ORGANIZATION_ID
,EXPENDITURE_ITEM_DATE
,CHARGE_ACCOUNT_ID
) VALUES (
p_intf_header_id,
l_intf_line_id,
PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
p_po_line_tbl(i).QUANTITY,
AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
AHL_GLOBAL.AHL_APPLICATION_ID,
SYSDATE,
FND_GLOBAL.USER_ID
-- Added by mpothuku on 10-oct-2007 to fix bug 6431740
,p_po_line_tbl(i).wip_entity_id
,DECODE(p_po_line_tbl(i).project_id, null, null, 'Y')
,DECODE(p_po_line_tbl(i).project_id, null, null, 'Y')
,p_po_line_tbl(i).project_id
,p_po_line_tbl(i).task_id
,DECODE(p_po_line_tbl(i).project_id, null, null, l_expenditure_item_type)
,DECODE(p_po_line_tbl(i).project_id, null, null,p_po_line_tbl(i).SHIP_TO_ORG_ID)
,l_task_completion_date
,l_charge_acct_id
);
' Inserting into PO_DISTRIBUTIONS_INTERFACE. INTERFACE_HEADER_ID = ' || p_intf_header_id ||
', INTERFACE_LINE_ID = ' || l_intf_line_id ||
', WIP_ENTITY_ID = ' || p_po_line_tbl(i).wip_entity_id ||
', PROJECT_ID = ' || p_po_line_tbl(i).project_id ||
', TASK_ID = ' || p_po_line_tbl(i).task_id ||
', EXPENDITURE_TYPE = ' ||l_expenditure_item_type ||
', EXPENDITURE_ORGANIZATION_ID = ' || p_po_line_tbl(i).SHIP_TO_ORG_ID ||
', EXPENDITURE_ITEM_DATE = ' || to_char(l_task_completion_date, 'DD-MON-YYYY HH24:MI:SS'));
END Insert_PO_Lines;
argument4 => G_NO_FLAG, -- Create or Update Items
argument5 => NULL, -- Create Sourcing Rules
argument6 => G_INCOMPLETE_STATUS, -- Approval Status
argument7 => NULL, -- Release Generation Method
argument8 => p_batch_id, -- Batch Id
argument9 => NULL, --Org Id
argument10 => NULL, --global agreement flag
--Changes by jrotich 04/04/2013 for fixing bug 16088388 Begin
argument11=>'',argument12=>'',argument13=>'',argument14=>'',argument15=>'',
argument16=>'',argument17=>'',argument18=>'',argument19=>'',argument20=>'',
argument21=>'',argument22=>'',argument23=>'',argument24=>'',argument25=>'',
argument26=>'',argument27=>'',argument28=>'',argument29=>'',argument30=>'',
argument31=>'',argument32=>'',argument33=>'',argument34=>'',argument35=>'',
argument36=>'',argument37=>'',argument38=>'',argument39=>'',argument40=>'',
argument41=>'',argument42=>'',argument43=>'',argument44=>'',argument45=>'',
argument46=>'',argument47=>'',argument48=>'',argument49=>'',argument50=>'',
argument51=>'',argument52=>'',argument53=>'',argument54=>'',argument55=>'',
argument56=>'',argument57=>'',argument58=>'',argument59=>'',argument60=>'',
argument61=>'',argument62=>'',argument63=>'',argument64=>'',argument65=>'',
argument66=>'',argument67=>'',argument68=>'',argument69=>'',argument70=>'',
argument71=>'',argument72=>'',argument73=>'',argument74=>'',argument75=>'',
argument76=>'',argument77=>'',argument78=>'',argument79=>'',argument80=>'',
argument81=>'',argument82=>'',argument83=>'',argument84=>'',argument85=>'',
argument86=>'',argument87=>'',argument88=>'',argument89=>'',argument90=>'',
argument91=>'',argument92=>'',argument93=>'',argument94=>'',argument95=>'',
argument96=>'',argument97=>'',argument98=>'',argument99=>'',argument100=>''
--Changes by jrotich end
);
X_selected_batch_id => p_batch_id,
X_buyer_id => NULL,
X_document_type => G_DOC_TYPE_CODE,
X_document_subtype => NULL,
X_create_items => G_NO_FLAG,
X_create_sourcing_rules_flag => G_NO_FLAG,
X_rel_gen_method => NULL,
X_approved_status => NULL,
X_commit_interval => 1,
X_process_code => G_PROCESS_CODE);
SELECT PO_HEADER_ID FROM PO_HEADERS_ALL
WHERE REFERENCE_NUM = p_osp_order_id AND
INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
SELECT PO.PO_HEADER_ID FROM PO_HEADERS_ALL PO, AHL_OSP_ORDERS_B OSP
WHERE PO.REFERENCE_NUM = p_osp_order_id AND
OSP.OSP_ORDER_ID = p_osp_order_id AND
PO.VENDOR_ID = OSP.VENDOR_ID AND
PO.VENDOR_SITE_ID = OSP.VENDOR_SITE_ID AND
PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
SELECT OSP_ORDER_LINE_ID FROM AHL_OSP_ORDER_LINES
WHERE PO_LINE_ID IS NULL
AND OSP_ORDER_ID = p_osp_order_id;
SELECT PO_LINE_ID FROM PO_LINES_ALL
WHERE LINE_REFERENCE_NUM = p_osp_order_line_id AND
PO_HEADER_ID = p_po_header_id;
SELECT PO_REQUEST_ID FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id;
SELECT PL.PO_LINE_ID, OL.OSP_ORDER_LINE_ID, PO.REFERENCE_NUM,
OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
FROM PO_LINES_ALL PL, PO_HEADERS_ALL PO, AHL_OSP_ORDER_LINES OL,
AHL_OSP_ORDERS_B OSP
WHERE PL.CANCEL_FLAG = 'Y' AND -- Canceled PO Line
PL.PO_HEADER_ID = PO.PO_HEADER_ID AND
PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME AND -- AHL Created PO
PO.REFERENCE_NUM = OL.OSP_ORDER_ID AND -- Related to the OSP Order
OSP.OSP_ORDER_ID = PO.REFERENCE_NUM AND
-- Added by jaramana on April 7, 2008 for bug 6609988
OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
OL.PO_LINE_ID = PL.PO_LINE_ID AND
NVL(OL.STATUS_CODE, ' ') <> G_OL_PO_CANCELLED_STATUS -- Not yet updated
ORDER BY PO.REFERENCE_NUM; -- One OSP Order at a time
l_osp_order_lines_tbl.DELETE;
l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
PROCEDURE Handle_Deleted_PO_Lines(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR l_get_deleted_po_lines_csr IS
SELECT OL.OSP_ORDER_ID, OL.OSP_ORDER_LINE_ID,
OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
FROM AHL_OSP_ORDER_LINES OL, AHL_OSP_ORDERS_B OSP
WHERE OL.PO_LINE_ID IS NOT NULL AND -- PO Created
NVL(OL.STATUS_CODE, ' ') <> G_OL_PO_DELETED_STATUS AND -- Not yet updated
OSP.OSP_ORDER_ID = OL.OSP_ORDER_ID AND
-- Added by jaramana on April 7, 2008 for bug 6609988
OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
NOT EXISTS (SELECT PO_LINE_ID FROM PO_LINES_ALL WHERE PO_LINE_ID = OL.PO_LINE_ID) -- PO Line Deleted
ORDER BY OL.OSP_ORDER_ID; -- One OSP Order at a time
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Lines';
SAVEPOINT Handle_Deleted_PO_Lines_pvt;
OPEN l_get_deleted_po_lines_csr;
FETCH l_get_deleted_po_lines_csr INTO l_osp_order_id,
l_osp_order_line_id,
l_ol_obj_ver_num,
l_osp_obj_ver_num;
EXIT WHEN l_get_deleted_po_lines_csr%NOTFOUND;
l_osp_order_lines_tbl.DELETE;
l_osp_order_lines_tbl(l_table_index).STATUS_CODE := G_OL_PO_DELETED_STATUS;
l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
CLOSE l_get_deleted_po_lines_csr;
ROLLBACK TO Handle_Deleted_PO_Lines_pvt;
ROLLBACK TO Handle_Deleted_PO_Lines_pvt;
ROLLBACK TO Handle_Deleted_PO_Lines_pvt;
p_procedure_name => 'Handle_Deleted_PO_Lines',
p_error_text => SUBSTR(SQLERRM,1,240));
END Handle_Deleted_PO_Lines;
SELECT OSP.OSP_ORDER_ID, OSP.PO_HEADER_ID, OSP.OE_HEADER_ID
FROM AHL_OSP_ORDERS_B OSP, PO_HEADERS_ALL PO
WHERE OSP.STATUS_CODE = G_OSP_PO_CREATED_STATUS AND -- PO Created
OSP.PO_HEADER_ID = PO.PO_HEADER_ID AND -- Join
-- Added by jaramana on April 7, 2008 for bug 6609988
OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
PO.APPROVED_FLAG = G_YES_FLAG AND -- Approved PO
NVL(PO.CANCEL_FLAG, G_NO_FLAG) <> G_YES_FLAG AND -- Not Cancelled
NVL(PO.CLOSED_CODE, G_PO_OPEN) NOT LIKE '%CLOSED'; -- Not Closed
/** The following two procedures Handle_Deleted_PO_Headers and Handle_Deleted_Sales_Orders
* were added by jaramana on March 31, 2006 to implement the ER 5074660
***/
----------------------------------------
-- This Procedure handles Deleted PO Headers and is Part of PO Synchronization.
-- This procedure commits its work if p_commit is set to true and
-- if there were no errors during the execution of this procedure.
-- It does not check the message list for performing the commit action.
-- Functionality:
-- After a PO has been created for an OSP Order, it is possible for the PO
-- to be manually deleted (using Purchasing responsibility) before the PO is approved.
-- Since this deletion will result in an OSP Order referring to a non-existent PO,
-- we need to change the OSP order to bring it to a consistent state.
-- This procedure basically looks for OSP Orders for which the PO has been deleted
-- and resets some values and corrects the status of the order as well as the lines
-- so that the OSP Order can be resubmitted and a different PO can be created.
-- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
-- tables and does not call the process_osp_order API to avoid unwanted validations
----------------------------------------
PROCEDURE Handle_Deleted_PO_Headers(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR get_po_deleted_osps_csr IS
SELECT osp.osp_order_id,
osp.object_version_number,
osp.po_header_id,
osp.status_code,
osp.order_type_code
FROM ahl_osp_orders_b osp
WHERE osp.status_code = G_OSP_PO_CREATED_STATUS AND
osp.order_type_code in ('SERVICE', 'EXCHANGE') AND
-- Added by jaramana on April 7, 2008 for bug 6609988
osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
NOT EXISTS (SELECT 1 FROM po_headers_all where po_header_id = osp.po_header_id);
SELECT ospl.osp_order_id,
ospl.osp_order_line_id,
ospl.object_version_number,
ospl.status_code,
ospl.po_line_id
FROM ahl_osp_order_lines ospl
WHERE ospl.osp_order_id = c_osp_order_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Headers';
l_osp_details_rec get_po_deleted_osps_csr%ROWTYPE;
SAVEPOINT Handle_Deleted_PO_Headers_pvt;
OPEN get_po_deleted_osps_csr;
FETCH get_po_deleted_osps_csr into l_osp_details_rec;
EXIT WHEN get_po_deleted_osps_csr%NOTFOUND;
DELETE FROM ahl_osp_order_lines
WHERE osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
update ahl_osp_orders_b
set po_header_id = null,
po_batch_id = null,
po_request_id = null,
po_interface_header_id = null,
status_code = G_OSP_ENTERED_STATUS,
object_version_number = l_osp_details_rec.object_version_number + 1,
last_update_date = TRUNC(sysdate),
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where osp_order_id = l_osp_details_rec.osp_order_id;
CLOSE get_po_deleted_osps_csr;
ROLLBACK TO Handle_Deleted_PO_Headers_pvt;
ROLLBACK TO Handle_Deleted_PO_Headers_pvt;
ROLLBACK TO Handle_Deleted_PO_Headers_pvt;
p_procedure_name => 'Handle_Deleted_PO_Headers',
p_error_text => SUBSTR(SQLERRM,1,240));
END Handle_Deleted_PO_Headers;
PROCEDURE Handle_Deleted_Sales_Orders(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR get_so_deleted_osps_csr IS
SELECT osp.osp_order_id,
osp.object_version_number,
osp.oe_header_id,
osp.status_code
FROM ahl_osp_orders_b osp
WHERE osp.status_code <> 'CLOSED' AND
osp.oe_header_id IS NOT NULL AND
-- Added by jaramana on April 7, 2008 for bug 6609988
osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
NOT EXISTS (SELECT 1 FROM oe_order_headers_all where header_id = osp.oe_header_id);
SELECT ospl.osp_order_line_id,
ospl.object_version_number,
ospl.oe_ship_line_id,
ospl.oe_return_line_id
FROM ahl_osp_order_lines ospl
WHERE ospl.osp_order_id = c_osp_order_id AND
(ospl.oe_ship_line_id IS NOT NULL OR ospl.oe_return_line_id IS NOT NULL);
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_Sales_Orders';
l_osp_details_rec get_so_deleted_osps_csr%ROWTYPE;
SAVEPOINT Handle_Deleted_SOs_pvt;
OPEN get_so_deleted_osps_csr;
FETCH get_so_deleted_osps_csr into l_osp_details_rec;
EXIT WHEN get_so_deleted_osps_csr%NOTFOUND;
update ahl_osp_order_lines
set oe_ship_line_id = null,
oe_return_line_id = null,
object_version_number = l_osp_line_details_rec.object_version_number + 1,
last_update_date = TRUNC(sysdate),
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
update ahl_osp_orders_b
set OE_HEADER_ID = null,
object_version_number = l_osp_details_rec.object_version_number + 1,
last_update_date = TRUNC(sysdate),
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where osp_order_id = l_osp_details_rec.osp_order_id;
CLOSE get_so_deleted_osps_csr;
ROLLBACK TO Handle_Deleted_SOs_pvt;
ROLLBACK TO Handle_Deleted_SOs_pvt;
ROLLBACK TO Handle_Deleted_SOs_pvt;
p_procedure_name => 'Handle_Deleted_Sales_Orders',
p_error_text => SUBSTR(SQLERRM,1,240));
END Handle_Deleted_Sales_Orders;
Update_OSP_Order(p_osp_order_id => p_osp_order_id,
p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_intf_hdr_id => p_intf_hdr_id);
Update_OSP_Order(p_osp_order_id => p_osp_order_id,
p_po_header_id => p_po_header_id,
p_status_code => G_OSP_PO_CREATED_STATUS);
Update_OSP_Order(p_osp_order_id => p_osp_order_id,
p_status_code => G_OSP_SUB_FAILED_STATUS);
SELECT
OBJECT_VERSION_NUMBER,
OSP_ORDER_ID,
OSP_LINE_NUMBER,
STATUS_CODE,
PO_LINE_TYPE_ID,
SERVICE_ITEM_ID,
SERVICE_ITEM_DESCRIPTION,
SERVICE_ITEM_UOM_CODE,
NEED_BY_DATE,
SHIP_BY_DATE,
PO_LINE_ID,
OE_SHIP_LINE_ID,
OE_RETURN_LINE_ID,
WORKORDER_ID,
OPERATION_ID,
QUANTITY,
EXCHANGE_INSTANCE_ID,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
INVENTORY_ITEM_UOM,
INVENTORY_ITEM_QUANTITY,
SUB_INVENTORY,
LOT_NUMBER,
SERIAL_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
-- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
PO_REQ_LINE_ID
-- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
FROM AHL_OSP_ORDER_LINES
WHERE OSP_ORDER_LINE_ID = p_osp_order_line_id;
AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW(
P_OSP_ORDER_LINE_ID => p_osp_order_line_id,
P_OBJECT_VERSION_NUMBER => l_osp_line_dtls_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
P_OSP_ORDER_ID => l_osp_line_dtls_rec.OSP_ORDER_ID,
P_OSP_LINE_NUMBER => l_osp_line_dtls_rec.OSP_LINE_NUMBER,
P_STATUS_CODE => l_osp_line_dtls_rec.STATUS_CODE,
P_PO_LINE_TYPE_ID => l_osp_line_dtls_rec.PO_LINE_TYPE_ID,
P_SERVICE_ITEM_ID => l_osp_line_dtls_rec.SERVICE_ITEM_ID,
P_SERVICE_ITEM_DESCRIPTION => l_osp_line_dtls_rec.SERVICE_ITEM_DESCRIPTION,
P_SERVICE_ITEM_UOM_CODE => l_osp_line_dtls_rec.SERVICE_ITEM_UOM_CODE,
P_NEED_BY_DATE => l_osp_line_dtls_rec.NEED_BY_DATE,
P_SHIP_BY_DATE => l_osp_line_dtls_rec.SHIP_BY_DATE,
P_PO_LINE_ID => l_osp_line_dtls_rec.PO_LINE_ID, -- Updated
P_OE_SHIP_LINE_ID => l_osp_line_dtls_rec.OE_SHIP_LINE_ID,
P_OE_RETURN_LINE_ID => l_osp_line_dtls_rec.OE_RETURN_LINE_ID,
P_WORKORDER_ID => l_osp_line_dtls_rec.WORKORDER_ID,
P_OPERATION_ID => l_osp_line_dtls_rec.OPERATION_ID,
P_QUANTITY => l_osp_line_dtls_rec.QUANTITY,
P_EXCHANGE_INSTANCE_ID => l_osp_line_dtls_rec.EXCHANGE_INSTANCE_ID,
P_INVENTORY_ITEM_ID => l_osp_line_dtls_rec.INVENTORY_ITEM_ID,
P_INVENTORY_ORG_ID => l_osp_line_dtls_rec.INVENTORY_ORG_ID,
P_INVENTORY_ITEM_UOM => l_osp_line_dtls_rec.INVENTORY_ITEM_UOM,
P_INVENTORY_ITEM_QUANTITY => l_osp_line_dtls_rec.INVENTORY_ITEM_QUANTITY,
P_SUB_INVENTORY => l_osp_line_dtls_rec.SUB_INVENTORY,
P_LOT_NUMBER => l_osp_line_dtls_rec.LOT_NUMBER,
P_SERIAL_NUMBER => l_osp_line_dtls_rec.SERIAL_NUMBER,
P_ATTRIBUTE_CATEGORY => l_osp_line_dtls_rec.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => l_osp_line_dtls_rec.ATTRIBUTE1,
P_ATTRIBUTE2 => l_osp_line_dtls_rec.ATTRIBUTE2,
P_ATTRIBUTE3 => l_osp_line_dtls_rec.ATTRIBUTE3,
P_ATTRIBUTE4 => l_osp_line_dtls_rec.ATTRIBUTE4,
P_ATTRIBUTE5 => l_osp_line_dtls_rec.ATTRIBUTE5,
P_ATTRIBUTE6 => l_osp_line_dtls_rec.ATTRIBUTE6,
P_ATTRIBUTE7 => l_osp_line_dtls_rec.ATTRIBUTE7,
P_ATTRIBUTE8 => l_osp_line_dtls_rec.ATTRIBUTE8,
P_ATTRIBUTE9 => l_osp_line_dtls_rec.ATTRIBUTE9,
P_ATTRIBUTE10 => l_osp_line_dtls_rec.ATTRIBUTE10,
P_ATTRIBUTE11 => l_osp_line_dtls_rec.ATTRIBUTE11,
P_ATTRIBUTE12 => l_osp_line_dtls_rec.ATTRIBUTE12,
P_ATTRIBUTE13 => l_osp_line_dtls_rec.ATTRIBUTE13,
P_ATTRIBUTE14 => l_osp_line_dtls_rec.ATTRIBUTE14,
P_ATTRIBUTE15 => l_osp_line_dtls_rec.ATTRIBUTE15,
-- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
P_PO_REQ_LINE_ID => l_osp_line_dtls_rec.PO_REQ_LINE_ID,
-- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
P_LAST_UPDATE_DATE => TRUNC(sysdate), -- Updated
P_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
P_LAST_UPDATE_LOGIN => fnd_global.login_id); -- Updated
PROCEDURE Update_OSP_Order(
p_osp_order_id IN NUMBER,
p_batch_id IN NUMBER := NULL,
p_request_id IN NUMBER := NULL,
p_status_code IN VARCHAR2 := NULL,
p_po_header_id IN NUMBER := NULL,
p_intf_hdr_id IN NUMBER := NULL
) IS
CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
SELECT
OBJECT_VERSION_NUMBER,
OSP_ORDER_NUMBER,
ORDER_TYPE_CODE,
SINGLE_INSTANCE_FLAG,
PO_HEADER_ID,
OE_HEADER_ID,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
CUSTOMER_ID,
ORDER_DATE,
CONTRACT_ID,
CONTRACT_TERMS,
OPERATING_UNIT_ID,
PO_SYNCH_FLAG,
STATUS_CODE,
PO_BATCH_ID,
PO_INTERFACE_HEADER_ID,
PO_REQUEST_ID,
PO_AGENT_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DESCRIPTION,
PO_REQ_HEADER_ID -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
FROM AHL_OSP_ORDERS_VL
WHERE OSP_ORDER_ID = p_osp_order_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_OSP_Order';
AHL_OSP_ORDERS_PKG.UPDATE_ROW(
X_OSP_ORDER_ID => p_osp_order_id,
X_OBJECT_VERSION_NUMBER => l_osp_dtls_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
X_OSP_ORDER_NUMBER => l_osp_dtls_rec.OSP_ORDER_NUMBER,
X_ORDER_TYPE_CODE => l_osp_dtls_rec.ORDER_TYPE_CODE,
X_SINGLE_INSTANCE_FLAG => l_osp_dtls_rec.SINGLE_INSTANCE_FLAG,
X_PO_HEADER_ID => l_osp_dtls_rec.PO_HEADER_ID, -- Updated
X_OE_HEADER_ID => l_osp_dtls_rec.OE_HEADER_ID,
X_VENDOR_ID => l_osp_dtls_rec.VENDOR_ID,
X_VENDOR_SITE_ID => l_osp_dtls_rec.VENDOR_SITE_ID,
X_VENDOR_CONTACT_ID => l_osp_dtls_rec.VENDOR_CONTACT_ID,
X_CUSTOMER_ID => l_osp_dtls_rec.CUSTOMER_ID,
X_ORDER_DATE => l_osp_dtls_rec.ORDER_DATE,
X_CONTRACT_ID => l_osp_dtls_rec.CONTRACT_ID,
X_CONTRACT_TERMS => l_osp_dtls_rec.CONTRACT_TERMS,
X_OPERATING_UNIT_ID => l_osp_dtls_rec.OPERATING_UNIT_ID,
X_PO_SYNCH_FLAG => l_osp_dtls_rec.PO_SYNCH_FLAG,
X_STATUS_CODE => l_osp_dtls_rec.STATUS_CODE, -- Updated
X_PO_BATCH_ID => l_osp_dtls_rec.PO_BATCH_ID, -- Updated
X_PO_REQUEST_ID => l_osp_dtls_rec.PO_REQUEST_ID, -- Updated
X_PO_INTERFACE_HEADER_ID => l_osp_dtls_rec.PO_INTERFACE_HEADER_ID, -- Updated
X_PO_AGENT_ID => l_osp_dtls_rec.PO_AGENT_ID,
X_ATTRIBUTE_CATEGORY => l_osp_dtls_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_osp_dtls_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_osp_dtls_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_osp_dtls_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_osp_dtls_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_osp_dtls_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_osp_dtls_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_osp_dtls_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_osp_dtls_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_osp_dtls_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_osp_dtls_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_osp_dtls_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_osp_dtls_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_osp_dtls_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_osp_dtls_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_osp_dtls_rec.ATTRIBUTE15,
X_DESCRIPTION => l_osp_dtls_rec.DESCRIPTION,
X_PO_REQ_HEADER_ID => l_osp_dtls_rec.PO_REQ_HEADER_ID, -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
X_LAST_UPDATE_DATE => TRUNC(sysdate), -- Updated
X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
X_LAST_UPDATE_LOGIN => fnd_global.login_id); -- Updated
END Update_OSP_Order;
SELECT LIST_PRICE_PER_UNIT FROM MTL_SYSTEM_ITEMS_B MSIB, AHL_OSP_ORDER_LINES ospl
WHERE MSIB.INVENTORY_ITEM_ID = ospl.service_item_id
AND MSIB.ORGANIZATION_ID = ospl.inventory_org_id
AND ospl.osp_order_line_id = p_osp_line_id;
SELECT MSIK.LIST_PRICE_PER_UNIT FROM MTL_SYSTEM_ITEMS_KFV MSIK, MTL_PARAMETERS MP
WHERE MSIK.INVENTORY_ITEM_ID = p_item_id
AND MSIK.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = p_org_id;
SELECT expense_account
FROM mtl_system_items_b
WHERE organization_id = c_inv_org_id
AND inventory_item_id = c_item_id;
SELECT material_account
FROM mtl_parameters
WHERE organization_id = c_inv_org_id;
SELECT 'X'
FROM PO_SYSTEM_PARAMETERS_ALL
WHERE user_defined_po_num_code = 'MANUAL'
AND org_id = c_org_id;
SELECT 'X'
FROM PO_SYSTEM_PARAMETERS_ALL
WHERE manual_po_num_type = 'ALPHANUMERIC'
AND org_id = c_org_id;
SELECT osp_order_number
FROM AHL_OSP_ORDERS_B
WHERE osp_order_id = c_order_id;