[Home] [Help]
PACKAGE BODY: APPS.CSP_REQUIREMENT_POPULATE_PVT
Source
1 PACKAGE BODY CSP_REQUIREMENT_POPULATE_PVT AS
2 /* $Header: cspgrqpb.pls 120.3.12020000.2 2012/07/17 06:11:53 htank ship $ */
3 -- Start of Comments
4 -- Package name : CSP_SCH_INT_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgrqpb.pls';
12
13 PROCEDURE POPULATE_REQUIREMENTS(p_task_id IN NUMBER
14 ,p_api_version IN NUMBER
15 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
16 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17 ,x_return_status OUT NOCOPY VARCHAR2
18 ,x_msg_data OUT NOCOPY NUMBER
19 ,x_msg_count OUT NOCOPY NUMBER
20 ,px_header_id IN OUT NOCOPY NUMBER
21 ,p_called_by IN NUMBER) IS
22
23
24 CURSOR product_task_id(l_task_id NUMBER) IS
25 SELECT p.product_task_id,
26 p.ACTUAL_TIMES_USED
27 FROM jtf_tasks_b j,
28 CS_INCIDENTS_ALL_B i,
29 csp_product_tasks p,
30 JTF_TASK_TEMPLATES_B jtt,
31 jtf_task_types_b ttype
32 WHERE j.task_id = l_task_id
33 AND j.SOURCE_OBJECT_TYPE_CODE = 'SR'
34 AND i.incident_id = j.SOURCE_OBJECT_ID
35 AND p.product_id = i.inventory_item_id
36 AND p.task_template_id = j.template_id
37 AND j.template_id = jtt.task_template_id
38 AND jtt.task_type_id = ttype.task_type_id
39 AND NVL(ttype.spares_allowed_flag, 'N') = 'Y'
40 UNION ALL
41 SELECT cpp.product_task_id,
42 cpp.ACTUAL_TIMES_USED
43 FROM jtf_tasks_b jtb,
44 csd_repairs cr,
45 csp_product_tasks cpp,
46 JTF_TASK_TEMPLATES_B jtt,
47 jtf_task_types_b ttype
48 WHERE jtb.task_id = l_task_id
49 AND jtb.SOURCE_OBJECT_TYPE_CODE = 'DR'
50 AND cr.repair_line_id = jtb.SOURCE_OBJECT_ID
51 AND cpp.product_id = cr.inventory_item_id
52 AND cpp.task_template_id = jtb.template_id
53 AND jtb.template_id = jtt.task_template_id
54 AND jtt.task_type_id = ttype.task_type_id
55 AND NVL(ttype.spares_allowed_flag, 'N') = 'Y';
56
57
58 /* Commented to solve bug..
59
60
61 CURSOR get_parts(temp_product_task_id NUMBER) IS
62 SELECT INVENTORY_ITEM_ID,PRIMARY_UOM_CODE,QUANTITY,PERCENTAGE
63 FROM CSP_TASK_PARTS_V
64 WHERE PRODUCT_TASK_ID = temp_product_task_id;*/
65
66 CURSOR get_parts(temp_product_task_id NUMBER) IS
67 SELECT INVENTORY_ITEM_ID,PRIMARY_UOM_CODE,QUANTITY,PERCENTAGE,MANUAL_QUANTITY,ROLLUP_QUANTITY,REVISION
68 FROM CSP_TASK_PARTS_V
69 WHERE PRODUCT_TASK_ID = temp_product_task_id
70 AND INVENTORY_ITEM_FLAG = 'Y'
71 AND NVL( trunc(START_DATE),trunc(sysdate)) <= trunc(sysdate)
72 AND NVL( trunc(END_DATE),trunc(sysdate+1)) >= trunc(sysdate);
73
74 l_user_id NUMBER;
75 l_login_id NUMBER;
76 l_product_task_id NUMBER;
77 l_quantity NUMBER;
78 l_item_id NUMBER;
79 l_uom_code VARCHAR2(3);
80 l_ship_set VARCHAR2(10);
81 l_ship_complete VARCHAR2(1);
82 l_likelihood NUMBER;
83 l_requirement_header_rec CSP_Requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type;
84 l_requirement_line_tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type ;
85 l_requirement_header_id NUMBER;
86 l_header_return_status VARCHAR2(128);
87 l_line_return_status VARCHAR2(128);
88 l_api_version_number NUMBER := 1.0;
89 x_requirement_header_id NUMBER;
90 x_requirement_line_tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type ;
91 l_msg varchar2(2000);
92 count1 NUMBER := 0;
93 L_API_NAME CONSTANT VARCHAR2(30) := 'POPULATE_REQUIREMENTS';
94 l_manual_quantity NUMBER;
95 l_times_used NUMBER;
96 l_rollup_quantity NUMBER;
97 l_revision VARCHAR2(30);
98 BEGIN
99 SAVEPOINT CSP_REQUIREMENT_POPULATE_PVT;
100 x_msg_count := 0;
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 IF NOT FND_API.Compatible_API_Call
104 ( l_api_version_number
105 , p_api_version
106 , L_API_NAME
107 , G_PKG_NAME
108 )
109 THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 -- Initialize message stack if required
114 IF FND_API.to_Boolean(p_init_msg_list)
115 THEN
116 FND_MSG_PUB.initialize;
117 END IF;
118
119 l_user_id := fnd_global.user_id;
120 l_login_id := fnd_global.login_id;
121 IF l_login_id = -1 THEN
122 l_login_id := fnd_global.conc_login_id;
123 END IF;
124 l_ship_complete := fnd_profile.value(name => 'CSP_SHIP_COMPLETE');
125 IF l_ship_complete ='Y' THEN
126 l_ship_set := TO_CHAR(1);
127 END IF;
128 OPEN product_task_id(p_task_id);
129 LOOP
130 FETCH product_task_id INTO l_product_task_id ,l_times_used ;
131 EXIT WHEN product_task_id%NOTFOUND;
132 END LOOP;
133 IF l_product_task_id IS NOT NULL THEN
134 OPEN get_parts(l_product_task_id);
135 LOOP
136 FETCH get_parts INTO l_item_id,l_uom_code,l_quantity,l_likelihood,l_manual_quantity,l_rollup_quantity,l_revision;
137 EXIT WHEN get_parts%NOTFOUND;
138 l_header_return_status := FND_API.G_RET_STS_SUCCESS;
139 IF get_parts % ROWCOUNT = 1 AND p_called_by = 1 THEN
140 l_requirement_header_rec.CREATED_BY := l_user_id ;
141 l_requirement_header_rec.CREATION_DATE := SYSDATE ;
142 l_requirement_header_rec.LAST_UPDATED_BY := l_user_id ;
143 l_requirement_header_rec.LAST_UPDATE_DATE := SYSDATE;
144 l_requirement_header_rec.LAST_UPDATE_LOGIN:= l_login_id;
145 l_requirement_header_rec.OPEN_REQUIREMENT := 'Yes';
146 l_requirement_header_rec.TASK_ID := p_task_id;
147 l_requirement_header_rec.order_type_id := fnd_profile.value(name => 'CSP_ORDER_TYPE');
148 l_requirement_header_rec.address_type := 'R';
149 CSP_Requirement_headers_PVT.Create_requirement_headers(
150 P_Api_Version_Number => l_api_version_number
151 ,P_Init_Msg_List => FND_API.G_FALSE
152 ,P_Commit => FND_API.G_TRUE
153 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
154 ,P_REQUIREMENT_HEADER_Rec => l_requirement_header_rec
155 ,X_REQUIREMENT_HEADER_ID => px_header_id
156 ,X_Return_Status => l_header_return_status
157 ,X_Msg_Count => x_msg_count
158 ,X_Msg_Data => x_msg_data
159 );
160
161 END IF;
162 IF px_header_id is null and p_called_by = 2 THEN
163 select CSP_Requirement_headers_s1.nextval
164 INTO px_header_id
165 FROM DUAL;
166 END IF;
167 IF l_header_return_status = FND_API.G_RET_STS_SUCCESS THEN
168 If l_manual_quantity is null then
169 IF l_rollup_quantity is not null THEN
170 IF l_times_used >= FND_PROFILE.value(name => 'CSP_PROD_TASK_HIST_RULE') Then
171 count1 := count1 + 1;
172 l_requirement_line_tbl(count1).CREATED_BY := l_user_id;
173 l_requirement_line_tbl(count1).CREATION_DATE := SYSDATE;
174 l_requirement_line_tbl(count1).LAST_UPDATED_BY := l_user_id;
175 l_requirement_line_tbl(count1).LAST_UPDATE_DATE := SYSDATE ;
176 l_requirement_line_tbl(count1).LAST_UPDATE_LOGIN := l_login_id ;
177 l_requirement_line_tbl(count1).REQUIREMENT_HEADER_ID := px_header_id;
178 l_requirement_line_tbl(count1).INVENTORY_ITEM_ID := l_item_id;
179 l_requirement_line_tbl(count1).UOM_CODE := l_uom_code;
180 l_requirement_line_tbl(count1).REQUIRED_QUANTITY := l_quantity;
181 l_requirement_line_tbl(count1).SHIP_COMPLETE_FLAG := l_ship_set;
182 l_requirement_line_tbl(count1).LIKELIHOOD := l_likelihood;
183 l_requirement_line_tbl(count1).REVISION := l_revision;
184 END IF;
185 END IF;
186 else
187 count1 := count1 + 1;
188 l_requirement_line_tbl(count1).CREATED_BY := l_user_id;
189 l_requirement_line_tbl(count1).CREATION_DATE := SYSDATE;
190 l_requirement_line_tbl(count1).LAST_UPDATED_BY := l_user_id;
191 l_requirement_line_tbl(count1).LAST_UPDATE_DATE := SYSDATE ;
192 l_requirement_line_tbl(count1).LAST_UPDATE_LOGIN := l_login_id ;
193 l_requirement_line_tbl(count1).REQUIREMENT_HEADER_ID := px_header_id;
194 l_requirement_line_tbl(count1).INVENTORY_ITEM_ID := l_item_id;
195 l_requirement_line_tbl(count1).UOM_CODE := l_uom_code;
196 l_requirement_line_tbl(count1).REQUIRED_QUANTITY := l_quantity;
197 l_requirement_line_tbl(count1).SHIP_COMPLETE_FLAG := l_ship_set;
198 l_requirement_line_tbl(count1).LIKELIHOOD := l_likelihood;
199 l_requirement_line_tbl(count1).REVISION := l_revision;
200 end if;
201 ELSE
202 x_return_status := l_header_return_status;
203 RETURN;
204 END IF;
205 END LOOP;
206 IF l_header_return_status = FND_API.G_RET_STS_SUCCESS THEN
207 CSP_Requirement_Lines_PVT.Create_requirement_lines(
208 P_Api_Version_Number => l_api_version_number
209 ,P_Init_Msg_List => FND_API.G_FALSE
210 ,P_Commit => FND_API.G_TRUE
211 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
212 ,P_Requirement_Line_TBL => l_Requirement_Line_tbl
213 ,X_Requirement_Line_TBL => x_requirement_line_tbl
214 ,X_Return_Status => l_line_return_status
215 ,X_Msg_Count => x_msg_count
216 ,X_Msg_Data => x_msg_data
217 );
218 IF l_line_return_status = FND_API.G_RET_STS_SUCCESS THEN
219 x_return_status := l_line_return_status;
220 IF FND_API.to_Boolean( p_commit ) THEN
221 COMMIT;
222 END IF;
223 ELSE
224 x_return_status := l_line_return_status;
225 RETURN;
226 END IF;
227 END IF;
228 END IF;
229 CLOSE product_task_id;
230 x_return_status :=FND_API.G_RET_STS_SUCCESS;
231 EXCEPTION
232 WHEN OTHERS THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 ROLLBACK TO CSP_REQUIREMENT_POPULATE_PVT;
235 END POPULATE_REQUIREMENTS;
236 END CSP_REQUIREMENT_POPULATE_PVT;