[Home] [Help]
157: )
158: IS
159: BEGIN
160: IF (p_organization_id IS NOT NULL AND
161: p_organization_id <> FND_API.G_MISS_NUM)
162: THEN
163: SELECT organization_id
164: INTO x_organization_id
165: FROM HR_ALL_ORGANIZATION_UNITS
169: INTO x_organization_id
170: FROM HR_ALL_ORGANIZATION_UNITS
171: WHERE NAME = p_org_name;
172: END IF;
173: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
174: EXCEPTION
175: WHEN NO_DATA_FOUND THEN
176: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
177: x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
172: END IF;
173: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
174: EXCEPTION
175: WHEN NO_DATA_FOUND THEN
176: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
177: x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
178: WHEN TOO_MANY_ROWS THEN
179: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
180: x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
175: WHEN NO_DATA_FOUND THEN
176: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
177: x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
178: WHEN TOO_MANY_ROWS THEN
179: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
180: x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
181: WHEN OTHERS THEN
182: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
183: RAISE;
178: WHEN TOO_MANY_ROWS THEN
179: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
180: x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
181: WHEN OTHERS THEN
182: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
183: RAISE;
184: END Check_org_name_Or_Id;
185: --
186: PROCEDURE Check_dept_desc_Or_Id
194: )
195: IS
196: BEGIN
197: IF (p_department_id IS NOT NULL AND
198: p_department_id <> FND_API.G_MISS_NUM)
199: THEN
200: SELECT department_id
201: INTO x_department_id
202: FROM BOM_DEPARTMENTS
209: FROM BOM_DEPARTMENTS
210: WHERE organization_id = p_organization_id
211: AND description = p_dept_description;
212: END IF;
213: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
214: EXCEPTION
215: WHEN NO_DATA_FOUND THEN
216: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
217: x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
212: END IF;
213: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
214: EXCEPTION
215: WHEN NO_DATA_FOUND THEN
216: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
217: x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
218: WHEN TOO_MANY_ROWS THEN
219: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
220: x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
215: WHEN NO_DATA_FOUND THEN
216: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
217: x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
218: WHEN TOO_MANY_ROWS THEN
219: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
220: x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
221: WHEN OTHERS THEN
222: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
223: RAISE;
218: WHEN TOO_MANY_ROWS THEN
219: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
220: x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
221: WHEN OTHERS THEN
222: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
223: RAISE;
224: END Check_dept_desc_Or_Id;
225: --
226: PROCEDURE Get_visit_task_Id
230: x_error_msg_code OUT NOCOPY VARCHAR2
231: ) IS
232: BEGIN
233: IF (p_workorder_id IS NOT NULL AND
234: p_workorder_id <> FND_API.G_MISS_NUM) THEN
235: SELECT visit_task_id INTO x_visit_task_id
236: FROM AHL_WORKORDERS
237: WHERE workorder_id = p_workorder_id;
238: END IF;
235: SELECT visit_task_id INTO x_visit_task_id
236: FROM AHL_WORKORDERS
237: WHERE workorder_id = p_workorder_id;
238: END IF;
239: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
240: IF G_DEBUG='Y' THEN
241: AHL_DEBUG_PUB.debug( 'XVISITTASK:'|| x_visit_task_id);
242: END IF;
243: EXCEPTION
241: AHL_DEBUG_PUB.debug( 'XVISITTASK:'|| x_visit_task_id);
242: END IF;
243: EXCEPTION
244: WHEN NO_DATA_FOUND THEN
245: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
246: x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
247: WHEN TOO_MANY_ROWS THEN
248: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
249: x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
244: WHEN NO_DATA_FOUND THEN
245: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
246: x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
247: WHEN TOO_MANY_ROWS THEN
248: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
249: x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
250: WHEN OTHERS THEN
251: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
252: RAISE;
247: WHEN TOO_MANY_ROWS THEN
248: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
249: x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
250: WHEN OTHERS THEN
251: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
252: RAISE;
253: END Get_visit_task_Id;
254: --
255: PROCEDURE Get_workorder_Id
272: SELECT workorder_id INTO x_workorder_id
273: FROM AHL_WORKORDERS
274: WHERE workorder_name = p_job_number;
275: END IF;
276: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
277: IF G_DEBUG='Y' THEN
278: AHL_DEBUG_PUB.debug( 'XWOID:'|| x_workorder_id);
279: END IF;
280: EXCEPTION
278: AHL_DEBUG_PUB.debug( 'XWOID:'|| x_workorder_id);
279: END IF;
280: EXCEPTION
281: WHEN NO_DATA_FOUND THEN
282: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
283: x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
284: WHEN TOO_MANY_ROWS THEN
285: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
286: x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
281: WHEN NO_DATA_FOUND THEN
282: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
283: x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
284: WHEN TOO_MANY_ROWS THEN
285: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
286: x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
287: WHEN OTHERS THEN
288: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
289: RAISE;
284: WHEN TOO_MANY_ROWS THEN
285: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
286: x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
287: WHEN OTHERS THEN
288: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
289: RAISE;
290: END Get_workorder_Id;
291: --
292: PROCEDURE Get_workorder_operation_Id
305: WHERE workorder_id = p_workorder_id
306: AND operation_sequence_num = p_operation_sequence;
307: --
308: END IF;
309: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
310: EXCEPTION
311: WHEN NO_DATA_FOUND THEN
312: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
313: x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
308: END IF;
309: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
310: EXCEPTION
311: WHEN NO_DATA_FOUND THEN
312: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
313: x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
314: WHEN TOO_MANY_ROWS THEN
315: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
316: x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
311: WHEN NO_DATA_FOUND THEN
312: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
313: x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
314: WHEN TOO_MANY_ROWS THEN
315: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
316: x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
317: WHEN OTHERS THEN
318: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
319: RAISE;
314: WHEN TOO_MANY_ROWS THEN
315: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
316: x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
317: WHEN OTHERS THEN
318: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
319: RAISE;
320: END Get_workorder_operation_Id;
321: --
322: PROCEDURE Get_inventory_item_Id
328: x_error_msg_code OUT NOCOPY VARCHAR2
329: ) IS
330: BEGIN
331: IF (p_inventory_item_id IS NOT NULL AND
332: p_inventory_item_id <> FND_API.G_MISS_NUM) THEN
333: --
334: SELECT inventory_item_id INTO x_inventory_item_id
335: FROM MTL_SYSTEM_ITEMS_KFV
336: WHERE inventory_item_id = p_inventory_item_id
351: AND mtl_transactions_enabled_flag = 'Y'
352: AND nvl(enabled_flag,'N') = 'Y'
353: AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
354: END IF;
355: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
356: EXCEPTION
357: WHEN NO_DATA_FOUND THEN
358: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
359: x_error_msg_code:= 'AHL_PP_INVALID_ITEM';
354: END IF;
355: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
356: EXCEPTION
357: WHEN NO_DATA_FOUND THEN
358: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
359: x_error_msg_code:= 'AHL_PP_INVALID_ITEM';
360: WHEN OTHERS THEN
361: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
362: RAISE;
357: WHEN NO_DATA_FOUND THEN
358: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
359: x_error_msg_code:= 'AHL_PP_INVALID_ITEM';
360: WHEN OTHERS THEN
361: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
362: RAISE;
363: END Get_inventory_item_Id;
364: --
365: PROCEDURE Get_visit_task_details
767: NAME = c_unit_name ) AND
768: AMH.NAME = c_mc_name ;
769: l_unit_name VARCHAR2(80);
770: BEGIN
771: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
772: --Get unit name
773: OPEN Get_Wo_Unit_Name_cur(p_workorder_id);
774: FETCH Get_Wo_Unit_Name_cur INTO l_unit_name;
775: IF Get_Wo_Unit_Name_cur%NOTFOUND THEN
776: l_unit_name := null;
777: END IF;
778: CLOSE Get_Wo_Unit_Name_cur;
779: IF (p_mc_header_id IS NOT NULL AND
780: p_mc_header_id <> FND_API.G_MISS_NUM) THEN
781: --
782: OPEN Get_rec_from_id(l_unit_name, p_mc_header_id );
783: FETCH Get_rec_from_id INTO
784: x_mc_header_id;
782: OPEN Get_rec_from_id(l_unit_name, p_mc_header_id );
783: FETCH Get_rec_from_id INTO
784: x_mc_header_id;
785: IF Get_rec_from_id%NOTFOUND THEN
786: x_return_status := FND_API.G_RET_STS_ERROR;
787: x_msg_data := 'AHL_PP_INVALID_MC';
788: END IF;
789: CLOSE Get_rec_from_id;
790: RETURN;
795: FETCH get_rec_from_value INTO x_mc_header_id;
796: EXIT WHEN get_rec_from_value%NOTFOUND;
797: END LOOP;
798: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
799: x_return_status := FND_API.G_RET_STS_ERROR;
800: x_msg_data := 'AHL_PP_INVALID_MC';
801: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
802: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
803: ELSE
798: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
799: x_return_status := FND_API.G_RET_STS_ERROR;
800: x_msg_data := 'AHL_PP_INVALID_MC';
801: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
802: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
803: ELSE
804: x_return_status := FND_API.G_RET_STS_ERROR;
805: x_msg_data := 'AHL_PP_TOO_MANY_MC';
806: x_mc_header_id := NULL;
800: x_msg_data := 'AHL_PP_INVALID_MC';
801: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
802: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
803: ELSE
804: x_return_status := FND_API.G_RET_STS_ERROR;
805: x_msg_data := 'AHL_PP_TOO_MANY_MC';
806: x_mc_header_id := NULL;
807: END IF;
808: CLOSE get_rec_from_value;
809: RETURN;
810: END IF;
811: EXCEPTION
812: WHEN OTHERS THEN
813: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
814: RAISE;
815: END Get_mc_header_id;
816: --Procedure to get mc_position_key from id or value
817: PROCEDURE Get_mc_position_key
854: AMR.MC_HEADER_ID = c_mc_header_id AND
855: FLV.MEANING = c_mc_position;
856:
857: BEGIN
858: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
859: IF (p_mc_position_key IS NOT NULL AND
860: p_mc_position_key <> FND_API.G_MISS_NUM) THEN
861: --
862: OPEN Get_rec_from_id(p_mc_header_id, p_mc_position_key );
856:
857: BEGIN
858: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
859: IF (p_mc_position_key IS NOT NULL AND
860: p_mc_position_key <> FND_API.G_MISS_NUM) THEN
861: --
862: OPEN Get_rec_from_id(p_mc_header_id, p_mc_position_key );
863: FETCH Get_rec_from_id INTO x_mc_position_key, x_relationship_id;
864: IF Get_rec_from_id%NOTFOUND THEN
861: --
862: OPEN Get_rec_from_id(p_mc_header_id, p_mc_position_key );
863: FETCH Get_rec_from_id INTO x_mc_position_key, x_relationship_id;
864: IF Get_rec_from_id%NOTFOUND THEN
865: x_return_status := FND_API.G_RET_STS_ERROR;
866: x_msg_data := 'AHL_PP_INVALID_MC_POS';
867: END IF;
868: CLOSE Get_rec_from_id;
869: RETURN;
874: FETCH get_rec_from_value INTO x_mc_position_key, x_relationship_id;
875: EXIT WHEN get_rec_from_value%NOTFOUND;
876: END LOOP;
877: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
878: x_return_status := FND_API.G_RET_STS_ERROR;
879: x_msg_data := 'AHL_PP_INVALID_MC_POS';
880: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
881: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
882: ELSE
877: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
878: x_return_status := FND_API.G_RET_STS_ERROR;
879: x_msg_data := 'AHL_PP_INVALID_MC_POS';
880: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
881: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
882: ELSE
883: x_return_status := FND_API.G_RET_STS_ERROR;
884: x_msg_data := 'AHL_PP_TOO_MANY_MC_POS';
885: x_mc_position_key := NULL;
879: x_msg_data := 'AHL_PP_INVALID_MC_POS';
880: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
881: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
882: ELSE
883: x_return_status := FND_API.G_RET_STS_ERROR;
884: x_msg_data := 'AHL_PP_TOO_MANY_MC_POS';
885: x_mc_position_key := NULL;
886: x_relationship_id := NULL;
887: END IF;
889: RETURN;
890: END IF;
891: EXCEPTION
892: WHEN OTHERS THEN
893: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
894: RAISE;
895: END Get_mc_position_key;
896: --Procedure to get mc_item_group_id from id or value
897: PROCEDURE Get_item_group_id
920: ITMGRP.STATUS_CODE = 'COMPLETE'AND
921: ITMGRP.NAME = c_item_group_name;
922:
923: BEGIN
924: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
925:
926: IF (p_item_group_id IS NOT NULL AND
927: p_item_group_id <> FND_API.G_MISS_NUM) THEN
928: --
923: BEGIN
924: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
925:
926: IF (p_item_group_id IS NOT NULL AND
927: p_item_group_id <> FND_API.G_MISS_NUM) THEN
928: --
929: OPEN Get_rec_from_id(p_item_group_id );
930: FETCH Get_rec_from_id INTO x_item_group_id;
931: IF Get_rec_from_id%NOTFOUND THEN
928: --
929: OPEN Get_rec_from_id(p_item_group_id );
930: FETCH Get_rec_from_id INTO x_item_group_id;
931: IF Get_rec_from_id%NOTFOUND THEN
932: x_return_status := FND_API.G_RET_STS_ERROR;
933: x_msg_data := 'AHL_PP_INVALID_MC_ITM_GRP';
934: END IF;
935: CLOSE Get_rec_from_id;
936: RETURN;
941: FETCH get_rec_from_value INTO x_item_group_id;
942: EXIT WHEN get_rec_from_value%NOTFOUND;
943: END LOOP;
944: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
945: x_return_status := FND_API.G_RET_STS_ERROR;
946: x_msg_data := 'AHL_PP_INVALID_MC_ITM_GRP';
947: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
948: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
949: ELSE
944: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
945: x_return_status := FND_API.G_RET_STS_ERROR;
946: x_msg_data := 'AHL_PP_INVALID_MC_ITM_GRP';
947: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
948: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
949: ELSE
950: x_return_status := FND_API.G_RET_STS_ERROR;
951: x_msg_data := 'AHL_PP_TOO_MANY_MC_ITM_GRP';
952: x_item_group_id := NULL;
946: x_msg_data := 'AHL_PP_INVALID_MC_ITM_GRP';
947: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
948: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
949: ELSE
950: x_return_status := FND_API.G_RET_STS_ERROR;
951: x_msg_data := 'AHL_PP_TOO_MANY_MC_ITM_GRP';
952: x_item_group_id := NULL;
953: END IF;
954: CLOSE get_rec_from_value;
955: RETURN;
956: END IF;
957: EXCEPTION
958: WHEN OTHERS THEN
959: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
960: RAISE;
961: END Get_item_group_id;
962: --Procedure to get inventory_item_id from id or value based item group
963: PROCEDURE Get_item_Id_from_item_group
1003: AND ais.item_group_id = aig.item_group_id
1004: AND mtl.inventory_item_id = ais.inventory_item_id
1005: AND mtl.concatenated_segments = c_concatenated_segments;
1006: BEGIN
1007: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1008: IF (p_inventory_item_id IS NOT NULL AND
1009: p_inventory_item_id <> FND_API.G_MISS_NUM) THEN
1010: --
1011: OPEN Get_rec_from_id(p_inventory_item_id, p_item_group_id, p_organization_id );
1005: AND mtl.concatenated_segments = c_concatenated_segments;
1006: BEGIN
1007: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1008: IF (p_inventory_item_id IS NOT NULL AND
1009: p_inventory_item_id <> FND_API.G_MISS_NUM) THEN
1010: --
1011: OPEN Get_rec_from_id(p_inventory_item_id, p_item_group_id, p_organization_id );
1012: FETCH Get_rec_from_id INTO
1013: x_inventory_item_id;
1011: OPEN Get_rec_from_id(p_inventory_item_id, p_item_group_id, p_organization_id );
1012: FETCH Get_rec_from_id INTO
1013: x_inventory_item_id;
1014: IF Get_rec_from_id%NOTFOUND THEN
1015: x_return_status := FND_API.G_RET_STS_ERROR;
1016: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1017: END IF;
1018: CLOSE Get_rec_from_id;
1019: RETURN;
1024: FETCH get_rec_from_value INTO x_inventory_item_id;
1025: EXIT WHEN get_rec_from_value%NOTFOUND;
1026: END LOOP;
1027: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1028: x_return_status := FND_API.G_RET_STS_ERROR;
1029: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1030: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1031: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1032: ELSE
1027: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1028: x_return_status := FND_API.G_RET_STS_ERROR;
1029: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1030: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1031: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1032: ELSE
1033: x_return_status := FND_API.G_RET_STS_ERROR;
1034: x_msg_data := 'AHL_PP_TOO_MANY_MC_GRP_ITEM';
1035: x_inventory_item_id := NULL;
1029: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1030: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1031: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1032: ELSE
1033: x_return_status := FND_API.G_RET_STS_ERROR;
1034: x_msg_data := 'AHL_PP_TOO_MANY_MC_GRP_ITEM';
1035: x_inventory_item_id := NULL;
1036: END IF;
1037: CLOSE get_rec_from_value;
1038: RETURN;
1039: END IF;
1040: EXCEPTION
1041: WHEN OTHERS THEN
1042: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
1043: RAISE;
1044: END Get_item_Id_from_item_group;
1045:
1046: PROCEDURE Get_item_Id_from_mc_pos
1122: TRUNC(NVL(MSI.END_DATE_ACTIVE, SYSDATE)) AND
1123: MSI.ORGANIZATION_ID = c_organization_id AND
1124: MSI.CONCATENATED_SEGMENTS = c_concatenated_segments;
1125: BEGIN
1126: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1127: IF (p_inventory_item_id IS NOT NULL AND
1128: p_inventory_item_id <> FND_API.G_MISS_NUM) THEN
1129: --
1130: OPEN Get_rec_from_id(p_inventory_item_id, p_relationship_id, p_organization_id );
1124: MSI.CONCATENATED_SEGMENTS = c_concatenated_segments;
1125: BEGIN
1126: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1127: IF (p_inventory_item_id IS NOT NULL AND
1128: p_inventory_item_id <> FND_API.G_MISS_NUM) THEN
1129: --
1130: OPEN Get_rec_from_id(p_inventory_item_id, p_relationship_id, p_organization_id );
1131: FETCH Get_rec_from_id INTO
1132: x_inventory_item_id;
1130: OPEN Get_rec_from_id(p_inventory_item_id, p_relationship_id, p_organization_id );
1131: FETCH Get_rec_from_id INTO
1132: x_inventory_item_id;
1133: IF Get_rec_from_id%NOTFOUND THEN
1134: x_return_status := FND_API.G_RET_STS_ERROR;
1135: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1136: END IF;
1137: CLOSE Get_rec_from_id;
1138: RETURN;
1143: FETCH get_rec_from_value INTO x_inventory_item_id;
1144: EXIT WHEN get_rec_from_value%NOTFOUND;
1145: END LOOP;
1146: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1147: x_return_status := FND_API.G_RET_STS_ERROR;
1148: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1149: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1150: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1151: ELSE
1146: IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1147: x_return_status := FND_API.G_RET_STS_ERROR;
1148: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1149: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1150: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1151: ELSE
1152: x_return_status := FND_API.G_RET_STS_ERROR;
1153: x_msg_data := 'AHL_PP_TOO_MANY_MC_GRP_ITEM';
1154: x_inventory_item_id := NULL;
1148: x_msg_data := 'AHL_PP_INVALID_MC_GRP_ITEM';
1149: ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1150: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1151: ELSE
1152: x_return_status := FND_API.G_RET_STS_ERROR;
1153: x_msg_data := 'AHL_PP_TOO_MANY_MC_GRP_ITEM';
1154: x_inventory_item_id := NULL;
1155: END IF;
1156: CLOSE get_rec_from_value;
1157: RETURN;
1158: END IF;
1159: EXCEPTION
1160: WHEN OTHERS THEN
1161: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
1162: RAISE;
1163: END Get_item_Id_from_mc_pos;
1164:
1165: PROCEDURE Validate_mat_req_record( p_mc_header_id IN NUMBER,
1170: x_msg_data OUT NOCOPY VARCHAR2
1171: )
1172: IS
1173: BEGIN
1174: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1175: IF ( (p_mc_header_id IS NOT NULL AND p_mc_header_id <> Fnd_Api.G_MISS_NUM) OR
1176: (p_mc_position_key IS NOT NULL AND p_mc_position_key <> Fnd_Api.G_MISS_NUM)) THEN
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1171: )
1172: IS
1173: BEGIN
1174: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1175: IF ( (p_mc_header_id IS NOT NULL AND p_mc_header_id <> Fnd_Api.G_MISS_NUM) OR
1176: (p_mc_position_key IS NOT NULL AND p_mc_position_key <> Fnd_Api.G_MISS_NUM)) THEN
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1172: IS
1173: BEGIN
1174: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1175: IF ( (p_mc_header_id IS NOT NULL AND p_mc_header_id <> Fnd_Api.G_MISS_NUM) OR
1176: (p_mc_position_key IS NOT NULL AND p_mc_position_key <> Fnd_Api.G_MISS_NUM)) THEN
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1180: (p_item_group_id IS NOT NULL AND p_item_group_id <> Fnd_Api.G_MISS_NUM) OR
1174: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1175: IF ( (p_mc_header_id IS NOT NULL AND p_mc_header_id <> Fnd_Api.G_MISS_NUM) OR
1176: (p_mc_position_key IS NOT NULL AND p_mc_position_key <> Fnd_Api.G_MISS_NUM)) THEN
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1180: (p_item_group_id IS NOT NULL AND p_item_group_id <> Fnd_Api.G_MISS_NUM) OR
1181: (p_inventory_item_id IS NULL OR p_inventory_item_id = Fnd_Api.G_MISS_NUM)) THEN
1182: x_return_status := Fnd_Api.G_RET_STS_ERROR;
1175: IF ( (p_mc_header_id IS NOT NULL AND p_mc_header_id <> Fnd_Api.G_MISS_NUM) OR
1176: (p_mc_position_key IS NOT NULL AND p_mc_position_key <> Fnd_Api.G_MISS_NUM)) THEN
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1180: (p_item_group_id IS NOT NULL AND p_item_group_id <> Fnd_Api.G_MISS_NUM) OR
1181: (p_inventory_item_id IS NULL OR p_inventory_item_id = Fnd_Api.G_MISS_NUM)) THEN
1182: x_return_status := Fnd_Api.G_RET_STS_ERROR;
1183: x_msg_data := 'AHL_PP_INVLD_MAT_REQ_COMB';
1176: (p_mc_position_key IS NOT NULL AND p_mc_position_key <> Fnd_Api.G_MISS_NUM)) THEN
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1180: (p_item_group_id IS NOT NULL AND p_item_group_id <> Fnd_Api.G_MISS_NUM) OR
1181: (p_inventory_item_id IS NULL OR p_inventory_item_id = Fnd_Api.G_MISS_NUM)) THEN
1182: x_return_status := Fnd_Api.G_RET_STS_ERROR;
1183: x_msg_data := 'AHL_PP_INVLD_MAT_REQ_COMB';
1184: END IF;
1177: --for position based mat. req. mc-pos and item must be present.
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1180: (p_item_group_id IS NOT NULL AND p_item_group_id <> Fnd_Api.G_MISS_NUM) OR
1181: (p_inventory_item_id IS NULL OR p_inventory_item_id = Fnd_Api.G_MISS_NUM)) THEN
1182: x_return_status := Fnd_Api.G_RET_STS_ERROR;
1183: x_msg_data := 'AHL_PP_INVLD_MAT_REQ_COMB';
1184: END IF;
1185: END IF;
1178: IF ((p_mc_header_id IS NULL OR p_mc_header_id = Fnd_Api.G_MISS_NUM) OR
1179: (p_mc_position_key IS NULL OR p_mc_position_key = Fnd_Api.G_MISS_NUM) OR
1180: (p_item_group_id IS NOT NULL AND p_item_group_id <> Fnd_Api.G_MISS_NUM) OR
1181: (p_inventory_item_id IS NULL OR p_inventory_item_id = Fnd_Api.G_MISS_NUM)) THEN
1182: x_return_status := Fnd_Api.G_RET_STS_ERROR;
1183: x_msg_data := 'AHL_PP_INVLD_MAT_REQ_COMB';
1184: END IF;
1185: END IF;
1186: END Validate_mat_req_record;
1196: -- Parameters :
1197: --
1198: -- Standard IN Parameters :
1199: -- p_api_version IN NUMBER Required
1200: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1201: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1202: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1203: -- p_module_type IN VARCHAR2 Default NULL
1204: -- p_interface_flag IN VARCHAR2,
1197: --
1198: -- Standard IN Parameters :
1199: -- p_api_version IN NUMBER Required
1200: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1201: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1202: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1203: -- p_module_type IN VARCHAR2 Default NULL
1204: -- p_interface_flag IN VARCHAR2,
1205: --
1198: -- Standard IN Parameters :
1199: -- p_api_version IN NUMBER Required
1200: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1201: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1202: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1203: -- p_module_type IN VARCHAR2 Default NULL
1204: -- p_interface_flag IN VARCHAR2,
1205: --
1206: -- Standard OUT Parameters :
1218: --
1219: -- End of Comments.
1220: PROCEDURE Create_Material_Reqst (
1221: p_api_version IN NUMBER,
1222: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1223: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1224: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1225: p_module_type IN VARCHAR2 := NULL,
1226: p_interface_flag IN VARCHAR2 ,
1219: -- End of Comments.
1220: PROCEDURE Create_Material_Reqst (
1221: p_api_version IN NUMBER,
1222: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1223: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1224: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1225: p_module_type IN VARCHAR2 := NULL,
1226: p_interface_flag IN VARCHAR2 ,
1227: p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
1220: PROCEDURE Create_Material_Reqst (
1221: p_api_version IN NUMBER,
1222: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1223: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1224: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1225: p_module_type IN VARCHAR2 := NULL,
1226: p_interface_flag IN VARCHAR2 ,
1227: p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
1228: x_job_return_status OUT NOCOPY VARCHAR2,
1382: l_wip_entity_id NUMBER;
1383: l_workorder_operation_id NUMBER;
1384: l_wo_organization_id NUMBER;
1385: l_object_version_number NUMBER;
1386: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1387: l_Req_Material_Tbl Req_Material_Tbl_Type;
1388: l_default VARCHAR2(30);
1389: l_wo_operation_txn_id NUMBER;
1390: l_schedule_start_date DATE;
1425: AHL_DEBUG_PUB.debug( 'INTERAFCE FALG'||p_interface_flag);
1426: AHL_DEBUG_PUB.debug( 'Total Number Records:'||p_x_req_material_tbl.COUNT);
1427: END IF;
1428: -- Standard call to check for call compatibility.
1429: IF FND_API.to_boolean(l_init_msg_list)
1430: THEN
1431: FND_MSG_PUB.initialize;
1432: END IF;
1433: -- Initialize API return status to success
1430: THEN
1431: FND_MSG_PUB.initialize;
1432: END IF;
1433: -- Initialize API return status to success
1434: x_return_status := FND_API.G_RET_STS_SUCCESS;
1435: -- Initialize message list if p_init_msg_list is set to TRUE.
1436: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1437: p_api_version,
1438: l_api_name,G_PKG_NAME)
1432: END IF;
1433: -- Initialize API return status to success
1434: x_return_status := FND_API.G_RET_STS_SUCCESS;
1435: -- Initialize message list if p_init_msg_list is set to TRUE.
1436: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1437: p_api_version,
1438: l_api_name,G_PKG_NAME)
1439: THEN
1440: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1437: p_api_version,
1438: l_api_name,G_PKG_NAME)
1439: THEN
1440: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441: END IF;
1442: --------------------Start of API Body-----------------------------------
1443: IF p_x_req_material_tbl.COUNT > 0 THEN
1444: FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
1449: AHL_DEBUG_PUB.debug( 'OPFLAG 1:'||p_x_req_material_tbl(i).operation_flag);
1450: AHL_DEBUG_PUB.debug( 'ITEM:'||i||'-'||p_x_req_material_tbl(i).concatenated_segments);
1451: END IF;
1452: IF ( ( p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
1453: p_x_req_material_tbl(i).workorder_id <> FND_API.G_MISS_NUM ) OR
1454: ( p_x_req_material_tbl(i).job_number IS NOT NULL AND
1455: p_x_req_material_tbl(i).job_number <> FND_API.G_MISS_CHAR ) )
1456: THEN
1457: --
1451: END IF;
1452: IF ( ( p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
1453: p_x_req_material_tbl(i).workorder_id <> FND_API.G_MISS_NUM ) OR
1454: ( p_x_req_material_tbl(i).job_number IS NOT NULL AND
1455: p_x_req_material_tbl(i).job_number <> FND_API.G_MISS_CHAR ) )
1456: THEN
1457: --
1458: IF G_DEBUG='Y' THEN
1459: AHL_DEBUG_PUB.debug( 'WOID 2:'||p_x_req_material_tbl(i).workorder_id);
1484: p_ue_id => NULL,
1485: p_visit_id => NULL,
1486: p_item_instance_id => NULL
1487: );
1488: IF l_return_status = FND_API.G_TRUE THEN
1489: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_CRT_MTL_UNTLCKD');
1490: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
1491: FND_MSG_PUB.ADD;
1492: RAISE FND_API.G_EXC_ERROR;
1488: IF l_return_status = FND_API.G_TRUE THEN
1489: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_CRT_MTL_UNTLCKD');
1490: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
1491: FND_MSG_PUB.ADD;
1492: RAISE FND_API.G_EXC_ERROR;
1493: END IF;
1494: -- rroy
1495: -- ACL Changes
1496: --
1496: --
1497: p_x_req_material_tbl(i).workorder_id := nvl(p_x_req_material_tbl(i).workorder_id,l_workorder_id);
1498: --Get visit task id
1499: IF (p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
1500: p_x_req_material_tbl(i).workorder_id <> Fnd_Api.G_MISS_NUM )
1501: THEN
1502: Get_visit_task_Id
1503: (p_workorder_id => p_x_req_material_tbl(i).workorder_id,
1504: x_visit_task_id => l_visit_task_id,
1590: --Assign null to relationship_id
1591: l_relationship_tbl(i) := null;
1592: --Get mc_header_id from mc_name
1593: IF (p_x_req_material_tbl(i).mc_name IS NOT NULL AND
1594: p_x_req_material_tbl(i).mc_name <> Fnd_Api.G_MISS_CHAR ) OR
1595: (p_x_req_material_tbl(i).mc_header_id IS NOT NULL AND
1596: p_x_req_material_tbl(i).mc_header_id <> Fnd_Api.G_MISS_NUM) THEN
1597: Get_mc_header_id(p_mc_header_id => p_x_req_material_tbl(i).mc_header_id,
1598: p_mc_name => p_x_req_material_tbl(i).mc_name,
1592: --Get mc_header_id from mc_name
1593: IF (p_x_req_material_tbl(i).mc_name IS NOT NULL AND
1594: p_x_req_material_tbl(i).mc_name <> Fnd_Api.G_MISS_CHAR ) OR
1595: (p_x_req_material_tbl(i).mc_header_id IS NOT NULL AND
1596: p_x_req_material_tbl(i).mc_header_id <> Fnd_Api.G_MISS_NUM) THEN
1597: Get_mc_header_id(p_mc_header_id => p_x_req_material_tbl(i).mc_header_id,
1598: p_mc_name => p_x_req_material_tbl(i).mc_name,
1599: p_workorder_id => p_x_req_material_tbl(i).workorder_id,
1600: x_mc_header_id => l_mc_header_id,
1603: IF NVL(l_return_status,'x') <> 'S'
1604: THEN
1605: Fnd_Message.SET_NAME('AHL',l_msg_data);
1606: IF ( p_x_req_material_tbl(i).mc_name IS NULL OR
1607: p_x_req_material_tbl(i).mc_name = FND_API.G_MISS_CHAR ) THEN
1608: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).mc_header_id );
1609: ELSE
1610: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).mc_name );
1611: END IF;
1615: p_x_req_material_tbl(i).mc_header_id := l_mc_header_id;
1616: END IF;
1617: --Get mc_position_key from position_ref_code
1618: IF (p_x_req_material_tbl(i).mc_position IS NOT NULL AND
1619: p_x_req_material_tbl(i).mc_position <> Fnd_Api.G_MISS_CHAR ) OR
1620: (p_x_req_material_tbl(i).mc_position_key IS NOT NULL AND
1621: p_x_req_material_tbl(i).mc_position_key <> Fnd_Api.G_MISS_NUM) THEN
1622: Get_mc_position_key(p_mc_header_id => p_x_req_material_tbl(i).mc_header_id,
1623: p_mc_position => p_x_req_material_tbl(i).mc_position,
1617: --Get mc_position_key from position_ref_code
1618: IF (p_x_req_material_tbl(i).mc_position IS NOT NULL AND
1619: p_x_req_material_tbl(i).mc_position <> Fnd_Api.G_MISS_CHAR ) OR
1620: (p_x_req_material_tbl(i).mc_position_key IS NOT NULL AND
1621: p_x_req_material_tbl(i).mc_position_key <> Fnd_Api.G_MISS_NUM) THEN
1622: Get_mc_position_key(p_mc_header_id => p_x_req_material_tbl(i).mc_header_id,
1623: p_mc_position => p_x_req_material_tbl(i).mc_position,
1624: p_mc_position_key => p_x_req_material_tbl(i).mc_position_key,
1625: x_mc_position_key => l_mc_position_key,
1629: IF NVL(l_return_status,'x') <> 'S'
1630: THEN
1631: Fnd_Message.SET_NAME('AHL',l_msg_data);
1632: IF ( p_x_req_material_tbl(i).mc_position IS NULL OR
1633: p_x_req_material_tbl(i).mc_position = FND_API.G_MISS_CHAR ) THEN
1634: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).mc_position_key );
1635: ELSE
1636: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).mc_position );
1637: END IF;
1641: p_x_req_material_tbl(i).mc_position_key := l_mc_position_key;
1642: END IF;
1643: --Get item_group_id from item_group_name only if mc or position is given
1644: IF ((p_x_req_material_tbl(i).item_group_name IS NOT NULL AND
1645: p_x_req_material_tbl(i).item_group_name <> Fnd_Api.G_MISS_CHAR ) OR
1646: (p_x_req_material_tbl(i).item_group_id IS NOT NULL AND
1647: p_x_req_material_tbl(i).item_group_id <> Fnd_Api.G_MISS_NUM ))
1648: THEN
1649: Get_item_group_id(p_item_group_name => p_x_req_material_tbl(i).item_group_name,
1643: --Get item_group_id from item_group_name only if mc or position is given
1644: IF ((p_x_req_material_tbl(i).item_group_name IS NOT NULL AND
1645: p_x_req_material_tbl(i).item_group_name <> Fnd_Api.G_MISS_CHAR ) OR
1646: (p_x_req_material_tbl(i).item_group_id IS NOT NULL AND
1647: p_x_req_material_tbl(i).item_group_id <> Fnd_Api.G_MISS_NUM ))
1648: THEN
1649: Get_item_group_id(p_item_group_name => p_x_req_material_tbl(i).item_group_name,
1650: p_item_group_id => p_x_req_material_tbl(i).item_group_id,
1651: x_item_group_id => l_item_group_id,
1654: IF NVL(l_return_status,'x') <> 'S'
1655: THEN
1656: Fnd_Message.SET_NAME('AHL',l_msg_data);
1657: IF ( p_x_req_material_tbl(i).item_group_name IS NULL OR
1658: p_x_req_material_tbl(i).item_group_name = FND_API.G_MISS_CHAR ) THEN
1659: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).item_group_id );
1660: ELSE
1661: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).item_group_name );
1662: END IF;
1667: END IF;
1668:
1669: -- Convert concatenated segments to Item ID
1670: IF (p_x_req_material_tbl(i).concatenated_segments IS NOT NULL AND
1671: p_x_req_material_tbl(i).concatenated_segments <> Fnd_Api.G_MISS_CHAR ) OR
1672: (p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND
1673: p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM) THEN
1674:
1675: IF(l_relationship_tbl(i) IS NOT NULL) THEN
1669: -- Convert concatenated segments to Item ID
1670: IF (p_x_req_material_tbl(i).concatenated_segments IS NOT NULL AND
1671: p_x_req_material_tbl(i).concatenated_segments <> Fnd_Api.G_MISS_CHAR ) OR
1672: (p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND
1673: p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM) THEN
1674:
1675: IF(l_relationship_tbl(i) IS NOT NULL) THEN
1676: --Get Item for position based material requirements i.e. against relationship_id
1677: Get_item_Id_from_mc_pos
1684: x_msg_data => l_msg_data
1685: );
1686:
1687: ELSIF (p_x_req_material_tbl(i).item_group_id IS NOT NULL AND
1688: p_x_req_material_tbl(i).item_group_id <> Fnd_Api.G_MISS_NUM ) THEN
1689: --if item group is present then validate item against item group
1690: Get_item_Id_from_item_group
1691: (p_item_group_id => p_x_req_material_tbl(i).item_group_id,
1692: p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
1698: IF NVL(l_return_status,'x') <> 'S'
1699: THEN
1700: Fnd_Message.SET_NAME('AHL',l_msg_data);
1701: IF ( p_x_req_material_tbl(i).concatenated_segments IS NULL OR
1702: p_x_req_material_tbl(i).concatenated_segments = FND_API.G_MISS_CHAR ) THEN
1703: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).inventory_item_id );
1704: ELSE
1705: FND_MESSAGE.set_token( 'FIELD', p_x_req_material_tbl(i).concatenated_segments );
1706: END IF;
1747: CLOSE Get_Wo_Uc_Header_Id;
1748: --Call Validate item procedure only for position based requirement.
1749: --If rt_oper_material_id is present, it means mat requirements is created when new operation is added to workorder.
1750: --in this case do not call sb rule validation as this is already done while deriving item for position based mat req.
1751: IF (p_x_req_material_tbl(i).mc_header_id IS NOT NULL AND p_x_req_material_tbl(i).mc_header_id <> Fnd_Api.G_MISS_NUM AND
1752: p_x_req_material_tbl(i).mc_position_key IS NOT NULL AND p_x_req_material_tbl(i).mc_position_key <> Fnd_Api.G_MISS_NUM AND
1753: p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM AND
1754: (p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID IS NULL OR p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID = Fnd_Api.G_MISS_NUM) AND
1755: l_uc_header_id IS NOT NULL
1748: --Call Validate item procedure only for position based requirement.
1749: --If rt_oper_material_id is present, it means mat requirements is created when new operation is added to workorder.
1750: --in this case do not call sb rule validation as this is already done while deriving item for position based mat req.
1751: IF (p_x_req_material_tbl(i).mc_header_id IS NOT NULL AND p_x_req_material_tbl(i).mc_header_id <> Fnd_Api.G_MISS_NUM AND
1752: p_x_req_material_tbl(i).mc_position_key IS NOT NULL AND p_x_req_material_tbl(i).mc_position_key <> Fnd_Api.G_MISS_NUM AND
1753: p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM AND
1754: (p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID IS NULL OR p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID = Fnd_Api.G_MISS_NUM) AND
1755: l_uc_header_id IS NOT NULL
1756: ) THEN
1749: --If rt_oper_material_id is present, it means mat requirements is created when new operation is added to workorder.
1750: --in this case do not call sb rule validation as this is already done while deriving item for position based mat req.
1751: IF (p_x_req_material_tbl(i).mc_header_id IS NOT NULL AND p_x_req_material_tbl(i).mc_header_id <> Fnd_Api.G_MISS_NUM AND
1752: p_x_req_material_tbl(i).mc_position_key IS NOT NULL AND p_x_req_material_tbl(i).mc_position_key <> Fnd_Api.G_MISS_NUM AND
1753: p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM AND
1754: (p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID IS NULL OR p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID = Fnd_Api.G_MISS_NUM) AND
1755: l_uc_header_id IS NOT NULL
1756: ) THEN
1757: --Following procedure valid position based requirement against SB rules and
1750: --in this case do not call sb rule validation as this is already done while deriving item for position based mat req.
1751: IF (p_x_req_material_tbl(i).mc_header_id IS NOT NULL AND p_x_req_material_tbl(i).mc_header_id <> Fnd_Api.G_MISS_NUM AND
1752: p_x_req_material_tbl(i).mc_position_key IS NOT NULL AND p_x_req_material_tbl(i).mc_position_key <> Fnd_Api.G_MISS_NUM AND
1753: p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM AND
1754: (p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID IS NULL OR p_x_req_material_tbl(i).RT_OPER_MATERIAL_ID = Fnd_Api.G_MISS_NUM) AND
1755: l_uc_header_id IS NOT NULL
1756: ) THEN
1757: --Following procedure valid position based requirement against SB rules and
1758: --insert error messages in Fnd_Msg_Pub... hence increase message count in case of error.
1769: AHL_DEBUG_PUB.debug( 'IVID'||p_x_req_material_tbl(i).requested_quantity);
1770: END IF;
1771: -- Validate for Requested Quantity
1772: IF (p_x_req_material_tbl(i).requested_quantity IS NULL OR
1773: p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM ) THEN
1774: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_REQUIRED');
1775: Fnd_Msg_Pub.ADD;
1776: ELSIF (p_x_req_material_tbl(i).requested_quantity IS NOT NULL AND
1777: p_x_req_material_tbl(i).requested_quantity <> FND_API.G_MISS_NUM) THEN
1773: p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM ) THEN
1774: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_REQUIRED');
1775: Fnd_Msg_Pub.ADD;
1776: ELSIF (p_x_req_material_tbl(i).requested_quantity IS NOT NULL AND
1777: p_x_req_material_tbl(i).requested_quantity <> FND_API.G_MISS_NUM) THEN
1778: -- Fix for FP bug# 6642084. -- Allow 0 quantity.
1779: IF p_x_req_material_tbl(i).requested_quantity < 0 THEN
1780: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_INVALID');
1781: Fnd_Msg_Pub.ADD;
1788: AHL_DEBUG_PUB.debug('Scheduled Quantity: ' || p_x_req_material_tbl(i).scheduled_quantity);
1789: END IF;
1790:
1791: IF ( p_x_req_material_tbl(i).scheduled_quantity IS NOT NULL AND
1792: p_x_req_material_tbl(i).scheduled_quantity <> FND_API.G_MISS_NUM ) THEN
1793:
1794: IF ( p_x_req_material_tbl(i).scheduled_quantity < 0 ) THEN
1795: FND_MESSAGE.set_name('AHL', 'AHL_PP_SCHLD_QUANTITY_INVLD');
1796: FND_MSG_PUB.add;
1806: -- following validation should not occur for VWP flows
1807: IF ((p_interface_flag = 'Y' OR p_interface_flag is null ) AND (p_module_type <> 'VWP'))THEN
1808: -- Validate for Requested Date
1809: IF (p_x_req_material_tbl(i).requested_date IS NULL OR
1810: p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE ) THEN
1811: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_REQUIRED');
1812: Fnd_Msg_Pub.ADD;
1813: ELSIF (p_x_req_material_tbl(i).requested_date IS NOT NULL AND
1814: p_x_req_material_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
1810: p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE ) THEN
1811: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_REQUIRED');
1812: Fnd_Msg_Pub.ADD;
1813: ELSIF (p_x_req_material_tbl(i).requested_date IS NOT NULL AND
1814: p_x_req_material_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
1815: IF p_x_req_material_tbl(i).requested_date < trunc(SYSDATE) THEN
1816: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_GT_SYSD');
1817: Fnd_Msg_Pub.ADD;
1818: END IF;
1824: AHL_DEBUG_PUB.debug( 'OSID'||p_x_req_material_tbl(i).operation_sequence);
1825: END IF;
1826: --Check for operation sequence
1827: IF( p_x_req_material_tbl(i).operation_sequence IS NULL OR
1828: p_x_req_material_tbl(i).operation_sequence = FND_API.G_MISS_NUM)
1829: THEN
1830: IF (p_x_req_material_tbl(i).workorder_operation_id IS NOT NULL AND
1831: p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM)
1832: THEN
1827: IF( p_x_req_material_tbl(i).operation_sequence IS NULL OR
1828: p_x_req_material_tbl(i).operation_sequence = FND_API.G_MISS_NUM)
1829: THEN
1830: IF (p_x_req_material_tbl(i).workorder_operation_id IS NOT NULL AND
1831: p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM)
1832: THEN
1833: --
1834: OPEN Get_Operation_Seq_cur(p_x_req_material_tbl(i).workorder_operation_id);
1835: FETCH Get_Operation_Seq_cur INTO p_x_req_material_tbl(i).operation_sequence;
1849: AHL_DEBUG_PUB.debug('OPID'||p_x_req_material_tbl(i).workorder_operation_id);
1850: END IF;
1851: --
1852: IF (p_x_req_material_tbl(i).workorder_operation_id IS NULL OR
1853: p_x_req_material_tbl(i).workorder_operation_id = FND_API.G_MISS_NUM)
1854: THEN
1855: -- Validate for workorder operation
1856: IF (p_x_req_material_tbl(i).operation_sequence IS NOT NULL AND
1857: p_x_req_material_tbl(i).operation_sequence <> FND_API.G_MISS_NUM) THEN
1853: p_x_req_material_tbl(i).workorder_operation_id = FND_API.G_MISS_NUM)
1854: THEN
1855: -- Validate for workorder operation
1856: IF (p_x_req_material_tbl(i).operation_sequence IS NOT NULL AND
1857: p_x_req_material_tbl(i).operation_sequence <> FND_API.G_MISS_NUM) THEN
1858: --
1859: Get_workorder_operation_Id
1860: (p_workorder_id => p_x_req_material_tbl(i).workorder_id,
1861: p_operation_sequence => p_x_req_material_tbl(i).operation_sequence,
1870: END IF;
1871: END IF;
1872: END IF;
1873: -- Assigns operation id when called from UI
1874: IF (p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM OR
1875: p_x_req_material_tbl(i).workorder_operation_id IS NULL )
1876: THEN
1877: --
1878: p_x_req_material_tbl(i).workorder_operation_id := NVL(p_x_req_material_tbl(i).workorder_operation_id,l_workorder_operation_id);
1895: -- Check added by balaji for bug # 4093650
1896: -- When workorder_operation_id is null or g_miss then date check should not
1897: -- be performed.
1898: p_x_req_material_tbl(i).workorder_operation_id IS NOT NULL AND
1899: p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM
1900: )
1901: )
1902: THEN
1903: --Check for requested date should be in schedule start date schedule end date
1942: --dbms_output.put_line( 'before uom conversion:'||p_x_req_material_tbl(i).inventory_item_id);
1943: -- dbms_output.put_line( 'before uom conversion:'||p_x_req_material_tbl(i).organization_id);
1944: -- dbms_output.put_line( 'before uom WO:'||p_x_req_material_tbl(i).workorder_id);
1945: -- Convert Uom code
1946: IF (p_x_req_material_tbl(i).UOM_MEANING IS NOT NULL AND p_x_req_material_tbl(i).UOM_MEANING <> FND_API.G_MISS_CHAR)
1947: THEN
1948: --
1949: OPEN Uom_cur(p_x_req_material_tbl(i).UOM_MEANING);
1950: FETCH Uom_cur INTO p_x_req_material_tbl(i).UOM_CODE;
1969: -- Standard call to get message count and if count is get message info.
1970: l_msg_count := FND_MSG_PUB.count_msg;
1971: IF l_msg_count > 0 THEN
1972: X_msg_count := l_msg_count;
1973: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1974: RAISE FND_API.G_EXC_ERROR;
1975: END IF;
1976: END LOOP;
1977: END IF;
1970: l_msg_count := FND_MSG_PUB.count_msg;
1971: IF l_msg_count > 0 THEN
1972: X_msg_count := l_msg_count;
1973: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1974: RAISE FND_API.G_EXC_ERROR;
1975: END IF;
1976: END LOOP;
1977: END IF;
1978: -- dbms_output.put_line( 'before wip jobs:'||p_x_req_material_tbl.COUNT);
2075: --
2076: IF l_msg_count > 0 THEN
2077: X_msg_count := l_msg_count;
2078: X_msg_data := l_msg_data;
2079: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2080: RAISE FND_API.G_EXC_ERROR;
2081: END IF;
2082: END IF;
2083: --
2076: IF l_msg_count > 0 THEN
2077: X_msg_count := l_msg_count;
2078: X_msg_data := l_msg_data;
2079: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2080: RAISE FND_API.G_EXC_ERROR;
2081: END IF;
2082: END IF;
2083: --
2084: IF G_DEBUG='Y' THEN
2095: --
2096: FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
2097: LOOP
2098: --
2099: IF (p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM OR
2100: p_x_req_material_tbl(i).schedule_material_id IS NULL )
2101: THEN
2102: -- Thease conditions are required for optional fields
2103: IF p_x_req_material_tbl(i).visit_start_date = FND_API.G_MISS_DATE
2099: IF (p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM OR
2100: p_x_req_material_tbl(i).schedule_material_id IS NULL )
2101: THEN
2102: -- Thease conditions are required for optional fields
2103: IF p_x_req_material_tbl(i).visit_start_date = FND_API.G_MISS_DATE
2104: THEN
2105: l_Req_Material_Tbl(i).visit_start_date := NULL;
2106: ELSE
2107: l_Req_Material_Tbl(i).visit_start_date := p_x_req_material_tbl(i).visit_start_date;
2106: ELSE
2107: l_Req_Material_Tbl(i).visit_start_date := p_x_req_material_tbl(i).visit_start_date;
2108: END IF;
2109: -- Scheduled Date
2110: IF p_x_req_material_tbl(i).scheduled_date = FND_API.G_MISS_DATE
2111: THEN
2112: l_Req_Material_Tbl(i).scheduled_date := NULL;
2113: ELSE
2114: l_Req_Material_Tbl(i).scheduled_date := p_x_req_material_tbl(i).scheduled_date;
2113: ELSE
2114: l_Req_Material_Tbl(i).scheduled_date := p_x_req_material_tbl(i).scheduled_date;
2115: END IF;
2116: -- Request ID
2117: IF p_x_req_material_tbl(i).request_id = FND_API.G_MISS_NUM
2118: THEN
2119: l_Req_Material_Tbl(i).request_id := NULL;
2120: ELSE
2121: l_Req_Material_Tbl(i).request_id := p_x_req_material_tbl(i).request_id;
2120: ELSE
2121: l_Req_Material_Tbl(i).request_id := p_x_req_material_tbl(i).request_id;
2122: END IF;
2123: --Scheduled quantity
2124: IF p_x_req_material_tbl(i).scheduled_quantity = FND_API.G_MISS_NUM
2125: THEN
2126: l_Req_Material_Tbl(i).scheduled_quantity := NULL;
2127: ELSE
2128: l_Req_Material_Tbl(i).scheduled_quantity := p_x_req_material_tbl(i).scheduled_quantity;
2127: ELSE
2128: l_Req_Material_Tbl(i).scheduled_quantity := p_x_req_material_tbl(i).scheduled_quantity;
2129: END IF;
2130: -- Operation Sequence
2131: IF p_x_req_material_tbl(i).operation_sequence = FND_API.G_MISS_NUM
2132: THEN
2133: l_Req_Material_Tbl(i).operation_sequence := NULL;
2134: ELSE
2135: l_Req_Material_Tbl(i).operation_sequence := p_x_req_material_tbl(i).operation_sequence;
2134: ELSE
2135: l_Req_Material_Tbl(i).operation_sequence := p_x_req_material_tbl(i).operation_sequence;
2136: END IF;
2137: -- UOM
2138: IF p_x_req_material_tbl(i).uom_code = FND_API.G_MISS_CHAR
2139: THEN
2140: l_Req_Material_Tbl(i).uom_code := NULL;
2141: ELSE
2142: l_Req_Material_Tbl(i).uom_code := p_x_req_material_tbl(i).uom_code;
2141: ELSE
2142: l_Req_Material_Tbl(i).uom_code := p_x_req_material_tbl(i).uom_code;
2143: END IF;
2144: -- Status
2145: IF p_x_req_material_tbl(i).status = FND_API.G_MISS_CHAR
2146: THEN
2147: l_Req_Material_Tbl(i).status := NULL;
2148: ELSE
2149: l_Req_Material_Tbl(i).status := p_x_req_material_tbl(i).status;
2148: ELSE
2149: l_Req_Material_Tbl(i).status := p_x_req_material_tbl(i).status;
2150: END IF;
2151: -- Operation code
2152: IF p_x_req_material_tbl(i).operation_code = FND_API.G_MISS_CHAR
2153: THEN
2154: l_Req_Material_Tbl(i).operation_code := NULL;
2155: ELSE
2156: l_Req_Material_Tbl(i).operation_code := p_x_req_material_tbl(i).operation_code;
2155: ELSE
2156: l_Req_Material_Tbl(i).operation_code := p_x_req_material_tbl(i).operation_code;
2157: END IF;
2158: -- Transaction ID
2159: IF p_x_req_material_tbl(i).transaction_id = FND_API.G_MISS_NUM
2160: THEN
2161: l_Req_Material_Tbl(i).transaction_id := NULL;
2162: ELSE
2163: l_Req_Material_Tbl(i).transaction_id := p_x_req_material_tbl(i).transaction_id;
2162: ELSE
2163: l_Req_Material_Tbl(i).transaction_id := p_x_req_material_tbl(i).transaction_id;
2164: END IF;
2165: -- Rt Oper Material ID
2166: IF p_x_req_material_tbl(i).rt_oper_material_id = FND_API.G_MISS_NUM
2167: THEN
2168: l_Req_Material_Tbl(i).rt_oper_material_id := NULL;
2169: ELSE
2170: l_Req_Material_Tbl(i).rt_oper_material_id := p_x_req_material_tbl(i).rt_oper_material_id;
2169: ELSE
2170: l_Req_Material_Tbl(i).rt_oper_material_id := p_x_req_material_tbl(i).rt_oper_material_id;
2171: END IF;
2172: -- Program ID
2173: IF p_x_req_material_tbl(i).program_id = FND_API.G_MISS_NUM
2174: THEN
2175: l_Req_Material_Tbl(i).program_id := NULL;
2176: ELSE
2177: l_Req_Material_Tbl(i).program_id := p_x_req_material_tbl(i).program_id;
2176: ELSE
2177: l_Req_Material_Tbl(i).program_id := p_x_req_material_tbl(i).program_id;
2178: END IF;
2179: -- Item group ID
2180: IF p_x_req_material_tbl(i).item_group_id = FND_API.G_MISS_NUM
2181: THEN
2182: l_Req_Material_Tbl(i).item_group_id := NULL;
2183: ELSE
2184: l_Req_Material_Tbl(i).item_group_id := p_x_req_material_tbl(i).item_group_id;
2183: ELSE
2184: l_Req_Material_Tbl(i).item_group_id := p_x_req_material_tbl(i).item_group_id;
2185: END IF;
2186: -- Program Update Date
2187: IF p_x_req_material_tbl(i).program_update_date = FND_API.G_MISS_DATE
2188: THEN
2189: l_Req_Material_Tbl(i).program_update_date := NULL;
2190: ELSE
2191: l_Req_Material_Tbl(i).program_update_date := p_x_req_material_tbl(i).program_update_date;
2190: ELSE
2191: l_Req_Material_Tbl(i).program_update_date := p_x_req_material_tbl(i).program_update_date;
2192: END IF;
2193: -- Last Updated Date
2194: IF p_x_req_material_tbl(i).last_updated_date = FND_API.G_MISS_DATE
2195: THEN
2196: l_Req_Material_Tbl(i).last_updated_date := NULL;
2197: ELSE
2198: l_Req_Material_Tbl(i).last_updated_date := p_x_req_material_tbl(i).last_updated_date;
2198: l_Req_Material_Tbl(i).last_updated_date := p_x_req_material_tbl(i).last_updated_date;
2199: END IF;
2200: --sukhwsin: SB Effectivity Code chanages - starts
2201: --mc_header_id
2202: IF p_x_req_material_tbl(i).mc_header_id = FND_API.G_MISS_NUM
2203: THEN
2204: l_Req_Material_Tbl(i).mc_header_id := NULL;
2205: ELSE
2206: l_Req_Material_Tbl(i).mc_header_id := p_x_req_material_tbl(i).mc_header_id;
2205: ELSE
2206: l_Req_Material_Tbl(i).mc_header_id := p_x_req_material_tbl(i).mc_header_id;
2207: END IF;
2208: --mc_name
2209: IF p_x_req_material_tbl(i).mc_name = FND_API.G_MISS_CHAR
2210: THEN
2211: l_Req_Material_Tbl(i).mc_name := NULL;
2212: ELSE
2213: l_Req_Material_Tbl(i).mc_name := p_x_req_material_tbl(i).mc_name;
2212: ELSE
2213: l_Req_Material_Tbl(i).mc_name := p_x_req_material_tbl(i).mc_name;
2214: END IF;
2215: --mc_position_key
2216: IF p_x_req_material_tbl(i).mc_position_key = FND_API.G_MISS_NUM
2217: THEN
2218: l_Req_Material_Tbl(i).mc_position_key := NULL;
2219: ELSE
2220: l_Req_Material_Tbl(i).mc_position_key := p_x_req_material_tbl(i).mc_position_key;
2219: ELSE
2220: l_Req_Material_Tbl(i).mc_position_key := p_x_req_material_tbl(i).mc_position_key;
2221: END IF;
2222: --mc_position
2223: IF p_x_req_material_tbl(i).mc_position = FND_API.G_MISS_CHAR
2224: THEN
2225: l_Req_Material_Tbl(i).mc_position := NULL;
2226: ELSE
2227: l_Req_Material_Tbl(i).mc_position := p_x_req_material_tbl(i).mc_position;
2238: IF G_DEBUG='Y' THEN
2239: AHL_DEBUG_PUB.debug('dff_default_values have been fetched');
2240: END IF;
2241: -- Attribte Category
2242: IF p_x_req_material_tbl(i).attribute_category = FND_API.G_MISS_CHAR
2243: THEN
2244: l_Req_Material_Tbl(i).attribute_category := NULL;
2245: ELSIF p_x_req_material_tbl(i).attribute_category IS NULL THEN
2246: l_Req_Material_Tbl(i).attribute_category := dff_default_values.attribute_category;
2247: ELSE
2248: l_Req_Material_Tbl(i).attribute_category := p_x_req_material_tbl(i).attribute_category;
2249: END IF;
2250: -- Attribte1
2251: IF p_x_req_material_tbl(i).attribute1 = FND_API.G_MISS_CHAR
2252: THEN
2253: l_Req_Material_Tbl(i).attribute1 := NULL;
2254: ELSIF p_x_req_material_tbl(i).attribute1 IS NULL THEN
2255: l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute1;
2256: ELSE
2257: l_Req_Material_Tbl(i).attribute1 := p_x_req_material_tbl(i).attribute1;
2258: END IF;
2259: -- Attribte2
2260: IF p_x_req_material_tbl(i).attribute2 = FND_API.G_MISS_CHAR
2261: THEN
2262: l_Req_Material_Tbl(i).attribute2 := NULL;
2263: ELSIF p_x_req_material_tbl(i).attribute2 IS NULL THEN
2264: l_Req_Material_Tbl(i).attribute2 := dff_default_values.attribute2;
2265: ELSE
2266: l_Req_Material_Tbl(i).attribute2 := p_x_req_material_tbl(i).attribute2;
2267: END IF;
2268: -- Attribte3
2269: IF p_x_req_material_tbl(i).attribute3 = FND_API.G_MISS_CHAR
2270: THEN
2271: l_Req_Material_Tbl(i).attribute3 := NULL;
2272: ELSIF p_x_req_material_tbl(i).attribute3 IS NULL THEN
2273: l_Req_Material_Tbl(i).attribute3 := dff_default_values.attribute3;
2274: ELSE
2275: l_Req_Material_Tbl(i).attribute3 := p_x_req_material_tbl(i).attribute3;
2276: END IF;
2277: -- Attribte4
2278: IF p_x_req_material_tbl(i).attribute4 = FND_API.G_MISS_CHAR
2279: THEN
2280: l_Req_Material_Tbl(i).attribute4 := NULL;
2281: ELSIF p_x_req_material_tbl(i).attribute4 IS NULL THEN
2282: l_Req_Material_Tbl(i).attribute4 := dff_default_values.attribute4;
2283: ELSE
2284: l_Req_Material_Tbl(i).attribute4 := p_x_req_material_tbl(i).attribute4;
2285: END IF;
2286: -- Attribte5
2287: IF p_x_req_material_tbl(i).attribute5 = FND_API.G_MISS_CHAR
2288: THEN
2289: l_Req_Material_Tbl(i).attribute5 := NULL;
2290: ELSIF p_x_req_material_tbl(i).attribute5 IS NULL THEN
2291: l_Req_Material_Tbl(i).attribute5 := dff_default_values.attribute5;
2292: ELSE
2293: l_Req_Material_Tbl(i).attribute5 := p_x_req_material_tbl(i).attribute5;
2294: END IF;
2295: -- Attribte6
2296: IF p_x_req_material_tbl(i).attribute6 = FND_API.G_MISS_CHAR
2297: THEN
2298: l_Req_Material_Tbl(i).attribute6 := NULL;
2299: ELSIF p_x_req_material_tbl(i).attribute6 IS NULL THEN
2300: l_Req_Material_Tbl(i).attribute6 := dff_default_values.attribute6;
2301: ELSE
2302: l_Req_Material_Tbl(i).attribute6 := p_x_req_material_tbl(i).attribute6;
2303: END IF;
2304: -- Attribte7
2305: IF p_x_req_material_tbl(i).attribute7 = FND_API.G_MISS_CHAR
2306: THEN
2307: l_Req_Material_Tbl(i).attribute7 := NULL;
2308: ELSIF p_x_req_material_tbl(i).attribute7 IS NULL THEN
2309: l_Req_Material_Tbl(i).attribute7 := dff_default_values.attribute7;
2310: ELSE
2311: l_Req_Material_Tbl(i).attribute7 := p_x_req_material_tbl(i).attribute7;
2312: END IF;
2313: -- Attribte8
2314: IF p_x_req_material_tbl(i).attribute8 = FND_API.G_MISS_CHAR
2315: THEN
2316: l_Req_Material_Tbl(i).attribute8 := NULL;
2317: ELSIF p_x_req_material_tbl(i).attribute8 IS NULL THEN
2318: l_Req_Material_Tbl(i).attribute8 := dff_default_values.attribute8;
2319: ELSE
2320: l_Req_Material_Tbl(i).attribute8 := p_x_req_material_tbl(i).attribute8;
2321: END IF;
2322: -- Attribte9
2323: IF p_x_req_material_tbl(i).attribute9 = FND_API.G_MISS_CHAR
2324: THEN
2325: l_Req_Material_Tbl(i).attribute9 := NULL;
2326: ELSIF p_x_req_material_tbl(i).attribute9 IS NULL THEN
2327: l_Req_Material_Tbl(i).attribute9 := dff_default_values.attribute9;
2328: ELSE
2329: l_Req_Material_Tbl(i).attribute9 := p_x_req_material_tbl(i).attribute9;
2330: END IF;
2331: -- Attribte10
2332: IF p_x_req_material_tbl(i).attribute10 = FND_API.G_MISS_CHAR
2333: THEN
2334: l_Req_Material_Tbl(i).attribute10 := NULL;
2335: ELSIF p_x_req_material_tbl(i).attribute10 IS NULL THEN
2336: l_Req_Material_Tbl(i).attribute10 := dff_default_values.attribute10;
2337: ELSE
2338: l_Req_Material_Tbl(i).attribute10 := p_x_req_material_tbl(i).attribute10;
2339: END IF;
2340: -- Attribte11
2341: IF p_x_req_material_tbl(i).attribute11 = FND_API.G_MISS_CHAR
2342: THEN
2343: l_Req_Material_Tbl(i).attribute11 := NULL;
2344: ELSIF p_x_req_material_tbl(i).attribute11 IS NULL THEN
2345: l_Req_Material_Tbl(i).attribute11 := dff_default_values.attribute11;
2346: ELSE
2347: l_Req_Material_Tbl(i).attribute11 := p_x_req_material_tbl(i).attribute11;
2348: END IF;
2349: -- Attribte12
2350: IF p_x_req_material_tbl(i).attribute12 = FND_API.G_MISS_CHAR
2351: THEN
2352: l_Req_Material_Tbl(i).attribute12 := NULL;
2353: ELSIF p_x_req_material_tbl(i).attribute12 IS NULL THEN
2354: l_Req_Material_Tbl(i).attribute12 := dff_default_values.attribute12;
2355: ELSE
2356: l_Req_Material_Tbl(i).attribute12 := p_x_req_material_tbl(i).attribute12;
2357: END IF;
2358: -- Attribte13
2359: IF p_x_req_material_tbl(i).attribute13 = FND_API.G_MISS_CHAR
2360: THEN
2361: l_Req_Material_Tbl(i).attribute13 := NULL;
2362: ELSIF p_x_req_material_tbl(i).attribute13 IS NULL THEN
2363: l_Req_Material_Tbl(i).attribute13 := dff_default_values.attribute13;
2364: ELSE
2365: l_Req_Material_Tbl(i).attribute13 := p_x_req_material_tbl(i).attribute13;
2366: END IF;
2367: -- Attribte14
2368: IF p_x_req_material_tbl(i).attribute14 = FND_API.G_MISS_CHAR
2369: THEN
2370: l_Req_Material_Tbl(i).attribute14 := NULL;
2371: ELSIF p_x_req_material_tbl(i).attribute14 IS NULL THEN
2372: l_Req_Material_Tbl(i).attribute14 := dff_default_values.attribute14;
2373: ELSE
2374: l_Req_Material_Tbl(i).attribute14 := p_x_req_material_tbl(i).attribute14;
2375: END IF;
2376: -- Attribte15
2377: IF p_x_req_material_tbl(i).attribute15 = FND_API.G_MISS_CHAR
2378: THEN
2379: l_Req_Material_Tbl(i).attribute15 := NULL;
2380: ELSIF p_x_req_material_tbl(i).attribute15 IS NULL THEN
2381: l_Req_Material_Tbl(i).attribute15 := dff_default_values.attribute15;
2384: END IF;
2385: --
2386: -- debadey: Marshalling FDD section 5.15 (update aog flag in workorder), 2/11/11
2387: -- AOG Flag
2388: IF p_x_req_material_tbl(i).aog_flag = FND_API.G_MISS_CHAR
2389: THEN
2390: l_Req_Material_Tbl(i).aog_flag := NULL;
2391: ELSE
2392: l_Req_Material_Tbl(i).aog_flag := p_x_req_material_tbl(i).aog_flag;
2410: AHL_DEBUG_PUB.debug( 'FLAG=N VTID:'||p_x_req_material_tbl(i).visit_task_id);
2411: AHL_DEBUG_PUB.debug( 'FLAG=N ITID:'||p_x_req_material_tbl(i).inventory_item_id);
2412: END IF;
2413: IF (p_x_req_material_tbl(i).rt_oper_material_id IS NOT NULL AND
2414: p_x_req_material_tbl(i).rt_oper_material_id <> FND_API.G_MISS_NUM)
2415: THEN
2416: --
2417: OPEN Get_rt_mat_cur (p_x_req_material_tbl(i).visit_task_id,
2418: p_x_req_material_tbl(i).rt_oper_material_id,
2703: X_return_status := 'S';
2704: x_job_return_status := 'S';
2705: ELSE
2706: x_job_return_status := 'E';
2707: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2708: RAISE Fnd_Api.G_EXC_ERROR;
2709: END IF; --status condition
2710: --Call notification API
2711: --Send Material Notification
2704: x_job_return_status := 'S';
2705: ELSE
2706: x_job_return_status := 'E';
2707: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2708: RAISE Fnd_Api.G_EXC_ERROR;
2709: END IF; --status condition
2710: --Call notification API
2711: --Send Material Notification
2712: IF X_return_status = 'S' THEN
2767: --Standard check to count messages
2768: l_msg_count := Fnd_Msg_Pub.count_msg;
2769: IF l_msg_count > 0 THEN
2770: X_msg_count := l_msg_count;
2771: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2772: RAISE Fnd_Api.G_EXC_ERROR;
2773: END IF;
2774: --Standard check for commit
2775: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2768: l_msg_count := Fnd_Msg_Pub.count_msg;
2769: IF l_msg_count > 0 THEN
2770: X_msg_count := l_msg_count;
2771: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2772: RAISE Fnd_Api.G_EXC_ERROR;
2773: END IF;
2774: --Standard check for commit
2775: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2776: COMMIT;
2771: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2772: RAISE Fnd_Api.G_EXC_ERROR;
2773: END IF;
2774: --Standard check for commit
2775: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2776: COMMIT;
2777: END IF;
2778: -- Debug info
2779: IF G_DEBUG='Y' THEN
2781: -- Check if API is called in debug mode. If yes, disable debug.
2782: Ahl_Debug_Pub.disable_debug;
2783: END IF;
2784: EXCEPTION
2785: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2786: ROLLBACK TO create_material_reqst;
2787: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2788: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2789: p_count => x_msg_count,
2783: END IF;
2784: EXCEPTION
2785: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2786: ROLLBACK TO create_material_reqst;
2787: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2788: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2789: p_count => x_msg_count,
2790: p_data => x_msg_data);
2791: IF G_DEBUG='Y' THEN
2784: EXCEPTION
2785: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2786: ROLLBACK TO create_material_reqst;
2787: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2788: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2789: p_count => x_msg_count,
2790: p_data => x_msg_data);
2791: IF G_DEBUG='Y' THEN
2792: AHL_DEBUG_PUB.log_app_messages (
2794: AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Create Material Reqst','+PPMRP+');
2795: -- Check if API is called in debug mode. If yes, disable debug.
2796: AHL_DEBUG_PUB.disable_debug;
2797: END IF;
2798: WHEN FND_API.G_EXC_ERROR THEN
2799: ROLLBACK TO create_material_reqst;
2800: X_return_status := FND_API.G_RET_STS_ERROR;
2801: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2802: p_count => x_msg_count,
2796: AHL_DEBUG_PUB.disable_debug;
2797: END IF;
2798: WHEN FND_API.G_EXC_ERROR THEN
2799: ROLLBACK TO create_material_reqst;
2800: X_return_status := FND_API.G_RET_STS_ERROR;
2801: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2802: p_count => x_msg_count,
2803: p_data => X_msg_data);
2804: IF G_DEBUG='Y' THEN
2797: END IF;
2798: WHEN FND_API.G_EXC_ERROR THEN
2799: ROLLBACK TO create_material_reqst;
2800: X_return_status := FND_API.G_RET_STS_ERROR;
2801: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2802: p_count => x_msg_count,
2803: p_data => X_msg_data);
2804: IF G_DEBUG='Y' THEN
2805: -- Debug info.
2810: AHL_DEBUG_PUB.disable_debug;
2811: END IF;
2812: WHEN OTHERS THEN
2813: ROLLBACK TO create_material_reqst;
2814: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2815: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2816: THEN
2817: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
2818: p_procedure_name => 'CREATE_MATERIAL_REQST',
2817: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
2818: p_procedure_name => 'CREATE_MATERIAL_REQST',
2819: p_error_text => SUBSTR(SQLERRM,1,240));
2820: END IF;
2821: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2822: p_count => x_msg_count,
2823: p_data => X_msg_data);
2824: IF G_DEBUG='Y' THEN
2825: -- Debug info.
2840: -- Parameters :
2841: --
2842: -- Standard IN Parameters :
2843: -- p_api_version IN NUMBER Required
2844: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2845: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2846: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2847: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2848: -- p_module_type IN VARCHAR2 Default NULL.
2841: --
2842: -- Standard IN Parameters :
2843: -- p_api_version IN NUMBER Required
2844: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2845: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2846: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2847: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2848: -- p_module_type IN VARCHAR2 Default NULL.
2849: --
2842: -- Standard IN Parameters :
2843: -- p_api_version IN NUMBER Required
2844: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2845: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2846: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2847: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2848: -- p_module_type IN VARCHAR2 Default NULL.
2849: --
2850: -- Standard OUT Parameters :
2843: -- p_api_version IN NUMBER Required
2844: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2845: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2846: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2847: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2848: -- p_module_type IN VARCHAR2 Default NULL.
2849: --
2850: -- Standard OUT Parameters :
2851: -- x_return_status OUT VARCHAR2 Required
2861: --
2862: -- End of Comments.
2863: PROCEDURE Update_Material_Reqst (
2864: p_api_version IN NUMBER,
2865: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2866: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2867: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2868: p_module_type IN VARCHAR2 := NULL,
2869: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2862: -- End of Comments.
2863: PROCEDURE Update_Material_Reqst (
2864: p_api_version IN NUMBER,
2865: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2866: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2867: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2868: p_module_type IN VARCHAR2 := NULL,
2869: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2870: x_return_status OUT NOCOPY VARCHAR2,
2863: PROCEDURE Update_Material_Reqst (
2864: p_api_version IN NUMBER,
2865: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2866: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2867: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2868: p_module_type IN VARCHAR2 := NULL,
2869: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2870: x_return_status OUT NOCOPY VARCHAR2,
2871: x_msg_count OUT NOCOPY NUMBER,
3018: -- Debug info.
3019: AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt. update material reqst','+PPMRP+');
3020: END IF;
3021: -- Standard call to check for call compatibility.
3022: IF FND_API.to_boolean(p_init_msg_list)
3023: THEN
3024: FND_MSG_PUB.initialize;
3025: END IF;
3026: -- Initialize API return status to success
3023: THEN
3024: FND_MSG_PUB.initialize;
3025: END IF;
3026: -- Initialize API return status to success
3027: x_return_status := FND_API.G_RET_STS_SUCCESS;
3028: -- Initialize message list if p_init_msg_list is set to TRUE.
3029: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3030: p_api_version,
3031: l_api_name,G_PKG_NAME)
3025: END IF;
3026: -- Initialize API return status to success
3027: x_return_status := FND_API.G_RET_STS_SUCCESS;
3028: -- Initialize message list if p_init_msg_list is set to TRUE.
3029: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3030: p_api_version,
3031: l_api_name,G_PKG_NAME)
3032: THEN
3033: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3029: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3030: p_api_version,
3031: l_api_name,G_PKG_NAME)
3032: THEN
3033: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3034: END IF;
3035: --------------------Start of API Body-----------------------------------
3036: IF p_x_req_material_tbl.COUNT > 0 THEN
3037: FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
3038: LOOP
3039: IF p_module_type <> 'API' THEN
3040: -- Value to ID Conversion
3041: IF ( ( p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
3042: p_x_req_material_tbl(i).workorder_id <> FND_API.G_MISS_NUM ) OR
3043: ( p_x_req_material_tbl(i).job_number IS NOT NULL AND
3044: p_x_req_material_tbl(i).job_number <> FND_API.G_MISS_CHAR ) )
3045: THEN
3046: --
3040: -- Value to ID Conversion
3041: IF ( ( p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
3042: p_x_req_material_tbl(i).workorder_id <> FND_API.G_MISS_NUM ) OR
3043: ( p_x_req_material_tbl(i).job_number IS NOT NULL AND
3044: p_x_req_material_tbl(i).job_number <> FND_API.G_MISS_CHAR ) )
3045: THEN
3046: --
3047: IF G_DEBUG='Y' THEN
3048: AHL_DEBUG_PUB.debug( 'WOID :'||p_x_req_material_tbl(i).workorder_id);
3072: p_workorder_id => p_x_req_material_tbl(i).workorder_id,
3073: p_ue_id => NULL,
3074: p_visit_id => NULL,
3075: p_item_instance_id => NULL);
3076: IF l_return_status = FND_API.G_TRUE THEN
3077: OPEN get_job_number(p_x_req_material_tbl(i).workorder_id);
3078: FETCH get_job_number INTO l_workorder_name;
3079: CLOSE get_job_number;
3080: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_UPD_MTL_UNTLCKD');
3079: CLOSE get_job_number;
3080: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_UPD_MTL_UNTLCKD');
3081: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
3082: FND_MSG_PUB.ADD;
3083: RAISE FND_API.G_EXC_ERROR;
3084: END IF;
3085: -- rroy
3086: -- ACL Changes
3087: --
3114: AHL_DEBUG_PUB.debug( 'INVID :'||p_x_req_material_tbl(i).inventory_item_id);
3115: END IF;
3116: -- Validate for Requested Quantity
3117: IF (p_x_req_material_tbl(i).requested_quantity IS NULL OR
3118: p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM) THEN
3119: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_REQUIRED');
3120: Fnd_Msg_Pub.ADD;
3121: ELSIF (p_x_req_material_tbl(i).requested_quantity IS NOT NULL AND
3122: p_x_req_material_tbl(i).requested_quantity <> FND_API.G_MISS_NUM) THEN
3118: p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM) THEN
3119: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_REQUIRED');
3120: Fnd_Msg_Pub.ADD;
3121: ELSIF (p_x_req_material_tbl(i).requested_quantity IS NOT NULL AND
3122: p_x_req_material_tbl(i).requested_quantity <> FND_API.G_MISS_NUM) THEN
3123: IF p_x_req_material_tbl(i).requested_quantity < 0 THEN
3124: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_INVALID');
3125: Fnd_Msg_Pub.ADD;
3126: END IF;
3150: END IF;
3151:
3152: -- MANESING::NR Analysis, 03-Jun-2011, Validate for Scheduled Quantity
3153: IF ( p_x_req_material_tbl(i).scheduled_quantity IS NOT NULL AND
3154: p_x_req_material_tbl(i).scheduled_quantity <> FND_API.G_MISS_NUM ) THEN
3155:
3156: IF ( p_x_req_material_tbl(i).scheduled_quantity < 0 ) THEN
3157: FND_MESSAGE.set_name('AHL', 'AHL_PP_SCHLD_QUANTITY_INVLD');
3158: FND_MSG_PUB.add;
3190:
3191: IF p_module_type NOT IN ('API', 'VWP') THEN --bug 14137417 :: added VWP by tchimira :: below validation should not occur for VWP flows
3192: -- Validate for Requested Date
3193: IF (p_x_req_material_tbl(i).requested_date IS NULL OR
3194: p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE) THEN
3195: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_REQUIRED');
3196: Fnd_Msg_Pub.ADD;
3197: ELSIF (p_x_req_material_tbl(i).requested_date IS NOT NULL AND
3198: P_X_REQ_MATERIAL_TBL(I).REQUESTED_DATE <> FND_API.G_MISS_DATE) THEN
3194: p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE) THEN
3195: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_REQUIRED');
3196: Fnd_Msg_Pub.ADD;
3197: ELSIF (p_x_req_material_tbl(i).requested_date IS NOT NULL AND
3198: P_X_REQ_MATERIAL_TBL(I).REQUESTED_DATE <> FND_API.G_MISS_DATE) THEN
3199:
3200: -- Included by sansatpa for bug - 14023722 - CERTAF: CANNOT UPDATE MATERIAL REQ WITH AOG FLAG WHEN REQ DATE < SYS DATE
3201: IF(( P_X_REQ_MATERIAL_TBL(I).AOG_FLAG = L_REQ_MATERIAL_REC.AOG_FLAG ) AND
3202: ( P_X_REQ_MATERIAL_TBL(I).REQUESTED_DATE < TRUNC(SYSDATE) ) AND
3210: AHL_DEBUG_PUB.debug( 'DATE :'||p_x_req_material_tbl(i).requested_date);
3211: END IF;
3212: -- Validate for Schedule Material ID
3213: IF (p_x_req_material_tbl(i).schedule_material_id IS NULL AND
3214: p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM) THEN
3215: Fnd_Message.SET_NAME('AHL','AHL_PP_SCH_MATRL_REQUIRED');
3216: Fnd_Msg_Pub.ADD;
3217: END IF;
3218: IF G_DEBUG='Y' THEN
3244: --Standard check to count messages
3245: l_msg_count := Fnd_Msg_Pub.count_msg;
3246: IF l_msg_count > 0 THEN
3247: X_msg_count := l_msg_count;
3248: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3249: RAISE Fnd_Api.G_EXC_ERROR;
3250: END IF;
3251: END LOOP; --for loop
3252: END IF;
3245: l_msg_count := Fnd_Msg_Pub.count_msg;
3246: IF l_msg_count > 0 THEN
3247: X_msg_count := l_msg_count;
3248: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3249: RAISE Fnd_Api.G_EXC_ERROR;
3250: END IF;
3251: END LOOP; --for loop
3252: END IF;
3253: -- Calling Wip job api
3392: END IF;
3393: -- call update reservations api.
3394: AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
3395: p_api_version => 1.0,
3396: p_init_msg_list => FND_API.G_FALSE,
3397: p_commit => FND_API.G_FALSE,
3398: p_module_type => NULL,
3399: x_return_status => x_return_status,
3400: x_msg_count => x_msg_count,
3393: -- call update reservations api.
3394: AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
3395: p_api_version => 1.0,
3396: p_init_msg_list => FND_API.G_FALSE,
3397: p_commit => FND_API.G_FALSE,
3398: p_module_type => NULL,
3399: x_return_status => x_return_status,
3400: x_msg_count => x_msg_count,
3401: x_msg_data => x_msg_data,
3404: IF G_DEBUG='Y' THEN
3405: AHL_DEBUG_PUB.debug('After Call to Upd RSV- Return Status:' || x_return_status);
3406: END IF;
3407: -- Raise error if exceptions occur
3408: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3409: RAISE FND_API.G_EXC_ERROR;
3410: END IF;
3411: END IF; -- p_x_req_material_tbl(i).requested_date <>
3412: --
3405: AHL_DEBUG_PUB.debug('After Call to Upd RSV- Return Status:' || x_return_status);
3406: END IF;
3407: -- Raise error if exceptions occur
3408: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3409: RAISE FND_API.G_EXC_ERROR;
3410: END IF;
3411: END IF; -- p_x_req_material_tbl(i).requested_date <>
3412: --
3413: --
3412: --
3413: --
3414: -- The following conditions compare the new record value with old record
3415: -- value, if its different then assign the new value else continue
3416: IF NVL(p_x_req_material_tbl(i).inventory_item_id, 0) <> FND_API.G_MISS_NUM
3417: THEN
3418: l_req_material_rec.inventory_item_id := p_x_req_material_tbl(i).inventory_item_id;
3419: END IF;
3420: --
3417: THEN
3418: l_req_material_rec.inventory_item_id := p_x_req_material_tbl(i).inventory_item_id;
3419: END IF;
3420: --
3421: IF NVL(p_x_req_material_tbl(i).requested_date,sysdate) <> FND_API.G_MISS_DATE
3422: THEN
3423: l_req_material_rec.requested_date := p_x_req_material_tbl(i).requested_date;
3424: END IF;
3425: --
3422: THEN
3423: l_req_material_rec.requested_date := p_x_req_material_tbl(i).requested_date;
3424: END IF;
3425: --
3426: IF NVL(p_x_req_material_tbl(i).requested_quantity, 0) <> FND_API.G_MISS_NUM
3427: THEN
3428: l_req_material_rec.requested_quantity := p_x_req_material_tbl(i).requested_quantity;
3429: END IF;
3430: --
3428: l_req_material_rec.requested_quantity := p_x_req_material_tbl(i).requested_quantity;
3429: END IF;
3430: --
3431: -- MANESING::NR Analysis, 03-Jun-2011, add scheduled quantity also to local record
3432: IF NVL(p_x_req_material_tbl(i).scheduled_quantity, 0) <> FND_API.G_MISS_NUM
3433: THEN
3434: l_req_material_rec.scheduled_quantity := p_x_req_material_tbl(i).scheduled_quantity;
3435: END IF;
3436: --
3433: THEN
3434: l_req_material_rec.scheduled_quantity := p_x_req_material_tbl(i).scheduled_quantity;
3435: END IF;
3436: --
3437: IF NVL(p_x_req_material_tbl(i).organization_id, 0) <> FND_API.G_MISS_NUM
3438: THEN
3439: l_req_material_rec.organization_id := p_x_req_material_tbl(i).organization_id;
3440: END IF;
3441: --
3438: THEN
3439: l_req_material_rec.organization_id := p_x_req_material_tbl(i).organization_id;
3440: END IF;
3441: --
3442: IF NVL(p_x_req_material_tbl(i).visit_id, 0) <> FND_API.G_MISS_NUM
3443: THEN
3444: l_req_material_rec.visit_id := p_x_req_material_tbl(i).visit_id;
3445: END IF;
3446: --
3443: THEN
3444: l_req_material_rec.visit_id := p_x_req_material_tbl(i).visit_id;
3445: END IF;
3446: --
3447: IF NVL(p_x_req_material_tbl(i).visit_task_id, 0) <> FND_API.G_MISS_NUM
3448: THEN
3449: l_req_material_rec.visit_task_id := p_x_req_material_tbl(i).visit_task_id;
3450: END IF;
3451: --
3448: THEN
3449: l_req_material_rec.visit_task_id := p_x_req_material_tbl(i).visit_task_id;
3450: END IF;
3451: --
3452: IF NVL(p_x_req_material_tbl(i).item_group_id, 0) <> FND_API.G_MISS_NUM
3453: THEN
3454: l_req_material_rec.item_group_id := p_x_req_material_tbl(i).item_group_id;
3455: END IF;
3456: --
3453: THEN
3454: l_req_material_rec.item_group_id := p_x_req_material_tbl(i).item_group_id;
3455: END IF;
3456: --
3457: IF NVL(p_x_req_material_tbl(i).rt_oper_material_id, 0) <> FND_API.G_MISS_NUM
3458: THEN
3459: l_req_material_rec.rt_oper_material_id := p_x_req_material_tbl(i).rt_oper_material_id;
3460: END IF;
3461: --sukhwsin: SB Effectivity Code changes - starts
3459: l_req_material_rec.rt_oper_material_id := p_x_req_material_tbl(i).rt_oper_material_id;
3460: END IF;
3461: --sukhwsin: SB Effectivity Code changes - starts
3462: --
3463: IF NVL(p_x_req_material_tbl(i).mc_header_id, 0) <> FND_API.G_MISS_NUM
3464: THEN
3465: l_req_material_rec.mc_header_id := p_x_req_material_tbl(i).mc_header_id;
3466: END IF;
3467: --
3464: THEN
3465: l_req_material_rec.mc_header_id := p_x_req_material_tbl(i).mc_header_id;
3466: END IF;
3467: --
3468: IF NVL(p_x_req_material_tbl(i).mc_position_key, 0) <> FND_API.G_MISS_NUM
3469: THEN
3470: l_req_material_rec.position_key := p_x_req_material_tbl(i).mc_position_key;
3471: END IF;
3472: --sukhwsin: SB Effectivity Code changes - ends
3471: END IF;
3472: --sukhwsin: SB Effectivity Code changes - ends
3473: --
3474: IF p_x_req_material_tbl(i).attribute_category IS NOT NULL AND
3475: p_x_req_material_tbl(i).attribute_category <> FND_API.G_MISS_CHAR
3476: THEN
3477: l_req_material_rec.attribute_category := p_x_req_material_tbl(i).attribute_category;
3478: ELSIF p_x_req_material_tbl(i).attribute_category = FND_API.G_MISS_CHAR THEN
3479: l_req_material_rec.attribute_category := NULL;
3474: IF p_x_req_material_tbl(i).attribute_category IS NOT NULL AND
3475: p_x_req_material_tbl(i).attribute_category <> FND_API.G_MISS_CHAR
3476: THEN
3477: l_req_material_rec.attribute_category := p_x_req_material_tbl(i).attribute_category;
3478: ELSIF p_x_req_material_tbl(i).attribute_category = FND_API.G_MISS_CHAR THEN
3479: l_req_material_rec.attribute_category := NULL;
3480: END IF;
3481: --
3482: IF p_x_req_material_tbl(i).attribute1 IS NOT NULL AND
3479: l_req_material_rec.attribute_category := NULL;
3480: END IF;
3481: --
3482: IF p_x_req_material_tbl(i).attribute1 IS NOT NULL AND
3483: p_x_req_material_tbl(i).attribute1 <> FND_API.G_MISS_CHAR
3484: THEN
3485: l_req_material_rec.attribute1 := p_x_req_material_tbl(i).attribute1;
3486: ELSIF p_x_req_material_tbl(i).attribute1 = FND_API.G_MISS_CHAR THEN
3487: l_req_material_rec.attribute1 := NULL;
3482: IF p_x_req_material_tbl(i).attribute1 IS NOT NULL AND
3483: p_x_req_material_tbl(i).attribute1 <> FND_API.G_MISS_CHAR
3484: THEN
3485: l_req_material_rec.attribute1 := p_x_req_material_tbl(i).attribute1;
3486: ELSIF p_x_req_material_tbl(i).attribute1 = FND_API.G_MISS_CHAR THEN
3487: l_req_material_rec.attribute1 := NULL;
3488: END IF;
3489: --
3490: IF p_x_req_material_tbl(i).attribute2 IS NOT NULL AND
3487: l_req_material_rec.attribute1 := NULL;
3488: END IF;
3489: --
3490: IF p_x_req_material_tbl(i).attribute2 IS NOT NULL AND
3491: p_x_req_material_tbl(i).attribute2 <> FND_API.G_MISS_CHAR
3492: THEN
3493: l_req_material_rec.attribute2 := p_x_req_material_tbl(i).attribute2;
3494: ELSIF p_x_req_material_tbl(i).attribute2 = FND_API.G_MISS_CHAR THEN
3495: l_req_material_rec.attribute2 := NULL;
3490: IF p_x_req_material_tbl(i).attribute2 IS NOT NULL AND
3491: p_x_req_material_tbl(i).attribute2 <> FND_API.G_MISS_CHAR
3492: THEN
3493: l_req_material_rec.attribute2 := p_x_req_material_tbl(i).attribute2;
3494: ELSIF p_x_req_material_tbl(i).attribute2 = FND_API.G_MISS_CHAR THEN
3495: l_req_material_rec.attribute2 := NULL;
3496: END IF;
3497: --
3498: IF p_x_req_material_tbl(i).attribute3 IS NOT NULL AND
3495: l_req_material_rec.attribute2 := NULL;
3496: END IF;
3497: --
3498: IF p_x_req_material_tbl(i).attribute3 IS NOT NULL AND
3499: p_x_req_material_tbl(i).attribute3 <> FND_API.G_MISS_CHAR
3500: THEN
3501: l_req_material_rec.attribute3 := p_x_req_material_tbl(i).attribute3;
3502: ELSIF p_x_req_material_tbl(i).attribute3 = FND_API.G_MISS_CHAR THEN
3503: l_req_material_rec.attribute3 := NULL;
3498: IF p_x_req_material_tbl(i).attribute3 IS NOT NULL AND
3499: p_x_req_material_tbl(i).attribute3 <> FND_API.G_MISS_CHAR
3500: THEN
3501: l_req_material_rec.attribute3 := p_x_req_material_tbl(i).attribute3;
3502: ELSIF p_x_req_material_tbl(i).attribute3 = FND_API.G_MISS_CHAR THEN
3503: l_req_material_rec.attribute3 := NULL;
3504: END IF;
3505: --
3506: IF p_x_req_material_tbl(i).attribute4 IS NOT NULL AND
3503: l_req_material_rec.attribute3 := NULL;
3504: END IF;
3505: --
3506: IF p_x_req_material_tbl(i).attribute4 IS NOT NULL AND
3507: p_x_req_material_tbl(i).attribute4 <> FND_API.G_MISS_CHAR
3508: THEN
3509: l_req_material_rec.attribute4 := p_x_req_material_tbl(i).attribute4;
3510: ELSIF p_x_req_material_tbl(i).attribute4 = FND_API.G_MISS_CHAR THEN
3511: l_req_material_rec.attribute4 := NULL;
3506: IF p_x_req_material_tbl(i).attribute4 IS NOT NULL AND
3507: p_x_req_material_tbl(i).attribute4 <> FND_API.G_MISS_CHAR
3508: THEN
3509: l_req_material_rec.attribute4 := p_x_req_material_tbl(i).attribute4;
3510: ELSIF p_x_req_material_tbl(i).attribute4 = FND_API.G_MISS_CHAR THEN
3511: l_req_material_rec.attribute4 := NULL;
3512: END IF;
3513: --
3514: IF p_x_req_material_tbl(i).attribute5 IS NOT NULL AND
3511: l_req_material_rec.attribute4 := NULL;
3512: END IF;
3513: --
3514: IF p_x_req_material_tbl(i).attribute5 IS NOT NULL AND
3515: p_x_req_material_tbl(i).attribute5 <> FND_API.G_MISS_CHAR
3516: THEN
3517: l_req_material_rec.attribute5 := p_x_req_material_tbl(i).attribute5;
3518: ELSIF p_x_req_material_tbl(i).attribute5 = FND_API.G_MISS_CHAR THEN
3519: l_req_material_rec.attribute5 := NULL;
3514: IF p_x_req_material_tbl(i).attribute5 IS NOT NULL AND
3515: p_x_req_material_tbl(i).attribute5 <> FND_API.G_MISS_CHAR
3516: THEN
3517: l_req_material_rec.attribute5 := p_x_req_material_tbl(i).attribute5;
3518: ELSIF p_x_req_material_tbl(i).attribute5 = FND_API.G_MISS_CHAR THEN
3519: l_req_material_rec.attribute5 := NULL;
3520: END IF;
3521: --
3522: IF p_x_req_material_tbl(i).attribute6 IS NOT NULL AND
3519: l_req_material_rec.attribute5 := NULL;
3520: END IF;
3521: --
3522: IF p_x_req_material_tbl(i).attribute6 IS NOT NULL AND
3523: p_x_req_material_tbl(i).attribute6 <> FND_API.G_MISS_CHAR
3524: THEN
3525: l_req_material_rec.attribute6 := p_x_req_material_tbl(i).attribute6;
3526: ELSIF p_x_req_material_tbl(i).attribute6 = FND_API.G_MISS_CHAR THEN
3527: l_req_material_rec.attribute6 := NULL;
3522: IF p_x_req_material_tbl(i).attribute6 IS NOT NULL AND
3523: p_x_req_material_tbl(i).attribute6 <> FND_API.G_MISS_CHAR
3524: THEN
3525: l_req_material_rec.attribute6 := p_x_req_material_tbl(i).attribute6;
3526: ELSIF p_x_req_material_tbl(i).attribute6 = FND_API.G_MISS_CHAR THEN
3527: l_req_material_rec.attribute6 := NULL;
3528: END IF;
3529: --
3530: IF p_x_req_material_tbl(i).attribute7 IS NOT NULL AND
3527: l_req_material_rec.attribute6 := NULL;
3528: END IF;
3529: --
3530: IF p_x_req_material_tbl(i).attribute7 IS NOT NULL AND
3531: p_x_req_material_tbl(i).attribute7 <> FND_API.G_MISS_CHAR
3532: THEN
3533: l_req_material_rec.attribute7 := p_x_req_material_tbl(i).attribute7;
3534: ELSIF p_x_req_material_tbl(i).attribute7 = FND_API.G_MISS_CHAR THEN
3535: l_req_material_rec.attribute7 := NULL;
3530: IF p_x_req_material_tbl(i).attribute7 IS NOT NULL AND
3531: p_x_req_material_tbl(i).attribute7 <> FND_API.G_MISS_CHAR
3532: THEN
3533: l_req_material_rec.attribute7 := p_x_req_material_tbl(i).attribute7;
3534: ELSIF p_x_req_material_tbl(i).attribute7 = FND_API.G_MISS_CHAR THEN
3535: l_req_material_rec.attribute7 := NULL;
3536: END IF;
3537: --
3538: IF p_x_req_material_tbl(i).attribute8 IS NOT NULL AND
3535: l_req_material_rec.attribute7 := NULL;
3536: END IF;
3537: --
3538: IF p_x_req_material_tbl(i).attribute8 IS NOT NULL AND
3539: p_x_req_material_tbl(i).attribute8 <> FND_API.G_MISS_CHAR
3540: THEN
3541: l_req_material_rec.attribute8 := p_x_req_material_tbl(i).attribute8;
3542: ELSIF p_x_req_material_tbl(i).attribute8 = FND_API.G_MISS_CHAR THEN
3543: l_req_material_rec.attribute8 := NULL;
3538: IF p_x_req_material_tbl(i).attribute8 IS NOT NULL AND
3539: p_x_req_material_tbl(i).attribute8 <> FND_API.G_MISS_CHAR
3540: THEN
3541: l_req_material_rec.attribute8 := p_x_req_material_tbl(i).attribute8;
3542: ELSIF p_x_req_material_tbl(i).attribute8 = FND_API.G_MISS_CHAR THEN
3543: l_req_material_rec.attribute8 := NULL;
3544: END IF;
3545: --
3546: IF p_x_req_material_tbl(i).attribute9 IS NOT NULL AND
3543: l_req_material_rec.attribute8 := NULL;
3544: END IF;
3545: --
3546: IF p_x_req_material_tbl(i).attribute9 IS NOT NULL AND
3547: p_x_req_material_tbl(i).attribute9 <> FND_API.G_MISS_CHAR
3548: THEN
3549: l_req_material_rec.attribute9 := p_x_req_material_tbl(i).attribute9;
3550: ELSIF p_x_req_material_tbl(i).attribute9 = FND_API.G_MISS_CHAR THEN
3551: l_req_material_rec.attribute9 := NULL;
3546: IF p_x_req_material_tbl(i).attribute9 IS NOT NULL AND
3547: p_x_req_material_tbl(i).attribute9 <> FND_API.G_MISS_CHAR
3548: THEN
3549: l_req_material_rec.attribute9 := p_x_req_material_tbl(i).attribute9;
3550: ELSIF p_x_req_material_tbl(i).attribute9 = FND_API.G_MISS_CHAR THEN
3551: l_req_material_rec.attribute9 := NULL;
3552: END IF;
3553: --
3554: IF p_x_req_material_tbl(i).attribute10 IS NOT NULL AND
3551: l_req_material_rec.attribute9 := NULL;
3552: END IF;
3553: --
3554: IF p_x_req_material_tbl(i).attribute10 IS NOT NULL AND
3555: p_x_req_material_tbl(i).attribute10 <> FND_API.G_MISS_CHAR
3556: THEN
3557: l_req_material_rec.attribute10 := p_x_req_material_tbl(i).attribute10;
3558: ELSIF p_x_req_material_tbl(i).attribute10 = FND_API.G_MISS_CHAR THEN
3559: l_req_material_rec.attribute10 := NULL;
3554: IF p_x_req_material_tbl(i).attribute10 IS NOT NULL AND
3555: p_x_req_material_tbl(i).attribute10 <> FND_API.G_MISS_CHAR
3556: THEN
3557: l_req_material_rec.attribute10 := p_x_req_material_tbl(i).attribute10;
3558: ELSIF p_x_req_material_tbl(i).attribute10 = FND_API.G_MISS_CHAR THEN
3559: l_req_material_rec.attribute10 := NULL;
3560: END IF;
3561: --
3562: IF p_x_req_material_tbl(i).attribute11 IS NOT NULL AND
3559: l_req_material_rec.attribute10 := NULL;
3560: END IF;
3561: --
3562: IF p_x_req_material_tbl(i).attribute11 IS NOT NULL AND
3563: p_x_req_material_tbl(i).attribute11 <> FND_API.G_MISS_CHAR
3564: THEN
3565: l_req_material_rec.attribute11 := p_x_req_material_tbl(i).attribute11;
3566: ELSIF p_x_req_material_tbl(i).attribute11 = FND_API.G_MISS_CHAR THEN
3567: l_req_material_rec.attribute11 := NULL;
3562: IF p_x_req_material_tbl(i).attribute11 IS NOT NULL AND
3563: p_x_req_material_tbl(i).attribute11 <> FND_API.G_MISS_CHAR
3564: THEN
3565: l_req_material_rec.attribute11 := p_x_req_material_tbl(i).attribute11;
3566: ELSIF p_x_req_material_tbl(i).attribute11 = FND_API.G_MISS_CHAR THEN
3567: l_req_material_rec.attribute11 := NULL;
3568: END IF;
3569: --
3570: IF p_x_req_material_tbl(i).attribute12 IS NOT NULL AND
3567: l_req_material_rec.attribute11 := NULL;
3568: END IF;
3569: --
3570: IF p_x_req_material_tbl(i).attribute12 IS NOT NULL AND
3571: p_x_req_material_tbl(i).attribute12 <> FND_API.G_MISS_CHAR
3572: THEN
3573: l_req_material_rec.attribute12 := p_x_req_material_tbl(i).attribute12;
3574: ELSIF p_x_req_material_tbl(i).attribute12 = FND_API.G_MISS_CHAR THEN
3575: l_req_material_rec.attribute12 := NULL;
3570: IF p_x_req_material_tbl(i).attribute12 IS NOT NULL AND
3571: p_x_req_material_tbl(i).attribute12 <> FND_API.G_MISS_CHAR
3572: THEN
3573: l_req_material_rec.attribute12 := p_x_req_material_tbl(i).attribute12;
3574: ELSIF p_x_req_material_tbl(i).attribute12 = FND_API.G_MISS_CHAR THEN
3575: l_req_material_rec.attribute12 := NULL;
3576: END IF;
3577: --
3578: IF p_x_req_material_tbl(i).attribute13 IS NOT NULL AND
3575: l_req_material_rec.attribute12 := NULL;
3576: END IF;
3577: --
3578: IF p_x_req_material_tbl(i).attribute13 IS NOT NULL AND
3579: p_x_req_material_tbl(i).attribute13 <> FND_API.G_MISS_CHAR
3580: THEN
3581: l_req_material_rec.attribute13 := p_x_req_material_tbl(i).attribute13;
3582: ELSIF p_x_req_material_tbl(i).attribute13 = FND_API.G_MISS_CHAR THEN
3583: l_req_material_rec.attribute13 := NULL;
3578: IF p_x_req_material_tbl(i).attribute13 IS NOT NULL AND
3579: p_x_req_material_tbl(i).attribute13 <> FND_API.G_MISS_CHAR
3580: THEN
3581: l_req_material_rec.attribute13 := p_x_req_material_tbl(i).attribute13;
3582: ELSIF p_x_req_material_tbl(i).attribute13 = FND_API.G_MISS_CHAR THEN
3583: l_req_material_rec.attribute13 := NULL;
3584: END IF;
3585: --
3586: IF p_x_req_material_tbl(i).attribute14 IS NOT NULL AND
3583: l_req_material_rec.attribute13 := NULL;
3584: END IF;
3585: --
3586: IF p_x_req_material_tbl(i).attribute14 IS NOT NULL AND
3587: p_x_req_material_tbl(i).attribute14 <> FND_API.G_MISS_CHAR
3588: THEN
3589: l_req_material_rec.attribute14 := p_x_req_material_tbl(i).attribute14;
3590: ELSIF p_x_req_material_tbl(i).attribute14 = FND_API.G_MISS_CHAR THEN
3591: l_req_material_rec.attribute14 := NULL;
3586: IF p_x_req_material_tbl(i).attribute14 IS NOT NULL AND
3587: p_x_req_material_tbl(i).attribute14 <> FND_API.G_MISS_CHAR
3588: THEN
3589: l_req_material_rec.attribute14 := p_x_req_material_tbl(i).attribute14;
3590: ELSIF p_x_req_material_tbl(i).attribute14 = FND_API.G_MISS_CHAR THEN
3591: l_req_material_rec.attribute14 := NULL;
3592: END IF;
3593: --
3594: IF p_x_req_material_tbl(i).attribute15 IS NOT NULL AND
3591: l_req_material_rec.attribute14 := NULL;
3592: END IF;
3593: --
3594: IF p_x_req_material_tbl(i).attribute15 IS NOT NULL AND
3595: p_x_req_material_tbl(i).attribute15 <> FND_API.G_MISS_CHAR
3596: THEN
3597: l_req_material_rec.attribute15 := p_x_req_material_tbl(i).attribute15;
3598: ELSIF p_x_req_material_tbl(i).attribute15 = FND_API.G_MISS_CHAR THEN
3599: l_req_material_rec.attribute15 := NULL;
3594: IF p_x_req_material_tbl(i).attribute15 IS NOT NULL AND
3595: p_x_req_material_tbl(i).attribute15 <> FND_API.G_MISS_CHAR
3596: THEN
3597: l_req_material_rec.attribute15 := p_x_req_material_tbl(i).attribute15;
3598: ELSIF p_x_req_material_tbl(i).attribute15 = FND_API.G_MISS_CHAR THEN
3599: l_req_material_rec.attribute15 := NULL;
3600: END IF;
3601:
3602: -- debadey: Start of changes for Marshalling FDD section 5.15
3601:
3602: -- debadey: Start of changes for Marshalling FDD section 5.15
3603: -- AOG Flag
3604: IF p_x_req_material_tbl(i).aog_flag IS NOT NULL AND
3605: p_x_req_material_tbl(i).aog_flag <> FND_API.G_MISS_CHAR
3606: THEN
3607: l_req_material_rec.aog_flag := p_x_req_material_tbl(i).aog_flag;
3608: ELSIF p_x_req_material_tbl(i).aog_flag = FND_API.G_MISS_CHAR
3609: THEN
3604: IF p_x_req_material_tbl(i).aog_flag IS NOT NULL AND
3605: p_x_req_material_tbl(i).aog_flag <> FND_API.G_MISS_CHAR
3606: THEN
3607: l_req_material_rec.aog_flag := p_x_req_material_tbl(i).aog_flag;
3608: ELSIF p_x_req_material_tbl(i).aog_flag = FND_API.G_MISS_CHAR
3609: THEN
3610: l_req_material_rec.aog_flag := NULL;
3611: END IF;
3612: -- end of changes by debadey for Marshalling
3658: END LOOP;
3659: END IF; -- p_x_req_material_tbl.COUNT
3660: --
3661: ELSE
3662: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3663: RAISE Fnd_Api.G_EXC_ERROR;
3664: END IF;-- Return status
3665: IF X_return_status = 'S' THEN
3666: --Check for Profile Option value If 'Y' Call
3659: END IF; -- p_x_req_material_tbl.COUNT
3660: --
3661: ELSE
3662: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3663: RAISE Fnd_Api.G_EXC_ERROR;
3664: END IF;-- Return status
3665: IF X_return_status = 'S' THEN
3666: --Check for Profile Option value If 'Y' Call
3667: IF FND_PROFILE.value( 'AHL_MTL_REQ_NOTIFICATION_ENABLED') = 'Y' THEN
3689: --Ignore messages in stack if return status is S after calls to EAM APIs.
3690: /*
3691: IF l_msg_count > 0 THEN
3692: X_msg_count := l_msg_count;
3693: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3694: RAISE Fnd_Api.G_EXC_ERROR;
3695: END IF;
3696: */
3697: --Standard check for commit
3690: /*
3691: IF l_msg_count > 0 THEN
3692: X_msg_count := l_msg_count;
3693: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3694: RAISE Fnd_Api.G_EXC_ERROR;
3695: END IF;
3696: */
3697: --Standard check for commit
3698: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3694: RAISE Fnd_Api.G_EXC_ERROR;
3695: END IF;
3696: */
3697: --Standard check for commit
3698: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3699: COMMIT;
3700: END IF;
3701: -- Debug info
3702: IF G_DEBUG='Y' THEN
3704: -- Check if API is called in debug mode. If yes, disable debug.
3705: Ahl_Debug_Pub.disable_debug;
3706: END IF;
3707: EXCEPTION
3708: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3709: ROLLBACK TO update_material_reqst;
3710: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3711: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3712: p_count => x_msg_count,
3706: END IF;
3707: EXCEPTION
3708: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3709: ROLLBACK TO update_material_reqst;
3710: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3711: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3712: p_count => x_msg_count,
3713: p_data => x_msg_data);
3714: IF G_DEBUG='Y' THEN
3707: EXCEPTION
3708: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3709: ROLLBACK TO update_material_reqst;
3710: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3711: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3712: p_count => x_msg_count,
3713: p_data => x_msg_data);
3714: IF G_DEBUG='Y' THEN
3715: AHL_DEBUG_PUB.log_app_messages (
3717: AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
3718: -- Check if API is called in debug mode. If yes, disable debug.
3719: AHL_DEBUG_PUB.disable_debug;
3720: END IF;
3721: WHEN FND_API.G_EXC_ERROR THEN
3722: ROLLBACK TO update_material_reqst;
3723: X_return_status := FND_API.G_RET_STS_ERROR;
3724: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3725: p_count => x_msg_count,
3719: AHL_DEBUG_PUB.disable_debug;
3720: END IF;
3721: WHEN FND_API.G_EXC_ERROR THEN
3722: ROLLBACK TO update_material_reqst;
3723: X_return_status := FND_API.G_RET_STS_ERROR;
3724: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3725: p_count => x_msg_count,
3726: p_data => X_msg_data);
3727: IF G_DEBUG='Y' THEN
3720: END IF;
3721: WHEN FND_API.G_EXC_ERROR THEN
3722: ROLLBACK TO update_material_reqst;
3723: X_return_status := FND_API.G_RET_STS_ERROR;
3724: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3725: p_count => x_msg_count,
3726: p_data => X_msg_data);
3727: IF G_DEBUG='Y' THEN
3728: -- Debug info.
3733: AHL_DEBUG_PUB.disable_debug;
3734: END IF;
3735: WHEN OTHERS THEN
3736: ROLLBACK TO update_material_reqst;
3737: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3738: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3739: THEN
3740: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
3741: p_procedure_name => 'UPDATE_MATERIAL_REQST',
3740: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
3741: p_procedure_name => 'UPDATE_MATERIAL_REQST',
3742: p_error_text => SUBSTR(SQLERRM,1,240));
3743: END IF;
3744: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3745: p_count => x_msg_count,
3746: p_data => X_msg_data);
3747: IF G_DEBUG='Y' THEN
3748: -- Debug info.
3762: -- Parameters :
3763: --
3764: -- Standard IN Parameters :
3765: -- p_api_version IN NUMBER Required
3766: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3767: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3768: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3769: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3770: -- p_module_type IN VARCHAR2 Default NULL.
3763: --
3764: -- Standard IN Parameters :
3765: -- p_api_version IN NUMBER Required
3766: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3767: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3768: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3769: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3770: -- p_module_type IN VARCHAR2 Default NULL.
3771: --
3764: -- Standard IN Parameters :
3765: -- p_api_version IN NUMBER Required
3766: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3767: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3768: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3769: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3770: -- p_module_type IN VARCHAR2 Default NULL.
3771: --
3772: -- Standard OUT Parameters :
3765: -- p_api_version IN NUMBER Required
3766: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3767: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3768: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3769: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3770: -- p_module_type IN VARCHAR2 Default NULL.
3771: --
3772: -- Standard OUT Parameters :
3773: -- x_return_status OUT VARCHAR2 Required
3783: --
3784: -- End of Comments.
3785: PROCEDURE Remove_Material_Request (
3786: p_api_version IN NUMBER,
3787: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
3788: p_commit IN VARCHAR2 := Fnd_Api.g_false,
3789: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
3790: p_module_type IN VARCHAR2 := 'JSP',
3791: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
3784: -- End of Comments.
3785: PROCEDURE Remove_Material_Request (
3786: p_api_version IN NUMBER,
3787: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
3788: p_commit IN VARCHAR2 := Fnd_Api.g_false,
3789: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
3790: p_module_type IN VARCHAR2 := 'JSP',
3791: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
3792: x_return_status OUT NOCOPY VARCHAR2,
3785: PROCEDURE Remove_Material_Request (
3786: p_api_version IN NUMBER,
3787: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
3788: p_commit IN VARCHAR2 := Fnd_Api.g_false,
3789: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
3790: p_module_type IN VARCHAR2 := 'JSP',
3791: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
3792: x_return_status OUT NOCOPY VARCHAR2,
3793: x_msg_count OUT NOCOPY NUMBER,
3929: Ahl_Debug_Pub.debug( 'enter ahl_pp_materials_pvt Remove Material Request ','+MAATP+');
3930: --
3931: END IF;
3932: -- Standard call to check for call compatibility.
3933: IF Fnd_Api.to_boolean(p_init_msg_list)
3934: THEN
3935: Fnd_Msg_Pub.initialize;
3936: END IF;
3937: -- Initialize API return status to success
3934: THEN
3935: Fnd_Msg_Pub.initialize;
3936: END IF;
3937: -- Initialize API return status to success
3938: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3939: -- Initialize message list if p_init_msg_list is set to TRUE.
3940: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3941: p_api_version,
3942: l_api_name,G_PKG_NAME)
3936: END IF;
3937: -- Initialize API return status to success
3938: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3939: -- Initialize message list if p_init_msg_list is set to TRUE.
3940: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3941: p_api_version,
3942: l_api_name,G_PKG_NAME)
3943: THEN
3944: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3940: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3941: p_api_version,
3942: l_api_name,G_PKG_NAME)
3943: THEN
3944: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3945: END IF;
3946: ------------------------Start API Body ---------------------------------
3947: IF p_x_req_material_tbl.COUNT > 0 THEN
3948: FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
3949: LOOP
3950: -- Value to ID Conversion
3951: --Get visit task id
3952: IF (p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
3953: p_x_req_material_tbl(i).workorder_id <> Fnd_Api.G_MISS_NUM )
3954: THEN
3955: --
3956: -- Bug # 6680137 - start
3957: OPEN Get_job_details(p_x_req_material_tbl(i).workorder_id);
3959: IF Get_job_details%NOTFOUND THEN
3960: Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_NOT_EXISTS');
3961: Fnd_Msg_Pub.ADD;
3962: CLOSE Get_job_details;
3963: RAISE FND_API.G_EXC_ERROR;
3964: END IF;
3965: CLOSE Get_job_details;
3966: -- Bug # 6680137 - end
3967: -- Balaji added this validation for Bug # 6680137 - begin.
3973: IF l_wo_status IN ('7', '5', '12')
3974: THEN
3975: Fnd_Message.SET_NAME('AHL','AHL_PP_WO_STATUS_INVALID');
3976: Fnd_Msg_Pub.ADD;
3977: RAISE FND_API.G_EXC_ERROR;
3978: END IF;
3979: -- Bug # 6680137 - end
3980: END IF;
3981: -- rroy
3985: p_workorder_id => p_x_req_material_tbl(i).workorder_id,
3986: p_ue_id => NULL,
3987: p_visit_id => NULL,
3988: p_item_instance_id => NULL);
3989: IF l_return_status = FND_API.G_TRUE THEN
3990: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_MTL_UNTLCKD');
3991: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
3992: FND_MSG_PUB.ADD;
3993: RAISE FND_API.G_EXC_ERROR;
3989: IF l_return_status = FND_API.G_TRUE THEN
3990: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_MTL_UNTLCKD');
3991: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
3992: FND_MSG_PUB.ADD;
3993: RAISE FND_API.G_EXC_ERROR;
3994: END IF;
3995: -- rroy
3996: -- ACL Changes
3997: -- Check for Schedule Material ID
3995: -- rroy
3996: -- ACL Changes
3997: -- Check for Schedule Material ID
3998: IF (p_x_req_material_tbl(i).schedule_material_id IS NULL OR
3999: p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM) THEN
4000: Fnd_Message.SET_NAME('AHL','AHL_PP_SCH_MATRL_REQUIRED');
4001: Fnd_Msg_Pub.ADD;
4002: END IF;
4003: --
4006: Ahl_Debug_Pub.debug( 'Sch mat Id:'||p_x_req_material_tbl(i).schedule_material_id);
4007: END IF;
4008: -- Check for object version number
4009: IF (p_x_req_material_tbl(i).object_version_number IS NOT NULL AND
4010: p_x_req_material_tbl(i).object_version_number <> FND_API.G_MISS_NUM) THEN
4011: --
4012: SELECT object_version_number,requested_quantity INTO l_object_version_number,
4013: p_x_req_material_tbl(i).requested_quantity
4014: FROM ahl_schedule_materials
4017: --
4018: IF p_x_req_material_tbl(i).object_version_number <> l_object_version_number THEN
4019: Fnd_Message.SET_NAME('AHL','AHL_PP_RECORD_CHANGED');
4020: Fnd_Msg_Pub.ADD;
4021: RAISE Fnd_Api.G_EXC_ERROR;
4022: END IF;
4023: --
4024: END IF;
4025: --Standard check to count messages
4025: --Standard check to count messages
4026: l_msg_count := Fnd_Msg_Pub.count_msg;
4027: IF l_msg_count > 0 THEN
4028: X_msg_count := l_msg_count;
4029: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4030: RAISE Fnd_Api.G_EXC_ERROR;
4031: END IF;
4032: END LOOP;
4033: END IF;
4026: l_msg_count := Fnd_Msg_Pub.count_msg;
4027: IF l_msg_count > 0 THEN
4028: X_msg_count := l_msg_count;
4029: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4030: RAISE Fnd_Api.G_EXC_ERROR;
4031: END IF;
4032: END LOOP;
4033: END IF;
4034: --
4080: END IF;
4081: -- delete reservations.
4082: AHL_RSV_RESERVATIONS_PVT.DELETE_RESERVATION(
4083: p_api_version => 1.0,
4084: p_init_msg_list => FND_API.G_FALSE,
4085: p_commit => FND_API.G_FALSE,
4086: p_module_type => NULL,
4087: x_return_status => x_return_status,
4088: x_msg_count => x_msg_count,
4081: -- delete reservations.
4082: AHL_RSV_RESERVATIONS_PVT.DELETE_RESERVATION(
4083: p_api_version => 1.0,
4084: p_init_msg_list => FND_API.G_FALSE,
4085: p_commit => FND_API.G_FALSE,
4086: p_module_type => NULL,
4087: x_return_status => x_return_status,
4088: x_msg_count => x_msg_count,
4089: x_msg_data => x_msg_data,
4091: IF G_DEBUG='Y' THEN
4092: AHL_DEBUG_PUB.debug('After calling delete reservation api. Return status:' || x_return_status);
4093: END IF;
4094: -- check return status.
4095: IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4096: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4097: ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4098: RAISE FND_API.G_EXC_ERROR;
4099: END IF;
4092: AHL_DEBUG_PUB.debug('After calling delete reservation api. Return status:' || x_return_status);
4093: END IF;
4094: -- check return status.
4095: IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4096: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4097: ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4098: RAISE FND_API.G_EXC_ERROR;
4099: END IF;
4100: END IF;
4093: END IF;
4094: -- check return status.
4095: IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4096: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4097: ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4098: RAISE FND_API.G_EXC_ERROR;
4099: END IF;
4100: END IF;
4101: END LOOP;
4094: -- check return status.
4095: IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4096: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4097: ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4098: RAISE FND_API.G_EXC_ERROR;
4099: END IF;
4100: END IF;
4101: END LOOP;
4102: END IF; --Material tbl
4143: END LOOP;
4144: END IF;
4145: --
4146: ELSE
4147: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4148: RAISE Fnd_Api.G_EXC_ERROR;
4149: --
4150: END IF; --Status
4151: ---------------------------End of Body---------------------------------------
4144: END IF;
4145: --
4146: ELSE
4147: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4148: RAISE Fnd_Api.G_EXC_ERROR;
4149: --
4150: END IF; --Status
4151: ---------------------------End of Body---------------------------------------
4152: --Standard check to count messages
4155: --Ignore messages in stack if return status is S after calls to EAM APIs.
4156: /*
4157: IF l_msg_count > 0 THEN
4158: X_msg_count := l_msg_count;
4159: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4160: RAISE Fnd_Api.G_EXC_ERROR;
4161: END IF;
4162: */
4163: --Standard check for commit
4156: /*
4157: IF l_msg_count > 0 THEN
4158: X_msg_count := l_msg_count;
4159: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4160: RAISE Fnd_Api.G_EXC_ERROR;
4161: END IF;
4162: */
4163: --Standard check for commit
4164: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4160: RAISE Fnd_Api.G_EXC_ERROR;
4161: END IF;
4162: */
4163: --Standard check for commit
4164: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4165: COMMIT;
4166: END IF;
4167: -- Debug info
4168: IF G_DEBUG='Y' THEN
4170: -- Check if API is called in debug mode. If yes, disable debug.
4171: Ahl_Debug_Pub.disable_debug;
4172: END IF;
4173: EXCEPTION
4174: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4175: ROLLBACK TO remove_material_request;
4176: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4177: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4178: p_count => x_msg_count,
4172: END IF;
4173: EXCEPTION
4174: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4175: ROLLBACK TO remove_material_request;
4176: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4177: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4178: p_count => x_msg_count,
4179: p_data => x_msg_data);
4180: IF G_DEBUG='Y' THEN
4173: EXCEPTION
4174: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4175: ROLLBACK TO remove_material_request;
4176: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4177: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4178: p_count => x_msg_count,
4179: p_data => x_msg_data);
4180: IF G_DEBUG='Y' THEN
4181: Ahl_Debug_Pub.log_app_messages (
4183: Ahl_Debug_Pub.debug( 'ahl_pp_materials_pvt. Remove Material Request ','+MAMRP+');
4184: -- Check if API is called in debug mode. If yes, disable debug.
4185: Ahl_Debug_Pub.disable_debug;
4186: END IF;
4187: WHEN Fnd_Api.G_EXC_ERROR THEN
4188: ROLLBACK TO remove_material_request;
4189: X_return_status := Fnd_Api.G_RET_STS_ERROR;
4190: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4191: p_count => x_msg_count,
4185: Ahl_Debug_Pub.disable_debug;
4186: END IF;
4187: WHEN Fnd_Api.G_EXC_ERROR THEN
4188: ROLLBACK TO remove_material_request;
4189: X_return_status := Fnd_Api.G_RET_STS_ERROR;
4190: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4191: p_count => x_msg_count,
4192: p_data => X_msg_data);
4193: IF G_DEBUG='Y' THEN
4186: END IF;
4187: WHEN Fnd_Api.G_EXC_ERROR THEN
4188: ROLLBACK TO remove_material_request;
4189: X_return_status := Fnd_Api.G_RET_STS_ERROR;
4190: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4191: p_count => x_msg_count,
4192: p_data => X_msg_data);
4193: IF G_DEBUG='Y' THEN
4194: -- Debug info.
4199: Ahl_Debug_Pub.disable_debug;
4200: END IF;
4201: WHEN OTHERS THEN
4202: ROLLBACK TO remove_material_request;
4203: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4204: IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
4205: THEN
4206: Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
4207: p_procedure_name => 'REMOVE_MATERIAL_REQUEST',
4206: Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
4207: p_procedure_name => 'REMOVE_MATERIAL_REQUEST',
4208: p_error_text => SUBSTR(SQLERRM,1,240));
4209: END IF;
4210: Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
4211: p_count => x_msg_count,
4212: p_data => X_msg_data);
4213: IF G_DEBUG='Y' THEN
4214: -- Debug info.
4229: -- Parameters :
4230: --
4231: -- Standard IN Parameters :
4232: -- p_api_version IN NUMBER Required
4233: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4234: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4235: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4236: -- p_module_type IN VARCHAR2 Default NULL.
4237: --
4230: --
4231: -- Standard IN Parameters :
4232: -- p_api_version IN NUMBER Required
4233: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4234: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4235: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4236: -- p_module_type IN VARCHAR2 Default NULL.
4237: --
4238: -- Standard OUT Parameters :
4231: -- Standard IN Parameters :
4232: -- p_api_version IN NUMBER Required
4233: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4234: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4235: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4236: -- p_module_type IN VARCHAR2 Default NULL.
4237: --
4238: -- Standard OUT Parameters :
4239: -- x_return_status OUT VARCHAR2 Required
4249: --
4250: -- End of Comments.
4251: PROCEDURE Change_Requirement_Item (
4252: p_api_version IN NUMBER,
4253: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4254: p_commit IN VARCHAR2 := Fnd_Api.g_false,
4255: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4256: p_module_type IN VARCHAR2 := 'API',
4257: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
4250: -- End of Comments.
4251: PROCEDURE Change_Requirement_Item (
4252: p_api_version IN NUMBER,
4253: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4254: p_commit IN VARCHAR2 := Fnd_Api.g_false,
4255: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4256: p_module_type IN VARCHAR2 := 'API',
4257: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
4258: x_return_status OUT NOCOPY VARCHAR2,
4251: PROCEDURE Change_Requirement_Item (
4252: p_api_version IN NUMBER,
4253: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4254: p_commit IN VARCHAR2 := Fnd_Api.g_false,
4255: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4256: p_module_type IN VARCHAR2 := 'API',
4257: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
4258: x_return_status OUT NOCOPY VARCHAR2,
4259: x_msg_count OUT NOCOPY NUMBER,
4367: 'At the start of PLSQL procedure.');
4368: END IF;
4369:
4370: -- Standard call to check for call compatibility.
4371: IF FND_API.to_boolean(p_init_msg_list)
4372: THEN
4373: FND_MSG_PUB.initialize;
4374: END IF;
4375: -- Initialize API return status to success
4372: THEN
4373: FND_MSG_PUB.initialize;
4374: END IF;
4375: -- Initialize API return status to success
4376: x_return_status := FND_API.G_RET_STS_SUCCESS;
4377: -- Initialize message list if p_init_msg_list is set to TRUE.
4378: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4379: p_api_version,
4380: l_api_name,G_PKG_NAME)
4374: END IF;
4375: -- Initialize API return status to success
4376: x_return_status := FND_API.G_RET_STS_SUCCESS;
4377: -- Initialize message list if p_init_msg_list is set to TRUE.
4378: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4379: p_api_version,
4380: l_api_name,G_PKG_NAME)
4381: THEN
4382: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4378: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4379: p_api_version,
4380: l_api_name,G_PKG_NAME)
4381: THEN
4382: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4383: END IF;
4384:
4385: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4386: fnd_log.string(fnd_log.level_statement, l_debug_key, 'requirements count'||p_x_req_material_tbl.COUNT);
4391: LOOP
4392: l_schedule_material_id := p_x_req_material_tbl(i).schedule_material_id;
4393: -- Check for Schedule Material ID
4394: IF (l_schedule_material_id IS NULL OR
4395: l_schedule_material_id = FND_API.G_MISS_NUM) THEN
4396: Fnd_Message.SET_NAME('AHL','AHL_PP_SCH_MATRL_REQUIRED');
4397: Fnd_Msg_Pub.ADD;
4398: RAISE FND_API.G_EXC_ERROR;
4399: END IF;
4394: IF (l_schedule_material_id IS NULL OR
4395: l_schedule_material_id = FND_API.G_MISS_NUM) THEN
4396: Fnd_Message.SET_NAME('AHL','AHL_PP_SCH_MATRL_REQUIRED');
4397: Fnd_Msg_Pub.ADD;
4398: RAISE FND_API.G_EXC_ERROR;
4399: END IF;
4400:
4401: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4402: fnd_log.string(fnd_log.level_statement, l_debug_key, 'Schedule Material ID'||l_schedule_material_id);
4417: IF Get_job_details_for_exe%NOTFOUND THEN
4418: Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_NOT_EXISTS');
4419: Fnd_Msg_Pub.ADD;
4420: CLOSE Get_job_details_for_exe;
4421: RAISE FND_API.G_EXC_ERROR;
4422: END IF;
4423: CLOSE Get_job_details_for_exe;
4424: ELSE
4425: -- Check for existance of valid work order
4428: IF Get_job_details%NOTFOUND THEN
4429: Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_NOT_EXISTS');
4430: Fnd_Msg_Pub.ADD;
4431: CLOSE Get_job_details;
4432: RAISE FND_API.G_EXC_ERROR;
4433: END IF;
4434: CLOSE Get_job_details;
4435: END IF;
4436:
4459: p_workorder_id => l_material_req_rec.workorder_id,
4460: p_ue_id => NULL,
4461: p_visit_id => NULL,
4462: p_item_instance_id => NULL);
4463: IF l_return_status = FND_API.G_TRUE THEN
4464: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_MTL_UNTLCKD');
4465: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
4466: FND_MSG_PUB.ADD;
4467: RAISE FND_API.G_EXC_ERROR;
4463: IF l_return_status = FND_API.G_TRUE THEN
4464: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_MTL_UNTLCKD');
4465: FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
4466: FND_MSG_PUB.ADD;
4467: RAISE FND_API.G_EXC_ERROR;
4468: END IF;
4469:
4470: --sukhwsin::Marshling Enhancements - Reseting l_return_status to S as it is used while calling AHL_EAM_JOB_PVT.process_material_req
4471: l_return_status := FND_API.G_RET_STS_SUCCESS;
4467: RAISE FND_API.G_EXC_ERROR;
4468: END IF;
4469:
4470: --sukhwsin::Marshling Enhancements - Reseting l_return_status to S as it is used while calling AHL_EAM_JOB_PVT.process_material_req
4471: l_return_status := FND_API.G_RET_STS_SUCCESS;
4472:
4473: -- Check for valid requested quantity
4474: IF (p_x_req_material_tbl(i).requested_quantity IS NULL OR (p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM)) THEN
4475: p_x_req_material_tbl(i).requested_quantity := l_material_req_rec.old_requested_quantity;
4470: --sukhwsin::Marshling Enhancements - Reseting l_return_status to S as it is used while calling AHL_EAM_JOB_PVT.process_material_req
4471: l_return_status := FND_API.G_RET_STS_SUCCESS;
4472:
4473: -- Check for valid requested quantity
4474: IF (p_x_req_material_tbl(i).requested_quantity IS NULL OR (p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM)) THEN
4475: p_x_req_material_tbl(i).requested_quantity := l_material_req_rec.old_requested_quantity;
4476: ELSIF p_x_req_material_tbl(i).requested_quantity < 0 THEN
4477: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_INVALID');
4478: Fnd_Msg_Pub.ADD;
4478: Fnd_Msg_Pub.ADD;
4479: END IF;
4480:
4481: -- Check for valid requested date
4482: IF (p_x_req_material_tbl(i).requested_date IS NULL OR (p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE)) THEN
4483: p_x_req_material_tbl(i).requested_date := l_material_req_rec.old_requested_date;
4484: ELSIF p_x_req_material_tbl(i).requested_date < trunc(SYSDATE) THEN
4485: Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_GT_EQ_SYSD');
4486: Fnd_Msg_Pub.ADD;
4489: --Standard check to count messages
4490: l_msg_count := Fnd_Msg_Pub.count_msg;
4491: IF l_msg_count > 0 THEN
4492: X_msg_count := l_msg_count;
4493: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4494: RAISE Fnd_Api.G_EXC_ERROR;
4495: END IF;
4496:
4497: -- If the inventory item id is updated.
4490: l_msg_count := Fnd_Msg_Pub.count_msg;
4491: IF l_msg_count > 0 THEN
4492: X_msg_count := l_msg_count;
4493: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4494: RAISE Fnd_Api.G_EXC_ERROR;
4495: END IF;
4496:
4497: -- If the inventory item id is updated.
4498: IF ( p_x_req_material_tbl(i).inventory_item_id <> l_material_req_rec.old_inventory_item_id ) THEN
4593: CLOSE Get_Item_Name_Cur;
4594: Fnd_Message.SET_NAME('AHL','AHL_PP_WIP_REQ_EXISTS'); -- WIP requirement already exists for the new item
4595: FND_MESSAGE.Set_Token('ITEM', l_item_name);
4596: Fnd_Msg_Pub.ADD;
4597: RAISE FND_API.G_EXC_ERROR;
4598: ELSE
4599: -- If no WIP requirement exists for the new item then create a WIP requirement.
4600: SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id FROM DUAL;
4601: --
4659: -- Call wip job api
4660: AHL_EAM_JOB_PVT.process_material_req
4661: (
4662: p_api_version => l_api_version,
4663: p_init_msg_list => Fnd_Api.g_false,
4664: p_commit => Fnd_Api.g_false,
4665: p_validation_level => p_validation_level,
4666: p_default => l_default,
4667: p_module_type => p_module_type,
4660: AHL_EAM_JOB_PVT.process_material_req
4661: (
4662: p_api_version => l_api_version,
4663: p_init_msg_list => Fnd_Api.g_false,
4664: p_commit => Fnd_Api.g_false,
4665: p_validation_level => p_validation_level,
4666: p_default => l_default,
4667: p_module_type => p_module_type,
4668: x_return_status => l_return_status,
4682: -- Call wip job api
4683: AHL_EAM_JOB_PVT.process_material_req
4684: (
4685: p_api_version => l_api_version,
4686: p_init_msg_list => Fnd_Api.g_false,
4687: p_commit => Fnd_Api.g_false,
4688: p_validation_level => p_validation_level,
4689: p_default => l_default,
4690: p_module_type => p_module_type,
4683: AHL_EAM_JOB_PVT.process_material_req
4684: (
4685: p_api_version => l_api_version,
4686: p_init_msg_list => Fnd_Api.g_false,
4687: p_commit => Fnd_Api.g_false,
4688: p_validation_level => p_validation_level,
4689: p_default => l_default,
4690: p_module_type => p_module_type,
4691: x_return_status => l_return_status,
4705: -- Call wip job api
4706: AHL_EAM_JOB_PVT.process_material_req
4707: (
4708: p_api_version => l_api_version,
4709: p_init_msg_list => Fnd_Api.g_false,
4710: p_commit => Fnd_Api.g_false,
4711: p_validation_level => p_validation_level,
4712: p_default => l_default,
4713: p_module_type => p_module_type,
4706: AHL_EAM_JOB_PVT.process_material_req
4707: (
4708: p_api_version => l_api_version,
4709: p_init_msg_list => Fnd_Api.g_false,
4710: p_commit => Fnd_Api.g_false,
4711: p_validation_level => p_validation_level,
4712: p_default => l_default,
4713: p_module_type => p_module_type,
4714: x_return_status => l_return_status,
4748: --Send Materil Notification
4749: MATERIAL_NOTIFICATION
4750: (
4751: p_api_version => p_api_version,
4752: p_init_msg_list => Fnd_Api.g_false,
4753: p_commit => Fnd_Api.g_false,
4754: p_validation_level => p_validation_level,
4755: p_Req_Material_Tbl => p_x_req_material_tbl,
4756: x_return_status => l_return_status,
4749: MATERIAL_NOTIFICATION
4750: (
4751: p_api_version => p_api_version,
4752: p_init_msg_list => Fnd_Api.g_false,
4753: p_commit => Fnd_Api.g_false,
4754: p_validation_level => p_validation_level,
4755: p_Req_Material_Tbl => p_x_req_material_tbl,
4756: x_return_status => l_return_status,
4757: x_msg_count => l_msg_count,
4761: l_msg_count := Fnd_Msg_Pub.count_msg;
4762: -- Check the return status of API call.
4763: IF (l_return_status <> 'S' OR l_msg_count > 0) THEN
4764: X_msg_count := l_msg_count;
4765: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4766: RAISE Fnd_Api.G_EXC_ERROR;
4767: END IF;
4768: --Standard check for commit
4769: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4762: -- Check the return status of API call.
4763: IF (l_return_status <> 'S' OR l_msg_count > 0) THEN
4764: X_msg_count := l_msg_count;
4765: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4766: RAISE Fnd_Api.G_EXC_ERROR;
4767: END IF;
4768: --Standard check for commit
4769: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4770: COMMIT;
4765: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4766: RAISE Fnd_Api.G_EXC_ERROR;
4767: END IF;
4768: --Standard check for commit
4769: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4770: COMMIT;
4771: END IF;
4772: -- Debug info
4773: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4774: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
4775: 'At the end of normal execution of the PLSQL procedure. Status: '||l_return_status );
4776: END IF;
4777: EXCEPTION
4778: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4779: ROLLBACK TO Change_Requirement_Item;
4780: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4781: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4782: p_count => x_msg_count,
4776: END IF;
4777: EXCEPTION
4778: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4779: ROLLBACK TO Change_Requirement_Item;
4780: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4781: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4782: p_count => x_msg_count,
4783: p_data => x_msg_data);
4784: WHEN FND_API.G_EXC_ERROR THEN
4777: EXCEPTION
4778: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4779: ROLLBACK TO Change_Requirement_Item;
4780: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4781: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4782: p_count => x_msg_count,
4783: p_data => x_msg_data);
4784: WHEN FND_API.G_EXC_ERROR THEN
4785: ROLLBACK TO Change_Requirement_Item;
4780: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4781: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4782: p_count => x_msg_count,
4783: p_data => x_msg_data);
4784: WHEN FND_API.G_EXC_ERROR THEN
4785: ROLLBACK TO Change_Requirement_Item;
4786: X_return_status := FND_API.G_RET_STS_ERROR;
4787: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4788: p_count => x_msg_count,
4782: p_count => x_msg_count,
4783: p_data => x_msg_data);
4784: WHEN FND_API.G_EXC_ERROR THEN
4785: ROLLBACK TO Change_Requirement_Item;
4786: X_return_status := FND_API.G_RET_STS_ERROR;
4787: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4788: p_count => x_msg_count,
4789: p_data => X_msg_data);
4790: WHEN OTHERS THEN
4783: p_data => x_msg_data);
4784: WHEN FND_API.G_EXC_ERROR THEN
4785: ROLLBACK TO Change_Requirement_Item;
4786: X_return_status := FND_API.G_RET_STS_ERROR;
4787: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4788: p_count => x_msg_count,
4789: p_data => X_msg_data);
4790: WHEN OTHERS THEN
4791: ROLLBACK TO Change_Requirement_Item;
4788: p_count => x_msg_count,
4789: p_data => X_msg_data);
4790: WHEN OTHERS THEN
4791: ROLLBACK TO Change_Requirement_Item;
4792: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4793: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4794: THEN
4795: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
4796: p_procedure_name => 'Change_Requirement_Item',
4795: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
4796: p_procedure_name => 'Change_Requirement_Item',
4797: p_error_text => SUBSTR(SQLERRM,1,240));
4798: END IF;
4799: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4800: p_count => x_msg_count,
4801: p_data => X_msg_data);
4802: END Change_Requirement_Item;
4803:
4812: -- Parameters :
4813: --
4814: -- Standard IN Parameters :
4815: -- p_api_version IN NUMBER Required
4816: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4817: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4818: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4819: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
4820: -- p_module_type IN VARCHAR2 Default NULL.
4813: --
4814: -- Standard IN Parameters :
4815: -- p_api_version IN NUMBER Required
4816: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4817: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4818: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4819: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
4820: -- p_module_type IN VARCHAR2 Default NULL.
4821: --
4814: -- Standard IN Parameters :
4815: -- p_api_version IN NUMBER Required
4816: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4817: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4818: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4819: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
4820: -- p_module_type IN VARCHAR2 Default NULL.
4821: --
4822: -- Standard OUT Parameters :
4815: -- p_api_version IN NUMBER Required
4816: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4817: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4818: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4819: -- p_default IN VARCHAR2 Default FND_API.G_TRUE
4820: -- p_module_type IN VARCHAR2 Default NULL.
4821: --
4822: -- Standard OUT Parameters :
4823: -- x_return_status OUT VARCHAR2 Required
4834: --
4835: -- End of Comments.
4836: PROCEDURE Process_Material_Request (
4837: p_api_version IN NUMBER,
4838: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
4839: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
4840: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
4841: p_module_type IN VARCHAR2 := NULL,
4842: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
4835: -- End of Comments.
4836: PROCEDURE Process_Material_Request (
4837: p_api_version IN NUMBER,
4838: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
4839: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
4840: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
4841: p_module_type IN VARCHAR2 := NULL,
4842: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
4843: x_return_status OUT NOCOPY VARCHAR2,
4836: PROCEDURE Process_Material_Request (
4837: p_api_version IN NUMBER,
4838: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
4839: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
4840: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
4841: p_module_type IN VARCHAR2 := NULL,
4842: p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
4843: x_return_status OUT NOCOPY VARCHAR2,
4844: x_msg_count OUT NOCOPY NUMBER,
4857: l_req_cr_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
4858: l_req_up_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
4859: l_req_re_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
4860: l_req_ci_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
4861: l_commit VARCHAR2(30) := Fnd_Api.G_FALSE;
4862:
4863: --Added by debadey for Aog notification
4864: l_asm_id_tbl AHL_MM_MTL_MGT_PVT.number_table_type;
4865: l_temp_var NUMBER;
4881: AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt.process material request','+PPMRP+');
4882: --
4883: END IF;
4884: -- Standard call to check for call compatibility.
4885: IF FND_API.to_boolean(p_init_msg_list)
4886: THEN
4887: FND_MSG_PUB.initialize;
4888: END IF;
4889: -- Initialize API return status to success
4886: THEN
4887: FND_MSG_PUB.initialize;
4888: END IF;
4889: -- Initialize API return status to success
4890: x_return_status := FND_API.G_RET_STS_SUCCESS;
4891: -- Initialize message list if p_init_msg_list is set to TRUE.
4892: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4893: p_api_version,
4894: l_api_name,G_PKG_NAME)
4888: END IF;
4889: -- Initialize API return status to success
4890: x_return_status := FND_API.G_RET_STS_SUCCESS;
4891: -- Initialize message list if p_init_msg_list is set to TRUE.
4892: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4893: p_api_version,
4894: l_api_name,G_PKG_NAME)
4895: THEN
4896: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4892: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4893: p_api_version,
4894: l_api_name,G_PKG_NAME)
4895: THEN
4896: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4897: END IF;
4898: --------------------Start of API Body-----------------------------------
4899: --debadey:logging
4900: IF G_DEBUG='Y' THEN
4973: x_return_status => l_return_status,
4974: x_msg_count => l_msg_count,
4975: x_msg_data => l_msg_data
4976: ) ;
4977: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4978: l_msg_count := FND_MSG_PUB.count_msg;
4979: IF l_msg_count > 0 THEN
4980: RAISE FND_API.G_EXC_ERROR;
4981: END IF;
4976: ) ;
4977: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4978: l_msg_count := FND_MSG_PUB.count_msg;
4979: IF l_msg_count > 0 THEN
4980: RAISE FND_API.G_EXC_ERROR;
4981: END IF;
4982: END IF;
4983: FOR i IN l_req_cr_material_tbl.First..l_req_cr_material_tbl.LAST LOOP
4984: p_x_req_material_tbl(i).SCHEDULE_MATERIAL_ID := l_req_cr_material_tbl(i).SCHEDULE_MATERIAL_ID;
5019: x_return_status => l_return_status,
5020: x_msg_count => l_msg_count,
5021: x_msg_data => l_msg_data
5022: );
5023: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5024: l_msg_count := FND_MSG_PUB.count_msg;
5025: IF l_msg_count > 0 THEN
5026: RAISE FND_API.G_EXC_ERROR;
5027: END IF;
5022: );
5023: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5024: l_msg_count := FND_MSG_PUB.count_msg;
5025: IF l_msg_count > 0 THEN
5026: RAISE FND_API.G_EXC_ERROR;
5027: END IF;
5028: END IF;
5029: END IF;
5030:
5041: x_return_status => l_return_status,
5042: x_msg_count => l_msg_count,
5043: x_msg_data => l_msg_data
5044: );
5045: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5046: l_msg_count := FND_MSG_PUB.count_msg;
5047: IF l_msg_count > 0 THEN
5048: RAISE FND_API.G_EXC_ERROR;
5049: END IF;
5044: );
5045: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5046: l_msg_count := FND_MSG_PUB.count_msg;
5047: IF l_msg_count > 0 THEN
5048: RAISE FND_API.G_EXC_ERROR;
5049: END IF;
5050: END IF;
5051: END IF;
5052:
5063: x_return_status => l_return_status,
5064: x_msg_count => l_msg_count,
5065: x_msg_data => l_msg_data
5066: );
5067: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5068: l_msg_count := FND_MSG_PUB.count_msg;
5069: IF l_msg_count > 0 THEN
5070: RAISE FND_API.G_EXC_ERROR;
5071: END IF;
5066: );
5067: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5068: l_msg_count := FND_MSG_PUB.count_msg;
5069: IF l_msg_count > 0 THEN
5070: RAISE FND_API.G_EXC_ERROR;
5071: END IF;
5072: END IF;
5073: END IF;
5074: -- Added by debadey for AOG notification and WO updation
5090: x_return_status => l_return_status,
5091: x_msg_count => l_msg_count,
5092: x_msg_data => l_msg_data
5093: );
5094: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5095: l_msg_count := FND_MSG_PUB.count_msg;
5096: IF l_msg_count > 0 THEN
5097: RAISE FND_API.G_EXC_ERROR;
5098: END IF;
5093: );
5094: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5095: l_msg_count := FND_MSG_PUB.count_msg;
5096: IF l_msg_count > 0 THEN
5097: RAISE FND_API.G_EXC_ERROR;
5098: END IF;
5099: END IF;
5100: END IF;
5101:
5103: --Standard check to count messages
5104: l_msg_count := Fnd_Msg_Pub.count_msg;
5105: IF l_msg_count > 0 THEN
5106: X_msg_count := l_msg_count;
5107: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5108: RAISE Fnd_Api.G_EXC_ERROR;
5109: END IF;
5110: --Standard check for commit
5111: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
5104: l_msg_count := Fnd_Msg_Pub.count_msg;
5105: IF l_msg_count > 0 THEN
5106: X_msg_count := l_msg_count;
5107: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5108: RAISE Fnd_Api.G_EXC_ERROR;
5109: END IF;
5110: --Standard check for commit
5111: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
5112: COMMIT;
5107: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5108: RAISE Fnd_Api.G_EXC_ERROR;
5109: END IF;
5110: --Standard check for commit
5111: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
5112: COMMIT;
5113: END IF;
5114: -- Debug info
5115: IF G_DEBUG='Y' THEN
5118: Ahl_Debug_Pub.disable_debug;
5119: --
5120: END IF;
5121: EXCEPTION
5122: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5123: ROLLBACK TO process_material_request;
5124: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5125: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5126: p_count => x_msg_count,
5120: END IF;
5121: EXCEPTION
5122: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5123: ROLLBACK TO process_material_request;
5124: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5125: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5126: p_count => x_msg_count,
5127: p_data => x_msg_data);
5128: IF G_DEBUG='Y' THEN
5121: EXCEPTION
5122: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5123: ROLLBACK TO process_material_request;
5124: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5125: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5126: p_count => x_msg_count,
5127: p_data => x_msg_data);
5128: IF G_DEBUG='Y' THEN
5129: AHL_DEBUG_PUB.log_app_messages (
5131: AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Process Material Request','+PPMRP+');
5132: -- Check if API is called in debug mode. If yes, disable debug.
5133: AHL_DEBUG_PUB.disable_debug;
5134: END IF;
5135: WHEN FND_API.G_EXC_ERROR THEN
5136: ROLLBACK TO process_material_request;
5137: X_return_status := FND_API.G_RET_STS_ERROR;
5138: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5139: p_count => x_msg_count,
5133: AHL_DEBUG_PUB.disable_debug;
5134: END IF;
5135: WHEN FND_API.G_EXC_ERROR THEN
5136: ROLLBACK TO process_material_request;
5137: X_return_status := FND_API.G_RET_STS_ERROR;
5138: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5139: p_count => x_msg_count,
5140: p_data => X_msg_data);
5141: IF G_DEBUG='Y' THEN
5134: END IF;
5135: WHEN FND_API.G_EXC_ERROR THEN
5136: ROLLBACK TO process_material_request;
5137: X_return_status := FND_API.G_RET_STS_ERROR;
5138: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5139: p_count => x_msg_count,
5140: p_data => X_msg_data);
5141: IF G_DEBUG='Y' THEN
5142: -- Debug info.
5148: --
5149: END IF;
5150: WHEN OTHERS THEN
5151: ROLLBACK TO process_material_request;
5152: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5153: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5154: THEN
5155: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
5156: p_procedure_name => 'PROCESS_MATERIAL_REQUEST',
5155: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
5156: p_procedure_name => 'PROCESS_MATERIAL_REQUEST',
5157: p_error_text => SUBSTR(SQLERRM,1,240));
5158: END IF;
5159: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5160: p_count => x_msg_count,
5161: p_data => X_msg_data);
5162: IF G_DEBUG='Y' THEN
5163: -- Debug info.
5577: -- Parameters :
5578: --
5579: -- Standard IN Parameters :
5580: -- p_api_version IN NUMBER Required
5581: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5582: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
5583: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5584: -- Based on this flag, the API will set the default attributes.
5585: -- Standard OUT Parameters :
5578: --
5579: -- Standard IN Parameters :
5580: -- p_api_version IN NUMBER Required
5581: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5582: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
5583: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5584: -- Based on this flag, the API will set the default attributes.
5585: -- Standard OUT Parameters :
5586: -- x_return_status OUT VARCHAR2 Required
5579: -- Standard IN Parameters :
5580: -- p_api_version IN NUMBER Required
5581: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5582: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
5583: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5584: -- Based on this flag, the API will set the default attributes.
5585: -- Standard OUT Parameters :
5586: -- x_return_status OUT VARCHAR2 Required
5587: -- x_msg_count OUT NUMBER Required
5593: -- List of Required materials for a job
5594: --
5595: PROCEDURE Process_Wo_Op_Materials (
5596: p_api_version IN NUMBER,
5597: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
5598: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
5599: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
5600: p_operation_flag IN VARCHAR2,
5601: p_prd_wooperation_tbl IN AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl,
5594: --
5595: PROCEDURE Process_Wo_Op_Materials (
5596: p_api_version IN NUMBER,
5597: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
5598: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
5599: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
5600: p_operation_flag IN VARCHAR2,
5601: p_prd_wooperation_tbl IN AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl,
5602: x_req_material_tbl OUT NOCOPY Req_Material_Tbl_Type,
5595: PROCEDURE Process_Wo_Op_Materials (
5596: p_api_version IN NUMBER,
5597: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
5598: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
5599: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
5600: p_operation_flag IN VARCHAR2,
5601: p_prd_wooperation_tbl IN AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl,
5602: x_req_material_tbl OUT NOCOPY Req_Material_Tbl_Type,
5603: x_return_status OUT NOCOPY VARCHAR2,
5691: --------------------Initialize ----------------------------------
5692: -- Standard Start of API savepoint
5693: SAVEPOINT Process_Wo_Op_Materials;
5694: -- Standard call to check for call compatibility.
5695: IF FND_API.to_boolean(p_init_msg_list)
5696: THEN
5697: FND_MSG_PUB.initialize;
5698: END IF;
5699: -- Initialize API return status to success
5696: THEN
5697: FND_MSG_PUB.initialize;
5698: END IF;
5699: -- Initialize API return status to success
5700: x_return_status := FND_API.G_RET_STS_SUCCESS;
5701: -- Initialize message list if p_init_msg_list is set to TRUE.
5702: IF NOT FND_API.COMPATIBLE_API_CALL(p_api_version,
5703: p_api_version,
5704: l_api_name,G_PKG_NAME)
5698: END IF;
5699: -- Initialize API return status to success
5700: x_return_status := FND_API.G_RET_STS_SUCCESS;
5701: -- Initialize message list if p_init_msg_list is set to TRUE.
5702: IF NOT FND_API.COMPATIBLE_API_CALL(p_api_version,
5703: p_api_version,
5704: l_api_name,G_PKG_NAME)
5705: THEN
5706: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5702: IF NOT FND_API.COMPATIBLE_API_CALL(p_api_version,
5703: p_api_version,
5704: l_api_name,G_PKG_NAME)
5705: THEN
5706: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5707: END IF;
5708: --
5709: IF l_prd_wooperation_tbl.COUNT > 0 THEN
5710: FOR i IN l_prd_wooperation_tbl.FIRST..l_prd_wooperation_tbl.LAST
5711: LOOP
5712: --
5713: IF (p_operation_flag = 'C' AND
5714: l_prd_wooperation_tbl(i).workorder_operation_id IS NOT NULL AND
5715: l_prd_wooperation_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM )
5716: THEN
5717: --Get visit id, visit task id
5718: OPEN Visit_Task_Cur(l_prd_wooperation_tbl(i).workorder_id);
5719: FETCH Visit_Task_Cur INTO l_Visit_Task_Rec;
5739: );
5740: END IF;
5741: --Check for one operation sequence exists means materials exist at route only
5742: IF (l_prd_wooperation_tbl(i).operation_sequence_num IS NOT NULL AND
5743: l_prd_wooperation_tbl(i).operation_sequence_num <> FND_API.G_MISS_NUM)
5744: THEN
5745: --
5746: l_idx := 0;
5747: FOR l_Sche_Mat_Rec IN Sche_Mat_Cur(l_Visit_Task_Rec.visit_task_id)
5958: LOOP
5959: --
5960: IF (p_operation_flag = 'S' AND
5961: l_prd_wooperation_tbl(i).workorder_operation_id IS NOT NULL AND
5962: l_prd_wooperation_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM AND
5963: l_prd_wooperation_tbl(i).operation_sequence_num IS NOT NULL AND
5964: l_prd_wooperation_tbl(i).operation_sequence_num <> FND_API.G_MISS_NUM)
5965: THEN
5966: --
5960: IF (p_operation_flag = 'S' AND
5961: l_prd_wooperation_tbl(i).workorder_operation_id IS NOT NULL AND
5962: l_prd_wooperation_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM AND
5963: l_prd_wooperation_tbl(i).operation_sequence_num IS NOT NULL AND
5964: l_prd_wooperation_tbl(i).operation_sequence_num <> FND_API.G_MISS_NUM)
5965: THEN
5966: --
5967: OPEN Material_Detail_Cur(l_prd_wooperation_tbl(i).workorder_operation_id,
5968: l_prd_wooperation_tbl(i).operation_sequence_num);
6006: --Standard check to count messages
6007: l_msg_count := Fnd_Msg_Pub.count_msg;
6008: IF l_msg_count > 0 THEN
6009: X_msg_count := l_msg_count;
6010: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6011: RAISE Fnd_Api.G_EXC_ERROR;
6012: END IF;
6013: --Standard check for commit
6014: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
6007: l_msg_count := Fnd_Msg_Pub.count_msg;
6008: IF l_msg_count > 0 THEN
6009: X_msg_count := l_msg_count;
6010: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6011: RAISE Fnd_Api.G_EXC_ERROR;
6012: END IF;
6013: --Standard check for commit
6014: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
6015: COMMIT;
6010: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6011: RAISE Fnd_Api.G_EXC_ERROR;
6012: END IF;
6013: --Standard check for commit
6014: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
6015: COMMIT;
6016: END IF;
6017: -- Debug info
6018: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
6023: 'At the end of PLSQL procedure'
6024: );
6025: END IF;
6026: EXCEPTION
6027: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6028: ROLLBACK TO Process_Wo_Op_Materials;
6029: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6030: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6031: p_count => x_msg_count,
6025: END IF;
6026: EXCEPTION
6027: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6028: ROLLBACK TO Process_Wo_Op_Materials;
6029: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6030: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6031: p_count => x_msg_count,
6032: p_data => x_msg_data);
6033: WHEN FND_API.G_EXC_ERROR THEN
6026: EXCEPTION
6027: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6028: ROLLBACK TO Process_Wo_Op_Materials;
6029: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6030: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6031: p_count => x_msg_count,
6032: p_data => x_msg_data);
6033: WHEN FND_API.G_EXC_ERROR THEN
6034: ROLLBACK TO Process_Wo_Op_Materials;
6029: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6030: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6031: p_count => x_msg_count,
6032: p_data => x_msg_data);
6033: WHEN FND_API.G_EXC_ERROR THEN
6034: ROLLBACK TO Process_Wo_Op_Materials;
6035: X_return_status := FND_API.G_RET_STS_ERROR;
6036: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6037: p_count => x_msg_count,
6031: p_count => x_msg_count,
6032: p_data => x_msg_data);
6033: WHEN FND_API.G_EXC_ERROR THEN
6034: ROLLBACK TO Process_Wo_Op_Materials;
6035: X_return_status := FND_API.G_RET_STS_ERROR;
6036: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6037: p_count => x_msg_count,
6038: p_data => X_msg_data);
6039: WHEN OTHERS THEN
6032: p_data => x_msg_data);
6033: WHEN FND_API.G_EXC_ERROR THEN
6034: ROLLBACK TO Process_Wo_Op_Materials;
6035: X_return_status := FND_API.G_RET_STS_ERROR;
6036: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6037: p_count => x_msg_count,
6038: p_data => X_msg_data);
6039: WHEN OTHERS THEN
6040: ROLLBACK TO Process_Wo_Op_Materials;
6037: p_count => x_msg_count,
6038: p_data => X_msg_data);
6039: WHEN OTHERS THEN
6040: ROLLBACK TO Process_Wo_Op_Materials;
6041: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6042: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6043: THEN
6044: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
6045: p_procedure_name => 'PROCESS_WO_OP_MATERIALS',
6044: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
6045: p_procedure_name => 'PROCESS_WO_OP_MATERIALS',
6046: p_error_text => SUBSTR(SQLERRM,1,240));
6047: END IF;
6048: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6049: p_count => x_msg_count,
6050: p_data => X_msg_data);
6051: END Process_Wo_Op_Materials;
6052: --
6060: -- Parameters :
6061: --
6062: -- Standard IN Parameters :
6063: -- p_api_version IN NUMBER Required
6064: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
6065: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
6066: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
6067: -- Based on this flag, the API will set the default attributes.
6068: -- Standard OUT Parameters :
6061: --
6062: -- Standard IN Parameters :
6063: -- p_api_version IN NUMBER Required
6064: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
6065: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
6066: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
6067: -- Based on this flag, the API will set the default attributes.
6068: -- Standard OUT Parameters :
6069: -- x_return_status OUT VARCHAR2 Required
6062: -- Standard IN Parameters :
6063: -- p_api_version IN NUMBER Required
6064: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
6065: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
6066: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
6067: -- Based on this flag, the API will set the default attributes.
6068: -- Standard OUT Parameters :
6069: -- x_return_status OUT VARCHAR2 Required
6070: -- x_msg_count OUT NUMBER Required
6181: END IF;
6182: -- Standard Start of API savepoint
6183: SAVEPOINT Material_Notification;
6184: -- Initialize message list if p_init_msg_list is set to TRUE.
6185: IF FND_API.to_boolean(p_init_msg_list)
6186: THEN
6187: FND_MSG_PUB.initialize;
6188: END IF;
6189: -- Initialize API return status to success
6186: THEN
6187: FND_MSG_PUB.initialize;
6188: END IF;
6189: -- Initialize API return status to success
6190: x_return_status := FND_API.G_RET_STS_SUCCESS;
6191: -- Standard call to check for call compatibility.
6192: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
6193: l_api_version,
6194: l_api_name,G_PKG_NAME)
6188: END IF;
6189: -- Initialize API return status to success
6190: x_return_status := FND_API.G_RET_STS_SUCCESS;
6191: -- Standard call to check for call compatibility.
6192: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
6193: l_api_version,
6194: l_api_name,G_PKG_NAME)
6195: THEN
6196: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6192: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
6193: l_api_version,
6194: l_api_name,G_PKG_NAME)
6195: THEN
6196: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6197: END IF;
6198: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
6199: fnd_log.string
6200: (
6229: 'After calling Ahl_Utility_Pvt.Get_Wf_Process_Name, Return Status : '|| l_return_status
6230: );
6231: END IF;
6232: -- Check Error Message stack.
6233: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
6234: RAISE FND_API.G_EXC_ERROR;
6235: END IF;
6236: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
6237: fnd_log.string
6230: );
6231: END IF;
6232: -- Check Error Message stack.
6233: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
6234: RAISE FND_API.G_EXC_ERROR;
6235: END IF;
6236: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
6237: fnd_log.string
6238: (
6296: CLOSE CursorNotify;
6297: FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_NTF_CONTENT');
6298: l_subject := fnd_message.get;
6299: l_role_name:=l_rec.approver_name;
6300: l_return_status := FND_API.G_RET_STS_SUCCESS;
6301: l_notif_id := WF_NOTIFICATION.Send
6302: ( role => l_role_name
6303: , msg_type => l_item_type
6304: , msg_name => l_message_name
6322: 'At the end of PLSQL procedure'
6323: );
6324: END IF;
6325: EXCEPTION
6326: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6327: ROLLBACK TO Material_Notification;
6328: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6329: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6330: p_count => x_msg_count,
6324: END IF;
6325: EXCEPTION
6326: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6327: ROLLBACK TO Material_Notification;
6328: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6329: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6330: p_count => x_msg_count,
6331: p_data => x_msg_data);
6332: WHEN FND_API.G_EXC_ERROR THEN
6325: EXCEPTION
6326: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6327: ROLLBACK TO Material_Notification;
6328: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6329: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6330: p_count => x_msg_count,
6331: p_data => x_msg_data);
6332: WHEN FND_API.G_EXC_ERROR THEN
6333: ROLLBACK TO MATERIAL_NOTIFICATION;
6328: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6329: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6330: p_count => x_msg_count,
6331: p_data => x_msg_data);
6332: WHEN FND_API.G_EXC_ERROR THEN
6333: ROLLBACK TO MATERIAL_NOTIFICATION;
6334: X_return_status := FND_API.G_RET_STS_ERROR;
6335: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6336: p_count => x_msg_count,
6330: p_count => x_msg_count,
6331: p_data => x_msg_data);
6332: WHEN FND_API.G_EXC_ERROR THEN
6333: ROLLBACK TO MATERIAL_NOTIFICATION;
6334: X_return_status := FND_API.G_RET_STS_ERROR;
6335: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6336: p_count => x_msg_count,
6337: p_data => X_msg_data);
6338: WHEN OTHERS THEN
6331: p_data => x_msg_data);
6332: WHEN FND_API.G_EXC_ERROR THEN
6333: ROLLBACK TO MATERIAL_NOTIFICATION;
6334: X_return_status := FND_API.G_RET_STS_ERROR;
6335: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6336: p_count => x_msg_count,
6337: p_data => X_msg_data);
6338: WHEN OTHERS THEN
6339: ROLLBACK TO Material_Notification;
6336: p_count => x_msg_count,
6337: p_data => X_msg_data);
6338: WHEN OTHERS THEN
6339: ROLLBACK TO Material_Notification;
6340: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6341: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6342: THEN
6343: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
6344: p_procedure_name => 'MATERIAL_NOTIFICATION',
6343: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
6344: p_procedure_name => 'MATERIAL_NOTIFICATION',
6345: p_error_text => SUBSTR(SQLERRM,1,240));
6346: END IF;
6347: FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
6348: p_count => x_msg_count,
6349: p_data => X_msg_data);
6350: END Material_Notification;
6351: PROCEDURE get_dff_default_values
6362: BEGIN
6363: fnd_dflex.get_flexfield('AHL', 'Material Reqmt Flex Field', flexfield, flexinfo);
6364: IF(p_req_material_rec.ATTRIBUTE_CATEGORY IS NULL)THEN
6365: flex_fields_defaults.ATTRIBUTE_CATEGORY := flexinfo.default_context_value;
6366: ELSIF (p_req_material_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR)THEN
6367: flex_fields_defaults.ATTRIBUTE_CATEGORY := NULL;
6368: ELSE
6369: flex_fields_defaults.ATTRIBUTE_CATEGORY := p_req_material_rec.ATTRIBUTE_CATEGORY;
6370: END IF;
6431: -- Parameters :
6432: --
6433: -- Standard IN Parameters :
6434: -- p_api_version IN NUMBER Required
6435: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
6436: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
6437: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
6438: -- Standard OUT Parameters :
6439: -- x_return_status OUT VARCHAR2 Required
6432: --
6433: -- Standard IN Parameters :
6434: -- p_api_version IN NUMBER Required
6435: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
6436: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
6437: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
6438: -- Standard OUT Parameters :
6439: -- x_return_status OUT VARCHAR2 Required
6440: -- x_msg_count OUT NUMBER Required
6433: -- Standard IN Parameters :
6434: -- p_api_version IN NUMBER Required
6435: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
6436: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
6437: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
6438: -- Standard OUT Parameters :
6439: -- x_return_status OUT VARCHAR2 Required
6440: -- x_msg_count OUT NUMBER Required
6441: -- x_msg_data OUT VARCHAR2 Required
6444: -- p_asm_id IN ahl_schedule_materials.schedule_material_id
6445: -- p_alternate_item_id IN New Inventory_Item_Id which will replace old inventory item.
6446: PROCEDURE Wo_Mat_Req_Item_Change(
6447: p_api_version IN NUMBER,
6448: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
6449: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
6450: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
6451: p_module_type IN VARCHAR2 := NULL,
6452: p_asm_id IN NUMBER,
6445: -- p_alternate_item_id IN New Inventory_Item_Id which will replace old inventory item.
6446: PROCEDURE Wo_Mat_Req_Item_Change(
6447: p_api_version IN NUMBER,
6448: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
6449: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
6450: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
6451: p_module_type IN VARCHAR2 := NULL,
6452: p_asm_id IN NUMBER,
6453: p_alternate_item_id IN NUMBER,
6446: PROCEDURE Wo_Mat_Req_Item_Change(
6447: p_api_version IN NUMBER,
6448: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
6449: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
6450: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
6451: p_module_type IN VARCHAR2 := NULL,
6452: p_asm_id IN NUMBER,
6453: p_alternate_item_id IN NUMBER,
6454: x_return_status OUT NOCOPY VARCHAR2,
6518: --
6519: END IF;
6520:
6521: -- Standard call to check for call compatibility.
6522: IF FND_API.to_boolean(p_init_msg_list)
6523: THEN
6524: FND_MSG_PUB.initialize;
6525: END IF;
6526: -- Initialize API return status to success
6523: THEN
6524: FND_MSG_PUB.initialize;
6525: END IF;
6526: -- Initialize API return status to success
6527: x_return_status := FND_API.G_RET_STS_SUCCESS;
6528: -- Initialize message list if p_init_msg_list is set to TRUE.
6529: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
6530: p_api_version,
6531: l_api_name,G_PKG_NAME)
6525: END IF;
6526: -- Initialize API return status to success
6527: x_return_status := FND_API.G_RET_STS_SUCCESS;
6528: -- Initialize message list if p_init_msg_list is set to TRUE.
6529: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
6530: p_api_version,
6531: l_api_name,G_PKG_NAME)
6532: THEN
6533: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6529: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
6530: p_api_version,
6531: l_api_name,G_PKG_NAME)
6532: THEN
6533: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6534: END IF;
6535: --------------------Start of Main Logic-----------------------------------
6536: --Get Existing Material Record Details
6537: OPEN Get_WO_Mat_Details(p_asm_id);
6539: IF (Get_WO_Mat_Details%NOTFOUND) THEN
6540: CLOSE Get_WO_Mat_Details;
6541: FND_MESSAGE.Set_Name('AHL', 'AHL_PP_MAT_ID_INVALID');
6542: FND_MSG_PUB.ADD;
6543: RAISE FND_API.G_EXC_ERROR;
6544: END IF;
6545: CLOSE Get_WO_Mat_Details;
6546:
6547: --Check old and new items are same or different. If same Items then Raise Error.
6547: --Check old and new items are same or different. If same Items then Raise Error.
6548: IF (p_alternate_item_id = l_current_mat_dtl_rec.inventory_item_id) THEN
6549: FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_ALT_ITEM_SAME'); --Old and New Items are same.
6550: FND_MSG_PUB.ADD;
6551: RAISE FND_API.G_EXC_ERROR;
6552: END IF;
6553:
6554: --Item validation based upon requirement type.
6555: IF (l_current_mat_dtl_rec.item_group_id IS NOT NULL) THEN
6567: --If alternate item is not a valid item in item group, then raise exception.
6568: Fnd_Message.SET_NAME('AHL',l_msg_data);
6569: FND_MESSAGE.set_token( 'FIELD', p_alternate_item_id ); -- Invalid Item 1
6570: Fnd_Msg_Pub.ADD;
6571: RAISE FND_API.G_EXC_ERROR;
6572: END IF;
6573: ELSIF(l_current_mat_dtl_rec.position_key IS NOT NULL) THEN
6574: --Perform Position based validation check.
6575: Get_item_Id_from_mc_pos
6585: --If alternate item is not a valid item in item group, then raise exception.
6586: Fnd_Message.SET_NAME('AHL',l_msg_data);
6587: FND_MESSAGE.set_token( 'FIELD', p_alternate_item_id ); -- Invalid Item 1
6588: Fnd_Msg_Pub.ADD;
6589: RAISE FND_API.G_EXC_ERROR;
6590: END IF;
6591: --Get UC associated with workorder
6592: OPEN Get_Wo_Uc_Header_Id(l_current_mat_dtl_rec.workorder_id);
6593: FETCH Get_Wo_Uc_Header_Id INTO l_uc_header_id;
6608: END IF;
6609: ELSE
6610: --Only item based requirement. In this case Raise Exception as item change is only possible for item group or position based demand.
6611: FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_IVLD_REQ_FOR_CHG'); --Item Change is not posible for Item Based requirement type.
6612: RAISE FND_API.G_EXC_ERROR;
6613: END IF;
6614: --Get primary uom of new item
6615: OPEN Get_Item_Uom_Code(p_alternate_item_id,l_current_mat_dtl_rec.organization_id);
6616: FETCH Get_Item_Uom_Code INTO l_item_name, l_uom_code;
6618: IF (l_uom_code IS NULL) THEN
6619: Fnd_Message.SET_NAME('AHL','AHL_ITEM_UOM_MISSING'); --Missing UOM at Item Setup for Item 1
6620: FND_MESSAGE.set_token( 'ITEM', l_item_name );
6621: Fnd_Msg_Pub.ADD;
6622: RAISE FND_API.G_EXC_ERROR;
6623: END IF;
6624: --After Validation of item under item group or position. call item change procedure.
6625: --Constructor table of item change
6626: l_req_ci_material_tbl.delete();
6632:
6633: Change_Requirement_Item
6634: (
6635: p_api_version => 1.0,
6636: p_init_msg_list => Fnd_Api.G_FALSE,
6637: p_commit => Fnd_Api.G_FALSE,
6638: p_validation_level => p_validation_level,
6639: p_module_type => p_module_type,
6640: p_x_req_material_tbl => l_req_ci_material_tbl,
6633: Change_Requirement_Item
6634: (
6635: p_api_version => 1.0,
6636: p_init_msg_list => Fnd_Api.G_FALSE,
6637: p_commit => Fnd_Api.G_FALSE,
6638: p_validation_level => p_validation_level,
6639: p_module_type => p_module_type,
6640: p_x_req_material_tbl => l_req_ci_material_tbl,
6641: x_return_status => l_return_status,
6641: x_return_status => l_return_status,
6642: x_msg_count => l_msg_count,
6643: x_msg_data => l_msg_data
6644: );
6645: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
6646: l_msg_count := FND_MSG_PUB.count_msg;
6647: IF l_msg_count > 0 THEN
6648: RAISE FND_API.G_EXC_ERROR;
6649: END IF;
6644: );
6645: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
6646: l_msg_count := FND_MSG_PUB.count_msg;
6647: IF l_msg_count > 0 THEN
6648: RAISE FND_API.G_EXC_ERROR;
6649: END IF;
6650: END IF;
6651:
6652: ------------------------End of Body---------------------------------------
6653: --Standard check to count messages
6654: l_msg_count := Fnd_Msg_Pub.count_msg;
6655: IF l_msg_count > 0 THEN
6656: X_msg_count := l_msg_count;
6657: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6658: RAISE Fnd_Api.G_EXC_ERROR;
6659: END IF;
6660: --Standard check for commit
6661: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
6654: l_msg_count := Fnd_Msg_Pub.count_msg;
6655: IF l_msg_count > 0 THEN
6656: X_msg_count := l_msg_count;
6657: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6658: RAISE Fnd_Api.G_EXC_ERROR;
6659: END IF;
6660: --Standard check for commit
6661: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
6662: COMMIT;
6657: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6658: RAISE Fnd_Api.G_EXC_ERROR;
6659: END IF;
6660: --Standard check for commit
6661: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
6662: COMMIT;
6663: END IF;
6664: -- Debug info
6665: IF G_DEBUG='Y' THEN
6665: IF G_DEBUG='Y' THEN
6666: Ahl_Debug_Pub.debug( 'End of api Wo_Mat_Req_Item_Change','+PPMRP+');
6667: END IF;
6668: EXCEPTION
6669: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6670: ROLLBACK TO Wo_Mat_Req_Item_Change;
6671: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6672: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6673: p_count => x_msg_count,
6667: END IF;
6668: EXCEPTION
6669: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6670: ROLLBACK TO Wo_Mat_Req_Item_Change;
6671: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6672: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6673: p_count => x_msg_count,
6674: p_data => x_msg_data);
6675: IF G_DEBUG='Y' THEN
6668: EXCEPTION
6669: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6670: ROLLBACK TO Wo_Mat_Req_Item_Change;
6671: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6672: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6673: p_count => x_msg_count,
6674: p_data => x_msg_data);
6675: IF G_DEBUG='Y' THEN
6676: AHL_DEBUG_PUB.log_app_messages (
6678: AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt.Wo_Mat_Req_Item_Change','+PPMRP+');
6679: -- Check if API is called in debug mode. If yes, disable debug.
6680: AHL_DEBUG_PUB.disable_debug;
6681: END IF;
6682: WHEN FND_API.G_EXC_ERROR THEN
6683: ROLLBACK TO Wo_Mat_Req_Item_Change;
6684: X_return_status := FND_API.G_RET_STS_ERROR;
6685: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6686: p_count => x_msg_count,
6680: AHL_DEBUG_PUB.disable_debug;
6681: END IF;
6682: WHEN FND_API.G_EXC_ERROR THEN
6683: ROLLBACK TO Wo_Mat_Req_Item_Change;
6684: X_return_status := FND_API.G_RET_STS_ERROR;
6685: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6686: p_count => x_msg_count,
6687: p_data => X_msg_data);
6688: IF G_DEBUG='Y' THEN
6681: END IF;
6682: WHEN FND_API.G_EXC_ERROR THEN
6683: ROLLBACK TO Wo_Mat_Req_Item_Change;
6684: X_return_status := FND_API.G_RET_STS_ERROR;
6685: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6686: p_count => x_msg_count,
6687: p_data => X_msg_data);
6688: IF G_DEBUG='Y' THEN
6689: -- Debug info.
6694: AHL_DEBUG_PUB.disable_debug;
6695: END IF;
6696: WHEN OTHERS THEN
6697: ROLLBACK TO Wo_Mat_Req_Item_Change;
6698: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6699: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6700: THEN
6701: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
6702: p_procedure_name => 'Wo_Mat_Req_Item_Change',
6701: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
6702: p_procedure_name => 'Wo_Mat_Req_Item_Change',
6703: p_error_text => SUBSTR(SQLERRM,1,240));
6704: END IF;
6705: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6706: p_count => x_msg_count,
6707: p_data => X_msg_data);
6708: IF G_DEBUG='Y' THEN
6709: -- Debug info.