1 PACKAGE BODY PO_INV_THIRD_PARTY_STOCK_MDTR AS
2 -- $Header: POXMTPSB.pls 120.1 2005/06/28 15:43:00 joswong noship $
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| POXMTPSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Consigned inventory PO/INV dependency wrapper API |
13 --| This mediator package is used to access INV objects from |
14 --| PO product. |
15 --| |
16 --| PROCEDURE LIST |
17 --| consumption_trans_exist |
18 --| |
19 --| HISTORY |
20 --| 12/09/02 vchu Created |
21 --| Contains empty stub of consumption_trans_exist() |
22 --| in order to avoid dependency of PO on INV |
23 --| 12/09/02 vchu Modified |
24 --| Replaced empty stub of consumption_trans_exist() |
25 --| with actual implmentation which has dependencies |
26 --| on MTL_MATERIAL_TRANSACTIONS and |
27 --| MTL_CONSUMPTION_TRANSACTIONS tables |
28 --| 12/12/02 vma Added empty stub of two functions |
29 --| Supplier_Owns_Tps and Sup_Site_Owns_Tps |
30 --| 12/12/02 vma Replace empty stub of Supplier_Owns_Tps and |
31 --| Sup_Site_Owns_Tps with actual implementation |
32 --| 10/29/03 vma Bug fix for performance bug #3131113 |
33 --+=======================================================================+
34
35 --=========================================================================
36 -- PROCEDURES AND FUNCTIONS
37 --=========================================================================
38
39 --=========================================================================
40 -- FUNCTION : consumption_trans_exist
41 -- PARAMETERS: p_transaction_source_id ID of the parent blanket
42 -- agreement
43 -- p_inventory_item_id Item ID of the transaction.
44 -- This field
45 -- RETURNS : Return 'Y' if there exists a consumption transaction
46 -- that is in process for the passed in transaction source
47 -- agreement ID and and item ID. The value 'Y' is returned
48 -- if the passed in item ID is null and if there exists
49 -- consumption transactions that are in process and match
50 -- with the passed in transaction source ID. The value 'N'
51 -- is returned if no corresponding consumption transactions
52 -- that are in process are found.
53 -- COMMENT : This function is called by PO Summary form to decide
54 -- whether it can provide the "Finally Close" and "Cancel"
55 -- actions in the the list of control actions for a PO
56 -- Header or a PO Line.
57 --=========================================================================
58
59 FUNCTION consumption_trans_exist
60 ( p_transaction_source_id IN NUMBER
61 , p_item_id IN NUMBER
62 )
63 RETURN VARCHAR2
64 IS
65
66 l_count_cons_trans NUMBER := 0;
67
68 BEGIN
69 IF(p_item_id IS NOT NULL)
70 THEN
71 SELECT count('Y')
72 INTO l_count_cons_trans
73 FROM dual
74 WHERE EXISTS(SELECT 'Y'
75 FROM MTL_CONSUMPTION_TRANSACTIONS MCT,
76 MTL_MATERIAL_TRANSACTIONS MMT
77 WHERE MMT.TRANSACTION_ID = MCT.TRANSACTION_ID
78 AND MMT.TRANSACTION_SOURCE_ID = p_transaction_source_id
79 AND MMT.INVENTORY_ITEM_ID = p_item_id
80 AND nvl(MCT.CONSUMPTION_PROCESSED_FLAG, 'N') <> 'Y');
81 ELSE
82 SELECT count('Y')
83 INTO l_count_cons_trans
84 FROM dual
85 WHERE EXISTS(SELECT 'Y'
86 FROM MTL_CONSUMPTION_TRANSACTIONS MCT,
87 MTL_MATERIAL_TRANSACTIONS MMT
88 WHERE MMT.TRANSACTION_ID = MCT.TRANSACTION_ID
89 AND MMT.TRANSACTION_SOURCE_ID = p_transaction_source_id
90 AND nvl(MCT.CONSUMPTION_PROCESSED_FLAG, 'N') <> 'Y');
91 END IF;
92
93 IF(l_count_cons_trans > 0)
94 THEN
95 RETURN 'Y';
96 ELSE
97 RETURN 'N';
98 END IF;
99
100 EXCEPTION
101
102 WHEN OTHERS THEN
103 RAISE;
104
105 END consumption_trans_exist;
106
107
108 --========================================================================
109 -- FUNCTION : Supplier_Owns_Tps PUBLIC
110 -- PARAMETERS : p_vendor_id IN NUMBER
111 -- RETURN : TRUE if on hand consigned stock exist for the supplier;
112 -- FALSE otherwise.
113 -- DESCRIPTION : Check whether on hand consigned stock exists for a given
114 -- supplier. The function checks whether any supplier site
115 -- of this supplier owns on hand consigned stock.
116 --
117 -- CHANGE HISTORY :
118 -- 18-Nov-2002 Created by VMA
119 -- 30-Oct-2003 Bug fix for bug #3131113:
120 -- 1. Added query to PO_APPROVED_SUPPLIER_LIST to make
121 -- use of index on MTL_ONHAND_QUANTITIES_DETAIL.
122 -- 2. Updated logic to check for existance of onhand
123 -- consigned stock. Old logic checked existence of
124 -- any record in MOQD. This is replaced with new logic
125 -- that checks the sum of primary_transaction_quantity
126 -- for an item owned by a supplier in MOQD.
127 --========================================================================
128 FUNCTION Supplier_Owns_Tps (p_vendor_id IN NUMBER) RETURN BOOLEAN IS
129
130 l_onhand_qty mtl_onhand_quantities_detail.primary_transaction_quantity%TYPE;
131
132 BEGIN
133
134 IF p_vendor_id IS NOT NULL
135 THEN
136 -- Start Bug 4459947
137 -- Do not hardcode schema names
138 -- GSCC checker parses asl as schema name even though in this case
139 -- it is just an alias. Changing the alias name from asl to po_asl.
140 FOR po_asl IN (SELECT item_id, vendor_site_id
141 FROM po_asl_attributes
142 WHERE vendor_id = p_vendor_id
143 AND vendor_site_id IS NOT NULL
144 AND consigned_from_supplier_flag IS NOT NULL
145 ORDER BY consigned_from_supplier_flag DESC)
146 LOOP
147 SELECT SUM(primary_transaction_quantity)
148 INTO l_onhand_qty
149 FROM mtl_onhand_quantities_detail
150 WHERE inventory_item_id = po_asl.item_id
151 AND owning_organization_id = po_asl.vendor_site_id
152 AND owning_tp_type = 1;
153
154 IF l_onhand_qty > 0
155 THEN
156 RETURN TRUE;
157 END IF;
158 END LOOP;
159 -- End Bug 4459947
160 END IF;
161
162 RETURN FALSE;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 RETURN FALSE;
167
168 END Supplier_Owns_Tps;
169
170
171 --========================================================================
172 -- FUNCTION : Sup_Site_Owns_Tps PUBLIC
173 -- PARAMETERS : p_vendor_site_id IN NUMBER
174 -- RETURN : TRUE if on hand consigned or VMI stock exist for the
175 -- supplier site; FALSE otherwise. If p_vendor_site_id
176 -- is null, FALSE is returned.
177 -- DESCRIPTION : Check whether on hand consigned or VMI stock exists for
178 -- a given supplier site.
179 --
180 -- CHANGE HISTORY :
181 -- 18-Nov-2002 Created by VMA
182 -- 30-Oct-2003 Bug fix for bug #3131113:
183 -- 1. Added query PO_APPROVED_SUPPLIER_LIST to use
184 -- index on MTL_ONHAND_QUANTITIES_DETAIL.
185 -- 2. Updated logic to check for existance of onhand
186 -- consigned stock. Old logic checked existence of
187 -- any record in MOQD. This is replaced with new logic
188 -- that checks the sum of primary_transaction_quantity
189 -- for an item owned by a supplier site in MOQD.
190 --========================================================================
191 FUNCTION Sup_Site_Owns_Tps(p_vendor_site_id IN Number) RETURN BOOLEAN IS
192
193 l_onhand_qty mtl_onhand_quantities_detail.primary_transaction_quantity%TYPE;
194
195 TYPE item_id_tbl_type IS TABLE OF po_asl_attributes_val_v.item_id%TYPE
196 INDEX BY BINARY_INTEGER;
197
198 item_id_tbl item_id_tbl_type;
199
200 BEGIN
201
202 IF p_vendor_site_id IS NOT NULL
203 THEN
204
205 SELECT item_id BULK COLLECT INTO item_id_tbl
206 FROM po_asl_attributes
207 WHERE vendor_site_id = p_vendor_site_id
208 AND consigned_from_supplier_flag IS NOT NULL
209 ORDER BY consigned_from_supplier_flag DESC;
210
211 FOR i IN item_id_tbl.FIRST..item_id_tbl.LAST
212 LOOP
213 SELECT SUM(primary_transaction_quantity)
214 INTO l_onhand_qty
215 FROM mtl_onhand_quantities_detail
216 WHERE inventory_item_id = item_id_tbl(i)
217 AND ((owning_organization_id = p_vendor_site_id
218 AND owning_tp_type = 1)
219 OR (planning_organization_id = p_vendor_site_id
220 AND planning_tp_type = 1));
221
222 IF l_onhand_qty > 0
223 THEN
224 RETURN TRUE;
225 END IF;
226 END LOOP;
227 END IF;
228
229 RETURN FALSE;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 RETURN FALSE;
234
235 END Sup_Site_Owns_Tps;
236
237 END PO_INV_THIRD_PARTY_STOCK_MDTR;
238
[Home] [Help]