[Home] [Help]
PACKAGE BODY: APPS.INV_SHORTCHECKPROCESSTRX_PVT
Source
1 PACKAGE BODY INV_ShortCheckProcessTrx_PVT AS
2 /* $Header: INVSPPVB.pls 120.2 2006/03/15 15:05:21 somanaam ship $*/
3 -- Start OF comments
4 -- API name : ProcessTransactions
5 -- TYPE : Private
6 -- Pre-reqs : None
7 -- FUNCTION :
8 -- Parameters:
9 -- IN :
10 --
11 -- OUT :
12 --
13 -- ERRBUF OUT VARCHAR2
14 -- Error code
15 --
16 -- RETCODE OUT NUMBER
17 -- Return completion status
18 --
19 -- Version: Current Version 1.0
20 -- Changed : Nothing
21 -- No Previous Version 0.0
22 -- Initial version 1.0
23 -- Notes :
24 -- END OF comments
25 PROCEDURE ProcessTransactions (
26 ERRBUF OUT NOCOPY VARCHAR2,
27 RETCODE OUT NOCOPY NUMBER
28 )
29 IS
30 --
31 L_return_status VARCHAR2(1);
32 L_msg_count NUMBER;
33 L_msg_data VARCHAR2(2000);
34 L_check_result VARCHAR2(1);
35 L_seq_num NUMBER;
36 L_conc_status BOOLEAN;
37 --
38 CURSOR L_SelectTrx_csr (l_days in number )IS
39 SELECT /*+ INDEX (mmt mtl_material_transactions_n5) */
40 organization_id,
41 inventory_item_id
42 FROM mtl_material_transactions
43 WHERE shortage_process_code = 1
44 and transaction_date > (sysdate - l_days )
45 GROUP BY organization_id,
46 inventory_item_id;
47 --
48 L_SelectTrx_rec L_SelectTrx_csr%ROWTYPE;
49 --
50 -- This cursor is to select the most recent transaction for an item
51 -- If multiple transactions have the same date, the transaction with the
52 -- higher quantity is choosen
53 CURSOR L_LastTrx_csr ( p_inventory_item_id IN NUMBER,
54 p_organization_id IN NUMBER ) IS
55 SELECT primary_quantity
56 FROM mtl_material_transactions
57 WHERE inventory_item_id = p_inventory_item_id
58 AND organization_id = p_organization_id
59 AND shortage_process_code = 1
60 ORDER BY transaction_date DESC,
61 primary_quantity;
62 --
63 L_LastTrx_rec L_LastTrx_csr%ROWTYPE;
64 --
65 l_days number := 30;
66 --
67 PROCEDURE UpdateTrx ( p_organization_id IN NUMBER,
68 p_inventory_item_id IN NUMBER,
69 p_shortage_process_code IN NUMBER )
70 IS
71 BEGIN
72 UPDATE mtl_material_transactions
73 SET shortage_process_code = p_shortage_process_code
74 WHERE organization_id = p_organization_id
75 AND inventory_item_id = p_inventory_item_id
76 AND shortage_process_code = 1;
77 END UpdateTrx;
78 --
79 BEGIN
80 -- Initialize RETCODE
81 RETCODE := 0;
82 -- Get the items that have to be checked from material transactions table
83 OPEN L_SelectTrx_csr(l_days);
84 LOOP
85 BEGIN
86 FETCH L_SelectTrx_csr INTO L_SelectTrx_rec;
87 EXIT WHEN L_SelectTrx_csr%NOTFOUND;
88 --
89 -- Since we only want to check the most recent transaction for an
90 -- item/org combination we open another cursor for the last transaction
91 -- and fetch just the first record
92 OPEN L_LastTrx_csr ( L_SelectTrx_rec.inventory_item_id,
93 L_SelectTrx_rec.organization_id );
94 FETCH L_LastTrx_csr INTO L_LastTrx_rec;
95 -- Call the shortage check procedure
96 INV_ShortCheckExec_PVT.ExecCheck (
97 p_api_version => 1.0,
98 p_init_msg_list => FND_API.G_TRUE,
99 p_commit => FND_API.G_TRUE,
100 x_return_status => L_return_status,
101 x_msg_count => L_msg_count,
102 x_msg_data => L_msg_data,
103 p_sum_detail_flag => 1,
104 p_organization_id => L_SelectTrx_rec.organization_id,
105 p_inventory_item_id => L_SelectTrx_rec.inventory_item_id,
106 p_comp_att_qty_flag => 1,
107 p_primary_quantity => L_LastTrx_rec.primary_quantity,
108 x_seq_num => L_seq_num,
109 x_check_result => L_check_result
110 );
111 --
112 -- If an error has occured set shortage process code to error,
113 -- write error msg data to ERRBUF and to log file and set RETCODE
114 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
115 UpdateTrx ( L_SelectTrx_rec.organization_id,
116 L_SelectTrx_rec.inventory_item_id,
117 3 );
118 ERRBUF := L_msg_data;
119 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
120 RETCODE := 1;
121 --
122 -- Else call send notifications proc if shortage has been detected
123 ELSE
124 IF L_check_result = FND_API.G_TRUE THEN
125 INV_ShortCheckExec_PVT.SendNotifications (
126 p_api_version => 1.0,
127 p_init_msg_list => FND_API.G_TRUE,
128 p_commit => FND_API.G_TRUE,
129 x_return_status => L_return_status,
130 x_msg_count => L_msg_count,
131 x_msg_data => L_msg_data,
132 p_organization_id => L_SelectTrx_rec.organization_id,
133 p_inventory_item_id => L_SelectTrx_rec.inventory_item_id,
134 p_seq_num => L_seq_num,
135 p_notification_type => 'R'
136 );
137 -- If an error has occured set shortage process code to error,
138 -- write error msg data to ERRBUF and to log file and set RETCODE
139 IF L_return_status <> FND_API.G_RET_STS_SUCCESS THEN
140 UpdateTrx ( L_SelectTrx_rec.organization_id,
141 L_SelectTrx_rec.inventory_item_id,
142 3 );
143 ERRBUF := L_msg_data;
144 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
145 RETCODE := 1;
146 --
147 ELSE
148 -- Update checked rows in material transactions table
149 UpdateTrx ( L_SelectTrx_rec.organization_id,
150 L_SelectTrx_rec.inventory_item_id,
151 2 );
152 END IF;
153 ELSE
154 -- Update checked rows in material transactions table
155 UpdateTrx ( L_SelectTrx_rec.organization_id,
156 L_SelectTrx_rec.inventory_item_id,
157 2 );
158 END IF;
159 END IF;
160 --
161 -- Purge the rows from the temp table (if there are any)
162 INV_ShortCheckExec_PVT.PurgeTempTable (
163 p_api_version => 1.0,
164 p_init_msg_list => FND_API.G_TRUE,
165 p_commit => FND_API.G_TRUE,
166 x_return_status => L_return_status,
167 x_msg_count => L_msg_count,
168 x_msg_data => L_msg_data,
169 p_seq_num => L_seq_num
170 );
171 --
172 CLOSE L_LastTrx_csr;
173 --
174 EXCEPTION
175 WHEN OTHERS THEN
176 -- If an error has occured set shortage process code to error,
177 UpdateTrx ( L_SelectTrx_rec.organization_id,
178 L_SelectTrx_rec.inventory_item_id,
179 3 );
180 --
181 -- write error msg data to ERRBUF and to log file and set RETCODE
182 ERRBUF := TO_CHAR(SQLCODE);
183 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
184 RETCODE := 1;
185 --
186 -- and purge the rows from the temp table (if there are any)
187 INV_ShortCheckExec_PVT.PurgeTempTable (
188 p_api_version => 1.0,
189 p_init_msg_list => FND_API.G_TRUE,
190 p_commit => FND_API.G_TRUE,
191 x_return_status => L_return_status,
192 x_msg_count => L_msg_count,
193 x_msg_data => L_msg_data,
194 p_seq_num => L_seq_num
195 );
196 END;
197 --
198 COMMIT;
199 END LOOP;
200 CLOSE L_SelectTrx_csr;
201 --
202 -- Set completion status and retcode
203 IF RETCODE = 0 THEN
204 L_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS ('NORMAL','');
205 ELSIF RETCODE = 1 THEN
206 L_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS ('WARNING','');
207 END IF;
208 --
209 END ProcessTransactions;
210 END INV_ShortCheckProcessTrx_PVT;