1 PACKAGE BODY PO_VMI_GRP as
2 --$Header: POXGVMIB.pls 115.10 2002/12/14 01:07:50 fdubois noship $
3
4 --===============+============================================================+
5 --| Copyright (c) 2002 Oracle Corporation |
6 --| Redwood Shores, California, USA |
7 --| All rights reserved. |
8 --============================================================================+
9 --| |
10 --| FILENAME : POXGVMIB.pls |
11 --| |
12 --| DESCRIPTION: This package contains the function that return TRUE|
13 --| if there exist a VMI ASL within the Operating Unit |
14 --| |
15 --| FUNCTION/PROCEDURE: exist_vmi_asl |
16 --| |
17 --| HISTORY: Created : fdubois - 27-FEB-2002: Empty Stubb |
18 --| Modified: fdubois - 28-FEB-2002: Add logic for the |
19 --| function |
20 --| Modified: fdubois - 13-MAR-2002 : Modified dbdrv |
21 --| syntax |
22 --| Modified :fdubois - 15-APR-2002 : Add |
23 --| validate_global_vmi_asl function |
24 --|===========================================================================+
25
26
27 /*===========================================================================
28 FUNCTION NAME: exist_vmi_asl
29
30 DESCRIPTION: the function retunrs TRUE if there exist a VMI ASL
31 within the Operating Unit. If there are none it
32 returns FALSE
33
34 PARAMETERS: In:
35 Out: TRUE if exists VMI ASL
36
37 DESIGN REFERENCES: APXSSFSO_VMI_DLD.doc
38
39
40 CHANGE HISTORY: Created 27-FEB-02 FDUBOIS
41 Empty Stubb
42 ===========================================================================*/
43 FUNCTION exist_vmi_asl RETURN BOOLEAN is
44
45 l_exist_VMI_ASL BOOLEAN ;
46 l_count_exist_VMI_ASL NUMBER ;
47
48 /* VMI FPH Start */
49 BEGIN
50
51 -- Returns 1 if there exist a VMI ASL within the current OU
52 SELECT count('x')
53 INTO l_count_exist_VMI_ASL
54 FROM dual
55 WHERE exists
56 ( SELECT 'X'
57 FROM po_approved_supplier_list pasl,
58 po_vendor_sites pvs ,
59 po_asl_status_rules_v pasr ,
60 po_asl_attributes paa
61 WHERE pasl.vendor_site_id = pvs.vendor_site_id
62 AND pasr.status_id = pasl.asl_status_id
63 AND pasr.business_rule like '2_SOURCING'
64 AND pasr.allow_action_flag like 'Y'
65 AND ( pasl.disable_flag = 'N'
66 OR pasl.disable_flag IS NULL)
67 AND paa.asl_id = pasl.asl_id
68 AND paa.enable_vmi_flag = 'Y' ) ;
69
70
71 -- Assign the boolean value depending on the return count
72 IF l_count_exist_VMI_ASL = 1
73 THEN
74 l_exist_VMI_ASL := TRUE ;
75 ELSE
76 l_exist_VMI_ASL := FALSE ;
77 END IF ;
78
79 -- Return the value
80 return l_exist_VMI_ASL ;
81
82 END exist_vmi_asl ;
83
84
85 /*===========================================================================
86 FUNCTION NAME: validate_global_vmi_asl
87
88 DESCRIPTION: the function retunrs TRUE if the Global ASL can be
89 VMI for the IN parameters (define the ASL). False
90 otherwize. It then also return the Validation Error
91 Message name
92
93 PARAMETERS: In: INVENTORY_ITEM_ID :Item identifier
94 SUPPLIER_SITE_ID : supplier site identifier
95
96 Out:VALIDATION_ERROR_NAME : Error message name
97
98 Return: TRUE if OK to have Global VMI ASL
99
100 DESIGN REFERENCES: MGD_VMI_ASL_DLD.rtf
101
102
103 CHANGE HISTORY: Created 15-APR-02 FDUBOIS
104 ===========================================================================*/
105 FUNCTION validate_global_vmi_asl (x_inventory_item_id IN number ,
106 x_supplier_site_id IN number ,
107 x_validation_error_name OUT NOCOPY varchar2 )
108 RETURN BOOLEAN is
109
110 l_purch_encumbrance_flag varchar2(1) ;
111 l_req_encumbrance_flag varchar2(1) ;
112
113
114 -- This cursor brings the info needed to validate the VMI Global ASL
115 -- It brings back the Item/Inventory Org Info for all inventory Org
116 -- / Item linked to the SOB that is associated with the supplier site
117 -- Operating Unit
118 cursor c_vmi_global_asl (x_inventory_item_id NUMBER ,
119 x_supplier_site_id NUMBER ) is
120 select hoi.organization_id ,
121 DECODE(HOI.ORG_INFORMATION_CONTEXT, 'Accounting Information',
122 TO_NUMBER(HOI.ORG_INFORMATION3), TO_NUMBER(NULL)) operating_unit ,
123 mp.wms_enabled_flag ,
124 imst.whse_code ,
125 msi.item_type ,
126 msi.outside_operation_flag ,
127 msi.eam_item_type ,
128 msi.base_item_id ,
129 msi.bom_item_type ,
130 msi.replenish_to_order_flag ,
131 msi.auto_created_config_flag
132 from gl_sets_of_books gsob ,
133 hr_organization_units hou ,
134 hr_organization_information hoi ,
135 mtl_parameters mp ,
136 hr_organization_information hoi2 ,
137 mtl_system_items msi ,
138 ic_whse_mst imst
139 where HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
140 and HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
141 and HOI.ORG_INFORMATION_CONTEXT||'' ='Accounting Information'
142 and HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
143 and hoi.organization_id = hoi2.organization_id
144 and hoi2.org_information_context = 'CLASS'
145 and hoi2.org_information1 = 'INV'
146 and msi.organization_id = hoi.organization_id
147 and msi.inventory_item_id = x_inventory_item_id
148 and hoi.organization_id = imst.mtl_organization_id (+)
149 and GSOB.SET_OF_BOOKS_ID = (
150 select set_of_books_id
151 from po_vendor_sites_all pvsa ,
152 HR_ORGANIZATION_INFORMATION HOI ,
153 GL_SETS_OF_BOOKS GSOB
154 where pvsa.vendor_site_id = x_supplier_site_id
155 and NVL(DECODE(HOI.ORG_INFORMATION_CONTEXT,'Accounting Information',
156 TO_NUMBER(HOI.ORG_INFORMATION3),TO_NUMBER(NULL)),-99)= NVL(pvsa.org_id,-99)
157 and HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
158 and rownum < 2) ;
159
160 BEGIN
161
162
163 -- First Validate the Encumbrance for the GLobal ASL
164 -- get the encumbrance flags for the OU linked to the vendor site id
165 SELECT fspa.purch_encumbrance_flag,
166 fspa.req_encumbrance_flag
167 INTO l_purch_encumbrance_flag ,
168 l_req_encumbrance_flag
169 FROM FINANCIALS_SYSTEM_PARAMS_ALL fspa ,
170 po_vendor_sites_all pvs
171 WHERE pvs.vendor_site_id = x_supplier_site_id
172 AND NVL(fspa.org_id,-99) = NVL(pvs.org_id,-99) ;
173
174
175 -- *** ENCUMBRANCE ACCOUNTING VALIDATION ***
176 -- First check for the encumbrance
177 IF l_purch_encumbrance_flag = 'Y' OR l_req_encumbrance_flag = 'Y'
178 THEN
179 -- Set the Validation error message
180 x_validation_error_name := 'PO_VMI_ENCUMBRANCE_ENABLED' ;
181 -- Fail validation
182 RETURN FALSE ;
183 END IF ;
184
185
186 -- Fetch the cursor into the record and loop
187 FOR c_vmi_global_asl_rec IN
188 c_vmi_global_asl(x_inventory_item_id,x_supplier_site_id)
189 LOOP
190
191
192 -- *** OPM ITEM VALIDATION ***
193 -- First check for OPM Item
194 IF c_vmi_global_asl_rec.whse_code IS NOT NULL
195 THEN
196 -- Set the Validation error message
197 x_validation_error_name := 'PO_VMI_OPM_ORG_GLOBAL' ;
198 -- Exit the cursor loop
199 EXIT ;
200 END IF ;
201
202
203 -- *** WMS INV. ORG VALIDATION ***
204 -- First check for WMS enable flag
205 IF c_vmi_global_asl_rec.wms_enabled_flag IN ('Y','y')
206 THEN
207 -- Set the Validation error message
208 x_validation_error_name := 'PO_VMI_WMS_INSTALLED_GLOBAL' ;
209 -- Exit the cursor loop
210 EXIT ;
211 END IF ;
212
213
214 -- *** OSP ITEM VALIDATION ***
215 -- First check for OSP Item
216 IF c_vmi_global_asl_rec.outside_operation_flag = 'Y'
217 THEN
218 -- Set the Validation error message
219 x_validation_error_name := 'PO_VMI_OSP_ITEM' ;
220 -- Exit the cursor loop
221 EXIT ;
222 END IF ;
223
224
225 -- *** CTO ITEM VALIDATION ***
226 -- First check for CTO Item
227 IF c_vmi_global_asl_rec.bom_item_type IN (1,2)
228 OR ( c_vmi_global_asl_rec.replenish_to_order_flag = 'Y' AND
229 c_vmi_global_asl_rec.base_item_id IS NULL AND
230 c_vmi_global_asl_rec.auto_created_config_flag = 'Y')
231 THEN
232 -- Set the Validation error message
233 x_validation_error_name := 'PO_VMI_CTO_ITEM' ;
234 -- Exit the cursor loop
235 EXIT ;
236 END IF ;
237
238
239 -- *** EAM ITEM VALIDATION ***
240 -- First check for EAM Item
241 IF c_vmi_global_asl_rec.eam_item_type IS NOT NULL
242 THEN
243 -- Set the Validation error message
244 x_validation_error_name := 'PO_VMI_EAM_ITEM' ;
245 -- Exit the cursor loop
246 EXIT ;
247 END IF ;
248
249
250 END LOOP ;
251
252
253 -- Test the If one record failed the validation
254 IF x_validation_error_name IS NOT NULL
255 THEN
256 -- Fail validation
257 RETURN FALSE ;
258 ELSE
259 -- Pass validation
260 RETURN TRUE ;
261 END IF ;
262
263 END validate_global_vmi_asl ;
264
265 /* VMI FPH End*/
266
267 END PO_VMI_GRP;
[Home] [Help]