1 PACKAGE CST_PAC_ITERATION_PROCESS_PVT AUTHID CURRENT_USER AS
2 -- $Header: CSTVIIPS.pls 120.16.12020000.1 2012/06/26 07:15:08 appldev ship $
3
4 -- API Name : Iteration_Process
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_init_msg_list IN VARCHAR2
10 -- p_validation_level IN NUMBER
11 -- p_legal_entity_id IN NUMBER
12 -- p_cost_type_id IN NUMBER
13 -- p_cost_method IN NUMBER
14 -- p_iteration_proc_flag IN VARCHAR2
15 -- p_period_id IN NUMBER
16 -- p_start_date IN DATE
17 -- p_end_date IN DATE
18 -- p_inventory_item_id IN NUMBER
19 -- p_inventory_item_number IN VARCHAR2(1025)
20 -- p_tolerance IN NUMBER
21 -- p_iteration_num IN NUMBER
22 -- p_run_options IN NUMBER
23 -- p_pac_rates_id IN NUMBER
24 -- p_uom_control IN NUMBER
25 -- p_user_id IN NUMBER
26 -- p_login_id IN NUMBER
27 -- p_req_id IN NUMBER
28 -- p_prg_id IN NUMBER
29 -- p_prg_appid IN NUMBER
30 -- OUT : x_return_status OUT VARCHAR2(1)
31 -- x_msg_count OUT NUMBER
32 -- x_msg_data OUT VARCHAR2(2000)
33 -- Version : Current Version : 1.0
34 -- Initial version 1.0
35 -- Notes :
36 -- +========================================================================+
37
38 -- +========================================================================+
39 -- GLOBAL CONSTANTS
40 -- +========================================================================+
41 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PAC_ITERATION_PROCESS_PVT';
42
43 -- +========================================================================+
44 -- PL/SQL table g_cst_group_tbl
45 -- This table contains all the valid Cost Groups for the user entered
46 -- Legal Entity
47 -- period new quantity is the total quantity of all cost owned transactions
48 -- upto interorg receipts across Cost groups i.e. upto txn category 8
49 -- +========================================================================+
50 TYPE cst_group_rec_type IS RECORD
51 ( cost_group_id NUMBER
52 , cost_group VARCHAR2(10)
53 , master_organization_id NUMBER
54 , period_new_quantity NUMBER
55 );
56
57 TYPE g_cst_group_table_type IS TABLE OF cst_group_rec_type
58 INDEX BY PLS_INTEGER;
59
60 G_CST_GROUP_TBL g_cst_group_table_type;
61
62 -- +========================================================================+
63 -- PL/SQL table g_cst_group_org_tbl
64 -- This table contains all the valid organizations in each Cost Group
65 -- +========================================================================+
66 TYPE cst_group_org_rec_type IS RECORD
67 ( cost_group_id NUMBER
68 , organization_id NUMBER
69 );
70
71 TYPE g_cst_group_org_table_type IS TABLE OF cst_group_org_rec_type
72 INDEX BY PLS_INTEGER;
73
74 G_CST_GROUP_ORG_TBL g_cst_group_org_table_type;
75
76 -- +========================================================================+
77 -- PL/SQL table g_pwac_new_cost_tbl
78 -- This table contains :
79 -- balance before interorg txns, qty before interorg txns,
80 -- period new balance of all interorg receipts, final new cost of the
81 -- cost element and level type
82 -- for a given inventory item id, cost group id and pac period id
83 -- This table is used get the final new cost which is assigned to all the
84 -- corresponding group 2 (cost derived) transactions in a given item id,
85 -- cost group and pac period.
86 -- period_new_quantity is same for all the cost elements, level in a given
87 -- item,cost group and pac period
88 -- This pl/sql table is flushed for each optimal cost group
89 -- +========================================================================+
90 TYPE pwac_new_cost_rec_type IS RECORD
91 ( final_new_cost NUMBER
92 , period_new_balance NUMBER
93 , period_new_quantity NUMBER
94 , period_bal_bef_intorg NUMBER
95 , period_qty_bef_intorg NUMBER
96 );
97
98 TYPE g_pwac_new_cost_table_type IS TABLE OF pwac_new_cost_rec_type INDEX BY PLS_INTEGER;
99 TYPE g_pwac_cost_table_type IS TABLE OF g_pwac_new_cost_table_type INDEX BY PLS_INTEGER;
100 G_PWAC_NEW_COST_TBL g_pwac_cost_table_type;
101
102
103 -- +========================================================================+
104 -- PL/SQL table g_cg_pwac_cost_tbl
105 -- This table contains final new cost of the cost element and level type
106 -- for a given inventory item id, cost group id and pac period id
107 -- This table is used to update cpicd.item_cost, item_balance for each
108 -- cost group
109 -- The table has the same record structure as that of pwac_new_cost_rec_type
110 -- This pl/sql table is flushed after Nth iteration, after updating cpicd
111 -- The pl/sql table is flushed in the procedure Update_Cpicd_With_New_Values
112 -- +========================================================================+
113
114 G_CG_PWAC_COST_TBL g_pwac_new_cost_table_type;
115
116 -- +========================================================================+
117 -- This pl/sql table is used to store interorg items in the current BOM level
118 -- considering the highest BOM level across cost groups
119 -- This pl/sql table is flushed before each BOM level
120 -- Item Id itself is the index
121 -- +========================================================================+
122 TYPE interorg_item_level_rec_type IS RECORD
123 (inventory_item_id NUMBER
124 ,bom_high_level_code NUMBER
125 );
126
127 TYPE g_interorg_item_level_tab_type IS TABLE OF interorg_item_level_rec_type
128 INDEX BY PLS_INTEGER;
129
130 G_INTERORG_ITEM_LEVEL_TBL g_interorg_item_level_tab_type;
131
132
133 -- +========================================================================+
134 -- This pl/sql table is used to store PCU value change period balance if any
135 -- for each cost group, cost_element, level_type for a given interorg item
136 -- This table is flushed after Nth iteration, after computing new periodic
137 -- balance
138 -- This table is accessed with combination of cost_group_id,cost_element_id,
139 -- level_type
140 -- +========================================================================+
141 TYPE g_pcu_value_bal_rec_type IS RECORD
142 (pcu_value_balance NUMBER
143 ,cost_group_id NUMBER
144 ,cost_element_id NUMBER
145 ,level_type NUMBER
146 );
147
148 TYPE g_pcu_value_bal_tab_type IS TABLE OF g_pcu_value_bal_rec_type
149 INDEX BY PLS_INTEGER;
150
151 G_PCU_VALUE_CHANGE_TBL g_pcu_value_bal_tab_type;
152
153
154 G_TOL_ACHIEVED_MESSAGE VARCHAR2(255);
155 G_TOL_NOT_ACHIEVED_MESSAGE VARCHAR2(255);
156
157 -- +========================================================================+
158 -- PROCEDURES AND FUNCTIONS
159 -- +========================================================================+
160
161 -- +========================================================================+
162 -- FUNCTION: Check_Cst_Group Local Utility
163 -- PARAMETERS:
164 -- p_cost_group_id user input
165 -- COMMENT:
166 -- Take p_cost_group_id and look in the PL/SQL table g_cst_group_tbl.
167 -- A return value 'Y' means that the cost group id belongs to user entered
168 -- legal entity and therefore its a valid cost group.
169 -- A return value 'N' means that the cost group is not valid since it is not
170 -- belong to Legal Entity
171 -- USAGE: This function is used within the SQL
172 -- PRE-COND: none
173 -- EXCEPTIONS: none
174 -- +========================================================================+
175 FUNCTION Check_Cst_Group
176 ( p_cost_group_id IN NUMBER
177 )
178 RETURN VARCHAR2;
179
180 -- +========================================================================+
181 -- FUNCTION: Check_Cst_Group_Org Local Utility
182 -- PARAMETERS:
183 -- p_organization_id
184 -- COMMENT:
185 -- Take p_organization_id and look in the PL/SQL table l_cst_group_org_tbl.
186 -- A return value 'Y' means that the organization id belongs to one of the
187 -- valid cost group in legal entity
188 -- A return value 'N' means that the organization id is NOT belong to
189 -- valid cost group
190 -- USAGE: This function is used within the SQL
191 -- PRE-COND: none
192 -- EXCEPTIONS: none
193 -- +========================================================================+
194 FUNCTION Check_Cst_Group_Org
195 ( p_organization_id IN NUMBER
196 )
197 RETURN VARCHAR2;
198
199
200 -- +========================================================================+
201 -- FUNCTION: Get_Cost_Group Local Utility
202 -- PARAMETERS:
203 -- p_organization_id IN NUMBER
204 -- COMMENT:
205 -- Get Cost Group of the corresponding p_organization_id
206 -- USAGE: This function is used in the sql cursor
207 -- PRE-COND: none
208 -- EXCEPTIONS: none
209 -- +========================================================================+
210 FUNCTION Get_Cost_Group
211 ( p_organization_id IN NUMBER
212 )
213 RETURN NUMBER;
214
215 -- +========================================================================+
216 -- PROCEDURE: Initialize
217 -- PARAMETERS:
218 -- p_legal_entity_id IN NUMBER
219 -- COMMENT:
220 -- This procedure is to initialize Global PL/SQL tables
221 -- G_CST_GROUP_TBL to store valid Cost Groups in Legal Entity
222 -- G_CST_GROUP_ORG_TBL to store valid organizations in those cost groups
223 -- This procedure is called by the API CST_INTERORG_TRANSFER_PROC
224 -- PRE-COND: none
225 -- EXCEPTIONS: none
226 -- +========================================================================+
227 PROCEDURE Initialize
228 ( p_legal_entity_id IN NUMBER
229 );
230
231
232 -- +========================================================================+
233 -- PROCEDURE: Set_Phase5_Status
234 -- PARAMETERS:
235 -- p_legal_entity_id NUMBER Legal Entity
236 -- p_cost_group_id NUMBER Valid Cost Group in LE
237 -- p_period_id NUMBER PAC Period Id
238 -- p_phase_status NUMBER
239 -- Not Applicable(0)
240 -- Un Processed (1)
241 -- Running (2)
242 -- Error (3)
243 -- Complete (4)
244 -- COMMENT:
245 -- This procedure sets the phase 5 status to Un Processed (1)
246 -- at the end of final iteration or when the tolerance is achieved
247 --
248 -- USAGE: This procedure is invoked from api:iteration_process
249 -- PRE-COND: none
250 -- EXCEPTIONS: none
251 -- +========================================================================+
252 PROCEDURE Set_Phase5_Status(p_legal_entity_id IN NUMBER
253 ,p_period_id IN NUMBER
254 ,p_period_end_date IN DATE
255 ,p_phase_status IN NUMBER
256 );
257
258
259 -- +========================================================================+
260 -- PROCEDURE: Set_Process_Status
261 -- PARAMETERS:
262 -- p_legal_entity_id NUMBER Legal Entity
263 -- p_period_id NUMBER PAC Period Id
264 -- p_period_end_date DATE
265 -- p_phase_status NUMBER
266 -- Not Applicable(0)
267 -- Un Processed (1)
268 -- Running (2)
269 -- Error (3)
270 -- Complete (4)
271 -- Resume (5) used when non-tolerance items exists
272 -- COMMENT:
273 -- This procedure sets the Interorg Transfer Cost Processor - iteration
274 -- process phase status. The phase will be 7. When the iteration process
275 -- is invoked through main program, the phase status will be set to 1
276 -- to start with indicating that the status is in Un Processed.
277 -- When the iteration process begins, the phase status will be set to 2
278 -- indicating that the status is in Running for all the valid cost groups
279 -- in the Legal Entity
280 -- If the iteration process completed with error the status is 3
281 -- If the iteration process completed where all the items achieved
282 -- tolerance, then the status is set to 4 - Complete.
283 -- If the iteration process completed where some of the items are left over
284 -- with no tolerance achieved AND the resume option is Iteration for non
285 -- tolerance items, then the status is set to 5 indicating that the
286 -- status is in Resume where the process is not completed yet.
287 -- If the iteration process completed where some of the items are left over
288 -- with no tolerance achieved AND the resume option is Final Iteration, then
289 -- the status is set to 4 - Complete indicating that the Iteration Process
290 -- is completed.
291 --
292 -- USAGE: This procedure is invoked from api:iteration_process
293 -- PRE-COND: none
294 -- EXCEPTIONS: none
295 -- +========================================================================+
296 PROCEDURE Set_Process_Status( p_legal_entity_id IN NUMBER
297 , p_period_id IN NUMBER
298 , p_period_end_date IN DATE
299 , p_phase_status IN NUMBER
300 );
301
302 -- +========================================================================+
303 -- PROCEDURE: Populate_Temp_Tables
304 -- PARAMETERS:
305 -- p_cost_group_id IN NUMBER
306 -- p_period_id IN NUMBER
307 -- p_period_start_date IN DATE
308 -- p_period_end_date IN DATE
309 -- COMMENT:
310 -- This procedure is called by the Iterative PAC Worker
314 PROCEDURE Populate_Temp_Tables
311 -- PRE-COND: none
312 -- EXCEPTIONS: none
313 -- +==========================================================================+
315 ( p_cost_group_id IN NUMBER
316 , p_period_id IN NUMBER
317 , p_period_start_date IN DATE
318 , p_period_end_date IN DATE
319 );
320 -- +========================================================================+
321 -- PROCEDURE: Retrieve_Interorg_Items
322 -- PARAMETERS:
323 -- p_period_id IN NUMBER
324 -- p_cost_group_id IN NUMBER
325 -- p_period_start_date IN DATE
326 -- p_period_end_date IN DATE
327 -- COMMENT:
328 -- This procedure is called by the API iteration_process
329 -- PRE-COND: none
330 -- EXCEPTIONS: none
331 -- +==========================================================================+
332 PROCEDURE Retrieve_Interorg_Items
333 ( p_period_id IN NUMBER
334 , p_cost_group_id IN NUMBER
335 , p_period_start_date IN DATE
336 , p_period_end_date IN DATE
337 );
338
339 -- +========================================================================+
340 -- PROCEDURE: Process_Optimal_Sequence
341 -- PARAMETERS:
342 -- p_period_id IN NUMBER
343 -- COMMENT:
344 -- This procedure is called by the Absorption Cost Process
345 -- PRE-COND: none
346 -- EXCEPTIONS: none
347 -- +==========================================================================+
348 PROCEDURE Process_Optimal_Sequence
349 ( p_period_id IN NUMBER
350 );
351
352 -- +========================================================================+
353 -- PROCEDURE: Iteration_Process PRIVATE UTILITY
354 -- PARAMETERS:
355 -- p_init_msg_list IN VARCHAR2
356 -- p_validation_level IN NUMBER
357 -- x_return_status OUT VARCHAR2(1)
358 -- x_msg_count OUT NUMBER
359 -- x_msg_data OUT VARCHAR2(2000)
360 -- p_legal_entity_id IN NUMBER
361 -- p_cost_type_id IN NUMBER
362 -- p_cost_method IN NUMBER
363 -- p_iteration_proc_flag IN VARCHAR2(1)
364 -- p_period_id IN NUMBER
365 -- p_start_date IN DATE
366 -- p_end_date IN DATE
367 -- p_inventory_item_id IN NUMBER
368 -- p_inventory_item_number IN VARHCHAR2(1025)
369 -- p_tolerance IN NUMBER
370 -- p_iteration_num IN NUMBER
371 -- p_run_options IN NUMBER
372 -- p_pac_rates_id IN NUMBER
373 -- p_uom_control IN NUMBER
374 -- COMMENT:
375 -- This procedure is called by the Interorg Transfer Cost Process worker
376 -- after completing the necessary process in phase 5 of standard PAC
377 -- feature
378 -- PRE-COND: none
379 -- EXCEPTIONS: none
380 -- +==========================================================================+
381 PROCEDURE Iteration_Process
382 ( p_init_msg_list IN VARCHAR2
383 , p_validation_level IN NUMBER
384 , p_legal_entity_id IN NUMBER
385 , p_cost_type_id IN NUMBER
386 , p_cost_method IN NUMBER
387 , p_iteration_proc_flag IN VARCHAR2
388 , p_period_id IN NUMBER
389 , p_start_date IN DATE
390 , p_end_date IN DATE
391 , p_inventory_item_id IN NUMBER
392 , p_inventory_item_number IN VARCHAR2
393 , p_tolerance IN NUMBER
394 , p_iteration_num IN NUMBER
395 , p_run_options IN NUMBER
396 , p_pac_rates_id IN NUMBER
397 , p_uom_control IN NUMBER
398 , p_user_id IN NUMBER
399 , p_login_id IN NUMBER
400 , p_req_id IN NUMBER
401 , p_prg_id IN NUMBER
402 , p_prg_appid IN NUMBER
403 );
404
405
406 END CST_PAC_ITERATION_PROCESS_PVT;
[Home] [Help]