1 PACKAGE BODY PN_PURGE_INTERFACE_PKG AS
2 -- $Header: PNVPURGB.pls 120.0 2005/05/29 12:32:12 appldev noship $
3
4 l_Debug VarChar2(1) := 'N';
5
6 -------------------------------------------------------------------
7 -- PROCEDURE Purge_CAD
8 -------------------------------------------------------------------
9 PROCEDURE PURGE_CAD ( errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY NUMBER,
11 function_flag VARCHAR2,
12 p_batch_name VARCHAR2) IS
13
14 BEGIN
15
16 fnd_file.put_names('PNVPURGE.log', 'PNVPURGE.out', '/sqlcom/out');
17
18 if (l_Debug = 'Y') Then
19
20 fnd_message.set_name ('PN','PN_BATCH_NAME');
21 fnd_message.set_token ('NAME',p_batch_name);
22 pnp_debug_pkg.put_log_msg(fnd_message.get);
23
24 fnd_message.set_name ('PN','PN_VPURG_FN_FLAG');
25 fnd_message.set_token ('FLAG',function_flag);
26 pnp_debug_pkg.put_log_msg(fnd_message.get);
27
28 End If;
29
30 IF (function_flag = 'L') then
31 BEGIN
32 delete_locations(p_batch_name);
33 EXCEPTION
34 when OTHERS then
35 APP_EXCEPTION.raise_exception;
36 END;
37
38 ELSIF (function_flag = 'S') then
39 BEGIN
40 delete_space_allocations(p_batch_name);
41 EXCEPTION
42 when OTHERS then
43 APP_EXCEPTION.raise_exception;
44 END;
45
46 ELSIF (function_flag = 'A') then
47 BEGIN
48 delete_locations(p_batch_name);
49 pnp_debug_pkg.put_log_msg('=======================================================');
50 delete_space_allocations(p_batch_name);
51 EXCEPTION
52 when OTHERS then
53 APP_EXCEPTION.raise_exception;
54 END;
55
56 END IF;
57
58 END PURGE_CAD;
59
60
61 -----------------------------------------------------------------------
62 -- Procedure Delete_Locations
63 -----------------------------------------------------------------------
64 PROCEDURE Delete_Locations ( p_Batch_Name VARCHAR2) IS
65
66 l_Count Number;
67
68 BEGIN
69 IF P_BATCH_NAME is not null then /* IF and ELSIF has been added for the BUG#1850937 */
70 Select Count(*)
71 Into l_Count
72 From PN_LOCATIONS_ITF
73 Where Batch_Name = p_Batch_Name ;
74 ELSIF P_BATCH_NAME IS NULL then
75 Select Count(*)
76 Into l_Count
77 From PN_LOCATIONS_ITF;
78 END IF;
79
80
81 If (l_Debug = 'Y') Then
82 pnp_debug_pkg.log('About to Delete from PN_LOCATIONS_ITF ...');
83 End If;
84 IF P_BATCH_NAME is not null then /* IF and ELSIF has been added for the BUG#1850937 */
85 Delete From PN_LOCATIONS_ITF
86 Where Batch_Name = p_Batch_Name ;
87 ELSIF P_BATCH_NAME IS NULL then
88 Delete From PN_LOCATIONS_ITF;
89 END IF;
90
91 pnp_debug_pkg.log('Table: PN_LOCATIONS_ITF');
92
93 fnd_message.set_name ('PN','PN_BATCH_NAME');
94 fnd_message.set_token ('NAME',p_batch_name);
95 pnp_debug_pkg.put_log_msg(fnd_message.get);
96
97 fnd_message.set_name ('PN','PN_VPURG_ROWS_DEL');
98 fnd_message.set_token ('NUM',l_Count);
99 pnp_debug_pkg.put_log_msg(fnd_message.get);
100
101 EXCEPTION
102 When Others Then
103 Raise;
104
105 END delete_locations;
106
107
108 -----------------------------------------------------------------------
109 -- Procedure Delete_Space_Allocations
110 -----------------------------------------------------------------------
111 PROCEDURE Delete_Space_Allocations ( p_Batch_Name VARCHAR2) IS
112
113 l_Count Number;
114
115 BEGIN
116 IF P_BATCH_NAME is not null then /* IF and ELSIF has been added for the BUG#1850937 */
117 Select Count(*)
118 Into l_Count
119 From PN_EMP_SPACE_ASSIGN_ITF
120 Where Batch_Name = p_Batch_Name ;
121 ELSIF P_BATCH_NAME IS NULL then
122 Select Count(*)
123 Into l_Count
124 From PN_EMP_SPACE_ASSIGN_ITF;
125 END IF;
126 If (l_Debug = 'Y') Then
127 pnp_debug_pkg.log('About to Delete from PN_EMP_SPACE_ASSIGN_ITF ...');
128 End If;
129
130 IF P_BATCH_NAME is not null then /* IF and ELSIF has been added for the BUG#1850937 */
131 Delete From PN_EMP_SPACE_ASSIGN_ITF
132 Where Batch_Name = p_Batch_Name ;
133 ELSIF P_BATCH_NAME is null then
134 Delete From PN_EMP_SPACE_ASSIGN_ITF;
135 END IF;
136
137 pnp_debug_pkg.log('Table: PN_EMP_SPACE_ASSIGN_ITF');
138 fnd_message.set_name ('PN','PN_BATCH_NAME');
139 fnd_message.set_token ('NAME',p_batch_name);
140 pnp_debug_pkg.put_log_msg(fnd_message.get);
141
142 fnd_message.set_name ('PN','PN_VPURG_ROWS_DEL');
143 fnd_message.set_token ('NUM',l_Count);
144 pnp_debug_pkg.put_log_msg(fnd_message.get);
145
146 EXCEPTION
147 When Others Then
148 Raise;
149
150 END Delete_Space_Allocations ;
151
152
153
154 -----------------------------------------------------------------------
155 -- End of Package
156 -----------------------------------------------------------------------
157 END PN_PURGE_INTERFACE_PKG;