1 PACKAGE BODY PO_INV_THIRD_PARTY_STOCK_MDTR AS
2 -- $Header: POXMTPSB.pls 120.1.12020000.2 2013/02/10 21:35:35 vegajula ship $
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
11 --| DESCRIPTION |
8 --| FILENAME |
9 --| POXMTPSB.pls |
10 --| |
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 CURSOR c_item_id IS
69 SELECT /*+FIRST_ROWS */ 1
70 FROM mtl_consumption_transactions mct,
71 mtl_material_transactions mmt
72 WHERE mmt.transaction_id = mct.transaction_id
73 AND mmt.transaction_source_id = p_transaction_source_id
74 AND mmt.inventory_item_id = p_item_id
75 AND Nvl(mct.consumption_processed_flag,'N') <> 'Y';
76
77 CURSOR c_item_id_null IS
78 SELECT /*+FIRST_ROWS */ 1
79 FROM mtl_consumption_transactions mct,
80 mtl_material_transactions mmt
81 WHERE mmt.transaction_id = mct.transaction_id
82 AND mmt.transaction_source_id = p_transaction_source_id
83 AND Nvl(mct.consumption_processed_flag,'N') <> 'Y';
84
85
86 BEGIN
87 IF(p_item_id IS NOT NULL)
88 THEN
89 /* Bug 14541173 Used cursors c_item_id, c_item_id_null and 'FIRST_ROWS' optimizer hint to improve performance */
90 open c_item_id;
91 fetch c_item_id into l_count_cons_trans;
92 close c_item_id;
93 ELSE
94 open c_item_id_null;
95 fetch c_item_id_null into l_count_cons_trans;
96 close c_item_id_null;
97 END IF;
98
99
100 /* Bug 14541173 Commented the below code */
101
102 /*
103 SELECT count('Y')
104 INTO l_count_cons_trans
105 FROM dual
106 WHERE EXISTS(SELECT 'Y'
107 FROM MTL_CONSUMPTION_TRANSACTIONS MCT,
108 MTL_MATERIAL_TRANSACTIONS MMT
109 WHERE MMT.TRANSACTION_ID = MCT.TRANSACTION_ID
110 AND MMT.TRANSACTION_SOURCE_ID = p_transaction_source_id
111 AND MMT.INVENTORY_ITEM_ID = p_item_id
112 AND nvl(MCT.CONSUMPTION_PROCESSED_FLAG, 'N') <> 'Y');
113 ELSE
114 SELECT count('Y')
115 INTO l_count_cons_trans
116 FROM dual
117 WHERE EXISTS(SELECT 'Y'
118 FROM MTL_CONSUMPTION_TRANSACTIONS MCT,
122 AND nvl(MCT.CONSUMPTION_PROCESSED_FLAG, 'N') <> 'Y');
119 MTL_MATERIAL_TRANSACTIONS MMT
120 WHERE MMT.TRANSACTION_ID = MCT.TRANSACTION_ID
121 AND MMT.TRANSACTION_SOURCE_ID = p_transaction_source_id
123
124
125 END IF; */
126
127 IF(l_count_cons_trans > 0)
128 THEN
129 RETURN 'Y';
130 ELSE
131 RETURN 'N';
132 END IF;
133
134 EXCEPTION
135
136 WHEN OTHERS THEN
137 RAISE;
138
139 END consumption_trans_exist;
140
141
142 --========================================================================
143 -- FUNCTION : Supplier_Owns_Tps PUBLIC
144 -- PARAMETERS : p_vendor_id IN NUMBER
145 -- RETURN : TRUE if on hand consigned stock exist for the supplier;
146 -- FALSE otherwise.
147 -- DESCRIPTION : Check whether on hand consigned stock exists for a given
148 -- supplier. The function checks whether any supplier site
149 -- of this supplier owns on hand consigned stock.
150 --
151 -- CHANGE HISTORY :
152 -- 18-Nov-2002 Created by VMA
153 -- 30-Oct-2003 Bug fix for bug #3131113:
154 -- 1. Added query to PO_APPROVED_SUPPLIER_LIST to make
155 -- use of index on MTL_ONHAND_QUANTITIES_DETAIL.
156 -- 2. Updated logic to check for existance of onhand
157 -- consigned stock. Old logic checked existence of
158 -- any record in MOQD. This is replaced with new logic
159 -- that checks the sum of primary_transaction_quantity
160 -- for an item owned by a supplier in MOQD.
161 --========================================================================
162 FUNCTION Supplier_Owns_Tps (p_vendor_id IN NUMBER) RETURN BOOLEAN IS
163
164 l_onhand_qty mtl_onhand_quantities_detail.primary_transaction_quantity%TYPE;
165
166 BEGIN
167
168 IF p_vendor_id IS NOT NULL
169 THEN
170 -- Start Bug 4459947
171 -- Do not hardcode schema names
172 -- GSCC checker parses asl as schema name even though in this case
173 -- it is just an alias. Changing the alias name from asl to po_asl.
174 FOR po_asl IN (SELECT item_id, vendor_site_id
175 FROM po_asl_attributes
176 WHERE vendor_id = p_vendor_id
177 AND vendor_site_id IS NOT NULL
178 AND consigned_from_supplier_flag IS NOT NULL
179 ORDER BY consigned_from_supplier_flag DESC)
180 LOOP
181 SELECT SUM(primary_transaction_quantity)
182 INTO l_onhand_qty
183 FROM mtl_onhand_quantities_detail
184 WHERE inventory_item_id = po_asl.item_id
185 AND owning_organization_id = po_asl.vendor_site_id
186 AND owning_tp_type = 1;
187
188 IF l_onhand_qty > 0
189 THEN
190 RETURN TRUE;
191 END IF;
192 END LOOP;
193 -- End Bug 4459947
194 END IF;
195
196 RETURN FALSE;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 RETURN FALSE;
201
202 END Supplier_Owns_Tps;
203
204
205 --========================================================================
206 -- FUNCTION : Sup_Site_Owns_Tps PUBLIC
207 -- PARAMETERS : p_vendor_site_id IN NUMBER
208 -- RETURN : TRUE if on hand consigned or VMI stock exist for the
209 -- supplier site; FALSE otherwise. If p_vendor_site_id
210 -- is null, FALSE is returned.
211 -- DESCRIPTION : Check whether on hand consigned or VMI stock exists for
212 -- a given supplier site.
213 --
214 -- CHANGE HISTORY :
215 -- 18-Nov-2002 Created by VMA
216 -- 30-Oct-2003 Bug fix for bug #3131113:
217 -- 1. Added query PO_APPROVED_SUPPLIER_LIST to use
218 -- index on MTL_ONHAND_QUANTITIES_DETAIL.
219 -- 2. Updated logic to check for existance of onhand
220 -- consigned stock. Old logic checked existence of
221 -- any record in MOQD. This is replaced with new logic
222 -- that checks the sum of primary_transaction_quantity
223 -- for an item owned by a supplier site in MOQD.
224 --========================================================================
225 FUNCTION Sup_Site_Owns_Tps(p_vendor_site_id IN Number) RETURN BOOLEAN IS
226
227 l_onhand_qty mtl_onhand_quantities_detail.primary_transaction_quantity%TYPE;
228
229 TYPE item_id_tbl_type IS TABLE OF po_asl_attributes_val_v.item_id%TYPE
230 INDEX BY BINARY_INTEGER;
231
232 item_id_tbl item_id_tbl_type;
233
234 BEGIN
235
236 IF p_vendor_site_id IS NOT NULL
237 THEN
238
239 SELECT item_id BULK COLLECT INTO item_id_tbl
240 FROM po_asl_attributes
241 WHERE vendor_site_id = p_vendor_site_id
242 AND consigned_from_supplier_flag IS NOT NULL
243 ORDER BY consigned_from_supplier_flag DESC;
244
245 FOR i IN item_id_tbl.FIRST..item_id_tbl.LAST
246 LOOP
247 SELECT SUM(primary_transaction_quantity)
248 INTO l_onhand_qty
249 FROM mtl_onhand_quantities_detail
250 WHERE inventory_item_id = item_id_tbl(i)
251 AND ((owning_organization_id = p_vendor_site_id
252 AND owning_tp_type = 1)
253 OR (planning_organization_id = p_vendor_site_id
254 AND planning_tp_type = 1));
255
256 IF l_onhand_qty > 0
257 THEN
258 RETURN TRUE;
259 END IF;
260 END LOOP;
261 END IF;
262
263 RETURN FALSE;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 RETURN FALSE;
268
269 END Sup_Site_Owns_Tps;
270
271 END PO_INV_THIRD_PARTY_STOCK_MDTR;
272
[Home] [Help]