1 PACKAGE AHL_LTP_MTL_REQ_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVLMRS.pls 115.1 2003/12/02 19:30:56 jaramana noship $ */
3
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 TYPE Route_Mtl_Req_Rec_Type IS RECORD (
8 ROUTE_OPERATION_ID NUMBER,
9 INVENTORY_ITEM_ID NUMBER,
10 INV_MASTER_ORG_ID NUMBER,
11 ITEM_GROUP_ID NUMBER,
12 QUANTITY NUMBER,
13 UOM_CODE VARCHAR2(3),
14 RT_OPER_MATERIAL_ID NUMBER,
15 POSITION_PATH_ID NUMBER,
16 RELATIONSHIP_ID NUMBER,
17 ITEM_COMP_DETAIL_ID NUMBER
18 );
19
20
21 ----------------------------------------------
22 -- Define Table Type for records structures --
23 ----------------------------------------------
24 TYPE Route_Mtl_Req_Tbl_Type IS TABLE OF Route_Mtl_Req_Rec_Type INDEX BY BINARY_INTEGER;
25
26
27 ------------------------
28 -- Declare Procedures --
29 ------------------------
30
31 -- Start of Comments --
32 -- Procedure name : Get_Route_Mtl_Req
33 -- Type : Private
34 -- Function : Private API to get the Material requirements for a Route.
35 -- For FORECAST request type, it aggregates requirements at the
36 -- route level (across operations), and gets the highest priority item
37 -- ignoring the inventory org. Also, a disposition list requirement is
38 -- considered for FORECAST only if the REPLACE_PERCENT = 100%.
39 -- For PLANNED, no aggregation is done, NO specific item is obtained
40 -- within an item group and the REPLACE_PERCENT is not considered.
41 -- Pre-reqs :
42 -- Parameters :
43 --
44 -- Standard IN Parameters :
45 -- p_api_version IN NUMBER Required
46 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
47 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
48 --
49 -- Standard OUT Parameters :
50 -- x_return_status OUT VARCHAR2 Required
51 -- x_msg_count OUT NUMBER Required
52 -- x_msg_data OUT VARCHAR2 Required
53 --
54 -- Get_Route_Mtl_Req Parameters:
55 -- p_route_id IN NUMBER Not Required only if p_mr_route_id is not null
56 -- The Id of Route for which to determine the material requirements
57 -- p_mr_route_id IN NUMBER Not Required only if p_route_id is not null
58 -- The Id of MR Route for which to determine the material requirements
59 -- p_item_instance_id IN NUMBER Required
60 -- The Id of Instance for which to plan the material requirements
61 -- p_requirement_date IN DATE Not Required
62 -- The date when the materials are required. If provided, the positions of Master Configs
63 -- (for position path based disposition list requirement) are validated against this date.
64 -- p_request_type IN VARCHAR2 Required
65 -- Should be either 'FORECAST' or 'PLANNED'
66 -- x_route_mtl_req_tbl OUT AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl Required
67 -- The Table of records containing the material requirements for the route
68 --
69 -- Version :
70 -- Initial Version 1.0
71 --
72 -- End of Comments.
73
74 PROCEDURE Get_Route_Mtl_Req
75 (
76 p_api_version IN NUMBER,
77 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
78 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_msg_data OUT NOCOPY VARCHAR2,
82 p_route_id IN NUMBER,
83 p_mr_route_id IN NUMBER,
84 p_item_instance_id IN NUMBER,
85 p_requirement_date IN DATE := null,
86 p_request_type IN VARCHAR2,
87 x_route_mtl_req_tbl OUT NOCOPY AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type
88 );
89
90 -- Start of Comments --
91 -- Function name : Get_Primary_UOM_Qty
92 -- Type : Private
93 -- Function : Private helper function to convert a quantity of an item from one
94 -- UOM to the Primary UOM. The inputs are the item id, the quantity
95 -- and the source UOM. The output is the quantity in the primary uom.
96 -- Pre-reqs :
97 -- Parameters :
98 --
99 --
100 -- Get_Primary_UOM_Qty Parameters:
101 -- p_inventory_item_id IN NUMBER Required
102 -- The Id of Inventory item. If this is null, this function returns null.
103 -- p_source_uom_code IN VARCHAR2 Required
104 -- The code of the UOM in which the quantity is currently mentioned.
105 -- If this is null, this function returns null.
106 -- p_quantity IN NUMBER Required
107 -- The quantity of the item in the indicated UOM.
108 -- If this is null, this function returns null.
109 --
110 -- Version :
111 -- Initial Version 1.0
112 --
113 -- End of Comments.
114
115 FUNCTION Get_Primary_UOM_Qty
116 (
117 p_inventory_item_id IN NUMBER,
118 p_source_uom_code IN VARCHAR2,
119 p_quantity IN NUMBER
120 ) RETURN NUMBER;
121
122 -- Start of Comments --
123 -- Function name : Get_Primary_UOM
124 -- Type : Private
125 -- Function : Private helper function to get the Primary UOM of an item
126 -- The inputs are the item id and the inventory org id.
127 -- Pre-reqs :
128 -- Parameters :
129 --
130 --
131 -- Get_Primary_UOM Parameters:
132 -- p_inventory_item_id IN NUMBER Required
133 -- The Id of Inventory item. If this is null, this function returns null.
134 -- p_inventory_org_id IN NUMBER Required
135 -- The inventory org id of the item. If this is null, this function returns null.
136 --
137 -- Version :
138 -- Initial Version 1.0
139 --
140 -- End of Comments.
141
142 FUNCTION Get_Primary_UOM
143 (
144 p_inventory_item_id IN NUMBER,
145 p_inventory_org_id IN NUMBER
146 ) RETURN VARCHAR2;
147
148 End AHL_LTP_MTL_REQ_PVT;