[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_OL_REQ_DELETED_STATUS CONSTANT VARCHAR2(30) := 'REQ_DELETED';
PROCEDURE Insert_Into_Req_Interface(
p_po_req_hdr_rec IN PO_Req_Header_Rec_Type,
p_po_req_line_tbl IN PO_Req_Line_Tbl_Type,
x_batch_id OUT NOCOPY NUMBER);
PROCEDURE Update_OSP_Order(
p_osp_order_id IN NUMBER,
p_po_req_header_id IN NUMBER := NULL,
p_batch_id IN NUMBER := NULL,
p_request_id IN NUMBER := NULL,
p_status_code IN VARCHAR2 := NULL
);
PROCEDURE Update_Osp_Order_Lines(
p_osp_order_line_id IN NUMBER,
p_po_req_line_id IN NUMBER := NULL);
PROCEDURE Handle_Deleted_Req_Headers(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE Handle_Deleted_Req_Lines(
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 B.VENDOR_ID,
B.VENDOR_SITE_ID,
B.OPERATING_UNIT_ID,
B.PO_AGENT_ID,
B.PO_BATCH_ID,
TL.DESCRIPTION,
B.PO_INTERFACE_HEADER_ID,
B.VENDOR_CONTACT_ID,
B.STATUS_CODE
FROM AHL_OSP_ORDERS_B B, AHL_OSP_ORDERS_TL TL
WHERE B.OSP_ORDER_ID = p_osp_order_id
AND TL.OSP_ORDER_ID = B.OSP_ORDER_ID
AND TL.LANGUAGE = userenv('LANG');
SELECT OL.OSP_ORDER_LINE_ID,
OL.OBJECT_VERSION_NUMBER,
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,
DECODE(OL.WORKORDER_ID, NULL, HAOU.LOCATION_ID, BOM.LOCATION_ID),
WO.WIP_ENTITY_ID,
WDJ.PROJECT_ID,
WDJ.TASK_ID
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
WDJ.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID AND
BOM.DEPARTMENT_ID (+) = WDJ.OWNING_DEPARTMENT AND
HAOU.ORGANIZATION_ID = OL.INVENTORY_ORG_ID
ORDER BY OL.OSP_LINE_NUMBER;
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_req_line_tbl.DELETE(l_temp_num); -- Delete the last (null) record
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_req_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_req_header.OSP_ORDER_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;
Insert_Into_Req_Interface(p_po_req_hdr_rec => l_po_req_header,
p_po_req_line_tbl => l_po_req_line_tbl,
x_batch_id => l_batch_id);
Update_OSP_Order(p_osp_order_id => p_osp_order_id,
p_batch_id => l_batch_id,
p_request_id => l_request_id);
Handle_Deleted_Req_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 Requisition Headers.');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Requisition Headers, Return Status = ' || x_return_status);
Handle_Deleted_Req_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 Requisition Lines.');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Requisition Lines, Return Status = ' || x_return_status);
SELECT OSP_ORDER_ID FROM AHL_OSP_ORDERS_B
WHERE STATUS_CODE = G_OSP_REQ_SUBMITTED_STATUS
AND PO_REQ_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();
PROCEDURE Insert_Into_Req_Interface
(
p_po_req_hdr_rec IN PO_Req_Header_Rec_Type,
p_po_req_line_tbl IN PO_Req_Line_Tbl_Type,
x_batch_id OUT NOCOPY NUMBER
) IS
l_org_id NUMBER := NULL;
SELECT COMPLETION_DATE from pa_tasks
where task_id = c_task_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Insert_Into_Req_Interface';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id before inserting: '|| l_charge_acct_id);
INSERT INTO po_requisitions_interface_all
(
INTERFACE_SOURCE_CODE,
INTERFACE_SOURCE_LINE_ID,
REFERENCE_NUM,
LINE_TYPE_ID,
SOURCE_TYPE_CODE,
DESTINATION_TYPE_CODE,
AUTHORIZATION_STATUS,
PREPARER_ID,
ITEM_ID,
ITEM_DESCRIPTION,
QUANTITY,
CATEGORY_SEGMENT1,
CATEGORY_SEGMENT2,
UOM_CODE,
UNIT_PRICE,
NEED_BY_DATE,
DESTINATION_ORGANIZATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_REQUESTOR_ID,
SUGGESTED_BUYER_ID,
SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE_ID,
SUGGESTED_VENDOR_CONTACT_ID,
HEADER_DESCRIPTION,
--Project related fields to link the distributions to Projects
WIP_ENTITY_ID,
PROJECT_ID,
TASK_ID,
--Project related fields End
GROUP_CODE,
BATCH_ID,
ORG_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
CHARGE_ACCOUNT_ID,
--Project related fields to link the distributions to Projects
PROJECT_ACCOUNTING_CONTEXT,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE
--Project related fields End
)
VALUES
(
AHL_GLOBAL.AHL_APP_SHORT_NAME, --INTERFACE_SOURCE_CODE
p_po_req_hdr_rec.OSP_ORDER_ID, --INTERFACE_SOURCE_LINE_ID
p_po_req_line_tbl(i).OSP_LINE_ID, --REFERENCE_NUM
p_po_req_line_tbl(i).PO_LINE_TYPE_ID, --LINE_TYPE_ID
'VENDOR', --SOURCE_TYPE_CODE
'EXPENSE', --DESTINATION_TYPE_CODE
'INCOMPLETE', --AUTHORIZATION_STATUS
FND_GLOBAL.EMPLOYEE_ID, --PREPARER_ID (Should be logged in user)
p_po_req_line_tbl(i).ITEM_ID, --ITEM_ID
p_po_req_line_tbl(i).ITEM_DESCRIPTION, --ITEM_DESCRIPTION
p_po_req_line_tbl(i).QUANTITY, --QUANTITY
l_category_seg1, --CATEGORY_SEGMENT1
l_category_seg2, --CATEGORY_SEGMENT2
p_po_req_line_tbl(i).UOM_CODE, --UOM_CODE
l_price, --UNIT_PRICE
p_po_req_line_tbl(i).NEED_BY_DATE, --NEED_BY_DATE
p_po_req_line_tbl(i).SHIP_TO_ORG_ID, --DESTINATION_ORGANIZATION_ID
p_po_req_line_tbl(i).SHIP_TO_LOC_ID, --DELIVER_TO_LOCATION_ID
FND_GLOBAL.EMPLOYEE_ID, --DELIVER_TO_REQUESTOR_ID
p_po_req_hdr_rec.BUYER_ID, --SUGGESTED_BUYER_ID
p_po_req_hdr_rec.VENDOR_ID, --SUGGESTED_VENDOR_ID
p_po_req_hdr_rec.VENDOR_SITE_ID, --SUGGESTED_VENDOR_SITE_ID
p_po_req_hdr_rec.VENDOR_CONTACT_ID, --SUGGESTED_VENDOR_CONTACT_ID
SUBSTR(p_po_req_hdr_rec.DESCRIPTION, 1, 240), --HEADER_DESCRIPTION
p_po_req_line_tbl(i).WIP_ENTITY_ID, --WIP_ENTITY_ID
p_po_req_line_tbl(i).PROJECT_ID, --PROJECT_ID
p_po_req_line_tbl(i).TASK_ID, --TASK_ID
p_po_req_hdr_rec.OSP_ORDER_ID, --GROUP_CODE
p_po_req_hdr_rec.OSP_ORDER_ID, --BATCH_ID
p_po_req_hdr_rec.OPERATING_UNIT_ID, --ORG_ID
AHL_GLOBAL.AHL_OSP_PROGRAM_ID, --PROGRAM_ID
AHL_GLOBAL.AHL_APPLICATION_ID, --PROGRAM_APPLICATION_ID
l_charge_acct_id, --CHARGE_ACCOUNT_ID
decode(p_po_req_line_tbl(i).PROJECT_ID, null, null, 'Y'), --PROJECT_ACCOUNTING_CONTEXT
decode(p_po_req_line_tbl(i).PROJECT_ID, null, null, l_expenditure_item_type), --EXPENDITURE_TYPE
decode(p_po_req_line_tbl(i).PROJECT_ID, null, null, p_po_req_line_tbl(i).SHIP_TO_ORG_ID), --EXPENDITURE_ORGANIZATION_ID
l_task_completion_date --EXPENDITURE_ITEM_DATE
);
' Inserting into po_requisitions_interface_all: '||
' WIP_ENTITY_ID = ' || p_po_req_line_tbl(i).WIP_ENTITY_ID ||
', PROJECT_ID = ' || p_po_req_line_tbl(i).PROJECT_ID ||
', TASK_ID = ' || p_po_req_line_tbl(i).TASK_ID ||
', EXPENDITURE_TYPE = ' || l_expenditure_item_type ||
', EXPENDITURE_ORGANIZATION_ID = ' || p_po_req_line_tbl(i).SHIP_TO_ORG_ID ||
', EXPENDITURE_ITEM_DATE = ' || to_char(l_task_completion_date, 'DD-MON-YYYY HH24:MI:SS'));
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inserted One Record.');
END Insert_Into_Req_Interface;
SELECT 'x' FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id
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 Update_OSP_Order(
p_osp_order_id IN NUMBER,
p_po_req_header_id IN NUMBER := NULL,
p_batch_id IN NUMBER := NULL,
p_request_id IN NUMBER := NULL,
p_status_code IN VARCHAR2 := 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,
PO_REQ_HEADER_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DESCRIPTION
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,
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,
X_PO_REQ_HEADER_ID => l_osp_dtls_rec.PO_REQ_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_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 POREQ.REQUISITION_HEADER_ID
FROM PO_REQUISITION_HEADERS_ALL POREQ, AHL_OSP_ORDERS_B OSP
WHERE POREQ.INTERFACE_SOURCE_LINE_ID = p_osp_order_id AND
OSP.OSP_ORDER_ID = p_osp_order_id AND
OSP.OPERATING_UNIT_ID = POREQ.ORG_ID AND
POREQ.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
SELECT OSP_ORDER_LINE_ID FROM AHL_OSP_ORDER_LINES
WHERE PO_REQ_LINE_ID IS NULL
AND OSP_ORDER_ID = p_osp_order_id;
SELECT REQUISITION_LINE_ID FROM PO_REQUISITION_LINES_ALL
WHERE REFERENCE_NUM = p_osp_order_line_id AND
REQUISITION_HEADER_ID = p_po_req_header_id;
SELECT PO_REQUEST_ID FROM AHL_OSP_ORDERS_B
WHERE OSP_ORDER_ID = p_osp_order_id;
select 1 from dual where
(select count(*) from AHL_OSP_ORDER_LINES where OSP_ORDER_ID = p_osp_order_id) =
(select count(*) from
PO_REQUISITION_LINES_ALL REQL, AHL_OSP_ORDER_LINES OSPL
where OSPL.OSP_ORDER_ID = p_osp_order_id
AND REQL.REFERENCE_NUM = OSPL.osp_order_line_id
AND REQL.REQUISITION_HEADER_ID = c_po_req_header_id);
Update_Osp_Order_Lines(p_osp_order_line_id => l_osp_order_line_id,
p_po_req_line_id => l_po_req_line_id);
Update_OSP_Order(p_osp_order_id => p_osp_order_id,
p_po_req_header_id => p_po_req_header_id,
p_status_code => G_OSP_REQ_CREATED_STATUS);
PROCEDURE Update_Osp_Order_Lines(
p_osp_order_line_id IN NUMBER,
p_po_req_line_id IN NUMBER := NULL) IS
CURSOR l_osp_line_dtls_csr(p_osp_line_id IN NUMBER) IS
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,
PO_REQ_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
FROM AHL_OSP_ORDER_LINES
WHERE OSP_ORDER_LINE_ID = p_osp_order_line_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Osp_Order_Lines';
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,
P_PO_REQ_LINE_ID => l_osp_line_dtls_rec.PO_REQ_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,
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
END Update_Osp_Order_Lines;
Update_OSP_Order(p_osp_order_id => p_osp_order_id,
p_status_code => G_OSP_REQ_SUB_FAILED_STATUS);
PROCEDURE Handle_Deleted_Req_Headers(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR get_req_deleted_osps_csr IS
SELECT osp.osp_order_id,
osp.object_version_number,
osp.po_req_header_id,
osp.status_code,
osp.order_type_code
FROM ahl_osp_orders_b osp
WHERE osp.status_code = G_OSP_REQ_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_requisition_headers_all where requisition_header_id = osp.po_req_header_id);
SELECT ospl.osp_order_id,
ospl.osp_order_line_id,
ospl.object_version_number,
ospl.status_code,
ospl.po_req_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_Req_Headers';
l_osp_details_rec get_req_deleted_osps_csr%ROWTYPE;
SAVEPOINT Handle_Deleted_Req_Headers_pvt;
OPEN get_req_deleted_osps_csr;
FETCH get_req_deleted_osps_csr into l_osp_details_rec;
EXIT WHEN get_req_deleted_osps_csr%NOTFOUND;
update ahl_osp_order_lines
set po_req_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;
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_req_header_id = null,
po_batch_id = null,
po_request_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_req_deleted_osps_csr;
ROLLBACK TO Handle_Deleted_Req_Headers_pvt;
ROLLBACK TO Handle_Deleted_Req_Headers_pvt;
ROLLBACK TO Handle_Deleted_Req_Headers_pvt;
p_procedure_name => 'Handle_Deleted_Req_Headers',
p_error_text => SUBSTR(SQLERRM,1,240));
END Handle_Deleted_Req_Headers;
SELECT REQL.REQUISITION_LINE_ID,
OL.OSP_ORDER_LINE_ID,
REQH.INTERFACE_SOURCE_LINE_ID,
OL.OBJECT_VERSION_NUMBER,
OSP.OBJECT_VERSION_NUMBER
FROM PO_REQUISITION_LINES_ALL REQL,
PO_REQUISITION_HEADERS_ALL REQH,
AHL_OSP_ORDER_LINES OL,
AHL_OSP_ORDERS_B OSP
WHERE nvl(REQL.CANCEL_FLAG,'N') = 'Y' AND -- Canceled Req Line
REQL.REQUISITION_HEADER_ID = REQH.REQUISITION_HEADER_ID AND
REQH.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME AND -- AHL Created Req
REQH.INTERFACE_SOURCE_LINE_ID = OSP.OSP_ORDER_ID AND -- Related to the OSP Order
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
OL.PO_REQ_LINE_ID = REQL.REQUISITION_LINE_ID AND
NVL(OL.STATUS_CODE, ' ') <> G_OL_REQ_CANCELLED_STATUS -- Not yet updated
ORDER BY REQH.INTERFACE_SOURCE_LINE_ID; -- 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_Req_Lines(
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR l_get_deleted_req_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_REQ_LINE_ID IS NOT NULL AND -- PO Created
NVL(OL.STATUS_CODE, ' ') <> G_OL_REQ_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 REQUISITION_LINE_ID FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_LINE_ID = OL.PO_REQ_LINE_ID) -- Req 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_Req_Lines';
SAVEPOINT Handle_Deleted_Req_Lines_pvt;
OPEN l_get_deleted_req_lines_csr;
FETCH l_get_deleted_req_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_req_lines_csr%NOTFOUND;
l_osp_order_lines_tbl.DELETE;
l_osp_order_lines_tbl(l_table_index).STATUS_CODE := G_OL_REQ_DELETED_STATUS;
l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
CLOSE l_get_deleted_req_lines_csr;
ROLLBACK TO Handle_Deleted_Req_Lines_pvt;
ROLLBACK TO Handle_Deleted_Req_Lines_pvt;
ROLLBACK TO Handle_Deleted_Req_Lines_pvt;
p_procedure_name => 'Handle_Deleted_Req_Lines',
p_error_text => SUBSTR(SQLERRM,1,240));
END Handle_Deleted_Req_Lines;
SELECT 1
FROM po_requisition_headers_all poh
WHERE poh.requisition_header_id = p_po_req_header_id
AND (nvl(poh.closed_code, 'OPEN') IN ('CANCELLED', 'CLOSED','FINALLY CLOSED', 'REJECTED', 'RETURNED')
OR
nvl(poh.authorization_status, 'INCOMPLETE') = 'CANCELLED'
);
SELECT poh.requisition_header_id
FROM po_requisition_headers_all poh
WHERE poh.requisition_header_id = p_po_req_header_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;