1 PACKAGE AHL_LTP_MTL_REQ_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVLMRS.pls 120.0.12020000.2 2012/12/10 13:49:49 shnatu ship $ */
3
4 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
5 -- Added MC_HEADER_ID and POSITION_KEY for Position based material requirements.
6 ---------------------------------------------------------------------
7 -- Define Record Types for record structures needed by the APIs --
8 ---------------------------------------------------------------------
9 TYPE Route_Mtl_Req_Rec_Type IS RECORD (
10 ROUTE_OPERATION_ID NUMBER,
11 INVENTORY_ITEM_ID NUMBER,
12 INV_MASTER_ORG_ID NUMBER,
13 ITEM_GROUP_ID NUMBER,
14 QUANTITY NUMBER,
15 UOM_CODE VARCHAR2(3),
16 RT_OPER_MATERIAL_ID NUMBER,
17 POSITION_PATH_ID NUMBER,
18 RELATIONSHIP_ID NUMBER,
19 ITEM_COMP_DETAIL_ID NUMBER,
20 MC_HEADER_ID NUMBER,
21 POSITION_KEY NUMBER
22 );
23
24
25 ----------------------------------------------
26 -- Define Table Type for records structures --
27 ----------------------------------------------
28 TYPE Route_Mtl_Req_Tbl_Type IS TABLE OF Route_Mtl_Req_Rec_Type INDEX BY BINARY_INTEGER;
29
30 -- SURRKUMA :: MARSHALING :: 20-DEC-2011
31 -------------------------------------------
32 -- Define Table Type for Alternate Items --
33 -------------------------------------------
34 TYPE Alt_Items_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
35
36 ------------------------
37 -- Declare Procedures --
38 ------------------------
39
40 -- Start of Comments --
41 -- Procedure name : Get_Route_Mtl_Req
42 -- Type : Private
43 -- Function : Private API to get the Material requirements for a Route.
44 -- For FORECAST request type, it aggregates requirements at the
45 -- route level (across operations), and gets the highest priority item
46 -- ignoring the inventory org. Also, a disposition list requirement is
47 -- considered for FORECAST only if the REPLACE_PERCENT = 100%.
48 -- For PLANNED, no aggregation is done, NO specific item is obtained
49 -- within an item group and the REPLACE_PERCENT is not considered.
50 -- Pre-reqs :
51 -- Parameters :
52 --
53 -- Standard IN Parameters :
54 -- p_api_version IN NUMBER Required
55 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
56 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
57 --
58 -- Standard OUT Parameters :
59 -- x_return_status OUT VARCHAR2 Required
60 -- x_msg_count OUT NUMBER Required
61 -- x_msg_data OUT VARCHAR2 Required
62 --
63 -- Get_Route_Mtl_Req Parameters:
64 -- p_route_id IN NUMBER Not Required only if p_mr_route_id is not null
65 -- The Id of Route for which to determine the material requirements
66 -- p_mr_route_id IN NUMBER Not Required only if p_route_id is not null
67 -- The Id of MR Route for which to determine the material requirements
68 -- p_item_instance_id IN NUMBER Required
69 -- The Id of Instance for which to plan the material requirements
70 -- p_visit_id IN NUMBER Not Requried
71 -- The Visid Id for which Material Requirements needs to determined.
72 -- p_requirement_date IN DATE Not Required
73 -- The date when the materials are required. If provided, the positions of Master Configs
74 -- (for position path based disposition list requirement) are validated against this date.
75 -- p_request_type IN VARCHAR2 Required
76 -- Should be either 'FORECAST' or 'PLANNED'
77 -- x_route_mtl_req_tbl OUT AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl Required
78 -- The Table of records containing the material requirements for the route
79 --
80 -- Version :
81 -- Initial Version 1.0
82 --
83 -- End of Comments.
84
85 PROCEDURE Get_Route_Mtl_Req
86 (
87 p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
89 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2,
93 p_route_id IN NUMBER,
94 p_mr_route_id IN NUMBER,
95 p_item_instance_id IN NUMBER,
96 p_visit_id IN NUMBER := null, -- Added by surrkuma for Service Bulletin, 07-Jun-2011
97 p_requirement_date IN DATE := null,
98 p_request_type IN VARCHAR2,
99 x_route_mtl_req_tbl OUT NOCOPY AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type
100 );
101
102 -- Start of Comments --
103 -- Procedure name : Get_Material_Req_For_Pos
104 -- Type : Private
105 -- Function : Gets the Material requirement for a position specified in
106 -- Route/Operation level requriements
107 -- Pre-reqs :
108 -- Parameters :
109 --
110 -- Standard IN Parameters :
111 -- p_api_version IN NUMBER Required
112 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
113 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
114 --
115 -- Standard OUT Parameters :
116 -- x_return_status OUT VARCHAR2 Required
117 -- x_msg_count OUT NUMBER Required
118 -- x_msg_data OUT VARCHAR2 Required
119 --
120 -- Get_Material_Req_For_Pos Parameters:
121 -- p_uc_header_id IN NUMBER Required
122 -- Unit Configuration Header ID of the Visit's Unit
123 -- p_visit_id IN NUMBER Not Requried when p_visit_id is NULL
124 -- The Id of Visit for which material requirements needs to be determined.
125 -- p_quantity IN NUMBER Not Required when p_quantity is NULL
126 -- Requested Quanity.
127 -- p_uom_code IN VARCHAR2 Not Required when p_uom_code is NULL
128 -- Unit of Measure of the defined at requirement request level.
129 -- p_x_index IN OUT NUMBER Not Required
130 -- p_x_route_mtl_reqs material requirements table index
131 -- p_x_route_mtl_reqs IN OUT Route_Mtl_Req_Tbl_Type Required
132 -- The table containing the route based material requirements.
133 --
134 -- Version :
135 -- Initial Version 1.0
136 -- Modified by surrkuma on 29-Jul-2011, modified the procedure signature
137 -- to support control position based material requirements.
138 --
139 -- End of Comments.
140 PROCEDURE Get_Material_Req_For_Pos (
141 p_api_version IN NUMBER,
142 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
143 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 p_uc_header_id IN NUMBER,
148 p_visit_id IN NUMBER := NULL,
149 p_quantity IN NUMBER := NULL,
150 p_uom_code IN VARCHAR2 := NULL,
151 p_x_index IN OUT NOCOPY NUMBER,
152 p_x_route_mtl_reqs IN OUT NOCOPY Route_Mtl_Req_Tbl_Type);
153
154 ------------------------------------------------------------------------------------
155 -- Start of Comments
156 -- Procedure name : Get_Alternate_Items
157 -- Type : Private
158 -- Function : Gets all the applicable alternate items for given ahl_schedule_material
159 -- requirement.
160 -- Pre-reqs :
161 -- Parameters :
162 --
163 -- Standard IN Parameters :
164 -- p_api_version IN NUMBER Required
165 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
166 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
167 --
168 -- Standard OUT Parameters :
169 -- x_return_status OUT VARCHAR2 Required
170 -- x_msg_count OUT NUMBER Required
171 -- x_msg_data OUT VARCHAR2 Required
172 --
173 -- Get_Alternate_Items Parameters:
174 -- p_schedule_material_id IN NUMBER schedule material id Required
175 -- x_alt_items OUT Alt_Items_Tbl_Type Alternate Items for material requirement
176 --
177 -- Version :
178 -- Initial Version 1.0
179 -- Added by SURRKUMA on 20-DEC-2011
180 -- End of Comments
181 PROCEDURE Get_Alternate_Items(
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
184 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
185 p_schedule_material_id IN NUMBER,
186 p_curr_item_flag IN VARCHAR2 := FND_API.G_FALSE,
187 x_alt_items OUT NOCOPY Alt_Items_Tbl_Type,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2);
191
192 -- Start of Comments --
196 -- UOM to the Primary UOM. The inputs are the item id, the quantity
193 -- Function name : Get_Primary_UOM_Qty
194 -- Type : Private
195 -- Function : Private helper function to convert a quantity of an item from one
197 -- and the source UOM. The output is the quantity in the primary uom.
198 -- Pre-reqs :
199 -- Parameters :
200 --
201 --
202 -- Get_Primary_UOM_Qty Parameters:
203 -- p_inventory_item_id IN NUMBER Required
204 -- The Id of Inventory item. If this is null, this function returns null.
205 -- p_source_uom_code IN VARCHAR2 Required
206 -- The code of the UOM in which the quantity is currently mentioned.
207 -- If this is null, this function returns null.
208 -- p_quantity IN NUMBER Required
209 -- The quantity of the item in the indicated UOM.
210 -- If this is null, this function returns null.
211 --
212 -- Version :
213 -- Initial Version 1.0
214 --
215 -- End of Comments.
216
217 FUNCTION Get_Primary_UOM_Qty
218 (
219 p_inventory_item_id IN NUMBER,
220 p_source_uom_code IN VARCHAR2,
221 p_quantity IN NUMBER
222 ) RETURN NUMBER;
223
224 -- Start of Comments --
225 -- Function name : Get_Primary_UOM
226 -- Type : Private
227 -- Function : Private helper function to get the Primary UOM of an item
228 -- The inputs are the item id and the inventory org id.
229 -- Pre-reqs :
230 -- Parameters :
231 --
232 --
233 -- Get_Primary_UOM Parameters:
234 -- p_inventory_item_id IN NUMBER Required
235 -- The Id of Inventory item. If this is null, this function returns null.
236 -- p_inventory_org_id IN NUMBER Required
237 -- The inventory org id of the item. If this is null, this function returns null.
238 --
239 -- Version :
240 -- Initial Version 1.0
241 --
242 -- End of Comments.
243
244 FUNCTION Get_Primary_UOM
245 (
246 p_inventory_item_id IN NUMBER,
247 p_inventory_org_id IN NUMBER
248 ) RETURN VARCHAR2;
249
250 End AHL_LTP_MTL_REQ_PVT;
[Home] [Help]