[Home] [Help]
PACKAGE BODY: APPS.WIP_MASS_LOAD_PROCESSOR
Source
1 PACKAGE BODY WIP_MASS_LOAD_PROCESSOR AS
2 /* $Header: wipmlprb.pls 120.1.12020000.2 2013/01/25 05:51:36 sisankar ship $ */
3
4
5 PROCEDURE Delete_Completed_Records(P_Group_Id IN NUMBER) IS
6
7 -- Here, we fetch all requests that were processed successfully
8 -- or that had a kanban reference. The kanban reference is a special
9 -- case: we want these rows deleted regardless of whether they
10 -- were processed correctly.
11 cursor old_requests is
12 select rowid, interface_id, header_id
13 from wip_job_schedule_interface
14 where
15 group_id = p_group_id and
16 ((process_phase = WIP_CONSTANTS.ML_COMPLETE and
17 process_status = WIP_CONSTANTS.ML_COMPLETE))
18 for update ;
19
20 BEGIN
21
22 for old_request in old_requests loop
23
24 delete from wip_interface_errors
25 where interface_id = old_request.interface_id ;
26
27 delete from wip_job_dtls_interface
28 where group_id = p_group_id
29 and parent_header_id = old_request.header_id;
30
31 delete from wip_job_schedule_interface
32 where rowid = old_request.rowid ;
33
34 end loop ;
35
36 END Delete_Completed_Records;
37
38
39 PROCEDURE ML_Release(P_Wip_Entity_Id IN NUMBER,
40 P_Organization_Id IN NUMBER,
41 P_Class_Code IN VARCHAR2,
42 P_New_Status_Type IN NUMBER,
43 P_Success_Flag OUT NOCOPY NUMBER,
44 P_Error_Msg OUT NOCOPY VARCHAR2,
45 P_Release_Date IN Date DEFAULT NULL) IS /* 2424987 */
46 x_dummy NUMBER;
47 BEGIN
48 WIP_CHANGE_STATUS.Release
49 (P_Wip_Entity_Id,
50 P_Organization_Id,
51 NULL, NULL,
52 P_Class_Code,
53 WIP_CONSTANTS.UNRELEASED,
54 P_New_Status_Type,
55 x_dummy,
56 nvl(P_Release_Date,sysdate)); /* 2424987 */
57
58 P_Success_Flag := 1;
59 EXCEPTION
60 WHEN OTHERS THEN
61 P_Success_Flag := 0;
62 P_Error_Msg := SUBSTR(FND_MESSAGE.get,1,500);
63 END ML_Release;
64
65 PROCEDURE ML_Status_Change(P_Wip_Entity_Id IN NUMBER,
66 P_Organization_Id IN NUMBER,
67 P_Class_Code IN VARCHAR2,
68 P_New_Status_Type IN NUMBER,
69 P_Old_Status_Type IN NUMBER,
70 P_Success_Flag OUT NOCOPY NUMBER,
71 P_Error_Msg OUT NOCOPY VARCHAR2,
72 P_Release_Date IN Date DEFAULT NULL) IS /* 2424987 */
73 BEGIN
74
75 /* bug#3388658: added more combination of new and old job status
76 types instead of just P_New_Status_Type=RELEASED */
77 /* Bug 4955616. Removed WIP_CONSTANTS.CANCELLED from new status list and added to old status list*/
78 IF(P_New_Status_Type IN (WIP_CONSTANTS.RELEASED,
79 WIP_CONSTANTS.COMP_CHRG,
80 WIP_CONSTANTS.HOLD) AND
81 P_Old_Status_Type IN (WIP_CONSTANTS.UNRELEASED,
82 WIP_CONSTANTS.FAIL_BOM,
83 WIP_CONSTANTS.FAIL_ROUT,
84 WIP_CONSTANTS.PEND_SCHED,
85 WIP_CONSTANTS.CANCELLED)) THEN
86 ML_Release(P_Wip_Entity_Id,
87 P_Organization_Id,
88 P_Class_Code,
89 P_New_Status_Type,
90 P_Success_Flag,
91 P_Error_Msg,
92 nvl(P_Release_Date,sysdate)); /* 2424987 */
93
94 ELSIF(P_New_Status_Type = WIP_CONSTANTS.UNRELEASED) THEN
95 WIP_UNRELEASE.Unrelease(P_Organization_Id,
96 P_Wip_Entity_Id,
97 NULL,
98 NULL,
99 1);
100
101 END IF;
102
103 P_Success_Flag := 1;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 P_Success_Flag := 0;
108 P_Error_Msg := SUBSTR(FND_MESSAGE.get,1,500);
109 END;
110
111 PROCEDURE Raise_Sql_Error(P_Group_Id IN NUMBER) IS
112 err_num NUMBER;
113 error_text varchar2(500);
114 BEGIN
115
116 UPDATE WIP_JOB_SCHEDULE_INTERFACE
117 SET PROCESS_STATUS = WIP_CONSTANTS.ERROR
118 WHERE GROUP_ID = P_Group_Id
119 AND PROCESS_PHASE <> WIP_CONSTANTS.ML_COMPLETE;
120
121 err_num := SQLCODE;
122 error_text := SUBSTR(SQLERRM, 1, 500);
123
124 INSERT INTO WIP_INTERFACE_ERRORS(
125 interface_id,
126 error_type,
127 error,
128 last_update_date,
129 creation_date,
130 created_by,
131 last_update_login,
132 last_updated_by)
133 SELECT interface_id, 1,
134 Error_Text, sysdate, sysdate,
135 created_by, last_update_login,
136 last_updated_by
137 FROM WIP_JOB_SCHEDULE_INTERFACE
138 WHERE GROUP_ID = P_Group_Id
139 AND PROCESS_PHASE <> WIP_CONSTANTS.ML_COMPLETE;
140
141 END Raise_Sql_Error;
142
143 END WIP_MASS_LOAD_PROCESSOR;