DBA Data[Home] [Help]

PACKAGE BODY: APPS.PO_LINES_SV5

Source


1 PACKAGE BODY PO_LINES_SV5 as
2 /* $Header: POXPOL5B.pls 120.1.12010000.2 2008/08/04 08:31:51 rramasam 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;