[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select Count(*)
From OE_DROP_SHIP_SOURCES
Where Drop_Ship_Source_ID = P_Drop_Ship_Source_ID ;
PROCEDURE : Insert_OE_Drop_Ship_Source
DESCRIPTION:
-----------------------------------------------------------------*/
PROCEDURE Insert_OE_Drop_Ship_Sources
(P_Drop_Ship_Source_ID In Number,
P_Header_ID In Number,
P_Line_ID In Number,
P_Org_ID In Number,
P_Destination_Organization_ID In Number,
P_Requisition_Header_ID In Number,
P_Requisition_Line_ID In Number,
P_PO_Header_ID In Number,
P_PO_Line_ID In Number,
P_Line_Location_ID In Number, P_PO_Release_ID In Number Default Null)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
Insert Into OE_Drop_Ship_Sources
(
Drop_Ship_Source_ID,
Header_ID,
Line_ID,
Org_ID,
Destination_Organization_ID,
Requisition_Header_ID,
Requisition_Line_ID,
PO_Header_ID,
PO_Line_ID,
Line_Location_ID,
PO_Release_ID,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By
)
Values
(
P_Drop_Ship_Source_ID,
P_Header_ID,
P_Line_ID,
P_Org_ID,
P_Destination_Organization_ID,
P_Requisition_Header_ID,
P_Requisition_Line_ID,
P_PO_Header_ID,
P_PO_Line_ID,
P_Line_Location_ID,
P_PO_Release_ID,
Trunc(Sysdate),
Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
Trunc(Sysdate),
Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
);
End Insert_OE_Drop_Ship_Sources;
PROCEDURE : Update_Req_Info
DESCRIPTION: Update_Req_Info is an OE procedure that is called by
Oracle Purchasing to update requisition information for
a drop shipped line. This procedure is called in the
Requisition Import (ReqImport) process of Oracle Purchasing
-----------------------------------------------------------------*/
PROCEDURE Update_Req_Info
(P_API_Version In Number,
P_Return_Status out nocopy Varchar2,
P_Msg_Count out nocopy Number,
P_MSG_Data out nocopy Varchar2,
P_Interface_Source_Code In Varchar2,
P_Interface_Source_Line_ID In Number,
P_Requisition_Header_ID In Number,
P_Requisition_Line_ID In Number)
IS
L_API_Name Constant Varchar2(30) := 'UPDATE_REQ_INFO';
SELECT Drop_Ship_Source_ID,
Header_ID,
Line_ID,
Org_ID,
Destination_Organization_ID,
Requisition_Header_ID,
Requisition_Line_ID,
PO_Header_ID,
PO_Line_ID
FROM OE_Drop_Ship_Sources
WHERE Drop_Ship_source_ID = P_Interface_Source_Line_ID
FOR UPDATE OF Requisition_Header_ID NOWAIT; -- bug 4503620
SavePoint Update_Req_Info_GRP;
Rollback to Update_Req_INfo_GRP;
UPDATE OE_Drop_Ship_Sources
SET Requisition_Header_ID = P_Requisition_Header_ID,
Requisition_LIne_ID = P_Requisition_Line_ID,
Last_Update_Date = Trunc(Sysdate),
Last_Updated_By = Nvl(To_Number(FND_PROFILE.VALUE
('USER_ID')),-1)
WHERE Current of L_OE_Drop_Ship_Source_Csr;
Insert_OE_Drop_Ship_Sources
(P_Drop_Ship_Source_ID => G_Drop_Ship_Source_ID,
P_Header_ID => G_Header_ID,
P_Line_ID => G_Line_ID,
P_Org_ID => G_Org_ID,
P_Destination_Organization_ID =>
G_Destination_Organization_ID,
P_Requisition_Header_ID =>
P_Requisition_Header_ID,
P_Requisition_LIne_ID => P_Requisition_Line_ID,
P_PO_Header_ID => Null,
P_PO_Line_ID => Null,
P_Line_Location_ID => Null);
Rollback to Update_Req_Info_Grp;
oe_debug_pub.add('OEXUDSHB.pls:Update_Req_Info- unable to lock the line',1);
Rollback to Update_Req_Info_Grp;
END Update_Req_Info;
PROCEDURE : Update_All_Reqs_In_Process
DESCRIPTION: Update_All_Reqs_In_Process is an OE procedure that is
called by Oracle Purchasing to update requisition
information for a drop shipped line. This procedure is
called in the Requisition Import (ReqImport) process of
Oracle Purchasing.
-----------------------------------------------------------------*/
PROCEDURE Update_All_Reqs_In_Process
(P_API_Version In Number,
P_Return_Status out nocopy Varchar2,
P_Msg_Count out nocopy Number,
P_MSG_Data out nocopy Varchar2,
P_Requisition_Header_ID In Number,
P_Request_Id In Number,
P_Process_Flag In Varchar2)
IS
L_API_Name Constant Varchar2(30)
:= 'UPDATE_ALL_REQS_IN_PROCESS';
SELECT Interface_Source_Code,Interface_source_line_id,
requisition_header_id,requisition_line_id
FROM po_requisitions_interface
WHERE requisition_header_id = P_Requisition_Header_ID
AND process_flag = P_Process_Flag
AND request_id = P_Request_Id;
SAVEPOINT UPDATE_ALL_REQS_IN_PROCESS;
Update_Req_Info(1.0,
L_Return_Status,
L_Msg_Count,
L_MSG_Data,
L_Interface_Source_Code,
L_Interface_Source_Line_ID,
L_Requisition_Header_ID,
L_Requisition_Line_ID);
ROLLBACK TO UPDATE_ALL_REQS_IN_PROCESS;
END Update_All_Reqs_In_Process;
PROCEDURE : Update_PO_Info
DESCRIPTION: Update_PO_Info is an OE procedure that is called by
Oracle Purchasing to update purchase order information
for a drop shipped line. This procedure is called in the
Auto create process of Oracle Purchasing
-----------------------------------------------------------------*/
PROCEDURE Update_PO_Info (P_API_Version In Number,
P_Return_Status out nocopy Varchar2,
P_Msg_Count out nocopy Number,
P_MSG_Data out nocopy Varchar2,
P_Req_Header_ID In Number,
P_Req_Line_ID In Number,
P_PO_Header_Id In Number,
P_PO_Line_Id In Number,
P_Line_Location_ID In Number,
P_PO_Release_ID In Number Default Null)
IS
L_API_Name Constant Varchar2(30) := 'UPDATE_PO_INFO';
IS SELECT Drop_Ship_Source_ID,
Header_ID,
Line_ID,
Org_ID,
Destination_Organization_ID,
Requisition_Header_ID,
Requisition_Line_ID,
PO_Header_ID,
PO_Line_ID
FROM OE_Drop_Ship_Sources
WHERE Requisition_Line_ID = P_Req_Line_ID
AND Requisition_Header_ID = P_Req_Header_ID
FOR Update of PO_Header_ID NOWAIT; -- bug 4503620
SavePoint Update_PO_Info_GRP;
Update OE_Drop_Ship_Sources
SET PO_Header_ID = P_PO_Header_ID,
PO_Line_ID = P_PO_Line_ID,
Line_Location_ID = P_Line_Location_ID,
PO_Release_ID = Nvl(P_PO_Release_Id,PO_Release_ID),
Last_Update_Date = Trunc(Sysdate),
Last_Updated_By = Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
WHERE Current of L_OE_Drop_Ship_Source_Csr;
Insert_OE_Drop_Ship_Sources
(P_Drop_Ship_Source_ID => G_Drop_Ship_Source_ID,
P_Header_ID => G_Header_ID,
P_Line_ID => G_Line_ID,
P_Org_ID => G_Org_ID,
P_Destination_Organization_ID => G_Destination_Organization_ID,
P_Requisition_Header_ID => G_Requisition_Header_ID,
P_Requisition_Line_ID => G_Requisition_Line_ID,
P_PO_Header_ID => P_PO_Header_ID,
P_PO_Line_ID => P_PO_Line_ID,
P_Line_Location_ID => P_Line_Location_ID,
P_PO_Release_Id => P_PO_Release_ID);
Rollback to Update_PO_Info_GRP;
oe_debug_pub.add('OEXUDSHB.pls:Update_PO_Info-unable to lock the line',1);
ROLLBACK TO Update_PO_Info_GRP;
END Update_PO_Info;
SELECT Line_Id
FROM OE_Drop_Ship_Sources
WHERE Requisition_Line_ID = P_Req_Line_ID;
SELECT Line_Id
FROM OE_Drop_Ship_Sources
WHERE Line_Location_Id = P_PO_Line_Location_ID;
Procedure : Update_Drop_Ship_Links
Description : This procedure will be called by PO
in the case of supplier initiated PO cancellation.
This will update line in oe dropship sources
with new requistion id.
----------------------------------------------------*/
Procedure Update_Drop_Ship_Links
( p_api_version IN NUMBER
,p_po_header_id IN NUMBER
,p_po_line_id IN NUMBER
,p_po_line_location_id IN NUMBER
,p_po_release_id IN NUMBER
,p_new_req_hdr_id IN NUMBER
,p_new_req_line_id IN NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
SAVEPOINT Update_Drop_Ship_Links_GRP;
OE_DEBUG_PUB.Add('Entering Update_Drop_Ship_Links...',1);
UPDATE oe_drop_ship_sources
SET requisition_header_id = p_new_req_hdr_id,
requisition_line_id = p_new_req_line_id,
po_header_id = NULL,
po_line_id = NULL,
po_release_id = NULL,
line_location_id = NULL,
last_update_date = Trunc(Sysdate),
last_updated_by = NVL(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
last_update_login = NVL(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
WHERE line_id = l_line_id
AND header_id = l_header_id;
OE_DEBUG_PUB.Add('Updated the Drop Ship Links...',1);
OE_DEBUG_PUB.Add('Exiting Update_Drop_Ship_Links...',1);
OE_DEBUG_PUB.Add('No DAta Found in Update_Drop_Ship_Links...',1);
ROLLBACK TO Update_Drop_Ship_Links;
ROLLBACK TO Update_Drop_Ship_Links;
'Update_Drop_Ship_Links');
END Update_Drop_Ship_Links;
SELECT ol.line_id
INTO l_line_id
FROM oe_order_lines_all ol,
oe_drop_ship_sources od,
rcv_transactions rt
WHERE ol.line_id = od.line_id
AND ol.source_type_code = 'EXTERNAL'
AND od.po_header_id = rt.po_header_id
AND od.po_line_id = rt.po_line_id
AND od.line_location_id = rt.po_line_location_id
AND rt.transaction_id = p_rcv_transaction_id
AND ROWNUM = 1;
SELECT oel.line_id,
oel.header_id,
oel.org_id
INTO x_line_id,
x_header_id,
x_org_id
FROM oe_drop_ship_sources ds,
oe_order_lines_all oel
WHERE line_location_id = p_po_line_location_id
AND oel.line_id = ds.line_id
AND oel.header_id = ds.header_id
AND nvl(oel.open_flag,'Y') = 'Y'
AND oel.shipped_quantity is NULL;
SELECT oel.line_id,
oel.header_id,
oel.org_id
INTO x_line_id,
x_header_id,
x_org_id
FROM oe_drop_ship_sources ds,
oe_order_lines_all oel
WHERE line_location_id = p_po_line_location_id
AND oel.line_id = ds.line_id
AND oel.header_id = ds.header_id;
SELECT oel.header_id,
oel.org_id
INTO x_header_id,
x_org_id
FROM oe_drop_ship_sources ds,
oe_order_lines_all oel
WHERE line_location_id = p_po_line_location_id
AND oel.line_id = ds.line_id
AND oel.header_id = ds.header_id
AND rownum = 1;
SELECT MAX(OL.line_id)
INTO x_line_id
FROM oe_order_lines_all OL,
oe_drop_ship_sources OD,
rcv_transactions RT
WHERE OL.line_id = OD.line_id
AND OL.source_type_code = 'EXTERNAL'
AND OD.po_header_id = RT.po_header_id
AND OD.po_line_id = RT.po_line_id
AND OD.line_location_id = RT.po_line_location_id
AND RT.transaction_id = p_rcv_transaction_id
AND OL.shipped_quantity is NOT NULL;
SELECT count(*)
INTO x_num_lines
FROM oe_drop_ship_sources ds,
oe_order_lines_all oel
WHERE line_location_id = p_po_line_location_id
AND oel.line_id = ds.line_id
AND oel.header_id = ds.header_id;
OE_DEBUG_PUB.Add('No DAta Found in Update_Drop_Ship_Links...',1);
ROLLBACK TO Update_Drop_Ship_Links;
SELECT org_id
INTO l_po_org_id -- l_num_lines 4148163
FROM po_line_locations_all
WHERE line_location_id = p_po_line_location_id;
SELECT order_number
INTO x_order_line_info_rec.sales_order_number
FROM oe_order_headers_all --oe_order_headers --Changes for BUG#6032405
WHERE header_id = l_header_id;
SELECT ship_to_contact_id,
deliver_to_contact_id,
ship_to_org_id,
deliver_to_org_id,
sold_to_org_id,
shipping_method_code,
shipping_instructions,
packing_instructions,
inventory_item_id,
item_identifier_type,
ordered_item,
ordered_item_id, -- Bug# 13100856
user_item_description,
cust_po_number,
customer_line_number,
customer_shipment_number,
DECODE(p_mode, 0, null, RTRIM(line_number || '.'
|| shipment_number || '.'
|| option_number || '.'
|| component_number || '.'
|| service_number, '.')),
DECODE(p_mode, 0, null, ordered_quantity),
DECODE(p_mode, 0, null, shipped_quantity),
DECODE(p_mode, 0, null, ordered_quantity2), -- INVCONV
DECODE(p_mode, 0, null, shipped_quantity2), -- INVCONV
DECODE(p_mode, 0, null, oelup.meaning)
INTO l_ship_to_contact_id,
l_deliver_to_contact_id,
l_ship_to_org_id,
l_deliver_to_org_id,
l_sold_to_org_id,
l_shipping_method_code,
x_order_line_info_rec.shipping_instructions,
x_order_line_info_rec.packing_instructions,
l_inventory_item_id,
l_item_identifier_type,
l_ordered_item,
l_ordered_item_id, -- Bug# 13100856
x_order_line_info_rec.customer_product_description,
x_order_line_info_rec.customer_po_number,
x_order_line_info_rec.customer_po_line_number,
x_order_line_info_rec.customer_po_shipment_number,
x_order_line_info_rec.sales_order_line_number,
x_order_line_info_rec.sales_order_line_ordered_qty,
x_order_line_info_rec.sales_order_line_shipped_qty,
x_order_line_info_rec.sales_order_line_ordered_qty2, -- INVCONV
x_order_line_info_rec.sales_order_line_shipped_qty2, -- INVCONV
x_order_line_info_rec.sales_order_line_status
FROM oe_order_lines_all oel, --oe_order_lines oel, -- Changes for BUG#6032405
oe_lookups oelup
WHERE line_id = l_line_id
AND oelup.lookup_code = oel.flow_status_code
AND oelup.lookup_type = 'LINE_FLOW_STATUS';
SELECT ship_method_meaning
INTO x_order_line_info_rec.shipping_method
FROM wsh_carrier_services wshca
WHERE SHIP_METHOD_CODE = l_shipping_method_code;
SELECT line_id
FROM oe_drop_ship_sources
WHERE (p_entity = 'PO_REQUISITION_HEADERS' AND
requisition_header_id = cp_entity_id) OR
(p_entity = 'PO_HEADERS' AND
po_header_id = cp_entity_id)
FOR UPDATE NOWAIT;
DELETE
FROM oe_drop_ship_sources
WHERE (p_entity = 'PO_REQUISITION_HEADERS' AND
requisition_header_id = p_entity_id_tbl(I)) OR
(p_entity = 'PO_HEADERS' AND
po_header_id = p_entity_id_tbl(I));
SELECT count(*)
INTO l_count
FROM oe_drop_ship_sources ds,
oe_order_lines_all l
WHERE requisition_header_id = p_entity_id_tbl(I)
AND l.line_id = ds.line_id
AND l.header_id = ds.header_id
AND nvl(l.open_flag,'Y') = 'Y'
AND l.shipped_quantity is NULL;
SELECT count(*)
INTO l_count
FROM oe_drop_ship_sources ds,
oe_order_lines_all l
WHERE po_header_id = p_entity_id_tbl(I)
AND l.line_id = ds.line_id
AND l.header_id = ds.header_id
AND nvl(l.open_flag,'Y') = 'Y'
AND l.shipped_quantity is NULL;