1 PACKAGE PO_THIRD_PARTY_STOCK_GRP AUTHID CURRENT_USER as
2 --$Header: POXGTPSS.pls 120.2.12000000.2 2007/04/17 06:59:14 vdurbhak ship $
3 --+===========================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : POXGTPSS.pls |
10 --| |
11 --| DESCRIPTION: This package is used to the VMI and consigned from |
12 --| supplier validation |
13 --| |
14 --| HISTORY: 18-SEP-2002 : fdubois |
15 --+===========================================================================+
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_THIRD_PARTY_STOCK_GRP';
18
19 --==========================================================================
20 -- FUNCTION NAME: Validate_Local_Asl
21 --
22 -- DESCRIPTION: the function returns TRUE if the Local ASL can be
23 -- VMI or Consigned from Supplier for the IN parameters
24 -- (define the ASL and the validation type). False
25 -- otherwize. It then also return the Validation Error
26 -- Message name
27 --
28 -- PARAMETERS: In: p_api_version Standard API parameter
29 -- p_init_msg_list Standard API parameter
30 -- p_commit Standard API parameter
31 -- p_validation_level Standard API parameter
32 -- p_inventory_item_id Item identifier
33 -- p_supplier_site_id Supplier site identifier
34 -- p_inventory_org_id Inventory Organization
35 -- p_validation_type Validation to perform:
36 -- VMI or SUP_CONS
37 --
38 -- Out: x_return_status Standard API parameter
39 -- x_msg_count Standard API parameter
40 -- x_msg_data Standard API parameter
41 -- x_validation_error_name Error message name
42 --
43 -- Return: TRUE if OK to have Local VMI/Consigned from supplier ASL
44 --
45 --
46 -- DESIGN REFERENCES: ASL_CONSSUP_DLD.doc
47 --
48 --
49 -- CHANGE HISTORY: 18-Sep-02 FDUBOIS Created.
50 -- 15-Jan-03 VMA Add standard API parameters to comply
51 -- with PL/SQL API standard.
52 --===========================================================================
53 FUNCTION validate_local_asl
54 ( p_api_version IN NUMBER
55 , p_init_msg_list IN VARCHAR2
56 , p_commit IN VARCHAR2
57 , p_validation_level IN NUMBER
58 , x_return_status OUT NOCOPY VARCHAR2
59 , x_msg_count OUT NOCOPY NUMBER
60 , x_msg_data OUT NOCOPY VARCHAR2
61 , p_inventory_item_id IN NUMBER
62 , p_supplier_site_id IN NUMBER
63 , p_inventory_org_id IN NUMBER
64 , p_validation_type IN VARCHAR2
65 , x_validation_error_name OUT NOCOPY VARCHAR2
66 )
67 RETURN BOOLEAN;
68
69
70 --===========================================================================
71 -- FUNCTION NAME: Validate_Global_Asl
72 --
73 -- DESCRIPTION: the function retunrs TRUE if the Global ASL can be
74 -- VMI or Consigned from supplier for the IN parameters
75 -- (define the ASL). False otherwize. It then also
76 -- return the Validation Error Message name
77 --
78 -- PARAMETERS: In: p_api_version Standard API parameter
79 -- p_init_msg_list Standard API parameter
80 -- p_commit Standard API parameter
81 -- p_validation_level Standard API parameter
82 -- p_inventory_item_id Item identifier
83 -- p_supplier_site_id Supplier site identifier
84 -- p_validation_type Validation to perform:
85 -- VMI or SUP_CONS
86 --
87 -- Out: x_return_status Standard API parameter
88 -- x_msg_count Standard API parameter
89 -- x_msg_data Standard API parameter
90 -- x_validation_error_name Error message name
91 --
92 -- Return: TRUE if OK to have Global VMI/Consigned ASL
93 --
94 --
95 -- DESIGN REFERENCES: ASL_CONSSUP_DLD.doc
96 --
97 -- CHANGE HISTORY: 22-Sep-02 FDUBOIS Created.
98 -- 15-Jan-03 VMA Add standard API parameters to comply
99 -- with PL/SQL API standard.
100 --===========================================================================
101 FUNCTION validate_global_asl
102 ( p_api_version IN NUMBER
103 , p_init_msg_list IN VARCHAR2
104 , p_commit IN VARCHAR2
105 , p_validation_level IN NUMBER
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_msg_count OUT NOCOPY NUMBER
108 , x_msg_data OUT NOCOPY VARCHAR2
109 , p_inventory_item_id IN NUMBER
110 , p_supplier_site_id IN NUMBER
111 , p_validation_type IN VARCHAR2
112 , x_validation_error_name OUT NOCOPY VARCHAR2
113 )
114 RETURN BOOLEAN;
115
116 --===========================================================================
117 -- FUNCTION NAME: Exist_TPS_ASL
118 --
119 -- DESCRIPTION: the function returns TRUE if there exist a
120 -- VMI/Consined ASL within the Operating Unit.
121 -- If there is none it returns FALSE.
122 --
123 -- PARAMETERS:
124 -- Return: TRUE if exists VMI/Consigned ASL
125 --
126 -- DESIGN REFERENCES: APXSSFSO_CONSSUP_DLD.doc
127 --
128 -- CHANGE HISTORY: 26-Sep-02 FDUBOIS Created.
129 --===========================================================================
130 FUNCTION Exist_TPS_ASL RETURN BOOLEAN;
131
132 --===========================================================================
133 -- API NAME : Validate_Supplier_Purge
134 -- API TYPE : Public
135 -- DESCRIPTION : Checks whether a supplier can be
136 -- purged according to Consigned Inventory criteria.
137 -- A supplier cannot be purged if any of its vendor site
138 -- has on hand consigned stock. The function returns
139 -- 'TRUE' is the supplier does not have any on hand
140 -- consigned stock - in this case the supplier may be
141 -- purged. The function returns 'FALSE' if the supplier
142 -- has on hand consigned stock - in this case, the
143 -- supplier should not be purged.
144 --
145 -- PARAMETERS : p_vendor_id
146 --
147 -- RETURN : 'TRUE' if the purge may proceed; 'FALSE' if the purge
148 -- should not proceed.
149 --
150 -- DESIGN DOC : SUPPUR_CONSSUP_DLD.doc
151 --
152 -- HISTORY : 11-12-02 vma Created
153 -- 12-12-02 vma The function Supplier_Owns_Tps in
154 -- INV_SUPPLIER_OWNED_STOCK_GRP
155 -- has been moved to
156 -- PO_INV_THIRD_PARTY_STOCK_MDTR.
157 -- Modify call accordingly.
158 --===========================================================================
159 FUNCTION Validate_Supplier_Purge(p_vendor_id IN NUMBER) RETURN VARCHAR2;
160
161 --===========================================================================
162 -- API NAME : Validate_Supplier_Merge
163 -- TYPE : Public
164 -- Pre-condition : Supplier site exists. If the supplier site does not
165 -- exist, x_can_merge will contain value FND_API.G_TRUE
166 -- DESCRIPTION : Checks whether a supplier site can be
167 -- merged according to Consigned/VMI criteria.
168 -- A merge should fail if for the FROM supplier site:
169 -- - on hand quantity exists in consigned or VMI stock
170 -- - open consigned shipments exist
171 -- - open consumption advices exist
172 -- - open VMI release lines exist
173 -- ('open' meaning neither FINALLY CLOSED nor CANCELLED)
174 --
175 -- PARAMETERS : p_api_version Standard API parameter
176 -- p_init_msg_list Standard API parameter
177 -- p_commit Standard API parameter
178 -- p_validation_level Standard API parameter
179 -- x_return_status Standard API parameter
180 -- x_msg_count Standard API parameter
181 -- x_msg_data Standard API parameter
182 -- p_vendor_site_id Vendor site id
183 -- x_can_merge FND_API.G_FALSE if the supplier
184 -- site cannot be merged;
185 -- FND_API.G_TRUE otherwise.
186 -- x_validation_error Name of validation error.
187 -- 'PO_SUP_CONS_FAIL_MERGE_TPS' if
188 -- merge should fail because on hand
189 -- consigned/VMI stock exists;
190 -- 'PO_SUP_CONS_FAIL_MERGE_DOC' if
191 -- merge should fail because open PO
192 -- documents exist.
193 -- p_vendor_id Vendor ID
194 --
195 -- DESIGN DOC : SUPPUR_CONSSUP_DLD.doc
196 --
197 -- HISTORY : 11-12-02 vma Created
198 -- 12-12-02 vma The function Sup_Site_Owns_Tps in
199 -- INV_SUPPLIER_OWNED_STOCK_GRP
200 -- has been moved to
201 -- PO_INV_THIRD_PARTY_STOCK_MDTR.
202 -- Modify call accordingly.
203 -- Added standard API parameters to
204 -- comply with PL/SQL API coding standard.
205 --===========================================================================
206 PROCEDURE Validate_Supplier_Merge
207 ( p_api_version IN NUMBER
208 , p_init_msg_list IN VARCHAR2
209 , p_commit IN VARCHAR2
210 , p_validation_level IN NUMBER
211 , x_return_status OUT NOCOPY VARCHAR2
212 , x_msg_count OUT NOCOPY NUMBER
213 , x_msg_data OUT NOCOPY VARCHAR2
214 , p_vendor_site_id IN NUMBER
215 , p_vendor_id IN NUMBER
216 , x_can_merge OUT NOCOPY VARCHAR2
217 , x_validation_error OUT NOCOPY VARCHAR2
218 );
219
220 --=============================================================================
221 -- API NAME : Get_Asl_Attributes
222 -- TYPE : PUBLIC
223 -- PRE-CONDITION : The inventory_item_id, vendor_id, vendor_site_id and
224 -- using organization_id passed in should be not NULL, or else
225 -- all the out parameters will have NULL values
226 -- DESCRIPTION : This procedure returns the Consigned from Supplier
227 -- and VMI setting of the ASL entry that corresponds to
228 -- the passed in item/supplier/supplier site/organization
229 -- combination, as OUT parameters.
230 -- PARAMETERS :
231 -- p_api_version REQUIRED. API version
232 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the
233 -- message list.
234 -- NULL value is regarded as
235 -- FND_API.G_FALSE.
236 -- x_return_status REQUIRED. Value can be
237 -- FND_API.G_RET_STS_SUCCESS
238 -- FND_API.G_RET_STS_ERROR
239 -- FND_API.G_RET_STS_UNEXP_ERROR
240 -- x_msg_count REQUIRED. Number of messages on the message
241 -- list
242 -- x_msg_data REQUIRED. Return message data if message
243 -- count is 1
244 -- p_inventory_item_id REQUIRED. Item identifier.
245 -- p_vendor_id REQUIRED. Supplier identifier.
246 -- p_vendor_site_id REQUIRED. Supplier site identifier.
247 -- p_using_organization_id REQUIRED. Identifier of the organization to
248 -- which the shipments are delivered
249 -- to.
250 -- x_consigned_from_supplier_flag REQUIRED. Consigned setting of the ASL
251 -- x_enable_vmi_flag REQUIRED. VMI setting of the ASL
255 -- x_consigned_billing_cycle REQUIRED. The number of days before
252 -- x_last_billing_date REQUIRED. Last date when the consigned
253 -- consumption concurrent program
254 -- ran
256 -- summarizing the consigned POs
257 -- received and transfer the
258 -- goods to regular stock
259 -- EXCEPTIONS :
260 --
261 --=============================================================================
262 PROCEDURE Get_Asl_Attributes
263 ( p_api_version IN NUMBER
264 , p_init_msg_list IN VARCHAR2
265 , x_return_status OUT NOCOPY VARCHAR2
266 , x_msg_count OUT NOCOPY NUMBER
267 , x_msg_data OUT NOCOPY VARCHAR2
268 , p_inventory_item_id IN NUMBER
269 , p_vendor_id IN NUMBER
270 , p_vendor_site_id IN NUMBER
271 , p_using_organization_id IN NUMBER
272 , x_consigned_from_supplier_flag OUT NOCOPY VARCHAR2
273 , x_enable_vmi_flag OUT NOCOPY VARCHAR2
274 , x_last_billing_date OUT NOCOPY DATE
275 , x_consigned_billing_cycle OUT NOCOPY NUMBER);
276
277 --=============================================================================
278 -- API NAME : Get_Item_Inv_Asset_Flag
279 -- TYPE : PUBLIC
280 -- PRE-CONDITION : Item must exist, or else the NO_DATA_FOUND exception
281 -- would be thrown and the out parameter
282 -- x_inventory_asset_flag would be set to NULL.
283 -- DESCRIPTION : Get the INVENTORY_ASSET_FLAG for a particular item. This
284 -- procedure is typically for determining whether an item is
285 -- expense or not.
286 -- PARAMETERS :
287 -- p_api_version REQUIRED. API version
288 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message
289 -- list.
290 -- NULL value is regarded as
291 -- FND_API.G_FALSE.
292 -- x_return_status REQUIRED. Value can be
293 -- FND_API.G_RET_STS_SUCCESS
294 -- FND_API.G_RET_STS_ERROR
295 -- FND_API.G_RET_STS_UNEXP_ERROR
296 -- x_msg_count REQUIRED. Number of messages on the message list
297 -- x_msg_data REQUIRED. Return message data if message count
298 -- is 1
299 -- p_organization_id REQUIRED. Identifier of the organization to
300 -- which the item was assigned to
301 -- p_inventory_item_id REQUIRED. Item identifier.
302 -- x_inventory_asset_flag REQUIRED. Inventory Asset Flag of the specified
303 -- item.
304 -- EXCEPTIONS :
305 --
306 --=============================================================================
307 PROCEDURE Get_Item_Inv_Asset_Flag
308 ( p_api_version IN NUMBER
309 , p_init_msg_list IN VARCHAR2
310 , x_return_status OUT NOCOPY VARCHAR2
311 , x_msg_count OUT NOCOPY NUMBER
312 , x_msg_data OUT NOCOPY VARCHAR2
313 , p_organization_id IN NUMBER
314 , p_inventory_item_id IN NUMBER
315 , x_inventory_asset_flag OUT NOCOPY VARCHAR2
316 );
317
318 --=============================================================================
319 -- API NAME : Consigned_Status_Affected
320 -- TYPE : PUBLIC
321 -- PRE-CONDITION : None
322 -- DESCRIPTION : Returns 'Y' to the out parameter x_consigned_status_affected
323 -- if the passed in vendor and vendor site would lead to changes
324 -- of the the consigned status on any child shipments that
325 -- belong to the PO specified by the passed in PO_HEADER_ID
326 -- PARAMETERS :
327 -- p_api_version REQUIRED. API version
328 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the
329 -- message list.
330 -- NULL value is regarded as
331 -- FND_API.G_FALSE.
332 -- x_return_status REQUIRED. Value can be
333 -- FND_API.G_RET_STS_SUCCESS
334 -- FND_API.G_RET_STS_ERROR
335 -- FND_API.G_RET_STS_UNEXP_ERROR
336 -- x_msg_count REQUIRED. Number of messages on the message
337 -- list
338 -- x_msg_data REQUIRED. Return message data if message
339 -- count is 1
340 -- p_vendor_id REQUIRED. Supplier identifier.
341 -- p_vendor_site_id REQUIRED. Supplier Site identifier.
342 -- p_po_header_id REQUIRED. Header identifier of the PO to be
343 -- validated
344 -- x_consigned_status_affected REQUIRED. Y if any of the shipment lines
345 -- would change in the consigned
346 -- status if adopting the passed in
347 -- vendor and vendor site. N otherwise.
348 -- EXCEPTIONS :
349 --
350 --=============================================================================
351 PROCEDURE consigned_status_affected
352 ( p_api_version IN NUMBER
353 , p_init_msg_list IN VARCHAR2
354 , x_return_status OUT NOCOPY VARCHAR2
355 , x_msg_count OUT NOCOPY NUMBER
356 , x_msg_data OUT NOCOPY VARCHAR2
357 , p_vendor_id IN NUMBER
358 , p_vendor_site_id IN NUMBER
359 , p_po_header_id IN NUMBER
360 , x_consigned_status_affected OUT NOCOPY VARCHAR2
361 );
362
363 -- <ACHTML R12 START>
364 FUNCTION get_consigned_flag(
365 p_org_id IN NUMBER,
366 p_item_id IN NUMBER,
367 p_supplier_id IN NUMBER,
368 p_site_id IN NUMBER,
369 p_inv_org_id IN NUMBER --Bug 5976612 Added this new parameter.
370 ) RETURN VARCHAR2;
371 -- <ACHTML R12 END>
372
373 PROCEDURE IS_ASL_CONSIGNED_FROM_SUPPLIER(p_use_ship_to_org_ids IN PO_TBL_NUMBER,
374 p_item_id IN NUMBER,
375 p_vendor_id IN NUMBER,
376 p_vendor_site_id IN NUMBER,
377 x_consigned_from_supplier_flag OUT NOCOPY VARCHAR2);
378
379 END PO_THIRD_PARTY_STOCK_GRP;
[Home] [Help]