[Home] [Help]
PACKAGE BODY: APPS.CSP_PARTS_REPAIR
Source
1 PACKAGE BODY CSP_PARTS_REPAIR AS
2 /* $Header: cspvprpb.pls 120.1 2006/04/24 15:49:44 phegde noship $ */
3 --
4 --
5 -- Purpose: This package will contain procedures for creating internal orders
6 -- and repair order for repair notifications
7
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- phegde 05/02/03 Created package
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'csp_parts_repair';
13 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvprpb.pls';
14
15
16 PROCEDURE create_orders
17 ( p_api_version IN NUMBER
18 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
19 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
20 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
21 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
22 ,p_Repair_supplier_id IN NUMBER
23 ,x_return_status OUT NOCOPY VARCHAR2
24 ,x_msg_count OUT NOCOPY NUMBER
25 ,x_msg_data OUT NOCOPY VARCHAR2)
26 IS
27 l_api_version_number CONSTANT NUMBER := 1.0;
28 l_api_name CONSTANT VARCHAR2(30) := 'create_orders';
29 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(2000);
32 l_line_Rec csp_parts_Requirement.line_rec_type;
33 l_order_hdr_rec csp_parts_requirement.header_rec_type;
34 l_order_line_tbl csp_parts_requirement.line_tbl_type;
35 l_IO1_header_id NUMBER;
36 l_IR1_requisition_id NUMBER;
37 l_IO2_header_id NUMBER;
38 l_IR2_requisition_id NUMBER;
39 /* l_Repair_order_Rec csd_repairs_pub.repln_rec_Type;
40 l_repair_line_id NUMBER; */
41 l_service_request_number VARCHAR2(30);
42
43 -- Get unique requisition_header_id
44 CURSOR req_header_id_cur IS
45 SELECT po_requisition_headers_s.nextval
46 FROM sys.dual;
47
48 CURSOR supercess_items_cur(p_item_id NUMBER) IS
49 SELECT related_item_id
50 FROM mtl_related_items_view
51 WHERE relationship_type_id = 18
52 AND inventory_item_id = p_item_id;
53
54 l_Repair_to_item_id NUMBER;
55
56 BEGIN
57 SAVEPOINT Create_Orders_PUB;
58 -- initialize return status
59 x_return_status := FND_API.G_RET_STS_SUCCESS;
60
61 l_line_Rec := px_line_Table(1);
62
63 IF p_repair_supplier_id IS NOT NULL THEN
64 -- create internal order 1 with repair supplier found above as destination
65
66 -- create header record for creating IO1
67 FND_PROFILE.GET('CSP_ORDER_TYPE', l_order_hdr_rec.order_type_id);
68
69 -- get ship to of repair organization
70 begin
71 select location_id
72 into l_order_hdr_Rec.ship_to_location_id
73 from hr_organization_units
74 where organization_id = p_repair_supplier_id;
75 exception
76 when no_data_found then
77 null;
78 end;
79 l_order_hdr_Rec.dest_organization_id := p_repair_supplier_id;
80 l_order_hdr_rec.operation := csp_parts_requirement.G_OPR_CREATE;
81
82 -- FIND NEED_BY_DATE
83
84 -- create line record for creating IO1
85 l_line_Rec.line_num := 1;
86 l_order_line_tbl(1) := l_line_Rec;
87
88 -- call process order
89 csp_parts_order.process_order(
90 p_api_version => l_api_Version_number
91 ,p_Init_Msg_List => FND_API.G_TRUE
92 ,p_commit => FND_API.G_FALSE
93 ,px_header_rec => l_order_hdr_Rec
94 ,px_line_table => l_order_Line_Tbl
95 ,p_process_type => 'BOTH'
96 ,x_return_status => l_return_status
97 ,x_msg_count => l_msg_count
98 ,x_msg_data => l_msg_data
99 );
100
101 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
102 RAISE FND_API.G_EXC_ERROR;
103 ELSE
104 l_IO1_header_id := l_order_hdr_Rec.order_header_id;
105 l_IR1_requisition_id := l_order_hdr_rec.requisition_header_id;
106 END IF;
107
108 -- create IO2 with with p_header_REc.dest_organization_id as destination
109 -- and repair supplier found above as source
110
111 l_line_Rec := px_line_Table(1);
112
113 -- get ship to of FS organization
114 begin
115 select location_id
116 into l_order_hdr_Rec.ship_to_location_id
117 from hr_organization_units
118 where organization_id = px_header_rec.dest_organization_id;
119 exception
120 when no_data_found then
121 null;
122 end;
123 l_order_hdr_Rec.dest_organization_id := px_header_rec.dest_organization_id;
124 l_order_hdr_rec.operation := csp_parts_requirement.G_OPR_CREATE;
125 l_order_hdr_rec.requisition_number := NULL;
126 l_order_hdr_rec.order_header_id := NULL;
127 l_order_hdr_rec.requisition_header_id := NULL;
128
129 -- create line record for creating IO2
130 OPEN supercess_items_cur(px_line_table(1).inventory_item_id);
131 FETCH supercess_items_cur INTO l_repair_to_item_id;
132 CLOSE supercess_items_cur;
133 l_line_rec.inventory_item_id := nvl(l_repair_to_item_id, px_line_table(1).inventory_item_id);
134
135 l_line_rec.source_organization_id := p_repair_supplier_id;
136 l_line_rec.requisition_line_id := NULL;
137 l_line_rec.line_num := 1;
138 l_line_rec.order_line_id := NULL;
139 l_line_rec.source_subinventory := NULL;
140 l_order_line_tbl(1) := l_line_Rec;
141
142 -- call process order
143 csp_parts_order.process_order(
144 p_api_version => l_api_Version_number
145 ,p_Init_Msg_List => FND_API.G_TRUE
146 ,p_commit => FND_API.G_FALSE
147 ,px_header_rec => l_order_hdr_Rec
148 ,px_line_table => l_order_Line_Tbl
149 ,p_process_type => 'BOTH'
150 ,x_return_status => l_return_status
151 ,x_msg_count => l_msg_count
152 ,x_msg_data => l_msg_data
153 );
154
155 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
156 RAISE FND_API.G_EXC_ERROR;
157 ELSE
158 l_IO2_header_id := l_order_hdr_Rec.order_header_id;
159 l_IR2_requisition_id := l_order_hdr_rec.requisition_header_id;
160 END IF;
161
162 -- create repair order
163 CSD_Refurbish_IRO_GRP.Create_InternalRO(
164 P_api_version => l_api_Version_number,
165 P_init_msg_list => FND_API.G_FALSE,
166 P_commit => FND_API.G_TRUE,
167 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
168 x_return_status => l_return_status,
169 x_msg_count => l_msg_count,
170 x_msg_data => l_msg_data,
171 P_req_header_id_in => l_IR1_requisition_id,
172 P_ISO_header_id_in => l_IO1_header_id,
173 P_req_header_id_out => l_IR2_requisition_id,
174 P_ISO_header_id_out => l_IO2_header_id,
175 x_service_request_number => l_service_Request_number);
176
177 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180 px_header_rec.order_header_id := l_IO2_header_id;
181 x_Return_status := FND_API.G_RET_STS_SUCCESS;
182 END IF;
183
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
187 P_API_NAME => L_API_NAME
188 ,P_PKG_NAME => G_PKG_NAME
189 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
190 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
191 ,X_MSG_COUNT => X_MSG_COUNT
192 ,X_MSG_DATA => X_MSG_DATA
193 ,X_RETURN_STATUS => X_RETURN_STATUS);
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
196 P_API_NAME => L_API_NAME
197 ,P_PKG_NAME => G_PKG_NAME
198 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
199 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
200 ,X_MSG_COUNT => X_MSG_COUNT
201 ,X_MSG_DATA => X_MSG_DATA
202 ,X_RETURN_STATUS => X_RETURN_STATUS);
203 WHEN OTHERS THEN
204 Rollback to create_orders_pub;
205 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
206 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
207 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
208 FND_MSG_PUB.ADD;
209 fnd_msg_pub.count_and_get
210 ( p_count => x_msg_count
211 , p_data => x_msg_data);
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 END;
214 END;