[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_PURGE_TRANS
Source
1 PACKAGE BODY INV_MGD_MVT_PURGE_TRANS AS
2 -- $Header: INVPURGB.pls 120.0.12010000.2 2008/10/22 13:39:38 ajmittal ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVPURGB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| |
14 --| PROCEDURE LIST |
15 --| Purge_Movement_Transactions |
16 --| |
17 --| HISTORY |
18 --| 06/12/00 pseshadr Created |
19 --+========================================================================
20
21 --===================
22 -- GLOBALS
23 --===================
24
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_PURGE_TRANS';
26 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_PURGE_TRANS.';
27 g_too_many_transactions_exc EXCEPTION;
28 g_no_data_transaction_exc EXCEPTION;
29 g_final_excp_list INV_MGD_MVT_DATA_STR.excp_list ;
30 g_trans_rec INV_MGD_MVT_DATA_STR.Trans_list;
31
32 --========================================================================
33 -- PROCEDURE : Purge_Movement_Transactions PUBLIC
34 --
35 -- PARAMETERS: x_return_status Procedure return status
36 -- x_msg_count Number of messages in the list
37 -- x_msg_data Message text
38 -- p_api_version_number Known Version Number
39 -- p_init_msg_list Empty PL/SQL Table listfor
40 -- Initialization
41 --
42 -- p_legal_entity_id Legal Entity
43 -- p_zone_code Zonal Code
44 -- p_usage_type Usage Type
45 -- p_stat_type Stat Type
46 ---- p_period_name Period Name for processing
47 -- p_document_source_type Document Source Type
48 -- (PO,SO,INV,RMA,RTV)
49 --
50 -- VERSION : current version 1.0
51 -- initial version 1.0
52 --
53 -- COMMENT : Procedure body to Update the Movement Status to 'O-Open',
54 -- EDI_SENT_FLAG = 'N'
55 --- for the given Input parameters.
56
57 --- UPDATED : 12/Jul/2000
58 --=======================================================================--
59
60 PROCEDURE Purge_Movement_Transactions
61 ( p_api_version_number IN NUMBER
62 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
63 , p_legal_entity_id IN NUMBER
64 , p_zone_code IN VARCHAR2
65 , p_usage_type IN VARCHAR2
66 , p_stat_type IN VARCHAR2
67 , p_period_name IN VARCHAR2
68 , p_document_source_type IN VARCHAR2
69 , x_return_status OUT NOCOPY VARCHAR2
70 , x_msg_count OUT NOCOPY NUMBER
71 , x_msg_data OUT NOCOPY VARCHAR2
72 )
73 IS
74 l_api_version_number CONSTANT NUMBER := 1.0;
75 l_procedure_name CONSTANT VARCHAR2(30) := 'Purge_Movement_Transactions';
76
77 BEGIN
78 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
79 THEN
80 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
81 , G_MODULE_NAME || l_procedure_name || '.begin'
82 ,'enter procedure'
83 );
84 END IF;
85
86 -- Standard call to check for call compatibility
87 IF NOT FND_API.Compatible_API_Call
88 ( l_api_version_number
89 , p_api_version_number
90 , l_procedure_name
91 , G_PKG_NAME
92 )
93 THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96
97 -- Initialize message stack if required
98 IF FND_API.to_Boolean(p_init_msg_list)
99 THEN
100 FND_MSG_PUB.initialize;
101 END IF;
102
103 x_return_status := FND_API.G_RET_STS_SUCCESS;/*bug #7499719 'Y' was hardcoded changed to standard*/
104
105 DELETE FROM MTL_MOVEMENT_STATISTICS
106 WHERE entity_org_id = p_legal_entity_id
107 AND upper(stat_type) like nvl(upper(p_stat_type),'%')
108 AND upper(usage_type) like nvl(upper(p_usage_type),'%')
109 AND upper(zone_code) like nvl(upper(p_zone_code),'%')
110 AND upper(period_name) = upper(p_period_name)
111 AND upper(document_source_type)
112 like nvl(upper(p_document_source_type),'%')
113 AND (MOVEMENT_STATUS IN ('F','I','X')
114 OR EDI_SENT_FLAG = 'Y')
115 ;
116
117
118 -- Commit the Operation
119 COMMIT;
120
121 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
122 THEN
123 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
124 , G_MODULE_NAME || l_procedure_name || '.end'
125 ,'exit procedure'
126 );
127 END IF;
128 EXCEPTION
129
130 WHEN NO_DATA_FOUND THEN
131 x_return_status := FND_API.G_RET_STS_ERROR;/*bug #7499719 'Y' was hardcoded changed to standard*/
132 FND_MESSAGE.Set_Name('INV', 'INV_NO_DATA_TRANSACTIONS');
133 FND_MSG_PUB.Add;
134 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
135 THEN
136 FND_MSG_PUB.Add_Exc_Msg
137 ( G_PKG_NAME
138 , 'Update_Movement_Status'
139 );
140 END IF;
141 RAISE g_no_data_transaction_exc;
142
143 WHEN TOO_MANY_ROWS THEN
144 x_return_status :=FND_API.G_RET_STS_ERROR;/*bug #7499719 'Y' was hardcoded changed to standard*/
145 FND_MESSAGE.Set_Name('INV', 'INV_TOO_MANY_TRANSACTIONS');
146 FND_MSG_PUB.Add;
147 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
148 THEN
149 FND_MSG_PUB.Add_Exc_Msg
150 ( G_PKG_NAME
151 , 'Update_Movement_Status'
152 );
153 END IF;
154 RAISE g_too_many_transactions_exc;
155
156 WHEN FND_API.G_EXC_ERROR THEN
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 -- Get message count and data
159 FND_MSG_PUB.Count_And_Get
160 ( p_count => x_msg_count
161 , p_data => x_msg_data
162 );
163
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
166 -- Get message count and data
167 FND_MSG_PUB.Count_And_Get
168 ( p_count => x_msg_count
169 , p_data => x_msg_data
170 );
171
172 WHEN OTHERS THEN
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 FND_MSG_PUB.Add_Exc_Msg
177 ( G_PKG_NAME
178 , 'Purge_Movement_Transactions'
179 );
180 END IF;
181 -- Get message count and data
182 FND_MSG_PUB.Count_And_Get
183 ( p_count => x_msg_count
184 , p_data => x_msg_data
185 );
186
187 END Purge_Movement_Transactions;
188
189 END INV_MGD_MVT_PURGE_TRANS;