1 PACKAGE BODY PO_ORGS_SV2 as
2 /* $Header: POXCOO1B.pls 120.0.12000000.2 2007/04/26 08:43:07 vdurbhak ship $*/
3 /*============================= PO_ORGS_SV2 =============================*/
4
5 /*===========================================================================
6
7 FUNCTION NAME : val_dest_org()
8
9 ===========================================================================*/
10 function val_dest_org(x_destination_org_id in number,
11 x_item_id in number,
12 x_item_revision in varchar2,
13 x_destination_type in varchar2,
14 x_sob_id in number,
15 x_source_type in varchar2) return boolean is
16
17 x_progress varchar2(3) := NULL;
18 x_org_count number := 0;
19
20 begin
21
22 /* Perform the following basic org check in all cases (item is
23 ** null or item is specified).
24 */
25
26 x_progress := '010';
27
28 SELECT count(1)
29 INTO x_org_count
30 FROM org_organization_definitions ood
31 WHERE ood.organization_id = x_destination_org_id
32 AND ood.set_of_books_id = x_sob_id
33 AND sysdate < nvl(ood.disable_date, sysdate + 1);
34
35 if (x_org_count = 0) then
36 return (FALSE);
37 else
38 x_org_count := 0;
39 end if;
40
41 /* Now check item/org characteristics for predefined items.
42 */
43 /* Changes made due to Bug: 647379
44 ** The Purchaseable attribute for destination type 'EXPENSE' need not be
45 ** 'Y', if the source is not 'SUPPLIER'.
46 ** Hence checking the code for destination type expense separately from Shopfl
47 oor destination
48 */
49
50
51 if (x_item_id is not null) then
52 if (x_destination_type = 'INVENTORY') then
53
54 /* If delivering to Inventory, the item need be Stockable always and purchaseable
55 ** only if sourced from a vendor. If sourced from stores,
56 ** this is not a requirement. If the source type is NULL
57 ** at this point, the decode allows this SELECT to succeed.
58 */
59
60 x_progress := '020';
61
62 SELECT count(1)
63 INTO x_org_count
64 FROM mtl_system_items msi
65 WHERE msi.organization_id = x_destination_org_id
66 AND msi.inventory_item_id = x_item_id
67 AND msi.stock_enabled_flag = 'Y'
68 AND decode(x_source_type, 'VENDOR', 'Y',
69 msi.purchasing_enabled_flag) = msi.purchasing_enabled_flag
70 AND (x_item_revision is null
71 OR
72 x_destination_org_id in
73 (SELECT mir.organization_id
74 FROM mtl_item_revisions mir
75 WHERE mir.inventory_item_id = x_item_id
76 AND mir.revision = x_item_revision));
77
78 elsif (x_destination_type = 'EXPENSE') then
79
80 /* If delivering to Expense , the item need be Purchaseable
81 ** only if sourced from a vendor. If sourced from stores,
82 ** this is not a requirement. If the source type is NULL
83 ** at this point, the decode allows this SELECT to succeed.
84 */
85
86 x_progress := '040';
87
88 SELECT count(1)
89 INTO x_org_count
90 FROM mtl_system_items msi
91 WHERE msi.organization_id = x_destination_org_id
92 AND msi.inventory_item_id = x_item_id
93 AND decode(x_source_type, 'VENDOR', 'Y',
94 msi.purchasing_enabled_flag) = msi.purchasing_enabled_flag;
95 else
96
97 /* Check the Shop Floor destinations. This
98 ** destination is always supported by purchase orders, so
99 ** the items must be purchaseable and outside_operation_flag = 'Y'.
100 */
101
102 x_progress := '030';
103
104 SELECT count(1)
105 INTO x_org_count
106 FROM mtl_system_items msi
107 WHERE msi.inventory_item_id = x_item_id
108 AND msi.organization_id = x_destination_org_id
109 AND msi.purchasing_enabled_flag = 'Y'
110 AND msi.outside_operation_flag = 'Y';
111 end if;
112
113 if (x_org_count = 0) then
114 return (FALSE);
115 end if;
116 end if;
117
118 return (TRUE);
119
120 exception
121
122 when others then
123 po_message_s.sql_error('val_dest_org', x_progress, sqlcode);
124 raise;
125
126 end val_dest_org;
127
128 /*===========================================================================
129
130 FUNCTION NAME : val_source_org()
131
132 ===========================================================================*/
133 function val_source_org(x_source_org_id in number,
134 x_destination_org_id in number,
135 x_destination_type in varchar2,
136 x_item_id in number,
137 x_item_revision in varchar2,
138 x_sob_id in number,
139 x_error_type in out NOCOPY varchar2) return boolean is
140
141 x_progress varchar2(3) := NULL;
142 x_intransit_type number := NULL;
143 x_org_count number := 0;
144 --bug#3464868 creating a local variable
145 --to hold the value of internal_order_enabled_flag for
146 --the current item
147 l_internal_ordered varchar2(1) := NULL;
148 begin
149
150 /* Given that this function can yield two different failure
151 ** types (each requiring a different error message), set an
152 ** error type to tell the calling procedure how to handle
153 ** a FALSE result. The default case is an invalid organization.
154 ** This is overwritten only if there is a problem with an item
155 ** control mismatch in the source/destination orgs.
156 */
157
158 /* Ben: 4/3/97 bug#441341 We should not check the interorg transfers table when
159 doing transfers within the same org, since this table will not have an entry
160 for going from/to the same org.
161 */
162
163 IF x_source_org_id <> x_destination_org_id THEN
164
165 x_error_type := 'INVALID_ORG';
166
167 x_progress := '010';
168
169 SELECT mip.intransit_type
170 INTO x_intransit_type
171 FROM org_organization_definitions ood,
172 mtl_system_items msi,
173 mtl_interorg_parameters mip
174 WHERE ood.organization_id = x_source_org_id
175 AND sysdate < nvl(ood.disable_date, sysdate + 1)
176 AND mip.from_organization_id = x_source_org_id
177 AND mip.to_organization_id = x_destination_org_id
178 AND msi.organization_id = x_source_org_id
179 AND msi.inventory_item_id = x_item_id
180 AND msi.internal_order_enabled_flag = 'Y'
181 AND msi.stock_enabled_flag = 'Y'
182 AND (x_item_revision is null
183 OR
184 x_source_org_id in
185 (SELECT mir.organization_id
186 FROM mtl_item_revisions mir
187 WHERE mir.inventory_item_id = x_item_id
188 AND mir.revision = x_item_revision));
189
190 x_progress := '020';
191
192 if ((x_intransit_type = 1) and
193 (x_destination_type = 'INVENTORY')) then
194
195 /* For direct transfers (intransit type = 1), you cannot
196 ** go from looser to tighter revision, lot, or serial
197 ** control.
198 */
199
200 /* DEBUG -- need George to tell us if it is still a valid b-rule
201 ** to check whether the rev is restricted as well for intransit
202 ** shipments (intransit_type = 2) . Spoke with Meg: the problem
203 ** from her understanding is NOT receiving -- we need to prevent
204 ** passing a revision to OE. This is what Req Import *should* be
205 ** checking -- and Enter Reqs should prevent specifying a rev
206 ** for inventory sourced lines.
207 */
208
209 /* Bug# 4446916, We need to allow for Source having serial control as
210 * 'At Sales Order Issue' to destination having serial control as
211 * 'At Receipt or Predefine'. Removed the 6( 'At Sales Order Issue') in code *
212 * -- (msi1.serial_number_control_code in (1,6) --
213 */
214
215 SELECT count(1)
216 INTO x_org_count
217 FROM mtl_system_items msi1,
218 mtl_system_items msi2
219 WHERE msi1.inventory_item_id = x_item_id
220 AND msi1.organization_id = x_source_org_id
221 AND msi2.inventory_item_id = x_item_id
222 AND msi2.organization_id = x_destination_org_id
223 AND ((msi1.lot_control_code = 1 AND
224 msi2.lot_control_code = 2)
225 OR
226 (msi1.serial_number_control_code in (1) AND
227 msi2.serial_number_control_code in (2,3,5))
228 OR
229 (msi1.revision_qty_control_code = 1 AND
230 msi2.revision_qty_control_code = 2));
231
232 if (x_org_count = 1) then
233 x_error_type := 'SRC_DEST_ORG_CONTROL_MISMATCH';
234 return (FALSE);
235 end if;
236 end if;
237 -- bug#3464868 we need to check if the item is internally orderable
238 --when the source and destination organization id's are the same
239
240 ELSIF (nvl(x_source_org_id,-1)=nvl(x_destination_org_id,-2)) THEN
241 x_error_type := 'PO_RI_INT_ORD_NOT_ENABLED';
242
243 SELECT 'Y'
244 INTO l_internal_ordered
245 FROM org_organization_definitions ood,
246 mtl_system_items msi
247 WHERE ood.organization_id = x_source_org_id
248 AND sysdate < nvl(ood.disable_date, sysdate + 1)
249 AND msi.organization_id = x_source_org_id
250 AND msi.inventory_item_id = x_item_id
251 AND msi.internal_order_enabled_flag = 'Y'
252 AND msi.stock_enabled_flag = 'Y'
253 AND (x_item_revision is null
254 OR
255 x_source_org_id in
256 (SELECT mir.organization_id
257 FROM mtl_item_revisions mir
258 WHERE mir.inventory_item_id = x_item_id
259 AND mir.revision = x_item_revision));
260
261 x_progress := '022';
262 --bug#3464868
263
264 END IF;
265 return (TRUE);
266
267 exception
268 when no_data_found then
269 return (FALSE);
270 when others then
271 po_message_s.sql_error('val_source_org', x_progress, sqlcode);
272 raise;
273
274 end val_source_org;
275
276 END PO_ORGS_SV2;
[Home] [Help]