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