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