1 PACKAGE BODY PO_SUBINVENTORIES_S2 as
2 /* $Header: POXCOS2B.pls 120.0.12020000.2 2013/03/29 06:49:15 rkandima ship $*/
3
4 /*=========================== PO_SUBINVENTORIES_S2 =========================*/
5
6 /*===========================================================================
7
8 FUNCTION NAME: val_subinventory()
9
10 ===========================================================================*/
11 function val_subinventory(x_dest_subinventory in varchar2,
12 x_destination_org_id in number,
13 x_source_type in varchar2,
14 x_source_subinventory in varchar2,
15 x_source_org_id in number,
16 x_transaction_date in date,
17 x_item_id in number,
18 x_destination_type in varchar2,
19 x_validation_type in varchar2,
20 x_error_type in out NOCOPY varchar2)
21 return boolean is
22
23 x_progress varchar2(3) := NULL;
24 x_sub_count number := 0;
25 x_org_id number := NULL;
26 x_subinventory varchar2(30) := NULL;
27 x_reservations varchar2(1) := NULL;
28 x_allow_expense_sub varchar2(1) := NULL;
29
30 begin
31
32 /*
33 ** If this function returns FALSE then
34 ** the error type is used by the calling
35 ** procedure to determine the message
36 ** to be displayed. By default the message
37 ** is set to 'INVALID_SUB'.
38 */
39
40 x_error_type := 'INVALID_SUB';
41
42 /* Determine whether we should be validating the
43 ** source or destination subinventories.
44 */
45
46 if (x_validation_type = 'DESTINATION') then
47 x_org_id := x_destination_org_id;
48 x_subinventory := x_dest_subinventory;
49
50 elsif (x_validation_type = 'SOURCE') then
51 x_org_id := x_source_org_id;
52 x_subinventory := x_source_subinventory;
53
54 end if;
55
56 /* Check the common business rules that apply to both
57 ** source and destination subinventories.
58 */
59
60 x_progress := '010';
61
62 SELECT count(1)
63 INTO x_sub_count
64 FROM mtl_secondary_inventories msub,
65 mtl_system_items msi
66 WHERE msub.secondary_inventory_name = x_subinventory
67 AND msub.organization_id = x_org_id
68 AND x_transaction_date < nvl(msub.disable_date, x_transaction_date + 1)
69 AND msi.inventory_item_id = x_item_id
70 AND msi.organization_id = x_org_id
71 AND (msi.restrict_subinventories_code = 2
72 OR
73 (msi.restrict_subinventories_code = 1 and exists
74 (SELECT null
75 FROM mtl_item_sub_inventories mis
76 WHERE mis.organization_id = x_org_id
77 AND mis.inventory_item_id = x_item_id
78 AND mis.secondary_inventory = x_subinventory)));
79
80 if (x_sub_count = 0) then
81 return (FALSE);
82 else
83 x_sub_count := 0;
84 end if;
85
86 /* Now check subinventory attributes that are unique to
87 ** Inventory-sourced orders if we are performing source
88 ** subinventory validation.
89 */
90
91 if ((x_validation_type = 'SOURCE') and
92 (x_source_type = 'INVENTORY')) then
93
94 /* If the source and destination orgs are the same, you cannot
95 ** source and deliver to the same subinventory. If the subs
96 ** differ but the item is MRP planned, then the source subinventory
97 ** must be non-nettable.
98 */
99
100 if ((x_source_org_id = x_destination_org_id) and
101 (x_destination_type = 'INVENTORY')) then
102
103 x_progress := '020';
104
105 if (x_source_subinventory = x_dest_subinventory) then
106
107 x_error_type := 'DEST_SUB_EQS_SRC_SUB';
108 return (FALSE);
109
110 elsif
111 (po_subinventories_s2.val_mrp_src_sub(x_source_subinventory,
112 x_source_org_id,
113 x_destination_org_id,
114 x_item_id) = FALSE) then
115
116 return (FALSE);
117 end if;
118 end if;
119
120 /* Get the Order Entry reservations flag. If
121 ** this flag is set to 'Y', then the source
122 ** subinventory must be reservable
123 ** (reservable_type = 1).
124 */
125
126 x_progress := '030';
127 fnd_profile.get('SO_RESERVATIONS', x_reservations);
128
129 /* Ben: 2/13/97
130 ** If order entry reservation is ON, then the source subinventory must
131 ** be quantity-tracked (quantity_tracked=1) and reservable_type=1.
132 ** Issue error message if that is not the case.
133 */
134
135 x_progress := '040';
136
137 SELECT count(1)
138 INTO x_sub_count
139 FROM mtl_secondary_inventories msub
140 WHERE msub.secondary_inventory_name = x_source_subinventory
141 AND msub.organization_id = x_source_org_id
142 AND msub.quantity_tracked = 1
143 AND decode(x_reservations, 'Y', 1, msub.reservable_type)
144 = msub.reservable_type;
145
146 IF x_sub_count = 0 THEN
147
148 x_error_type := 'PO_RI_SRC_SUB_NOT_RESERVABLE';
149
150 return(FALSE);
151 END IF;
152
153 end if; /* if ((x_validation_type = 'SOURCE')... */
154
155 /* Now for internally sourced items going to inventory, check
156 ** that the source and destination subinventories are valid based
157 ** on their asset status in combination with the item's asset status
158 ** in the source and destination orgs.
159 */
160
161 /* bug 14262930 : commenting following validation as it is taken care by
162 source sub inventory lov */
163
164 /* if (x_source_type = 'INVENTORY') then
165 if ((x_source_subinventory is not null) and
166 (x_source_org_id is not null) and
167 (x_dest_subinventory is not null) and
168 (x_destination_org_id is not null) and
169 (x_destination_type = 'INVENTORY')) then
170
171 if (po_subinventories_s3.val_expense_asset(x_item_id,
172 x_source_org_id,
173 x_source_subinventory,
174 x_destination_org_id,
175 x_dest_subinventory) = FALSE) then
176 x_error_type := 'INVALID_EXP_ASSET_SUBS';
177 return (FALSE);
178 end if;
179 end if;
180 end if; */
181
182 return (TRUE);
183
184 exception
185 when others then
186 po_message_s.sql_error('val_subinventory', x_progress, sqlcode);
187 raise;
188
189 end val_subinventory;
190
191
192 /*===========================================================================
193
194 FUNCTION NAME: val_mrp_src_sub()
195
196 ===========================================================================*/
197 function val_mrp_src_sub(x_subinventory in varchar2,
198 x_source_org_id in number,
199 x_destination_org_id in number,
200 x_item_id in number) return boolean is
201
202 x_progress varchar2(3) := NULL;
203 x_sub_count number := 0;
204
205 begin
206
207 /* If the source and destination organizations are the
208 ** same, if the item is MRP planned, verify that the
209 ** source subinventory is non-nettable. If the
210 ** mrp_planning_code is 3 or 4, the item is MRP planned
211 ** and the subinventory availability type must = 2
212 ** (non-nettable).
213 */
214
215 if (x_source_org_id = x_destination_org_id) then
216
217 x_progress := '010';
218
219 SELECT count(1)
220 INTO x_sub_count
221 FROM mtl_secondary_inventories msub,
222 mtl_system_items msi
223 WHERE msub.organization_id = x_source_org_id
224 AND msub.secondary_inventory_name = x_subinventory
225 AND msi.inventory_item_id = x_item_id
226 AND msi.organization_id = x_source_org_id
227 AND decode(msi.mrp_planning_code, '3', 2,
228 '4', 2, msub.availability_type)
229 = msub.availability_type;
230
231 if (x_sub_count = 0) then
232 return (FALSE);
233 end if;
234 end if;
235
236 return (TRUE);
237
238 exception
239 when others then
240 po_message_s.sql_error('val_mrp_src_sub', x_progress, sqlcode);
241 raise;
242 end val_mrp_src_sub;
243
244 END PO_SUBINVENTORIES_S2;
[Home] [Help]