1 PACKAGE BODY PO_SUBINVENTORIES_S2 as
2 /* $Header: POXCOS2B.pls 115.2 2002/11/25 23:37:48 sbull 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 if (x_source_type = 'INVENTORY') then
162 if ((x_source_subinventory is not null) and
163 (x_source_org_id is not null) and
164 (x_dest_subinventory is not null) and
165 (x_destination_org_id is not null) and
166 (x_destination_type = 'INVENTORY')) then
167
168 if (po_subinventories_s3.val_expense_asset(x_item_id,
169 x_source_org_id,
170 x_source_subinventory,
171 x_destination_org_id,
172 x_dest_subinventory) = FALSE) then
173 x_error_type := 'INVALID_EXP_ASSET_SUBS';
174 return (FALSE);
175 end if;
176 end if;
177 end if;
178
179 return (TRUE);
180
181 exception
182 when others then
183 po_message_s.sql_error('val_subinventory', x_progress, sqlcode);
184 raise;
185
186 end val_subinventory;
187
188
189 /*===========================================================================
190
191 FUNCTION NAME: val_mrp_src_sub()
192
193 ===========================================================================*/
194 function val_mrp_src_sub(x_subinventory in varchar2,
195 x_source_org_id in number,
196 x_destination_org_id in number,
197 x_item_id in number) return boolean is
198
199 x_progress varchar2(3) := NULL;
200 x_sub_count number := 0;
201
202 begin
203
204 /* If the source and destination organizations are the
205 ** same, if the item is MRP planned, verify that the
206 ** source subinventory is non-nettable. If the
207 ** mrp_planning_code is 3 or 4, the item is MRP planned
208 ** and the subinventory availability type must = 2
209 ** (non-nettable).
210 */
211
212 if (x_source_org_id = x_destination_org_id) then
213
214 x_progress := '010';
215
216 SELECT count(1)
217 INTO x_sub_count
218 FROM mtl_secondary_inventories msub,
219 mtl_system_items msi
220 WHERE msub.organization_id = x_source_org_id
221 AND msub.secondary_inventory_name = x_subinventory
222 AND msi.inventory_item_id = x_item_id
223 AND msi.organization_id = x_source_org_id
224 AND decode(msi.mrp_planning_code, '3', 2,
225 '4', 2, msub.availability_type)
226 = msub.availability_type;
227
228 if (x_sub_count = 0) then
229 return (FALSE);
230 end if;
231 end if;
232
233 return (TRUE);
234
235 exception
236 when others then
237 po_message_s.sql_error('val_mrp_src_sub', x_progress, sqlcode);
238 raise;
239 end val_mrp_src_sub;
240
241 END PO_SUBINVENTORIES_S2;
[Home] [Help]