SQL & PL/SQL

create or replace
PACKAGE BODY SF_CSD_CreateOrder
AS
G_PKG_NAME CONSTANT VARCHAR2(30) :='SF_CSD_CreateOrder';
PROCEDURE Create_Order
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_order_rec IN ORDER_HDR_REC,
p_order_lines IN ORDER_LINE_REC_TBL )
IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_Order';
l_api_version_number CONSTANT NUMBER := 1.0;
lx_incident_id NUMBER(10);
lx_incident_number VARCHAR2(50);
lx_return_status VARCHAR2(1);
lx_msg_count NUMBER(10);
lx_msg_data VARCHAR2(2000);
l_incident_id NUMBER;
l_repair_line_id NUMBER;
l_repair_number NUMBER;
xx_ro_status VARCHAR2(1);
xl_return_status VARCHAR2(1);
xl_msg_count NUMBER;
xl_msg_data VARCHAR2(2000);
l_order_line_id NUMBER;
l_order_header_id NUMBER;
xx_return_status VARCHAR2(1);
xx_msg_count NUMBER;
xx_msg_data VARCHAR2(2000);
l_order_line ORDER_LINE_REC_TBL;
l_createsr_rec SF_CSD_CREATEORDER_UTIL.CREATESR_REC;
l_sr_notes_tbl cs_servicerequest_pub.notes_table;
l_order_lines SF_CSD_CreateOrder.ORDER_LINE_REC_TBL;

CURSOR c_get_rma(p_repair_line_id NUMBER) IS
SELECT cpt.order_line_id,
cpt.ORDER_HEADER_ID
FROM csd_product_transactions cpt,
csd_repairs cr
WHERE cpt.repair_line_id=p_repair_line_id
AND cr.repair_line_id= cpt.repair_line_id
AND cpt.action_type='RMA';
BEGIN
-- Standard Start of API savepoint
SAVEPOINT Create_Order;
-- Standard call to check for call compatibility.
IF NOT FND_API.Compatible_API_Call(l_api_version_number, p_api_version_number, l_api_name,G_PKG_NAME) THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
-- Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
END IF;
-- Initialize API return status to success
x_return_status := FND_API.G_RET_STS_SUCCESS;
-- call SF_CSD_CREATEORDER_UTIL.Create_SR
-- this outputs incident_id (l_incident_id)

l_createsr_rec.party_id :=p_order_rec.party_id;
l_createsr_rec.cust_account_id :=p_order_rec.cust_account_id;


SF_CSD_CREATEORDER_UTIL.create_sr (
P_createsr_rec =>l_createsr_rec,
P_sr_notes_tbl =>l_sr_notes_tbl,
X_incident_id =>lx_incident_id,
X_incident_number =>lx_incident_number,
X_return_status =>lx_return_status,
X_msg_count =>lx_msg_count,
X_msg_data =>lx_msg_data
);

l_incident_id :=lx_incident_id;
FOR I IN l_order_line
LOOP
--- Use the incidentid returned by the above call (l_incident_id)
-- Create RO
l_createro_rec :=SF_CSD_CREATEORDER_UTIL.CREATERO_REC;
l_createro_rec.INVENTORY_ITEM_ID :=p_order_lines.inventory_item_id;
l_createro_rec.SERIAL_NUMBER :=p_order_lines.serial_number;
l_createro_rec.PROBLEM_DESCRIPTION :=p_order_lines.PROBLEM_DESCRIPTION;
SF_CSD_CREATEORDER_UTIL.create_ro (

p_CREATERO_REC =>l_createro_rec,
x_repair_line_id =>l_repair_line_id,
x_repair_number =>l_repair_number,
x_ro_status => xx_ro_status,
x_return_status => xl_return_status,
x_msg_count => xl_msg_count,
x_msg_data =>xl_msg_data
);
-- Call SF_CSD_CREATEORDER_UTIL.Create_repair_order
--- This outputs repair line id.(l_repair_line_id)
l_repair_line_id :=lx_repair_line_id;
l_auto_rcv :=SF_CSD_CREATEORDER_UTIL.AUTORCV_REC;

l_order_line_id :=null;
l_order_header_id :=null;
OPEN c_get_rma(l_auto_rcv.REPAIR_LINE_ID);
FETCH c_get_rma INTO l_order_line_id, l_order_header_id;
CLOSE c_get_rma;
l_auto_rcv.REPAIR_LINE_ID :=l_repair_line_id;
l_auto_rcv.ORDER_LINE_ID := l_order_line_id;
l_auto_rcv.ORDER_HEADER_ID := l_order_header_id;
SF_CSD_CREATEORDER_UTIL.auto_receive (
p_autorcv_tbl => l_auto_rcv,
x_return_status => xx_return_status,
x_msg_count => xx_msg_count,
x_msg_data => xx_msg_data );
-- Get the order line id for the RMA created above. use query to select from csd_product_txns table.
-- Select order_line_id from csd_product_txns where repair_line_Id = l_repair_line_id and action_type = 'RMA'
--- Call SF_CSD_CREATEORDER_UTIL.Auto_receive to receive the item for the rma created above
END LOOP;
END;
END SF_CSD_CreateOrder;



Error(27,11): PLS-00323: subprogram or cursor 'CREATE_ORDER' is declared in a package specification and must be defined in the package body
Error(52,91): PLS-00371: at most one declaration for 'G_PKG_NAME' is permitted
Error(79,10): PLS-00456: item 'L_ORDER_LINE' is not a cursor