[Home] [Help]
PACKAGE: APPS.AHL_PRD_MRSHL_PVT
Source
1 PACKAGE AHL_PRD_MRSHL_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVPMLS.pls 120.2.12020000.2 2012/12/07 07:57:16 sareepar ship $ */
3
4 TYPE Mrshl_search_rec_type IS RECORD
5 (
6 Visit_id NUMBER,
7 item_instance_id NUMBER,
8 Workorder_id NUMBER,
9 Workorder_name VARCHAR2(80),
10 Item_name VARCHAR2(240),
11 Item_Desc VARCHAR2(240),
12 Search_mode VARCHAR2(30)
13 );
14
15 TYPE Unavailable_items_rec_type IS RECORD
16 (
17 Scheduled_material_id NUMBER,
18 Inventory_item_id NUMBER,
19 Item_name VARCHAR2(240),
20 Item_Desc VARCHAR2(240),
21 Workorder_id NUMBER,
22 Workorder_name VARCHAR2(80),
23 Wip_Entity_Id NUMBER,
24 Organization_id NUMBER,
25 Visit_id NUMBER,
26 wo_status_code VARCHAR2(30),
27 wo_status VARCHAR2(80),
28 Wo_operation_id NUMBER,
29 Op_seq NUMBER,
30 Quantity NUMBER,
31 UOM VARCHAR2(3),
32 UOM_DESC VARCHAR2(25),
33 Required_date DATE,
34 Required_quantity NUMBER,
35 Issued_Quantity NUMBER,
36 Scheduled_date DATE,
37 Scheduled_quantity NUMBER,
38 Exception_date DATE,
39 Reserved_quantity NUMBER,
40 Onhand_quantity NUMBER,
41 Qty_per_assembly NUMBER,
42 Subinventory VARCHAR2(10),
43 Locator_id NUMBER,
44 Locator_segments VARCHAR2(240),
45 Serial_Number VARCHAR2(30),
46 Lot VARCHAR2(30),
47 Revision VARCHAR2(3),
48 Is_serialized VARCHAR2(1),
49 Is_Lot_Controlled VARCHAR2(1),
50 Is_Revision_Controlled VARCHAR2(1),
51 diposition_id NUMBER,
52 DIPOSITION_NAME VARCHAR2(80),
53 -- arunjk Added for Marshalling
54 AOG_FLAG VARCHAR2(1),
55 ASM_OBJECT_VERSION_NUMBER NUMBER
56
57 );
58
59 TYPE Unavailable_items_tbl_type IS TABLE OF Unavailable_items_rec_type INDEX BY BINARY_INTEGER;
60
61 TYPE Available_items_rec_type IS RECORD
62 (
63 Scheduled_material_id NUMBER,
64 Inventory_item_id NUMBER,
65 Item_name VARCHAR2(240),
66 Item_Desc VARCHAR2(240),
67 Workorder_id NUMBER,
68 Workorder_name VARCHAR2(80),
69 Wip_Entity_Id NUMBER,
70 Organization_id NUMBER,
71 Visit_id NUMBER,
72 wo_status_code VARCHAR2(30),
73 wo_status VARCHAR2(80),
74 Wo_operation_id NUMBER,
75 Op_seq NUMBER,
76 Quantity NUMBER,
77 UOM VARCHAR2(3),
78 UOM_DESC VARCHAR2(25),
79 Required_date DATE,
80 Required_quantity NUMBER,
81 Scheduled_date DATE,
82 Scheduled_quantity NUMBER,
83 Issued_Quantity NUMBER,
84 Exception_date DATE,
85 Reserved_quantity NUMBER,
86 Onhand_quantity NUMBER,
87 Qty_per_assembly NUMBER,
88 Subinventory VARCHAR2(10),
89 Locator_id NUMBER,
90 Locator_segments VARCHAR2(240),
91 Serial_Number VARCHAR2(30),
92 Lot VARCHAR2(30),
93 Revision VARCHAR2(3),
94 Item_source_wo_id NUMBER,
95 Item_source_wo_name VARCHAR2(80),
96 Item_source_wop_id NUMBER,
97 Item_source_wop_seq NUMBER,
98 Is_serialized VARCHAR2(1),
99 Is_Lot_Controlled VARCHAR2(1),
100 Is_Revision_Controlled VARCHAR2(1),
101 diposition_id NUMBER,
102 Diposition_Name Varchar2(80),
103 -- arunjk Added for Marshalling
104 AOG_FLAG VARCHAR2(1),
105 ASM_OBJECT_VERSION_NUMBER NUMBER
106 );
107
108 TYPE Available_items_tbl_type IS TABLE OF Available_items_rec_type INDEX BY BINARY_INTEGER;
109
110
111 TYPE mrshl_details_rec_type IS RECORD
112 (
113 Unit_Header_id NUMBER,
114 Unit_Name VARCHAR2(80),
115 relationship_id NUMBER,
116 parent_rel_id NUMBER,
117 root_instance_id NUMBER,
118 POSITION VARCHAR2(240),
119 IS_POSITION_SER_CTRLD VARCHAR2(1),
120 CURR_ITEM_ID NUMBER,
121 CURR_INSTANCE_ID NUMBER,
122 parent_instance_id NUMBER,
123 ALLOWED_QTY NUMBER,
124 CURR_ITEM_NUMBER VARCHAR2(240),
125 CURR_SERIAL_NUMBER VARCHAR2(30),
126 CURR_INSTLD_QTY NUMBER,
127 REQ_QTY NUMBER,
128 ISSUED_QTY NUMBER,
129 AVAILABLE_QTY NUMBER,
130 NOT_AVAILABLE_QTY NUMBER,
131 COMPL_WO_COUNT NUMBER,
132 TOTAL_WO_COUNT NUMBER,
133 CUMM_REQ_QTY NUMBER,
134 CUMM_ISSUED_QTY NUMBER,
135 CUMM_AVAILABLE_QTY NUMBER,
136 CUMM_NOT_AVAILABLE_QTY NUMBER,
137 CUMM_COMPL_WO_COUNT NUMBER,
138 CUMM_TOTAL_WO_COUNT NUMBER
139 );
140
141 TYPE mrshl_details_tbl_type IS TABLE OF mrshl_details_rec_type INDEX BY BINARY_INTEGER;
142
143
144
145 PROCEDURE Get_unavailable_items
146 (
147 p_api_version IN NUMBER := 1.0,
148 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
149 p_commit IN VARCHAR2 := FND_API.G_FALSE,
150 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
151 p_default IN VARCHAR2 := FND_API.G_FALSE,
152 p_module_type IN VARCHAR2 := NULL,
153 p_Mrshl_search_rec IN Mrshl_search_rec_type,
154 x_Unavailable_items_tbl OUT NOCOPY Unavailable_items_Tbl_Type,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2
158 );
159
160 PROCEDURE Get_available_items
161 (
162 p_api_version IN NUMBER := 1.0,
163 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
164 p_commit IN VARCHAR2 := FND_API.G_FALSE,
165 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
166 p_default IN VARCHAR2 := FND_API.G_FALSE,
167 p_module_type IN VARCHAR2 := NULL,
168 p_Mrshl_search_rec IN Mrshl_search_rec_type,
169 x_available_items_tbl OUT NOCOPY Available_items_Tbl_Type,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2
173 );
174
175 FUNCTION Get_workorder_count
176 (
177 p_visit_id IN NUMBER,
178 p_item_instance_id IN NUMBER,
179 p_mode IN VARCHAR2
180 ) RETURN NUMBER;
181
182 FUNCTION Get_item_count
183 (
184 p_visit_id IN NUMBER,
185 p_item_instance_id IN NUMBER :=NULL,
186 p_mode IN VARCHAR2
187 ) RETURN NUMBER;
188
189 FUNCTION Get_visit_completion_perc
190 (
191 p_visit_id IN NUMBER
192 ) RETURN NUMBER;
193
194 PROCEDURE Get_mrshl_details
195 (
196 p_api_version IN NUMBER := 1.0,
197 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
198 p_commit IN VARCHAR2 := FND_API.G_FALSE,
199 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
200 p_default IN VARCHAR2 := FND_API.G_FALSE,
201 p_module_type IN VARCHAR2 := NULL,
202 p_unit_header_id IN NUMBER,
203 p_item_instance_id IN NUMBER,
204 p_visit_id IN NUMBER,
205 x_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2
209 );
210
211 FUNCTION GET_ONHAND_AVAILABLE(
212 P_ORG_ID IN NUMBER,
213 P_ITEM_ID IN NUMBER,
214 p_SUBINVENTORY VARCHAR2,
215 p_locator_id NUMBER) RETURN NUMBER;
216
217 FUNCTION GET_ONHAND_NOTAVAILABLE(
218 P_ORG_ID IN NUMBER,
219 P_ITEM_ID IN NUMBER,
220 p_SUBINVENTORY VARCHAR2,
221 p_locator_id NUMBER) RETURN NUMBER;
222
223 End Ahl_Prd_Mrshl_Pvt;