[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_RESET_TRANS
Source
1 PACKAGE BODY INV_MGD_MVT_RESET_TRANS AS
2 -- $Header: INVRMVTB.pls 120.0 2005/05/25 05:39:20 appldev noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVRMVTB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| |
14 --| PROCEDURE LIST |
15 --| Reset_Transaction_Status |
16 --| |
17 --| HISTORY |
18 --| 06/12/2000 pseshadr Created |
19 --| 11/09/2001 yawang Add parameter p_reset_option |
20 --| increased version number to 2.0 |
21 --| 01/16/2002 yawang Add movement status "Pending" to support|
22 --| Reference Period |
23 --+========================================================================
24
25 --===================
26 -- GLOBALS
27 --===================
28
29 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_RESET_TRANS';
30 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_RESET_TRANS.';
31
32 --========================================================================
33 -- PROCEDURE : Reset_Transaction_Status 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 -- p_reset_option Reset Status Option
50 -- (All, Ignore Only,Exclude Ignore)
51 --
52 -- VERSION : current version 2.0
53 -- initial version 1.0
54 --
55 -- COMMENT : Procedure body to Update the Movement Status to 'O-Open',
56 -- EDI_SENT_FLAG = 'N'
57 --- for the given Input parameters.
58
59 --- UPDATED : 01/16/2002
60 --=======================================================================--
61
62 PROCEDURE Reset_Transaction_Status
63 ( p_api_version_number IN NUMBER
64 , p_init_msg_list IN VARCHAR2
65 , p_legal_entity_id IN NUMBER
66 , p_zone_code IN VARCHAR2
67 , p_usage_type IN VARCHAR2
68 , p_stat_type IN VARCHAR2
69 , p_period_name IN VARCHAR2
70 , p_document_source_type IN VARCHAR2
71 , p_reset_option IN VARCHAR2
72 , x_return_status OUT NOCOPY VARCHAR2
73 , x_msg_count OUT NOCOPY NUMBER
74 , x_msg_data OUT NOCOPY VARCHAR2
75 )
76 IS
77 l_api_version_number CONSTANT NUMBER := 2.0;
78 l_procedure_name CONSTANT VARCHAR2(30) := 'Reset_Transaction_Status';
79 BEGIN
80 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
81 THEN
82 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
83 , G_MODULE_NAME || l_procedure_name || '.begin'
84 ,'enter procedure'
85 );
86 END IF;
87
88 -- Standard call to check for call compatibility
89 IF NOT FND_API.Compatible_API_Call
90 ( l_api_version_number
91 , p_api_version_number
92 , l_procedure_name
93 , G_PKG_NAME
94 )
95 THEN
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98
99 -- Initialize message stack if required
100 IF FND_API.to_Boolean(p_init_msg_list)
101 THEN
102 FND_MSG_PUB.initialize;
103 END IF;
104
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106
107 IF p_reset_option = 'NOIGNORE'
108 THEN
109 UPDATE
110 mtl_movement_statistics
111 SET
112 movement_status = 'O'
113 , edi_sent_flag = 'N'
114 , last_updated_by = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
115 , last_update_date = SYSDATE
116 , report_reference = NULL
117 WHERE entity_org_id = p_legal_entity_id
118 AND zone_code = p_zone_code
119 AND stat_type = p_stat_type
120 AND usage_type = p_usage_type
121 AND period_name = p_period_name
122 AND document_source_type = NVL(p_document_source_type,
123 document_source_type)
124 AND movement_status NOT IN ('I', 'P');
125 ELSIF p_reset_option = 'IGNORE'
126 THEN
127 UPDATE
128 mtl_movement_statistics
129 SET
130 movement_status = 'O'
131 , edi_sent_flag = 'N'
132 , last_updated_by = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
133 , last_update_date = SYSDATE
134 , report_reference = NULL
135 WHERE entity_org_id = p_legal_entity_id
136 AND zone_code = p_zone_code
137 AND stat_type = p_stat_type
138 AND usage_type = p_usage_type
139 AND period_name = p_period_name
140 AND document_source_type = NVL(p_document_source_type,
141 document_source_type)
142 AND movement_status = 'I';
143 ELSE
144 UPDATE
145 mtl_movement_statistics
146 SET
147 movement_status = 'O'
148 , edi_sent_flag = 'N'
149 , last_updated_by = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
150 , last_update_date = SYSDATE
151 , report_reference = NULL
152 WHERE entity_org_id = p_legal_entity_id
153 AND zone_code = p_zone_code
154 AND stat_type = p_stat_type
155 AND usage_type = p_usage_type
156 AND period_name = p_period_name
157 AND document_source_type = NVL(p_document_source_type,
158 document_source_type)
159 AND movement_status <> 'P';
160 END IF;
161
162 -- Commit the Operation
163 COMMIT;
164
165 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
166 THEN
167 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
168 , G_MODULE_NAME || l_procedure_name || '.end'
169 ,'exit procedure'
170 );
171 END IF;
172 EXCEPTION
173 WHEN FND_API.G_EXC_ERROR THEN
174 x_return_status := FND_API.G_RET_STS_ERROR;
175 -- Get message count and data
176 FND_MSG_PUB.Count_And_Get
177 ( p_encoded => FND_API.G_FALSE
178 , p_count => x_msg_count
179 , p_data => x_msg_data
180 );
181
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
184 -- Get message count and data
185 FND_MSG_PUB.Count_And_Get
186 ( p_encoded => FND_API.G_FALSE
187 , p_count => x_msg_count
188 , p_data => x_msg_data
189 );
190
191 WHEN OTHERS THEN
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
193 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_Exc_Msg
196 ( G_PKG_NAME
197 , 'Reset_Transaction_Status'
198 );
199 END IF;
200 -- Get message count and data
201 FND_MSG_PUB.Count_And_Get
202 ( p_encoded => FND_API.G_FALSE
203 , p_count => x_msg_count
204 , p_data => x_msg_data
205 );
206 END Reset_Transaction_Status;
207
208 END INV_MGD_MVT_RESET_TRANS;