1 PACKAGE BODY INV_CONSIGN_UTIL AS
2 /* $Header: invpconb.pls 115.0 2003/09/26 14:55:35 nesoni noship $ */
3 FUNCTION Get_Asl_Id (
4 p_item_id IN NUMBER,
5 p_vendor_id IN NUMBER,
6 p_vendor_site_id IN NUMBER,
7 p_using_organization_id IN NUMBER
8 )
9 RETURN NUMBER
10 IS
11 l_asl_id NUMBER;
12 CURSOR C is
13 SELECT pasl.asl_id
14 FROM po_approved_supplier_lis_val_v pasl,
15 po_asl_attributes paa,
16 po_asl_status_rules_v pasr
17 WHERE pasl.item_id = p_item_id
18 AND pasl.vendor_id = p_vendor_id
19 AND nvl(pasl.vendor_site_id, -1) = nvl(p_vendor_site_id, -1)
20 AND pasl.using_organization_id IN (-1, p_using_organization_id)
21 AND pasl.asl_id = paa.asl_id
22 AND pasr.business_rule like '2_SOURCING'
23 AND pasr.allow_action_flag like 'Y'
24 AND pasr.status_id = pasl.asl_status_id
25 AND paa.using_organization_id =
26 (SELECT max(paa2.using_organization_id)
27 FROM po_asl_attributes paa2
28 WHERE paa2.asl_id = pasl.asl_id
29 AND paa2.using_organization_id IN (-1, p_using_organization_id))
30 ORDER BY pasl.using_organization_id DESC;
31 BEGIN
32 OPEN C;
33 FETCH C into l_asl_id;
34
35 IF (C%NOTFOUND) THEN
36 CLOSE C;
37 RETURN NULL;
38 END IF;
39 CLOSE C;
40 RETURN l_asl_id;
41 END;
42
43
44 END INV_CONSIGN_UTIL;
45
[Home] [Help]