1 PACKAGE BODY PO_SOURCING_SV4 as
2 /* $Header: POXSCS4B.pls 120.1 2006/02/14 12:28:41 dreddy noship $ */
3
4 /*============================= PO_SOURCING_SV4 ===========================*/
5
6 /*===========================================================================
7
8 FUNCTION NAME: get_disposition_message()
9
10 ===========================================================================*/
11 function get_disposition_message(x_item_id in number,
12 x_org_id in number,
13 x_cross_ref_type in varchar2,
14 x_message in out NOCOPY varchar2,
15 x_multiple_flag in out NOCOPY varchar2) return boolean is
16
17 x_progress varchar2(3) := NULL;
18
19 begin
20
21 /* Select all cross references having the cross reference type
22 ** identified in the PO: Item Cross Reference Type profile
23 ** option (this is passed in to get_disposition_message as
24 ** an argument.) This SELECCT will look for messages either having an
25 ** exact match with the specified organizaiton, or messages that
26 ** are applicable to all organizations. If > 1 message is
27 ** selected, the when TOO_MANY_ROWS exception handler will
28 ** set the x_multiple_flag to 'Y' and return TRUE.
29 */
30
31 x_progress := '010';
32
33 SELECT mcr.cross_reference
34 INTO x_message
35 FROM mtl_cross_references mcr
36 WHERE mcr.inventory_item_id = x_item_id
37 AND mcr.cross_reference_type = x_cross_ref_type
38 AND (mcr.organization_id = x_org_id
39 OR
40 mcr.org_independent_flag = 'Y');
41
42 return (TRUE);
43
44 exception
45
46 when no_data_found then
47 return (FALSE);
48
49 when too_many_rows then
50 x_multiple_flag := 'Y';
51 return (TRUE);
52
53 when others then
54 po_message_s.sql_error('get_disposition_message', x_progress, sqlcode);
55 raise;
56
57 end get_disposition_message;
58
59 /*===========================================================================
60
61 FUNCTION NAME: val_src_dest()
62
63 ===========================================================================*/
64 FUNCTION val_src_dest(x_val_level in varchar2,
65 x_sob_id in number,
66 x_item_id in number,
67 x_item_revision in varchar2,
68 x_ship_to in varchar2,
69 x_receiving in varchar2,
70 x_source_type in out NOCOPY varchar2,
71 x_destination_type in out NOCOPY varchar2,
72 x_destination_org_id in out NOCOPY number,
73 x_destination_loc_id in out NOCOPY number,
74 x_destination_subinventory in out NOCOPY varchar2,
75 x_source_org_id in out NOCOPY number,
76 x_source_subinventory in out NOCOPY varchar2,
77 x_error_type in out NOCOPY varchar2) return boolean is
78
79 x_progress varchar2(3) := NULL;
80 x_val_internal varchar2(1) := NULL;
81 x_validation_type varchar2(20) := NULL;
82 x_sub_error_type varchar2(50) := NULL;
83
84 x_dest_sub_valid BOOLEAN ;
85 x_source_sub_valid BOOLEAN;
86
87 begin
88
89 if (x_val_level = 'ORG') then
90
91 x_progress := '010';
92
93 if (po_orgs_sv2.val_dest_org(x_destination_org_id,
94 x_item_id,
95 x_item_revision,
96 x_destination_type,
97 x_sob_id,
98 x_source_type) = FALSE) then
99
100 x_destination_org_id := -1;
101 return (FALSE);
102
103 x_progress := '020';
104
105 elsif ((x_source_type = 'INVENTORY') and
106 (po_orgs_sv2.val_source_org(x_source_org_id,
107 x_destination_org_id,
108 x_destination_type,
109 x_item_id,
110 x_item_revision,
111 x_sob_id,
112 x_error_type) = FALSE)) then
113 --bug#3464868 if the item is not internally orderable when both
114 --the source organization and destination orgnaization are the same
115 --the error type is 'PO_RI_INT_ORD_NOT_ENABLED. In this case
116 --we assign -2 to the source_org_id so that an appropriate error may
117 --be displayed
118 if (x_error_type = 'PO_RI_INT_ORD_NOT_ENABLED') then
119 x_source_org_id := -2;
120 else
121 x_source_org_id := -1;
122 end if;
123 --bug#3464868
124 return (FALSE);
125
126 end if;
127 end if;
128
129
130 if ((x_val_level = 'ORG') or
131 (x_val_level = 'LOC')) then
132
133 if (x_destination_loc_id is not null) then
134
135 x_progress := '030';
136
137 if (x_source_type = 'INVENTORY') then
138 x_val_internal := 'Y';
139 end if;
140
141 -- Bug 5028505: Added source Org id param
142 if (po_locations_sv2.val_location(x_destination_loc_id,
143 x_destination_org_id,
144 x_ship_to,
145 x_receiving,
146 x_val_internal,
147 x_source_org_id ) = FALSE) then
148 x_destination_loc_id := -1;
149 end if;
150 end if;
151 end if;
152
153 /* Even if the location validation fails, we want to proceed
154 ** with the subinventory validation because they are independent.
155 */
156
157 if ((x_val_level = 'ORG') or
158 (x_val_level = 'LOC') or
159 (x_val_level = 'SUB')) then
160
161 if (x_destination_subinventory is not null) then
162
163 x_validation_type := 'DESTINATION';
164
165 x_progress := '040';
166
167 x_dest_sub_valid := po_subinventories_s2.val_subinventory(
168 x_destination_subinventory,
169 x_destination_org_id,
170 x_source_type,
171 x_source_subinventory,
172 x_source_org_id,
173 trunc(sysdate),
174 x_item_id,
175 x_destination_type,
176 x_validation_type,
177 x_sub_error_type);
178 IF x_dest_sub_valid = FALSE THEN
179
180 x_destination_subinventory := to_char(-1);
181 return(FALSE);
182
183 END IF;
184
185 end if; -- chek on x_destination_subinventory
186
187 x_progress := '050';
188
189 if (x_source_type = 'INVENTORY') and
190 (x_source_subinventory is not null) then
191
192 x_validation_type := 'SOURCE';
193
194 x_source_sub_valid := po_subinventories_s2.val_subinventory(
195 x_destination_subinventory,
196 x_destination_org_id,
197 x_source_type,
198 x_source_subinventory,
199 x_source_org_id,
200 trunc(sysdate),
201 x_item_id,
202 x_destination_type,
203 x_validation_type,
204 x_sub_error_type);
205 IF x_source_sub_valid = FALSE THEN
206
207 x_source_subinventory := to_char(-1);
208 return(FALSE);
209 END IF;
210
211 end if; -- check x_source_type
212
213 end if; -- check x_val_level
214
215 if ((x_destination_loc_id = -1) or
216 (x_destination_subinventory = '-1') or
217 (x_source_subinventory = '-1')) then
218 return (FALSE);
219 else
220 return (TRUE);
221 end if;
222
223 exception
224
225 when others then
226 po_message_s.sql_error('val_src_dest', x_progress, sqlcode);
227 raise;
228
229 end val_src_dest;
230
231 END PO_SOURCING_SV4;
[Home] [Help]