1 PACKAGE BODY AHL_LTP_ASCP_ORDERS_PVT AS
2 /* $Header: AHLSCMRB.pls 115.3 2004/01/29 00:05:59 ssurapan noship $*/
3 --
4 --
5 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_ASCP_ORDERS_PVT';
6 -- Start of Comments --
7 -- Procedure name : Update_Sheduling_Results
8 -- Type : Public
9 -- Function : This procedure Updates Scheduled Materials table with scheduled date
10 -- from APS
11 -- Pre-reqs :
12 -- Parameters :
13 --
14 -- Standard IN Parameters :
15 -- p_api_version IN NUMBER Required
16 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
17 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
18 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
19 -- Standard out Parameters :
20 -- x_return_status OUT VARCHAR2 Required
21 --
22 -- Update_Scheduling_Results :
23 --
24 --
25 --
26 PROCEDURE Update_Scheduling_Results (
27 p_api_version IN NUMBER := 1.0,
28 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
29 p_commit IN VARCHAR2 := FND_API.g_false,
30 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
31 p_sched_Orders_Tbl IN Sched_Orders_Tbl,
32 x_return_status OUT NOCOPY VARCHAR2)
33 IS
34
35 CURSOR Check_Sched_Mat_Cur(c_sch_mat_id IN NUMBER)
36 IS
37 SELECT 1 FROM ahl_schedule_materials
38 WHERE scheduled_material_id = c_sch_mat_id;
39
40 --Standard local variables
41 l_api_name CONSTANT VARCHAR2(30) := 'Update_Scheduling_Results';
42 l_api_version CONSTANT NUMBER := 1.0;
43 l_return_status VARCHAR2(1);
44 l_msg_data VARCHAR2(2000);
45 l_msg_count NUMBER;
46 l_dummy NUMBER;
47 BEGIN
48
49 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
50 fnd_log.string
51 (
52 fnd_log.level_procedure,
53 'ahl.plsql.AHL_LTP_ASCP_ORDERS_PVT.Update_Scheduling_Results.begin',
54 'At the start of PLSQL procedure'
55 );
56 END IF;
57 -- Standard Start of API savepoint
58 SAVEPOINT Update_Scheduling_Results;
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 IF FND_API.to_boolean(p_init_msg_list)
61 THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64 -- Initialize API return status to success
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66 -- Standard call to check for call compatibility.
67 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
68 l_api_version,
69 l_api_name,G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
75 fnd_log.string
76 (
77 fnd_log.level_statement,
78 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
79 'Request for Update Scheduled Sales Orders Number of Records : ' || p_sched_Orders_tbl.COUNT
80 );
81
82 END IF;
83
84
85 IF p_sched_Orders_tbl.COUNT > 0 THEN
86 FOR i IN p_sched_Orders_tbl.FIRST..p_sched_Orders_tbl.LAST
87 LOOP
88 -- Check for record exists in schedule materials
89 IF p_sched_Orders_tbl(i).order_line_id IS NOT NULL THEN
90 OPEN Check_Sched_Mat_Cur(p_sched_Orders_tbl(i).order_line_id);
91 FETCH Check_Sched_Mat_Cur INTO l_dummy;
92 IF Check_Sched_Mat_Cur%FOUND THEN
93 UPDATE AHL_SCHEDULE_MATERIALS
94 SET scheduled_date = p_sched_Orders_tbl(i).schedule_ship_date,
95 scheduled_quantity = p_sched_Orders_tbl(i).Quantity_By_Due_Date,
96 object_version_number = object_version_number + 1
97 WHERE scheduled_material_id = p_sched_Orders_tbl(i).order_line_id;
98 END IF;
99 CLOSE Check_Sched_Mat_Cur;
100 END IF;
101 END LOOP;
102 END IF;
103
104 --Standard check to count messages
105 l_msg_count := FND_MSG_PUB.count_msg;
106 IF l_msg_count > 0 THEN
107 RAISE FND_API.G_EXC_ERROR;
108 END IF;
109
110 -- Standard check of p_commit
111 IF FND_API.TO_BOOLEAN(p_commit) THEN
112 COMMIT WORK;
113 END IF;
114
115 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
116 fnd_log.string
117 (
118 fnd_log.level_procedure,
119 'ahl.plsql.AHL_LTP_ASCP_ORDERS_PVT.Update_Scheduling_Results.end',
120 'At the end of PLSQL procedure'
121 );
122 END IF;
123
124 EXCEPTION
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 ROLLBACK TO Update_Scheduling_Results;
127 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
129 p_count => l_msg_count,
130 p_data => l_msg_data);
131
132 WHEN FND_API.G_EXC_ERROR THEN
133 ROLLBACK TO Update_Scheduling_Results;
134 X_return_status := FND_API.G_RET_STS_ERROR;
135 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
136 p_count => l_msg_count,
137 p_data => l_msg_data);
138
139 WHEN OTHERS THEN
140 ROLLBACK TO Update_Scheduling_Results;
141 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
143 THEN
144 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_ASCP_ORDERS_PVT',
145 p_procedure_name => 'UPDATE_SCHEDULING_RESULTS',
146 p_error_text => SUBSTR(SQLERRM,1,240));
147 END IF;
148 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
149 p_count => l_msg_count,
150 p_data => l_msg_data);
151
152
153 END Update_Scheduling_Results;
154
155
156 END AHL_LTP_ASCP_ORDERS_PVT;
[Home] [Help]