[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_SV5
Source
1 PACKAGE BODY PO_LINES_SV5 as
2 /* $Header: POXPOL5B.pls 120.3 2008/07/30 10:26:09 lgoyal ship $ */
3
4 /*============================= PO_LINES_SV ===============================*/
5
6
7 /*===========================================================================
8
9 PROCEDURE NAME: po_lines_post_query()
10
11 ===========================================================================*/
12
13 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
14 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_LINES_SV5';
15 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_LINES_SV5.';
16
17
18 procedure po_lines_post_query ( X_po_Line_id IN number,
19 X_from_header_id IN number,
20 X_from_line_id IN number,
21 X_line_type_id IN number,
22 X_item_id IN number,
23 X_inventory_org_id IN number,
24 X_expense_accrual_code IN varchar2,
25 X_po_header_id IN number,
26 X_type_lookup_code IN varchar2,
27 X_receipt_required_flag IN OUT NOCOPY varchar2,
28 X_quantity_received IN OUT NOCOPY number,
29 X_quantity_billed IN OUT NOCOPY number,
30 X_encumbered_flag IN OUT NOCOPY varchar2,
31 X_prevent_price_update_flag IN OUT NOCOPY varchar2,
32 X_online_req_flag IN OUT NOCOPY varchar2,
33 X_quantity_released IN OUT NOCOPY number,
34 X_amount_released IN OUT NOCOPY number,
35 X_quotation_number IN OUT NOCOPY varchar2,
36 X_quotation_line IN OUT NOCOPY number,
37 X_quotation_type IN OUT NOCOPY varchar2,
38 X_vendor_quotation_number IN OUT NOCOPY varchar2,
39 X_num_of_ship IN OUT NOCOPY number,
40 --< NBD TZ/Timestamp FPJ Start >
41 --X_promised_date IN OUT NOCOPY varchar2,
42 --X_need_by IN OUT NOCOPY varchar2,
43 X_promised_date IN OUT NOCOPY DATE,
44 X_need_by IN OUT NOCOPY DATE,
45 --< NBD TZ/Timestamp FPJ End >
46 X_num_of_dist IN OUT NOCOPY number,
47 X_code_combination_id IN OUT NOCOPY number,
48 X_line_total IN OUT NOCOPY number,
49 X_ship_total IN OUT NOCOPY number,
50 X_ship_total_rtot_db IN OUT NOCOPY number,
51 --togeorge 10/03/2000
52 --added oke variables
53 X_oke_contract_header_id IN number,
54 X_oke_contract_num IN OUT NOCOPY varchar2
55 ) is
56
57 X_Progress varchar2(3) := NULL;
58 /* 1063532 - FRKHAN: New param. added to get_quotation_info proc. */
59
60 x_quote_terms_id number := null;
61 x_quote_ship_via_lookup_code varchar2(25) := null;
62 x_quote_fob_lookup_code varchar2(25) := null;
63 x_quote_freight_terms varchar2(25) := null;
64
65 begin
66 X_Progress := '010';
67 /* Call the get_total api for the Lines */
68
69 X_line_total := po_core_s.get_total('L',
70 X_po_line_id,
71 FALSE);
72
73 X_Progress := '020';
74 /* Maintain the Total for Shipment Quantity for this Line */
75
76 po_lines_pkg_scu.select_ship_total( X_po_line_id,
77 X_ship_total,
78 X_ship_total_rtot_db);
79
80 X_Progress := '030';
81 /* Figure out how many shipments and distributions exist */
82 po_lines_sv4.get_ship_dist_num(X_po_line_id,
83 X_num_of_ship,
84 X_promised_date,
85 X_need_by,
86 X_num_of_dist,
87 X_code_combination_id);
88
89 X_Progress := '040';
90 /* Populate the receipt Required Flag first from po_line_types and then,
91 ** IF it is Not a one-time item from the mtl_system_item
92 */
93 X_receipt_required_flag := po_lines_sv4.get_receipt_required_flag (X_line_type_id,
94 X_item_id,
95 X_inventory_org_id);
96
97 X_Progress := '050';
98 po_lines_sv4.get_ship_quantity_info (X_po_line_id,
99 X_expense_accrual_code,
100 X_po_header_id,
101 X_type_lookup_code,
102 X_quantity_received,
103 X_quantity_billed,
104 X_encumbered_flag,
105 X_prevent_price_update_flag,
106 X_online_req_flag,
107 X_quantity_released,
108 X_amount_released );
109 X_Progress := '060';
110 --FRKHAN 07/31/98. Bug 706970. Removed clause 'X_item_id is not null'
111 --so that quotation info is copied even if you don't have an item id
112 if X_from_header_id is not null THEN
113 po_lines_sv4.get_quotation_info (X_from_header_id,
114 X_from_line_id,
115 X_quotation_number,
116 X_quotation_line,
117 X_quotation_type,
118 X_vendor_quotation_number,
119 x_quote_terms_id,
120 x_quote_ship_via_lookup_code,
121 x_quote_fob_lookup_code,
122 x_quote_freight_terms);
123 end if;
124
125 --togeorge 10/03/2000
126 --added a call to get the oke header info
127 if X_oke_contract_header_id is not null then
128 po_lines_sv4.get_oke_contract_header_info(
129 X_oke_contract_header_id,
130 X_oke_contract_num);
131 end if;
132
133 exception
134 when others then
135 po_message_s.sql_error('po_lines_post_query', x_progress, sqlcode);
136 raise;
137 end po_lines_post_query;
138
139 /* Bug 4188362 : this function is called from when-new-item-instance from
140 * Unit_price field to check whether price is updateable or not */
141
142 FUNCTION price_update_allowed(p_po_line_id NUMBER)
143 return BOOLEAN IS
144 x_quantity_billed NUMBER;
145 x_quantity_received NUMBER;
146 x_accrual_option VARCHAR2(1);
147 x_encumbered_flag VARCHAR2(1);
148 x_retroactive_value VARCHAR2(20) := 'NEVER';
149 x_destination_code VARCHAR2(20);
150 l_retro_prj_allowed VARCHAR2(1) := 'N';
151 l_archive_mode_po PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
152
153 l_progress VARCHAR2(3) := '000';
154 l_api_name VARCHAR2(30) := 'PRICE_UPDATE_ALLOWED';
155
156
157 Begin
158 l_progress := '010';
159 IF g_debug_stmt THEN
160 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
161 END IF;
162
163 /* Get Retrocative Profile Value */
164 l_progress := '020';
165 x_retroactive_value := PO_RETROACTIVE_PRICING_PVT.Get_Retro_Mode;
166
167 IF g_debug_stmt THEN
168 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
169 p_token => l_progress,
170 p_message => 'Retroactive Value ::'|| x_retroactive_value);
171 END IF;
172
173 /* Get if PO has Project Information */
174 l_progress := '030';
175 l_retro_prj_allowed := PO_RETROACTIVE_PRICING_PVT.Is_Retro_Project_Allowed(
176 p_std_po_price_change => 'Y',
177 p_po_line_id => p_po_line_id,
178 p_po_line_loc_id => NULL);
179
180 IF g_debug_stmt THEN
181 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
182 p_token => l_progress,
183 p_message => 'l_retro_prj_allowed Value ::'|| l_retro_prj_allowed);
184 END IF;
185
186 /* Get Archival Mode */
187 l_archive_mode_po := PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode(
188 p_doc_type => 'PO',
189 p_doc_subtype => 'STANDARD');
190 IF g_debug_stmt THEN
191 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
192 p_token => l_progress,
193 p_message => 'l_archive_mode_po Value ::'|| l_archive_mode_po);
194 END IF;
195
196 /* Getting destination type for First distribution of the Shipment */
197 l_progress := '040';
198
199 x_destination_code:='INVENTORY'; --Setting destination_code to Inventory
200
201 BEGIN
202
203 --Checking if there is any destination_code = EXPENSE
204 SELECT 'EXPENSE'
205 INTO x_destination_code
206 FROM DUAL
207 WHERE EXISTS (
208 select destination_type_code
209 from po_distributions_all
210 where po_line_id = p_po_line_id
211 and destination_type_code IN ('EXPENSE')
212 );
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 x_destination_code:='INVENTORY';
216 END;
217
218 IF g_debug_stmt THEN
219 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
220 p_token => l_progress,
221 p_message => 'x_destination_code Value ::'|| x_destination_code);
222 END IF;
223
224
225 /* Considering Sum of all Shipments of the Line */
226 l_progress := '050';
227 select sum(quantity_received),
228 sum(quantity_billed),
229 max(nvl(accrue_on_receipt_flag,'N')),
230 max(nvl(encumbered_flag,'N'))
231 into x_quantity_received,
232 x_quantity_billed,
233 x_accrual_option,
234 x_encumbered_flag
235 from po_line_locations
236 where po_line_id = p_po_line_id;
237
238 IF g_debug_stmt THEN
239 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
240 p_token => l_progress,
241 p_message => 'x_quantity_received Value ::'|| x_quantity_received);
242 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
243 p_token => l_progress,
244 p_message => 'x_quantity_billed Value ::'|| x_quantity_billed);
245 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
246 p_token => l_progress,
247 p_message => 'x_accrual_option Value ::'|| x_accrual_option);
248 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
249 p_token => l_progress,
250 p_message => 'x_encumbered_flag Value ::'|| x_encumbered_flag);
251 END IF;
252
253 l_progress := '060';
254 if ( x_encumbered_flag = 'Y' OR --Encumbrance not supported
255 (
256 --If Received/Billed
257 ( nvl(x_quantity_received,0) > 0 OR nvl(x_quantity_billed,0) > 0 ) AND
258
259 --Checking Destination Type
260 ( x_destination_code IN ('INVENTORY','SHOP FLOOR') OR
261 ( x_destination_code='EXPENSE' AND ( x_accrual_option = 'RECEIPT' OR x_quantity_billed > 0 ) )
262 ) AND
263
264 -- Checking Retroactive Profile Value
265 ( x_retroactive_value <> 'ALL_RELEASES' OR
266 ( x_retroactive_value = 'ALL_RELEASES' AND l_archive_mode_po <> 'APPROVE' ) OR
267 ( x_retroactive_value = 'ALL_RELEASES' AND l_retro_prj_allowed = 'N' )
268 )
269 )
270 )
271 then
272
273 l_progress := '070';
274 IF g_debug_stmt THEN
275 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
276 p_token => l_progress,
277 p_message => 'Returning::FALSE');
278 END IF;
279
280 return (FALSE); /* Price Update NOT Allowed */
281
282 else
283
284 l_progress := '080';
285 IF g_debug_stmt THEN
286 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
287 p_token => l_progress,
288 p_message => 'Returning::TRUE');
289 END IF;
290
291 return (TRUE); /* Price Update Allowed */
292
293 end if;
294
295 IF g_debug_stmt THEN
296 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
297 END IF;
298
299 Exception
300 When NO_DATA_FOUND then
301 return (FALSE); /* Price Update NOT Allowed */
302 WHEN others then
303 raise;
304 end;
305
306 END PO_LINES_SV5;