[Home] [Help]
PACKAGE BODY: APPS.PO_SUBINVENTORIES_S3
Source
1 PACKAGE BODY PO_SUBINVENTORIES_S3 as
2 /* $Header: POXCOS3B.pls 120.0.12010000.1 2008/09/18 12:20:55 appldev noship $*/
3
4 /*=========================== PO_SUBINVENTORIES_S3 =========================*/
5 /*===========================================================================
6
7 FUNCTION NAME: val_expense_asset ()
8
9 ===========================================================================*/
10 function val_expense_asset(x_item_id in number,
11 x_src_org_id in number,
12 x_src_subinventory in varchar2,
13 x_dest_org_id in number,
14 x_dest_subinventory in varchar2) return boolean is
15
16 src_sub_asset_flag number := null;
17 src_item_asset_flag varchar2(1) := null;
18 dest_sub_asset_flag number := null;
19 dest_item_asset_flag varchar2(1) := null;
20 fob_point number := null;
21 intransit_type number := null;
22 progress varchar2(3) := null;
23
24 CURSOR asset (x_subinventory in varchar2,
25 x_org_id in number,
26 x_item_id in number) is
27
28 SELECT msub.asset_inventory,
29 msi.inventory_asset_flag
30 FROM mtl_secondary_inventories msub,
31 mtl_system_items msi
32 WHERE msub.secondary_inventory_name = x_subinventory
33 AND msub.organization_id = x_org_id
34 AND msi.inventory_item_id = x_item_id
35 AND msi.organization_id = x_org_id;
36
37 CURSOR transfer is
38
39 SELECT intransit_type,
40 fob_point
41 FROM mtl_interorg_parameters
42 WHERE from_organization_id = x_src_org_id
43 AND to_organization_id = x_dest_org_id;
44
45 begin
46
47 /* Identify whether the source subinventory is asset or
48 ** expense. 1 = Asset, 2 = Expense. Also identify
49 ** whether the item is an asset in the source org.
50 ** Y = asset item, N = expense item.
51 */
52
53 progress := '10';
54
55 open asset(x_src_subinventory,
56 x_src_org_id,
57 x_item_id);
58 loop
59
60 FETCH asset INTO src_sub_asset_flag,
61 src_item_asset_flag;
62
63 exit when asset%NOTFOUND;
64 end loop;
65 close asset;
66
67 /* Identify whether the destination subinventory is asset or
68 ** expense. 1 = Asset, 2 = Expense. Also identify
69 ** whether the item is an asset in the destination org.
70 ** Y = asset item, N = expense item.
71 */
72
73 progress := '20';
74
75 open asset(x_dest_subinventory,
76 x_dest_org_id,
77 x_item_id);
78 loop
79
80 FETCH asset INTO dest_sub_asset_flag,
81 dest_item_asset_flag;
82
83 exit when asset%NOTFOUND;
84 end loop;
85 close asset;
86
87 /* Get the intransit type and FOB point for interorg
88 ** transfers. 1 = Direct, 2 = Intransit. 1 = FOB
89 ** Shipment, 2 = FOB Receipt.
90 */
91
92 progress := '30';
93
94 open transfer;
95 loop
96
97 FETCH transfer INTO intransit_type,
98 fob_point;
99
100 exit when transfer%NOTFOUND;
101 end loop;
102 close transfer;
103
104 /* Now call the routine that checks whether the item/sub
105 ** combination is valid.
106 */
107
108 progress := '40';
109
110 if (val_expense_asset(x_item_id,
111 src_sub_asset_flag,
112 src_item_asset_flag,
113 dest_sub_asset_flag,
114 dest_item_asset_flag,
115 fob_point,
116 intransit_type) = FALSE) then
117 return (FALSE);
118 else
119 return (TRUE);
120 end if;
121
122 exception
123 when others then
124 po_message_s.sql_error('val_expense_asset', progress, sqlcode);
125 raise;
126
127 end val_expense_asset;
128
129 function val_expense_asset(x_item_id in number,
130 x_src_sub_asset_flag in number,
131 x_src_item_asset_flag in varchar2,
132 x_dest_sub_asset_flag in number,
133 x_dest_item_asset_flag in varchar2,
134 x_fob_point in number,
135 x_intransit_type in number) return boolean is
136
137 valid_combination boolean := TRUE;
138
139 begin
140
141 /* Note: key inventory values:
142 **
143 ** subinventory asset: 1 = Asset, 2 = Expense
144 **
145 ** item asset: Y = Asset, N = Expense
146 **
147 ** intransit type: 1= Direct, 2 = Intransit
148 **
149 ** fob point: 1 = Shipment, 2 = Receipt
150 **
151 */
152
153 /* You are performing a direct transfer (fob is
154 ** irrelevant).
155 **
156 ** Invalid combinations: source item = asset
157 ** to item = asset
158 ** source sub = expense
159 ** to sub = asset
160 **
161 ** source item = expense
162 ** to item = asset
163 ** to sub = asset
164 */
165
166 if (x_intransit_type = 1) then
167 if (((x_src_item_asset_flag = 'Y') and
168 (x_dest_item_asset_flag = 'Y') and
169 (x_src_sub_asset_flag = 2) and
170 (x_dest_sub_asset_flag = 1)) or
171 ((x_src_item_asset_flag = 'N') and
172 (x_dest_item_asset_flag = 'Y') and
173 (x_dest_sub_asset_flag = 2))) then
174 valid_combination := FALSE;
175 end if;
176
177 /* You are performing an instrasit transfer
178 ** with an fob point of origin (1 = Shipment).
179 **
180 ** Invalid combinations: source item = expense
181 ** to item = asset
182 **
183 ** source item = asset
184 ** to item = asset
185 ** from sub = expense
186 */
187
188 elsif (x_fob_point = 1) then
189 if (((x_src_item_asset_flag = 'Y') and
190 (x_dest_item_asset_flag = 'N') and
191 (x_src_sub_asset_flag = 2)) or
192 ((x_src_item_asset_flag = 'N') and
193 (x_dest_item_asset_flag = 'Y'))) then
194 valid_combination := FALSE;
195 end if;
196
197 /* You are performing an intransit transfer
198 ** with an fob point of destination (2 = Receipt).
199 **
200 ** Invalid combinations: source item = asset
201 ** source sub = expense
202 **
203 ** source item = expense
204 ** to item = asset
205 ** to sub = asset
206 */
207 /* Bug : 711688
208 ** Changed x_dest_sub_asset_flag to x_src_sub_asset_flag.
209 ** As per the comments above, the invalid combination is
210 ** source item = asset and source sub = expense */
211
212 elsif (x_fob_point = 2) then
213 if (((x_src_item_asset_flag = 'Y') and
214 (x_src_sub_asset_flag = 2)) or
215 ((x_src_item_asset_flag = 'N') and
216 (x_dest_item_asset_flag = 'Y') and
217 (x_dest_sub_asset_flag = 1))) then
218 valid_combination := FALSE;
219 end if;
220 end if;
221
222 return (valid_combination);
223
224 end val_expense_asset;
225
226 END PO_SUBINVENTORIES_S3;