[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_LINES
Source
1 PACKAGE BODY po_val_lines AS
2 -- $Header: PO_VAL_LINES.plb 120.42.12020000.13 2013/05/08 14:43:06 akyanama ship $
3
4 c_ENTITY_TYPE_LINE CONSTANT VARCHAR2(30) := PO_VALIDATIONS.C_ENTITY_TYPE_LINE;
5
6 c_RATE CONSTANT VARCHAR2(30) := 'RATE';
7 c_FIXED_PRICE CONSTANT VARCHAR2(30) := 'FIXED PRICE';
8 c_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
9 c_PLANNED CONSTANT VARCHAR2(30) := 'PLANNED';
10 c_TEMP_LABOR CONSTANT VARCHAR2(30) := 'TEMP LABOR';
11 c_BLANKET CONSTANT VARCHAR2(30) := 'BLANKET';
12 c_QUOTATION CONSTANT VARCHAR2(30) := 'QUOTATION';
13
14 -- <Complex Work R12 Start>
15 c_PREPAYMENT CONSTANT VARCHAR2(30) := 'PREPAYMENT';
16 c_LUMPSUM CONSTANT VARCHAR2(30) := 'LUMPSUM';
17 c_MILESTONE CONSTANT VARCHAR2(30) := 'MILESTONE';
18 c_ADVANCE CONSTANT VARCHAR2(30) := 'ADVANCE';
19 c_DELIVERY CONSTANT VARCHAR2(30) := 'DELIVERY';
20 -- <Complex Work R12 End>
21
22 c_NEW CONSTANT VARCHAR2(30) := 'NEW';
23
24 c_2_SOURCING CONSTANT VARCHAR2(30) := '2_SOURCING';
25
26 -- constants for columns:
27 c_COMMITTED_AMOUNT CONSTANT VARCHAR2(30) := 'COMMITTED_AMOUNT';
28 c_MIN_RELEASE_AMOUNT CONSTANT VARCHAR2(30) := 'MIN_RELEASE_AMOUNT';
29 c_QUANTITY CONSTANT VARCHAR2(30) := 'QUANTITY';
30 c_AMOUNT CONSTANT VARCHAR2(30) := 'AMOUNT';
31 c_LINE_NUM CONSTANT VARCHAR2(30) := 'LINE_NUM';
32 c_LINE_NUM_DISP CONSTANT VARCHAR2(30) := 'LINE_NUM_DISPLAY';
33 c_ITEM_ID CONSTANT VARCHAR2(30) := 'ITEM_ID';
34 c_START_DATE CONSTANT VARCHAR2(30) := 'START_DATE';
35 c_EXPIRATION_DATE CONSTANT VARCHAR2(30) := 'EXPIRATION_DATE';
36 c_UNIT_PRICE CONSTANT VARCHAR2(30) := 'UNIT_PRICE';
37 c_LIST_PRICE_PER_UNIT CONSTANT VARCHAR2(30) := 'LIST_PRICE_PER_UNIT';
38 c_MARKET_PRICE CONSTANT VARCHAR2(30) := 'MARKET_PRICE';
39 c_UNIT_MEAS_LOOKUP_CODE CONSTANT VARCHAR2(30) := 'UNIT_MEAS_LOOKUP_CODE';
40 c_ITEM_DESCRIPTION CONSTANT VARCHAR2(30) := 'ITEM_DESCRIPTION';
41 c_COMMENTS CONSTANT VARCHAR2(30) := 'COMMENTS'; --<PAR PROJECT>
42 c_CATEGORY_ID CONSTANT VARCHAR2(30) := 'CATEGORY_ID';
43 c_JOB_ID CONSTANT VARCHAR2(30) := 'JOB_ID';
44 c_LINE_TYPE_ID CONSTANT VARCHAR2(30) := 'LINE_TYPE_ID';
45 c_SECONDARY_QUANTITY CONSTANT VARCHAR2(30) := 'SECONDARY_QUANTITY';
46 c_FROM_LINE_ID CONSTANT VARCHAR2(30) := 'FROM_LINE_ID';
47 c_RECOUPMENT_RATE VARCHAR(30) := 'RECOUPMENT_RATE'; -- Bug 5072189
48 c_RETAINAGE_RATE VARCHAR(30) := 'RETAINAGE_RATE'; -- Bug 5072189
49 c_PROGRESS_PAYMENT_RATE VARCHAR(30) := 'PROGRESS_PAYMENT_RATE'; -- Bug 5072189
50 c_MAX_RETAINAGE_AMOUNT VARCHAR(30) := 'MAX_RETAINAGE_AMOUNT'; -- Bug 5221843
51 c_OPTION_FROM_DATE VARCHAR2(30) := 'CLM_OPTION_FROM_DATE';
52 c_OPTION_TO_DATE VARCHAR2(30) := 'CLM_OPTION_TO_DATE';
53 c_EXERCISED_DATE VARCHAR2(30) := 'CLM_EXERCISED_DATE';
54 --Mod Project
55 c_CONTROL_ACTION CONSTANT VARCHAR2(30) := 'CONTROL_ACTION';
56 -- Fed Fields
57 c_ORDER_FROM_DATE VARCHAR2(30) := 'CLM_ORDER_START_DATE';
58 c_ORDER_TO_DATE VARCHAR2(30) := 'CLM_ORDER_END_DATE';
59 c_CLM_MAX_TOTAL_AMOUNT VARCHAR2(30) := 'CLM_MAX_TOTAL_AMOUNT';
60 c_CLM_MAX_TOTAL_QUANTITY VARCHAR2(30) := 'CLM_MAX_TOTAL_QUANTITY';
61 c_CLM_MAX_ORDER_AMOUNT VARCHAR2(30) := 'CLM_MAX_ORDER_AMOUNT';
62 c_CLM_MAX_ORDER_QUANTITY VARCHAR2(30) := 'CLM_MAX_ORDER_QUANTITY';
63 -- CLM PDOI Integration
64 c_CLM_INFO_FLAG VARCHAR2(30) := 'CLM_INFO_FLAG';
65 c_CONTRACT_TYPE VARCHAR2(30) := 'CONTRACT_TYPE';
66 c_COST_CONSTRAINT VARCHAR2(30) := 'COST_CONSTRAINT ';
67 c_OKE_CONTRACT_HEADER_NUM VARCHAR2(30) := 'OKE_CONTRACT_HEADER_NUM';
68 c_OKE_CONTRACT_HEADER_ID VARCHAR2(30) := 'OKE_CONTRACT_HEADER_ID';
69 c_OKE_CONTRACT_VERSION_ID VARCHAR2(30) := 'OKE_CONTRACT_VERSION_ID';
70 c_CLM_OPTION_INDICATOR VARCHAR2(30) := 'CLM_OPTION_INDICATOR';
71 c_CLM_BASE_LINE_NUM VARCHAR2(30) := 'CLM_BASE_LINE_NUM';
72 c_CLM_OPTION_NUM VARCHAR2(30) := 'CLM_OPTION_NUM';
73 c_CLM_IDC_TYPE VARCHAR2(30) := 'CLM_IDC_TYPE';
74 c_FROM_HEADER_ID VARCHAR2(30) := 'FROM_HEADER_ID';
75 c_CLM_EXHIBIT_NAME CONSTANT VARCHAR2(30) := 'CLM_EXHIBIT_NAME';
76
77
78 -- The module base for this package.
79 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
80 PO_LOG.get_package_base('PO_VAL_LINES');
81
82 -- The module base for the subprogram.
83 D_amt_agreed_ge_zero CONSTANT VARCHAR2(100) :=
84 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amt_agreed_ge_zero');
85
86 -- The module base for the subprogram.
87 D_min_rel_amt_ge_zero CONSTANT VARCHAR2(100) :=
88 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'min_rel_amt_ge_zero');
89
90 -- The module base for the subprogram.
91 D_quantity_gt_zero CONSTANT VARCHAR2(100) :=
92 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_gt_zero');
93
94 -- The module base for the subprogram.
95 D_amount_gt_zero CONSTANT VARCHAR2(100) :=
96 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_gt_zero');
97
98 -- The module base for the subprogram.
99 D_line_num_gt_zero CONSTANT VARCHAR2(100) :=
100 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_num_gt_zero');
101
102 D_otl_inv_start_date_change CONSTANT VARCHAR2(100) :=
103 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'otl_invalid_start_date_change');
104
105 D_otl_invalid_end_date_change CONSTANT VARCHAR2(100) :=
106 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'otl_invalid_end_date_change');
107
108 D_quantity_notif_change CONSTANT VARCHAR2(100) :=
109 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_notif_change');
110
111 D_unit_price_ge_zero CONSTANT VARCHAR2(100) :=
112 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'unit_price_ge_zero');
113
114 D_list_price_ge_zero CONSTANT VARCHAR2(100) :=
115 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'list_price_ge_zero');
116
117 D_market_price_ge_zero CONSTANT VARCHAR2(100) :=
118 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'market_price_ge_zero');
119
120 D_quantity_ge_quantity_enc CONSTANT VARCHAR2(100) :=
121 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_ge_quantity_enc');
122
123 D_amount_ge_timecard CONSTANT VARCHAR2(100) :=
124 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_ge_timecard');
125
126 D_line_num_unique CONSTANT VARCHAR2(100) :=
127 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_num_unique');
128
129 --<Conc Mod Project>
130 D_clin_slin_num_check CONSTANT VARCHAR2(100) :=
131 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clin_slin_num_check');
132
133 D_vmi_asl_exists CONSTANT VARCHAR2(100) :=
134 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vmi_asl_exists');
135
136 D_start_date_le_end_date CONSTANT VARCHAR2(100) :=
137 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'start_date_le_end_date');
138
139 D_validate_unit_price_change CONSTANT VARCHAR2(100) :=
140 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'validate_unit_price_change');
141
142 D_expiration_ge_blanket_start CONSTANT VARCHAR2(100) :=
143 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'expiration_ge_blanket_start');
144
145 D_expiration_le_blanket_end CONSTANT VARCHAR2(100) :=
146 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'expiration_le_blanket_end');
147
148 -- <Complex Work R12 Start>: Removed/added debug variables
149 D_quantity_ge_quantity_exec CONSTANT VARCHAR2(100) :=
150 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'quantity_ge_quantity_exec');
151 D_amount_ge_amount_exec CONSTANT VARCHAR2(100) :=
152 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'amount_ge_amount_exec');
153 D_price_ge_price_mstone_exec CONSTANT VARCHAR2(100) :=
154 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'price_ge_price_milestone_exec');
155 D_qty_ge_qty_milestone_exec CONSTANT VARCHAR2(100) :=
156 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'qty_ge_qty_milestone_exec');
157 -- Bug 5072189 Start
158 D_recoupment_rate_range_check CONSTANT VARCHAR2(100) :=
159 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'recoupment_rate_range_check');
160 D_retainage_rate_range_check CONSTANT VARCHAR2(100) :=
161 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'retainage_rate_range_check');
162 D_prog_pay_rate_range_check CONSTANT VARCHAR2(100) :=
163 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'prog_pay_rate_range_check');
164 -- Bug 5072189 End
165 D_max_retain_amt_ge_zero CONSTANT VARCHAR2(100) :=
166 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_retain_amt_ge_zero'); --Bug 5221843
167 D_max_retain_amt_ge_retained CONSTANT VARCHAR2(100) :=
168 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_retain_amt_ge_retained'); --Bug 5453079
169 -- Bug 5070210 Start
170 D_advance_amt_le_amt CONSTANT VARCHAR2(100) :=
171 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'advance_amt_le_amt');
172 -- Bug 5070210 End
173 -- <PDOI for Complex PO Project: Start>
174 D_complex_po_attributes_check CONSTANT VARCHAR2(100) :=
175 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'complex_po_attributes_check');
176 -- <PDOI for Complex PO Project: End>
177 -- <Complex Work R12 End>
178 D_unit_meas_not_null CONSTANT VARCHAR2(100) :=
179 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'unit_meas_not_null');
180 D_item_description_not_null CONSTANT VARCHAR2(100) :=
181 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'item_description_not_null');
182 --<PAR Project>
183 D_validate_comments_not_null CONSTANT VARCHAR2(100) :=
184 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_comments_not_null');
185 D_line_num_disp_check CONSTANT VARCHAR2(100) :=
186 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'line_num_disp_check');
187
188 D_category_id_not_null CONSTANT VARCHAR2(100) :=
189 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'category_id_not_null');
190 D_item_id_not_null CONSTANT VARCHAR2(100) :=
191 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'item_id_not_null');
192 D_temp_labor_job_id_not_null CONSTANT VARCHAR2(100) :=
193 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'temp_labor_job_id_not_null');
194 D_line_type_id_not_null CONSTANT VARCHAR2(100) :=
195 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_type_id_not_null');
196 D_temp_lbr_start_date_not_null CONSTANT VARCHAR2(100) :=
197 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'temp_lbr_start_date_not_null');
198 D_src_doc_line_not_null CONSTANT VARCHAR2(100) :=
199 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'src_doc_line_not_null');
200 -- The module base for the subprogram.
201 D_line_qtys_within_deviation CONSTANT VARCHAR2(100) :=
202 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_qtys_within_deviation');
203 D_line_sec_quantity_gt_zero CONSTANT VARCHAR2(100) :=
204 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_sec_quantity_gt_zero');
205 D_from_line_id_not_null CONSTANT VARCHAR2(100) :=
206 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'from_line_id_not_null');
207
208 D_option_from_date_not_null CONSTANT VARCHAR2(100) :=
209 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'option_from_date_not_null');
210
211 D_option_to_date_not_null CONSTANT VARCHAR2(100) :=
212 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'option_to_date_not_null');
213
214 D_option_to_date_le_from_date CONSTANT VARCHAR2(100) :=
215 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'option_to_date_le_from_date');
216
217 D_option_from_date_le_sysdate CONSTANT VARCHAR2(100) :=
218 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'option_from_date_le_sysdate');
219
220 D_option_fm_date_le_ex_date CONSTANT VARCHAR2(100) :=
221 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'option_fm_date_le_ex_date');
222
223 -- Mod Project
224 D_mod_no_cancel_shp_complt CONSTANT VARCHAR2(100) :=
225 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'mod_no_cancel_shp_complt');
226
227 -- Fed Fields
228 D_order_start_date_gt_sysdate CONSTANT VARCHAR2(100) :=
229 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'order_start_date_gt_sysdate');
230 D_max_tot_amt_le_line_tot_amt CONSTANT VARCHAR2(100) :=
231 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_tot_amt_le_line_tot_amt');
232 D_max_tot_amt_ge_min_tot_amt CONSTANT VARCHAR2(100) :=
233 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_tot_amt_ge_min_tot_amt');
234 D_max_tot_qty_le_line_tot_qty CONSTANT VARCHAR2(100) :=
235 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_tot_qty_le_line_tot_qty');
236 D_max_tot_qty_ge_min_tot_qty CONSTANT VARCHAR2(100) :=
237 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_tot_qty_ge_min_tot_qty');
238 D_max_ord_amt_le_max_tot_amt CONSTANT VARCHAR2(100) :=
239 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_ord_amt_le_max_tot_amt');
240 D_max_ord_amt_ge_min_ord_amt CONSTANT VARCHAR2(100) :=
241 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_ord_amt_ge_min_ord_amt');
242 D_max_ord_qty_le_max_tot_qty CONSTANT VARCHAR2(100) :=
243 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_ord_qty_le_max_tot_qty');
244 D_max_ord_qty_ge_min_ord_qty CONSTANT VARCHAR2(100) :=
245 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_ord_qty_ge_min_ord_qty');
246 --CLM PDOI Integration
247 D_clin_slin_structure CONSTANT VARCHAR2(100) :=
248 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'clin_slin_structure');
249 D_validate_contract_type CONSTANT VARCHAR2(100) :=
250 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_contract_type');
251 D_validate_cost_constraint CONSTANT VARCHAR2(100) :=
252 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_cost_constraint');
253 D_check_contract_num_and_ver CONSTANT VARCHAR2(100) :=
254 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'check_contract_num_and_ver');
255 D_validate_option_indicator CONSTANT VARCHAR2(100) :=
256 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_option_indicator');
257 D_validate_clm_base_line_num CONSTANT VARCHAR2(100) :=
258 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_clm_base_line_num');
259 D_validate_clm_option_num CONSTANT VARCHAR2(100) :=
260 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_clm_option_num');
261 D_validate_clm_idc_type CONSTANT VARCHAR2(100) :=
262 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_clm_idc_type');
263 D_validate_clin_line_num_disp CONSTANT VARCHAR2(100) :=
264 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_clin_line_num_disp');
265 D_idv_reference_equal_check CONSTANT VARCHAR2(100) :=
266 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'idv_reference_equal_check');
267 D_validate_clm_line_type_id CONSTANT VARCHAR2(100) :=
268 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_clm_line_type_id');
269 -- bug 16461105
270 D_mod_undef_amt_le_line_amt CONSTANT VARCHAR2(100) :=
271 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'Validate_undef_amt_le_line_amt');
272 -- bug 16461105
273
274 -------------------------------------------------------------------------------
275 -- This procedure determines if Amount Agreed on blanket lines is greater
276 -- than or equal to zero. If not, return a failure.
277 -------------------------------------------------------------------------------
278 PROCEDURE amt_agreed_ge_zero(
279 p_line_id_tbl IN PO_TBL_NUMBER
280 , p_committed_amount_tbl IN PO_TBL_NUMBER
281 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
282 , x_result_type OUT NOCOPY VARCHAR2
283 )
284 IS
285 BEGIN
286
287 PO_VALIDATION_HELPER.greater_or_equal_zero(
288 p_calling_module => D_amt_agreed_ge_zero
289 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
290 , p_value_tbl => p_committed_amount_tbl
291 , p_entity_id_tbl => p_line_id_tbl
292 , p_entity_type => c_ENTITY_TYPE_LINE
293 , p_column_name => c_COMMITTED_AMOUNT
294 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
295 , x_results => x_results
296 , x_result_type => x_result_type
297 );
298
299 END amt_agreed_ge_zero;
300
301 -------------------------------------------------------------------------------
302 -- This procedure determines if Minimum Release Amount on blanket lines
303 -- is greater than or equal to zero. If not, return a failure.
304 -------------------------------------------------------------------------------
305 PROCEDURE min_rel_amt_ge_zero(
306 p_line_id_tbl IN PO_TBL_NUMBER
307 , p_min_release_amount_tbl IN PO_TBL_NUMBER
308 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
309 , x_result_type OUT NOCOPY VARCHAR2
310 )
311 IS
312 BEGIN
313
314 PO_VALIDATION_HELPER.greater_or_equal_zero(
315 p_calling_module => D_min_rel_amt_ge_zero
316 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
317 , p_value_tbl => p_min_release_amount_tbl
318 , p_entity_id_tbl => p_line_id_tbl
319 , p_entity_type => c_ENTITY_TYPE_LINE
320 , p_column_name => c_MIN_RELEASE_AMOUNT
321 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
322 , x_results => x_results
323 , x_result_type => x_result_type
324 );
325
326 END min_rel_amt_ge_zero;
327
328 -----------------------------------------------------------------------------
329 -- Validates that quantity is not null and greater than zero if it is not
330 -- a Rate or Fixed Price line.
331 -----------------------------------------------------------------------------
332 PROCEDURE quantity_gt_zero(
333 p_line_id_tbl IN PO_TBL_NUMBER
334 , p_quantity_tbl IN PO_TBL_NUMBER
335 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
336 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
337 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30 -- CLM changes
338 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
339 , x_result_type OUT NOCOPY VARCHAR2
340 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
341 )
342 IS
343 BEGIN
344
345 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
346 p_calling_module => D_quantity_gt_zero
347 , p_value_tbl => p_quantity_tbl
348 , p_entity_id_tbl => p_line_id_tbl
349 , p_order_type_lookup_code_tbl => p_order_type_lookup_code_tbl
350 , p_clm_info_flag_tbl => p_clm_info_flag_tbl
351 , p_cost_constraint_tbl => p_cost_constraint_tbl
352 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_YES
353 , p_entity_type => c_ENTITY_TYPE_LINE
354 , p_column_name => c_QUANTITY
355 , x_results => x_results
356 , x_result_type => x_result_type
357 , p_header_id_tbl => p_header_id_tbl --<Bug 15871378>
358 );
359
360 END quantity_gt_zero;
361
362 -- <Complex Work R12 Start>
363 -- Consolidated quantity execution checks to improve efficiency and
364 -- to give only the most relevant error
365 -- Removed: quantity_ge_quantity_billed, quantity_ge_quantity_rcvd,
366 -- Added: quantity_ge_quantity_exec
367
368 -----------------------------------------------------------------------------
369 -- Validates that quantity is greater than or equal to the quantity
370 -- received or billed
371 -- This check is only performed if quantity is being reduced below the
372 -- current transaction quantity, since over-receiving is allowed.
373 -- <Complex Work R12>: Ignore qty milestones (there are other checks for those)
374 -----------------------------------------------------------------------------
375 PROCEDURE quantity_ge_quantity_exec(
376 p_line_id_tbl IN PO_TBL_NUMBER
377 , p_quantity_tbl IN PO_TBL_NUMBER
378 , x_result_set_id IN OUT NOCOPY NUMBER
379 , x_result_type OUT NOCOPY VARCHAR2
380 )
381 IS
382 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_exec;
383 BEGIN
384
385 IF PO_LOG.d_proc THEN
386 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
387 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
388 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
389 END IF;
390
391 IF (x_result_set_id IS NULL) THEN
392 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
393 END IF;
394
395 FORALL i IN 1 .. p_line_id_tbl.COUNT
396 INSERT INTO PO_VALIDATION_RESULTS_GT
397 ( result_set_id
398 , entity_type
399 , entity_id
400 , column_name
401 , column_val
402 , message_name
403 , token1_name
404 , token1_value
405 )
406 SELECT
407 x_result_set_id
408 , c_ENTITY_TYPE_LINE
409 , p_line_id_tbl(i)
410 , c_QUANTITY
411 , TO_CHAR(p_quantity_tbl(i))
412 , (CASE
413 WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
414 THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
415 ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_RCVD_NA
416 END
417 )
418 , (CASE
419 WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
420 THEN PO_MESSAGE_S.c_QTY_BILLED_token
421 ELSE PO_MESSAGE_S.c_QTY_RCVD_token
422 END
423 )
424 , (CASE
425 WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
426 THEN TO_CHAR(POLL_TOTAL.qty_bill_actuals)
427 ELSE TO_CHAR(POLL_TOTAL.qty_recv_actuals)
428 END
429 )
430 FROM
431 ( SELECT
432 sum_qty_recv_actuals qty_recv_actuals,
433 sum_qty_bill_actuals qty_bill_actuals,
434 GREATEST(sum_qty_recv_actuals, sum_qty_bill_actuals) qty_executed
435 FROM
436 ( SELECT
437 NVL(SUM(
438 (CASE
439 WHEN PLL.shipment_type <> c_STANDARD THEN 0
440 ELSE NVL(PLL.quantity_received, 0)
441 END)), 0) sum_qty_recv_actuals,
442 NVL(SUM(
443 (CASE
444 WHEN PLL.shipment_type <> c_STANDARD THEN 0
445 ELSE GREATEST(NVL(PLL.quantity_billed, 0),
446 NVL(PLL.quantity_financed, 0))
447 END)), 0) sum_qty_bill_actuals
448 FROM PO_LINE_LOCATIONS_ALL PLL
449 WHERE PLL.po_line_id = p_line_id_tbl(i)
450 AND NVL(PLL.payment_type, c_DELIVERY) <> c_MILESTONE
451 )
452 ) POLL_TOTAL, PO_LINES_ALL POL
453 WHERE
454 POL.po_line_id = p_line_id_tbl(i)
455 AND p_quantity_tbl(i) IS NOT NULL
456 -- Quantity is being reduced below the current transaction quantity:
457 AND p_quantity_tbl(i) < POL.quantity
458 AND p_quantity_tbl(i) < POLL_TOTAL.qty_executed
459 ;
460
461 IF (SQL%ROWCOUNT > 0) THEN
462 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
463 ELSE
464 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
465 END IF;
466
467 IF PO_LOG.d_proc THEN
468 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
469 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
470 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
471 END IF;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 IF PO_LOG.d_exc THEN
476 PO_LOG.exc(d_mod,0,NULL);
477 END IF;
478 RAISE;
479
480 END quantity_ge_quantity_exec;
481
482 -- Bug 5070210 Start
483 -- Bug 5446881: If the amount is null, we need to check against price * quantity
484 PROCEDURE advance_amt_le_amt(
485 p_line_id_tbl IN PO_TBL_NUMBER
486 , p_advance_tbl IN PO_TBL_NUMBER
487 , p_amount_tbl IN PO_TBL_NUMBER
488 , p_quantity_tbl IN PO_TBL_NUMBER
489 , p_price_tbl IN PO_TBL_NUMBER
490 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
491 , x_result_type OUT NOCOPY VARCHAR2
492 )
493 IS
494 d_mod CONSTANT VARCHAR2(100) := D_advance_amt_le_amt;
495 l_results_count NUMBER;
496 BEGIN
497
498 IF p_line_id_tbl IS not null
499 THEN
500 IF PO_LOG.d_proc THEN
501 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
502 PO_LOG.proc_begin(d_mod,'p_advance_tbl',p_advance_tbl);
503 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
504 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl); -- PDOI for Complex PO Project
505 PO_LOG.proc_begin(d_mod,'p_price_tbl',p_price_tbl); -- PDOI for Complex PO Project
506 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
507 END IF;
508
509
510 IF (x_results IS NULL) THEN
511 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
512 END IF;
513
514 l_results_count := x_results.result_type.COUNT;
515
516 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
517 IF p_advance_tbl(i) IS NOT NULL THEN -- PDOI for Complex PO Project
518 IF (p_advance_tbl(i) > nvl(p_amount_tbl(i), p_quantity_tbl(i) * p_price_tbl(i))) THEN
519 x_results.add_result(
520 p_entity_type => c_ENTITY_TYPE_LINE
521 , p_entity_id => p_line_id_tbl(i)
522 , p_column_name => c_AMOUNT
523 , p_message_name => PO_MESSAGE_S.PO_ADVANCE_GT_LINE_AMOUNT
524 );
525 END IF;
526 END IF;
527 END LOOP;
528
529 IF (l_results_count < x_results.result_type.COUNT) THEN
530 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
531 ELSE
532 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
533 END IF;
534 END IF;
535
536 END advance_amt_le_amt;
537
538 -- Bug 5446881 End
539 -- Bug 5070210 End
540
541 -- <PDOI for Complex PO Project: Start>
542 PROCEDURE complex_po_attributes_check(
543 p_line_id_tbl IN PO_TBL_NUMBER
544 , p_style_id_tbl IN PO_TBL_NUMBER
545 , p_retainage_rate_tbl IN PO_TBL_NUMBER
546 , p_max_retain_amt_tbl IN PO_TBL_NUMBER
547 , p_prog_pay_rate_tbl IN PO_TBL_NUMBER
548 , p_recoupment_rate_tbl IN PO_TBL_NUMBER
549 , p_advance_tbl IN PO_TBL_NUMBER
550 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
551 , x_result_type OUT NOCOPY VARCHAR2
552 )
553 IS
554 d_mod CONSTANT VARCHAR2(100) := D_complex_po_attributes_check;
555 l_results_count NUMBER;
556 l_complex_work_flag VARCHAR2(1) := 'N';
557 l_financing_payments_flag VARCHAR2(1) := 'N';
558 l_retainage_allowed_flag VARCHAR2(1) := 'N';
559 l_advance_allowed_flag VARCHAR2(1) := 'N';
560 l_milestone_allowed_flag VARCHAR2(1) := 'N';
561 l_lumpsum_allowed_flag VARCHAR2(1) := 'N';
562 l_rate_allowed_flag VARCHAR2(1) := 'N';
563 BEGIN
564
565 IF p_line_id_tbl IS not null
566 THEN
567 IF PO_LOG.d_proc THEN
568 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
569 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
570 PO_LOG.proc_begin(d_mod,'p_retainage_rate_tbl',p_retainage_rate_tbl);
571 PO_LOG.proc_begin(d_mod,'p_max_retain_amt_tbl',p_max_retain_amt_tbl);
572 PO_LOG.proc_begin(d_mod,'p_prog_pay_rate_tbl',p_prog_pay_rate_tbl);
573 PO_LOG.proc_begin(d_mod,'p_recoupment_rate_tbl',p_recoupment_rate_tbl);
574 PO_LOG.proc_begin(d_mod,'p_advance_tbl',p_advance_tbl);
575 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
576 END IF;
577
578
579 IF (x_results IS NULL) THEN
580 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
581 END IF;
582
583 l_results_count := x_results.result_type.COUNT;
584
585 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
586 IF (p_retainage_rate_tbl(i) IS NOT NULL OR p_max_retain_amt_tbl(i) IS NOT NULL OR
587 p_prog_pay_rate_tbl(i) IS NOT NULL OR p_recoupment_rate_tbl(i) IS NOT NULL OR
588 p_advance_tbl(i) IS NOT NULL) THEN
589
590 PO_COMPLEX_WORK_PVT.get_payment_style_settings(
591 p_style_id => Nvl(p_style_id_tbl(i),1)
592 , x_complex_work_flag => l_complex_work_flag
593 , x_financing_payments_flag => l_financing_payments_flag
594 , x_retainage_allowed_flag => l_retainage_allowed_flag
595 , x_advance_allowed_flag => l_advance_allowed_flag
596 , x_milestone_allowed_flag => l_milestone_allowed_flag
597 , x_lumpsum_allowed_flag => l_lumpsum_allowed_flag
598 , x_rate_allowed_flag => l_rate_allowed_flag
599 );
600
601 IF (l_advance_allowed_flag = 'N' AND p_advance_tbl(i) IS NOT NULL) THEN
602 -- If Advance is not allowed for the style, ADVANCE amount is not allowed.
603 x_results.add_result(p_entity_type => c_ENTITY_TYPE_LINE,
604 p_entity_id => p_line_id_tbl(i),
605 p_column_name => 'ADVANCE_AMOUNT',
606 p_column_val => p_advance_tbl(i),
607 p_message_name => 'PO_PDOI_ADVANCE_NOT_ALLOWED',
608 p_token1_name => 'STYLE_ID',
609 p_token1_value => p_style_id_tbl(i)
610 );
611 END IF;
612 IF (l_advance_allowed_flag = 'N' AND p_recoupment_rate_tbl(i) IS NOT NULL) THEN
613 -- If Advance is not allowed for the style, RECOUPMENT_RATE is not allowed.
614 x_results.add_result(p_entity_type => c_ENTITY_TYPE_LINE,
615 p_entity_id => p_line_id_tbl(i),
616 p_column_name => 'RECOUPMENT_RATE',
617 p_column_val => p_advance_tbl(i),
618 p_message_name => 'PO_PDOI_RECOUP_RATE_DISALLOW',
619 p_token1_name => 'STYLE_ID',
620 p_token1_value => p_style_id_tbl(i)
621 );
622 END IF;
623 IF (l_retainage_allowed_flag = 'N' AND p_retainage_rate_tbl(i) IS NOT NULL) THEN
624 -- If Retainage is not allowed for the style, RETAINAGE_RATE is not allowed.
625 x_results.add_result(p_entity_type => c_ENTITY_TYPE_LINE,
626 p_entity_id => p_line_id_tbl(i),
627 p_column_name => 'RETAINAGE_RATE',
628 p_column_val => p_retainage_rate_tbl(i),
629 p_message_name => 'PO_PDOI_RETAIN_RATE_DISALLOW',
630 p_token1_name => 'STYLE_ID',
631 p_token1_value => p_style_id_tbl(i)
632 );
633 END IF;
634 IF (l_retainage_allowed_flag = 'N' AND p_max_retain_amt_tbl(i) IS NOT NULL) THEN
635 -- If Retainage is not allowed for the style, MAX_RETAINAGE_AMOUNT is not allowed.
636 x_results.add_result(p_entity_type => c_ENTITY_TYPE_LINE,
637 p_entity_id => p_line_id_tbl(i),
638 p_column_name => 'MAX_RETAINAGE_AMOUNT',
639 p_column_val => p_max_retain_amt_tbl(i),
640 p_message_name => 'PO_PDOI_MAX_RETAIN_AM_DISALLOW',
641 p_token1_name => 'STYLE_ID',
642 p_token1_value => p_style_id_tbl(i)
643 );
644 END IF;
645 IF (l_financing_payments_flag = 'N' AND p_prog_pay_rate_tbl(i) IS NOT NULL) THEN
646 -- If Financing is not allowed for the style, PROGRESS_PAYMENT_RATE is not allowed.
647 x_results.add_result(p_entity_type => c_ENTITY_TYPE_LINE,
648 p_entity_id => p_line_id_tbl(i),
649 p_column_name => 'PROGRESS_PAYMENT_RATE',
650 p_column_val => p_prog_pay_rate_tbl(i),
651 p_message_name => 'PO_PDOI_PROG_PAY_RATE_DISALLOW',
652 p_token1_name => 'STYLE_ID',
653 p_token1_value => p_style_id_tbl(i)
654 );
655 END IF;
656
657 END IF;
658 END LOOP;
659
660 IF (l_results_count < x_results.result_type.COUNT) THEN
661 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
662 ELSE
663 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
664 END IF;
665 END IF;
666
667 END complex_po_attributes_check;
668 -- <PDOI for Complex PO Project: End>
669
670 -- <Complex Work R12 End>
671
672 -----------------------------------------------------------------------------
673 -- Validates that quantity is greater than or equal to quantity encumbered.
674 -----------------------------------------------------------------------------
675 PROCEDURE quantity_ge_quantity_enc(
676 p_line_id_tbl IN PO_TBL_NUMBER
677 , p_quantity_tbl IN PO_TBL_NUMBER
678 , x_result_set_id IN OUT NOCOPY NUMBER
679 , x_result_type OUT NOCOPY VARCHAR2
680 )
681 IS
682 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_enc;
683 l_is_clm_po VARCHAR2(1) := 'N'; -- Bug#11782846
684 BEGIN
685
686 IF PO_LOG.d_proc THEN
687 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
688 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
689 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
690 END IF;
691
692 IF (x_result_set_id IS NULL) THEN
693 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
694 END IF;
695
696 /* Bug#11782846 : Unable to change Price in Modification */
697 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
698
699 FOR i IN 1 .. p_line_id_tbl.Count LOOP
700 l_is_clm_po := PO_CLM_INTG_GRP.Is_clm_po(p_po_line_id => p_line_id_tbl(i));
701
702 IF Nvl(l_is_clm_po,'N') = 'N' THEN
703 INSERT INTO PO_VALIDATION_RESULTS_GT
704 ( result_set_id
705 , entity_type
706 , entity_id
707 , column_name
708 , column_val
709 , message_name
710 --PBWC Message Change Impact: Removing a token
711 , token1_name
712 , token1_value
713 --, token2_name
714 --, token2_value
715 )
716 SELECT
717 x_result_set_id
718 , c_ENTITY_TYPE_LINE
719 , p_line_id_tbl(i)
720 , c_QUANTITY
721 , TO_CHAR(p_quantity_tbl(i))
722 , PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_ENC_NA
723 --PBWC Message Change Impact: Removing a token
724 --, PO_MESSAGE_S.c_QTY_ORD_token
725 --, TO_CHAR(p_quantity_tbl(i))
726 , PO_MESSAGE_S.c_QTY_ENC_token
727 , TO_CHAR(DIST_TOTAL.quantity_encumbered)
728 FROM
729 ( SELECT NVL(SUM(POD.quantity_ordered),0) quantity_encumbered
730 FROM
731 PO_DISTRIBUTIONS_ALL POD
732 WHERE
733 POD.po_line_id = p_line_id_tbl(i)
734 AND POD.distribution_type IN (c_STANDARD,c_PLANNED)
735 AND POD.encumbered_flag = 'Y'
736 ) DIST_TOTAL
737 WHERE
738 p_quantity_tbl(i) IS NOT NULL
739 AND p_quantity_tbl(i) < DIST_TOTAL.quantity_encumbered
740 ;
741
742 IF (SQL%ROWCOUNT > 0) THEN
743 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
744 END IF;
745 END IF;
746 END LOOP;
747
748 IF PO_LOG.d_proc THEN
749 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
750 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
751 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
752 END IF;
753
754 EXCEPTION
755 WHEN OTHERS THEN
756 IF PO_LOG.d_exc THEN
757 PO_LOG.exc(d_mod,0,NULL);
758 END IF;
759 RAISE;
760
761 END quantity_ge_quantity_enc;
762
763 -----------------------------------------------------------------------------
764 -- Shows a warning if quantity is changed and notification controls
765 -- are enabled.
766 -----------------------------------------------------------------------------
767 PROCEDURE quantity_notif_change(
768 p_line_id_tbl IN PO_TBL_NUMBER
769 , p_quantity_tbl IN PO_TBL_NUMBER
770 , x_result_set_id IN OUT NOCOPY NUMBER
771 , x_result_type OUT NOCOPY VARCHAR2
772 )
773 IS
774 BEGIN
775
776 PO_VALIDATION_HELPER.amount_notif_ctrl_warning(
777 p_calling_module => D_quantity_notif_change
778 , p_line_id_tbl => p_line_id_tbl
779 , p_quantity_tbl => p_quantity_tbl
780 , p_column_name => c_QUANTITY
781 , p_message_name => PO_MESSAGE_S.PO_PO_NFC_QTY_CHANGE
782 , x_result_set_id => x_result_set_id
783 , x_result_type => x_result_type
784 );
785
786 END quantity_notif_change;
787
788
789 -----------------------------------------------------------------------------
790 -- Validates that amount is not null and greater than zero if the line is
791 -- Rate or Fixed Price.
792 -----------------------------------------------------------------------------
793 PROCEDURE amount_gt_zero(
794 p_line_id_tbl IN PO_TBL_NUMBER
795 , p_amount_tbl IN PO_TBL_NUMBER
796 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
797 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
798 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30 -- CLM changes
799 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
800 , x_result_type OUT NOCOPY VARCHAR2
801 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
802 )
803 IS
804 BEGIN
805
806 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
807 p_calling_module => D_amount_gt_zero
808 , p_value_tbl => p_amount_tbl
809 , p_entity_id_tbl => p_line_id_tbl
810 , p_order_type_lookup_code_tbl => p_order_type_lookup_code_tbl
811 , p_clm_info_flag_tbl => p_clm_info_flag_tbl -- CLM changes
812 , p_cost_constraint_tbl => p_cost_constraint_tbl -- CLM changes
813 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_NO
814 , p_entity_type => c_ENTITY_TYPE_LINE
815 , p_column_name => c_AMOUNT
816 , x_results => x_results
817 , x_result_type => x_result_type
818 , p_header_id_tbl => p_header_id_tbl --<Bug 15871378>
819 );
820
821 END amount_gt_zero;
822
823 -- <Complex Work R12 Start>
824 -- Consolidated amount execution checks to improve efficiency and
825 -- to give only the most relevant error
826 -- Removed: amount_ge_amount_billed, amount_ge_amount_rcvd,
827 -- Added: amount_ge_amount_exec
828
829 -----------------------------------------------------------------------------
830 -- Validates that amount is greater than or equal to
831 -- amount billed and amount received.
832 -- This check is only performed if amount is being reduced below the
833 -- current transaction amount, since over-receiving is allowed.
834 -- <Complex Work R12>: Handle differing value bases and payment types
835 -----------------------------------------------------------------------------
836 PROCEDURE amount_ge_amount_exec(
837 p_line_id_tbl IN PO_TBL_NUMBER
838 , p_amount_tbl IN PO_TBL_NUMBER
839 , p_currency_code_tbl IN PO_TBL_VARCHAR30
840 , x_result_set_id IN OUT NOCOPY NUMBER
841 , x_result_type OUT NOCOPY VARCHAR2
842 )
843 IS
844 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_amount_exec;
845 d_progress NUMBER;
846 l_precision NUMBER;
847 l_min_acct_unit NUMBER;
848 l_gt_key NUMBER;
849 BEGIN
850
851 d_progress := 0.0;
852
853 IF PO_LOG.d_proc THEN
854 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
855 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
856 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
857 END IF;
858
859 d_progress := 10.0;
860
861 IF (x_result_set_id IS NULL) THEN
862 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
863 END IF;
864
865 d_progress := 20.0;
866
867 -- get currency info and put in GT table
868 l_gt_key := PO_CORE_S.get_session_gt_nextval();
869
870 d_progress := 30.0;
871
872 FORALL i IN 1 .. p_line_id_tbl.COUNT
873 INSERT INTO PO_SESSION_GT
874 ( key
875 , index_num1 -- po_line_id
876 , char1 -- currency_code
877 , num1 -- minimum_accountable_unit
878 , num2 -- precision
879 )
880 SELECT
881 l_gt_key
882 , p_line_id_tbl(i)
883 , p_currency_code_tbl(i)
884 , cur.minimum_accountable_unit
885 , cur.precision
886 FROM
887 fnd_currencies cur
888 WHERE
889 cur.currency_code = p_currency_code_tbl(i)
890 ;
891
892 d_progress := 40.0;
893
894 FORALL i IN 1 .. p_line_id_tbl.COUNT
895 INSERT INTO PO_VALIDATION_RESULTS_GT
896 ( result_set_id
897 , entity_type
898 , entity_id
899 , column_name
900 , column_val
901 , message_name
902 --PBWC Message Change Impact: Adding a token
903 , token1_name
904 , token1_value
905 )
906 SELECT
907 x_result_set_id
908 , c_ENTITY_TYPE_LINE
909 , p_line_id_tbl(i)
910 , c_AMOUNT
911 , TO_CHAR(p_amount_tbl(i))
912 , (CASE
913 WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
914 THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
915 ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_RCVD_NA
916 END
917 )
918 --PBWC Message Change Impact: Adding a token
919 , (CASE
920 WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
921 THEN PO_MESSAGE_S.c_AMT_BILLED_TOKEN
922 ELSE PO_MESSAGE_S.c_AMT_RCVD_TOKEN
923 END
924 )
925 , (CASE
926 WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
927 THEN TO_CHAR(POLL_TOTAL.amt_bill_actuals)
928 ELSE TO_CHAR(POLL_TOTAL.amt_recv_actuals)
929 END
930 )
931 --End PBWC Message Change Impact: Adding a token
932 FROM
933 ( SELECT
934 sum_amt_recv_actuals amt_recv_actuals,
935 sum_amt_bill_actuals amt_bill_actuals,
936 GREATEST(sum_amt_recv_actuals, sum_amt_bill_actuals) amt_executed
937 FROM
938 ( SELECT
939 NVL(SUM(
940 (CASE
941 WHEN PLL.shipment_type <> c_STANDARD
942 THEN 0
943 WHEN PLL.payment_type = c_RATE
944 THEN
945 CASE
946 WHEN gtt.num1 IS NOT NULL THEN
947 -- Round to minimum accountable unit.
948 ROUND(
949 NVL(PLL.quantity_received*PLL.price_override,0) / gtt.num1
950 ) * gtt.num1
951 ELSE
952 -- Round to currency precision.
953 ROUND( NVL(PLL.quantity_received*PLL.price_override,0)
954 , gtt.num2)
955 END
956 ELSE NVL(PLL.amount_received, 0)
957 END)), 0) sum_amt_recv_actuals,
958 NVL(SUM(
959 (CASE
960 WHEN PLL.shipment_type <> c_STANDARD
961 THEN 0
962 WHEN PLL.payment_type = c_RATE
963 THEN
964 CASE
965 WHEN gtt.num1 IS NOT NULL THEN
966 -- Round to minimum accountable unit.
967 ROUND(
968 NVL(PLL.quantity_billed*PLL.price_override,0) / gtt.num1
969 ) * gtt.num1
970 ELSE
971 -- Round to currency precision.
972 ROUND( NVL(PLL.quantity_billed*PLL.price_override,0)
973 , gtt.num2)
974 END
975 ELSE GREATEST(NVL(PLL.amount_billed, 0),
976 NVL(PLL.amount_financed, 0))
977 END)), 0) sum_amt_bill_actuals
978 FROM PO_LINE_LOCATIONS_ALL PLL
979 , PO_SESSION_GT GTT
980 WHERE PLL.po_line_id = p_line_id_tbl(i)
981 AND GTT.key = l_gt_key
982 AND GTT.index_num1(+) = PLL.po_line_id
983 )
984 ) POLL_TOTAL
985 , PO_LINES_ALL POL
986 WHERE
987 POL.po_line_id = p_line_id_tbl(i)
988 AND p_amount_tbl(i) IS NOT NULL
989 -- Amount is being reduced below the current transaction amount:
990 AND p_amount_tbl(i) < POL.amount
991 AND p_amount_tbl(i) < POLL_TOTAL.amt_executed
992 ;
993
994 d_progress := 50.0;
995
996 IF (SQL%ROWCOUNT > 0) THEN
997 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
998 ELSE
999 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1000 END IF;
1001
1002 d_progress := 60.0;
1003
1004 IF PO_LOG.d_proc THEN
1005 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1006 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1007 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1008 END IF;
1009
1010 d_progress := 70.0;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 IF PO_LOG.d_exc THEN
1015 PO_LOG.exc(d_mod,d_progress,NULL);
1016 END IF;
1017 RAISE;
1018
1019 END amount_ge_amount_exec;
1020
1021 -- <Complex Work R12 End>
1022
1023
1024 -----------------------------------------------------------------------------
1025 -- Validates that amount is greater than or equal to the sum of amounts
1026 -- on timecards against a Rate Based Standard PO line.
1027 -- This check is only performed if amount is being reduced below the
1028 -- current transaction amount.
1029 -----------------------------------------------------------------------------
1030 PROCEDURE amount_ge_timecard(
1031 p_line_id_tbl IN PO_TBL_NUMBER
1032 , p_amount_tbl IN PO_TBL_NUMBER
1033 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1034 , x_result_type OUT NOCOPY VARCHAR2
1035 )
1036 IS
1037 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_timecard;
1038
1039 l_results_count NUMBER;
1040 l_data_key NUMBER;
1041
1042 l_line_id_tbl PO_TBL_NUMBER;
1043 l_amount_tbl PO_TBL_NUMBER;
1044
1045 l_timecard_amount_sum NUMBER;
1046 l_return_status VARCHAR2(1);
1047 BEGIN
1048
1049 IF PO_LOG.d_proc THEN
1050 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1051 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
1052 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1053 END IF;
1054
1055 IF (x_results IS NULL) THEN
1056 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1057 END IF;
1058
1059 l_results_count := x_results.result_type.COUNT;
1060
1061 l_data_key := PO_CORE_S.get_session_gt_nextval();
1062
1063 FORALL i IN 1 .. p_line_id_tbl.COUNT
1064 INSERT INTO PO_SESSION_GT
1065 ( key
1066 , num1
1067 , num2
1068 )
1069 VALUES
1070 ( l_data_key
1071 , p_line_id_tbl(i)
1072 , p_amount_tbl(i)
1073 )
1074 ;
1075
1076 SELECT
1077 SES.num1
1078 , SES.num2
1079 BULK COLLECT INTO
1080 l_line_id_tbl
1081 , l_amount_tbl
1082 FROM
1083 PO_SESSION_GT SES
1084 , PO_LINES_ALL LINE
1085 , PO_HEADERS_ALL HEADER
1086 WHERE
1087 SES.key = l_data_key
1088 AND LINE.po_line_id = SES.num1
1089 AND HEADER.po_header_id = LINE.po_header_id
1090 AND HEADER.type_lookup_code = c_STANDARD
1091 AND LINE.order_type_lookup_code = c_RATE
1092 AND SES.num2 < LINE.amount
1093 ;
1094
1095 FOR i IN 1 .. l_line_id_tbl.COUNT LOOP
1096
1097 -- For Rate Based Standard PO lines where the amount has been decreased,
1098 -- call the OTL API and identify the ones where the new amount is less than
1099 -- the timecard sum.
1100
1101 PO_HXC_INTERFACE_PVT.get_timecard_amount(
1102 p_api_version => 1.0
1103 , x_return_status => l_return_status
1104 , p_po_line_id => l_line_id_tbl(i)
1105 , x_amount => l_timecard_amount_sum
1106 );
1107
1108 IF (l_return_status <> 'S') THEN
1109 RAISE FND_API.g_exc_unexpected_error;
1110 END IF;
1111
1112 IF (l_amount_tbl(i) < l_timecard_amount_sum) THEN
1113
1114 x_results.add_result(
1115 p_entity_type => c_ENTITY_TYPE_LINE
1116 , p_entity_id => l_line_id_tbl(i)
1117 , p_column_name => c_AMOUNT
1118 , p_column_val => TO_CHAR(l_amount_tbl(i))
1119 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_INVALID_AMOUNT
1120 , p_token1_name => PO_MESSAGE_S.c_TOTAL_AMT_token
1121 , p_token1_value => l_timecard_amount_sum
1122 );
1123
1124 END IF;
1125
1126 END LOOP;
1127
1128 IF (l_results_count < x_results.result_type.COUNT) THEN
1129 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1130 ELSE
1131 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1132 END IF;
1133
1134 IF PO_LOG.d_proc THEN
1135 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1136 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1137 END IF;
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 IF PO_LOG.d_exc THEN
1142 PO_LOG.exc(d_mod,0,NULL);
1143 END IF;
1144 RAISE;
1145
1146 END amount_ge_timecard;
1147
1148
1149
1150
1151
1152 -----------------------------------------------------------------------------
1153 -- Validates that all the line numbers for a given header are
1154 -- unique.
1155 -----------------------------------------------------------------------------
1156 -- Assumption:
1157 -- All of the unposted line data will be passed in
1158 -- to this routine in order to get accurate results.
1159 PROCEDURE line_num_unique(
1160 p_line_id_tbl IN PO_TBL_NUMBER
1161 , p_header_id_tbl IN PO_TBL_NUMBER
1162 , p_line_num_tbl IN PO_TBL_NUMBER
1163 , p_par_draft_id_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL --<PAR Project>
1164 , p_draft_id_tbl IN PO_TBL_NUMBER
1165 , x_result_set_id IN OUT NOCOPY NUMBER
1166 , x_result_type OUT NOCOPY VARCHAR2
1167 )
1168 IS
1169 BEGIN
1170
1171 PO_VALIDATION_HELPER.child_num_unique(
1172 p_calling_module => D_line_num_unique
1173 , p_entity_type => c_entity_type_LINE
1174 , p_entity_id_tbl => p_line_id_tbl
1175 , p_parent_id_tbl => p_header_id_tbl
1176 , p_entity_num_tbl => p_line_num_tbl
1177 , p_par_draft_id_tbl => p_par_draft_id_tbl -- <PAR Project>
1178 , p_draft_id_tbl => p_draft_id_tbl
1179 , x_result_set_id => x_result_set_id
1180 , x_result_type => x_result_type
1181 );
1182
1183 END line_num_unique;
1184
1185 -----------------------------------------------------------------------------
1186 -- Checks for null or non-positive line numbers.
1187 -----------------------------------------------------------------------------
1188
1189 PROCEDURE line_num_gt_zero(
1190 p_line_id_tbl IN PO_TBL_NUMBER
1191 , p_line_num_tbl IN PO_TBL_NUMBER
1192 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1193 , x_result_type OUT NOCOPY VARCHAR2
1194 )
1195 IS
1196 BEGIN
1197
1198 PO_VALIDATION_HELPER.greater_than_zero(
1199 p_calling_module => D_line_num_gt_zero
1200 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
1201 , p_value_tbl => p_line_num_tbl
1202 , p_entity_id_tbl => p_line_id_tbl
1203 , p_entity_type => c_ENTITY_TYPE_LINE
1204 , p_column_name => c_LINE_NUM
1205 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
1206 , x_results => x_results
1207 , x_result_type => x_result_type
1208 );
1209
1210 END line_num_gt_zero;
1211
1212 ------------------------------------------------------------------------
1213 -- Displays the warning 'PO_VMI_ASL_EXIST if all the following are true:
1214 -- 1) Profile PO_VMI_DISPLAY_WARNING is 'Y'
1215 -- 2) Item is not null
1216 -- 3) Document is an SPO
1217 -- 4) The item is set up for VMI in ASL
1218 --
1219 -- Where clauses derived from L_ITEM_CSR in PO_AUTOSOURCE_SV.get_asl_info
1220 ------------------------------------------------------------------------
1221 PROCEDURE vmi_asl_exists(
1222 p_line_id_tbl IN PO_TBL_NUMBER
1223 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1224 , p_item_id_tbl IN PO_TBL_NUMBER
1225 , p_org_id_tbl IN PO_TBL_NUMBER
1226 , p_vendor_id_tbl IN PO_TBL_NUMBER
1227 , p_vendor_site_id_tbl IN PO_TBL_NUMBER
1228 , x_result_set_id IN OUT NOCOPY NUMBER
1229 , x_result_type OUT NOCOPY VARCHAR2
1230 )
1231 IS
1232 d_mod CONSTANT VARCHAR2(100) := D_vmi_asl_exists;
1233
1234 -- Flag indicating if profile PO_VMI_DISPLAY_WARNING is on
1235 l_po_vmi_display_warning VARCHAR2(2000);
1236 BEGIN
1237
1238 IF PO_LOG.d_proc THEN
1239 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1240 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
1241 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
1242 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1243 PO_LOG.proc_begin(d_mod,'p_vendor_id_tbl',p_vendor_id_tbl);
1244 PO_LOG.proc_begin(d_mod,'p_vendor_site_id_tbl',p_vendor_site_id_tbl);
1245 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1246 END IF;
1247
1248 IF (x_result_set_id IS NULL) THEN
1249 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1250 END IF;
1251
1252 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1253
1254 -- Get profile PO_VMI_DISPLAY_WARNING
1255 fnd_profile.get(PO_PROFILES.PO_VMI_DISPLAY_WARNING,l_po_vmi_display_warning);
1256
1257 IF (l_po_vmi_display_warning = 'Y') THEN
1258
1259 FORALL i in 1 ..p_line_id_tbl.COUNT
1260 INSERT INTO PO_VALIDATION_RESULTS_GT
1261 ( result_set_id
1262 , result_type
1263 , entity_type
1264 , entity_id
1265 , column_name
1266 , column_val
1267 , message_name
1268 )
1269 SELECT
1270 x_result_set_id
1271 , PO_VALIDATIONS.c_result_type_WARNING
1272 , c_ENTITY_TYPE_LINE
1273 , p_line_id_tbl(i)
1274 , c_ITEM_ID
1275 , TO_CHAR(p_item_id_tbl(i))
1276 , PO_MESSAGE_S.PO_VMI_ASL_EXIST
1277 FROM
1278 PO_APPROVED_SUPPLIER_LIS_VAL_V PASL
1279 , PO_ASL_ATTRIBUTES PAA
1280 , PO_ASL_STATUS_RULES_V PASR
1281 WHERE
1282 -- item is not null
1283 p_item_id_tbl(i) IS NOT NULL
1284 -- Document is standard PO
1285 AND p_type_lookup_code_tbl(i) = c_STANDARD
1286
1287 --VMI is enabled
1288 AND paa.enable_vmi_flag = 'Y'
1289 AND pasl.item_id = p_item_id_tbl(i)
1290 AND pasl.vendor_id = p_vendor_id_tbl(i)
1291 AND nvl(pasl.vendor_site_id,-1) = nvl(p_vendor_site_id_tbl(i),-1)
1292 AND pasl.using_organization_id IN (p_org_id_tbl(i), -1)
1293 AND pasl.asl_id = paa.asl_id
1294 AND pasr.business_rule = c_2_SOURCING
1295 AND pasr.allow_action_flag = 'Y'
1296 AND pasr.status_id = pasl.asl_status_id
1297 AND paa.using_organization_id =
1298 (SELECT max(paa2.using_organization_id)
1299 FROM po_asl_attributes paa2
1300 WHERE paa2.asl_id = pasl.asl_id
1301 AND paa2.using_organization_id IN (-1, p_org_id_tbl(i)));
1302
1303 IF (SQL%ROWCOUNT > 0) THEN
1304 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
1305 END IF;
1306
1307 END IF;
1308
1309 IF PO_LOG.d_proc THEN
1310 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1311 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1312 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1313 END IF;
1314
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317 IF PO_LOG.d_exc THEN
1318 PO_LOG.exc(d_mod,0,NULL);
1319 END IF;
1320 RAISE;
1321
1322 END vmi_asl_exists;
1323
1324
1325 ------------------------------------------------------------------------
1326 -- Validates that the start date is less than or equal to the end date.
1327 -- Shows error 'PO_SVC_ASSIGNMENT_DATES'.
1328 ------------------------------------------------------------------------
1329 PROCEDURE start_date_le_end_date(
1330 p_line_id_tbl IN PO_TBL_NUMBER
1331 , p_start_date_tbl IN PO_TBL_DATE
1332 , p_expiration_date_tbl IN PO_TBL_DATE
1333 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1334 , x_result_type OUT NOCOPY VARCHAR2
1335 )
1336 IS
1337 BEGIN
1338
1339 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1340 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1341 RETURN;
1342 END IF;
1343
1344 PO_VALIDATION_HELPER.start_date_le_end_date(
1345 p_calling_module => D_start_date_le_end_date
1346 , p_start_date_tbl => p_start_date_tbl
1347 , p_end_date_tbl => p_expiration_date_tbl
1348 , p_entity_id_tbl => p_line_id_tbl
1349 , p_entity_type => c_ENTITY_TYPE_LINE
1350 , p_column_name => c_START_DATE
1351 , p_column_val_selector => NULL
1352 , p_message_name => PO_MESSAGE_S.PO_SVC_ASSIGNMENT_DATES
1353 , x_results => x_results
1354 , x_result_type => x_result_type
1355 );
1356
1357 END start_date_le_end_date;
1358
1359
1360
1361 ------------------------------------------------------------------------
1362 -- Validates that the order start date is less than or equal
1363 -- to the order end date.
1364 ------------------------------------------------------------------------
1365 PROCEDURE order_start_date_le_end_date
1366 (
1367 p_line_id_tbl IN PO_TBL_NUMBER
1368 , p_start_date_tbl IN PO_TBL_DATE
1369 , p_expiration_date_tbl IN PO_TBL_DATE
1370 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1371 , x_result_type OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 l_results_count NUMBER;
1375 BEGIN
1376
1377 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1378 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1379 RETURN;
1380 END IF;
1381
1382 IF (x_results IS NULL) THEN
1383 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1384 END IF;
1385
1386 l_results_count := x_results.result_type.COUNT;
1387
1388 FOR i IN 1 .. p_line_id_tbl.count LOOP
1389
1390 -- ensure that either both or none of clm_order_start_date and
1391 -- clm_order_end_date are entered by the user
1392 IF (p_expiration_date_tbl(i) IS NULL AND p_start_date_tbl(i) IS NOT NULL)
1393 OR (p_expiration_date_tbl(i) IS NOT NULL AND p_start_date_tbl(i) IS NULL)
1394 THEN
1395 x_results.add_result
1396 (
1397 p_entity_type => c_ENTITY_TYPE_LINE
1398 , p_entity_id => p_line_id_tbl(i)
1399 , p_column_name => c_ORDER_TO_DATE
1400 , p_message_name => PO_MESSAGE_S.PO_IDV_ORD_END_DT_NULL
1401 );
1402 END IF;
1403 END LOOP;
1404
1405 PO_VALIDATION_HELPER.start_date_le_end_date
1406 (
1407 p_calling_module => D_start_date_le_end_date
1408 , p_start_date_tbl => p_start_date_tbl
1409 , p_end_date_tbl => p_expiration_date_tbl
1410 , p_entity_id_tbl => p_line_id_tbl
1411 , p_entity_type => c_ENTITY_TYPE_LINE
1412 , p_column_name => c_ORDER_FROM_DATE
1413 , p_column_val_selector => NULL
1414 , p_message_name => PO_MESSAGE_S.PO_IDV_LINE_ORDER_DATES
1415 , x_results => x_results
1416 , x_result_type => x_result_type
1417 );
1418
1419 IF (l_results_count < x_results.result_type.COUNT) THEN
1420 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1421 ELSE
1422 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1423 END IF;
1424
1425 EXCEPTION WHEN OTHERS THEN
1426 RAISE;
1427 END order_start_date_le_end_date;
1428
1429
1430 PROCEDURE order_start_date_gt_sysdate
1431 (
1432 p_line_id_tbl IN PO_TBL_NUMBER
1433 , p_order_from_date_tbl IN PO_TBL_DATE
1434 , p_draft_id_tbl IN PO_TBL_NUMBER
1435 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1436 , x_result_type OUT NOCOPY VARCHAR2
1437 )
1438 IS
1439 l_results_count NUMBER;
1440 d_mod CONSTANT VARCHAR2(100) := D_order_start_date_gt_sysdate;
1441 document_order_date DATE :=NULL ;
1442 mod_document_order_date DATE :=NULL ;
1443 new_line BOOLEAN := true;
1444 line_count NUMBER := 0;
1445 BEGIN
1446
1447 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1448 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1449 RETURN;
1450 END IF;
1451
1452 IF PO_LOG.d_proc THEN
1453 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1454 PO_LOG.proc_begin(d_mod,'p_order_from_date_tbl',p_order_from_date_tbl);
1455 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
1456 END IF;
1457
1458 IF (x_results IS NULL) THEN
1459 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1460 END IF;
1461
1462 l_results_count := x_results.result_type.COUNT;
1463
1464 FOR i IN 1 .. p_line_id_tbl.count LOOP
1465
1466 IF p_order_from_date_tbl(i) IS NOT NULL
1467 THEN
1468 IF (p_draft_id_tbl(i) IS NOT NULL AND p_draft_id_tbl(i) <> -1) THEN
1469
1470 SELECT Count(po_line_id)
1471 INTO line_count
1472 FROM PO_LINES_MERGE_V
1473 WHERE (draft_id= -1
1474 OR draft_id IS NULL)
1475 AND po_line_id = p_line_id_tbl(i);
1476
1477 IF(line_count = 0) THEN
1478 new_line := TRUE;
1479 ELSE
1480 new_line := FALSE ;
1481 END IF ;
1482
1483 END IF;
1484
1485 IF ( new_line AND Trunc(p_order_from_date_tbl(i)) < Trunc(SYSDATE) )
1486 THEN
1487 x_results.add_result
1488 (
1489 p_entity_type => c_ENTITY_TYPE_LINE
1490 , p_entity_id => p_line_id_tbl(i)
1491 , p_column_name => c_ORDER_FROM_DATE
1492 , p_message_name => PO_MESSAGE_S.PO_IDV_ORD_ST_DT_GT_SYS
1493 );
1494 END IF;
1495 END IF;
1496 END LOOP;
1497
1498 IF (l_results_count < x_results.result_type.COUNT) THEN
1499 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1500 ELSE
1501 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1502 END IF;
1503
1504 IF PO_LOG.d_proc THEN
1505 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1506 END IF;
1507
1508 EXCEPTION WHEN OTHERS THEN
1509 IF PO_LOG.d_exc THEN
1510 PO_LOG.exc(d_mod,0,NULL);
1511 END IF;
1512 RAISE;
1513 END order_start_date_gt_sysdate;
1514
1515
1516 PROCEDURE max_tot_amt_le_line_tot_amt
1517 (
1518 p_line_id_tbl IN PO_TBL_NUMBER
1519 , p_max_total_amt_tbl IN PO_TBL_NUMBER
1520 , p_line_amount_tbl IN PO_TBL_NUMBER
1521 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1522 , x_result_type OUT NOCOPY VARCHAR2
1523 )
1524 IS
1525 d_mod CONSTANT VARCHAR2(100) := D_max_tot_amt_le_line_tot_amt;
1526 l_results_count NUMBER;
1527 BEGIN
1528
1529 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1530 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1531 RETURN;
1532 END IF;
1533
1534 IF p_line_id_tbl IS not null
1535 THEN
1536 IF PO_LOG.d_proc THEN
1537 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1538 PO_LOG.proc_begin(d_mod,'p_max_total_amt_tbl',p_max_total_amt_tbl);
1539 PO_LOG.proc_begin(d_mod,'p_line_amount_tbl',p_line_amount_tbl);
1540 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1541 END IF;
1542
1543 IF (x_results IS NULL) THEN
1544 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1545 END IF;
1546
1547 l_results_count := x_results.result_type.COUNT;
1548
1549 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1550 IF (p_max_total_amt_tbl(i) > p_line_amount_tbl(i)) THEN
1551 x_results.add_result
1552 (
1553 p_entity_type => c_ENTITY_TYPE_LINE
1554 , p_entity_id => p_line_id_tbl(i)
1555 , p_column_name => c_CLM_MAX_TOTAL_AMOUNT
1556 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_TOT_AMT_LE_LIN_TAMT
1557 );
1558 END IF;
1559 END LOOP;
1560
1561 IF (l_results_count < x_results.result_type.COUNT) THEN
1562 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1563 ELSE
1564 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1565 END IF;
1566 END IF;
1567
1568 END max_tot_amt_le_line_tot_amt;
1569
1570
1571 PROCEDURE max_tot_amt_ge_min_tot_amt
1572 (
1573 p_line_id_tbl IN PO_TBL_NUMBER
1574 , p_max_total_amt_tbl IN PO_TBL_NUMBER
1575 , p_min_total_amt_tbl IN PO_TBL_NUMBER
1576 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1577 , x_result_type OUT NOCOPY VARCHAR2
1578 )
1579 IS
1580 d_mod CONSTANT VARCHAR2(100) := D_max_tot_amt_ge_min_tot_amt;
1581 l_results_count NUMBER;
1582 BEGIN
1583
1584 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1585 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1586 RETURN;
1587 END IF;
1588
1589 IF p_line_id_tbl IS not null
1590 THEN
1591 IF PO_LOG.d_proc THEN
1592 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1593 PO_LOG.proc_begin(d_mod,'p_max_total_amt_tbl',p_max_total_amt_tbl);
1594 PO_LOG.proc_begin(d_mod,'p_min_total_amt_tbl',p_min_total_amt_tbl);
1595 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1596 END IF;
1597
1598 IF (x_results IS NULL) THEN
1599 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1600 END IF;
1601
1602 l_results_count := x_results.result_type.COUNT;
1603
1604 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1605 IF (p_max_total_amt_tbl(i) < p_min_total_amt_tbl(i)) THEN
1606 x_results.add_result
1607 (
1608 p_entity_type => c_ENTITY_TYPE_LINE
1609 , p_entity_id => p_line_id_tbl(i)
1610 , p_column_name => c_CLM_MAX_TOTAL_AMOUNT
1611 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_TOT_AMT_GE_MIN_TAMT
1612 );
1613 END IF;
1614 END LOOP;
1615
1616 IF (l_results_count < x_results.result_type.COUNT) THEN
1617 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1618 ELSE
1619 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1620 END IF;
1621 END IF;
1622
1623 END max_tot_amt_ge_min_tot_amt;
1624
1625 PROCEDURE max_tot_qty_le_line_tot_qty
1626 (
1627 p_line_id_tbl IN PO_TBL_NUMBER
1628 , p_max_total_qty_tbl IN PO_TBL_NUMBER
1629 , p_line_qty_tbl IN PO_TBL_NUMBER
1630 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1631 , x_result_type OUT NOCOPY VARCHAR2
1632 )
1633 IS
1634 d_mod CONSTANT VARCHAR2(100) := D_max_tot_qty_le_line_tot_qty;
1635 l_results_count NUMBER;
1636 BEGIN
1637
1638 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1639 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1640 RETURN;
1641 END IF;
1642
1643 IF p_line_id_tbl IS not null
1644 THEN
1645 IF PO_LOG.d_proc THEN
1646 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1647 PO_LOG.proc_begin(d_mod,'p_max_total_qty_tbl',p_max_total_qty_tbl);
1648 PO_LOG.proc_begin(d_mod,'p_line_qty_tbl',p_line_qty_tbl);
1649 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1650 END IF;
1651
1652 IF (x_results IS NULL) THEN
1653 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1654 END IF;
1655
1656 l_results_count := x_results.result_type.COUNT;
1657
1658 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1659 IF (p_max_total_qty_tbl(i) > p_line_qty_tbl(i)) THEN
1660 x_results.add_result
1661 (
1662 p_entity_type => c_ENTITY_TYPE_LINE
1663 , p_entity_id => p_line_id_tbl(i)
1664 , p_column_name => c_CLM_MAX_TOTAL_QUANTITY
1665 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_TOT_QTY_LE_LIN_TQTY
1666 );
1667 END IF;
1668 END LOOP;
1669
1670 IF (l_results_count < x_results.result_type.COUNT) THEN
1671 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1672 ELSE
1673 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1674 END IF;
1675 END IF;
1676
1677 END max_tot_qty_le_line_tot_qty;
1678
1679
1680 PROCEDURE max_tot_qty_ge_min_tot_qty
1681 (
1682 p_line_id_tbl IN PO_TBL_NUMBER
1683 , p_max_total_qty_tbl IN PO_TBL_NUMBER
1684 , p_min_total_qty_tbl IN PO_TBL_NUMBER
1685 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1686 , x_result_type OUT NOCOPY VARCHAR2
1687 )
1688 IS
1689 d_mod CONSTANT VARCHAR2(100) := D_max_tot_qty_ge_min_tot_qty;
1690 l_results_count NUMBER;
1691 BEGIN
1692
1693 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1694 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1695 RETURN;
1696 END IF;
1697
1698 IF p_line_id_tbl IS not null
1699 THEN
1700 IF PO_LOG.d_proc THEN
1701 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1702 PO_LOG.proc_begin(d_mod,'p_max_total_qty_tbl',p_max_total_qty_tbl);
1703 PO_LOG.proc_begin(d_mod,'p_min_total_qty_tbl',p_min_total_qty_tbl);
1704 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1705 END IF;
1706
1707 IF (x_results IS NULL) THEN
1708 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1709 END IF;
1710
1711 l_results_count := x_results.result_type.COUNT;
1712
1713 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1714 IF (p_max_total_qty_tbl(i) < p_min_total_qty_tbl(i)) THEN
1715 x_results.add_result
1716 (
1717 p_entity_type => c_ENTITY_TYPE_LINE
1718 , p_entity_id => p_line_id_tbl(i)
1719 , p_column_name => c_CLM_MAX_TOTAL_QUANTITY
1720 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_TOT_QTY_GE_MIN_TQTY
1721 );
1722 END IF;
1723 END LOOP;
1724
1725 IF (l_results_count < x_results.result_type.COUNT) THEN
1726 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1727 ELSE
1728 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1729 END IF;
1730 END IF;
1731
1732 END max_tot_qty_ge_min_tot_qty;
1733
1734
1735 PROCEDURE max_ord_amt_le_max_tot_amt
1736 (
1737 p_line_id_tbl IN PO_TBL_NUMBER
1738 , p_max_order_amt_tbl IN PO_TBL_NUMBER
1739 , p_max_tot_amount_tbl IN PO_TBL_NUMBER
1740 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1741 , x_result_type OUT NOCOPY VARCHAR2
1742 )
1743 IS
1744 d_mod CONSTANT VARCHAR2(100) := D_max_ord_amt_le_max_tot_amt;
1745 l_results_count NUMBER;
1746 BEGIN
1747
1748 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1749 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1750 RETURN;
1751 END IF;
1752
1753 IF p_line_id_tbl IS not null
1754 THEN
1755 IF PO_LOG.d_proc THEN
1756 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1757 PO_LOG.proc_begin(d_mod,'p_max_order_amt_tbl',p_max_order_amt_tbl);
1758 PO_LOG.proc_begin(d_mod,'p_max_tot_amount_tbl',p_max_tot_amount_tbl);
1759 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1760 END IF;
1761
1762 IF (x_results IS NULL) THEN
1763 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1764 END IF;
1765
1766 l_results_count := x_results.result_type.COUNT;
1767
1768 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1769 IF (p_max_order_amt_tbl(i) > p_max_tot_amount_tbl(i)) THEN
1770 x_results.add_result
1771 (
1772 p_entity_type => c_ENTITY_TYPE_LINE
1773 , p_entity_id => p_line_id_tbl(i)
1774 , p_column_name => c_CLM_MAX_ORDER_AMOUNT
1775 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_ORD_AMT_LE_MAX_TAMT
1776 );
1777 END IF;
1778 END LOOP;
1779
1780 IF (l_results_count < x_results.result_type.COUNT) THEN
1781 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1782 ELSE
1783 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1784 END IF;
1785 END IF;
1786
1787 END max_ord_amt_le_max_tot_amt;
1788
1789
1790 PROCEDURE max_ord_amt_ge_min_ord_amt
1791 (
1792 p_line_id_tbl IN PO_TBL_NUMBER
1793 , p_max_ord_amt_tbl IN PO_TBL_NUMBER
1794 , p_min_ord_amt_tbl IN PO_TBL_NUMBER
1795 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1796 , x_result_type OUT NOCOPY VARCHAR2
1797 )
1798 IS
1799 d_mod CONSTANT VARCHAR2(100) := D_max_ord_amt_ge_min_ord_amt;
1800 l_results_count NUMBER;
1801 BEGIN
1802
1803 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1804 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1805 RETURN;
1806 END IF;
1807
1808 IF p_line_id_tbl IS not null
1809 THEN
1810 IF PO_LOG.d_proc THEN
1811 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1812 PO_LOG.proc_begin(d_mod,'p_max_ord_amt_tbl',p_max_ord_amt_tbl);
1813 PO_LOG.proc_begin(d_mod,'p_min_ord_amt_tbl',p_min_ord_amt_tbl);
1814 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1815 END IF;
1816
1817 IF (x_results IS NULL) THEN
1818 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1819 END IF;
1820
1821 l_results_count := x_results.result_type.COUNT;
1822
1823 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1824 IF (p_max_ord_amt_tbl(i) < p_min_ord_amt_tbl(i)) THEN
1825 x_results.add_result
1826 (
1827 p_entity_type => c_ENTITY_TYPE_LINE
1828 , p_entity_id => p_line_id_tbl(i)
1829 , p_column_name => c_CLM_MAX_ORDER_AMOUNT
1830 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_ORD_AMT_GE_MIN_OAMT
1831 );
1832 END IF;
1833 END LOOP;
1834
1835 IF (l_results_count < x_results.result_type.COUNT) THEN
1836 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1837 ELSE
1838 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1839 END IF;
1840 END IF;
1841
1842 END max_ord_amt_ge_min_ord_amt;
1843
1844 PROCEDURE max_ord_qty_le_max_tot_qty
1845 (
1846 p_line_id_tbl IN PO_TBL_NUMBER
1847 , p_max_order_qty_tbl IN PO_TBL_NUMBER
1848 , p_max_tot_qty_tbl IN PO_TBL_NUMBER
1849 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1850 , x_result_type OUT NOCOPY VARCHAR2
1851 )
1852 IS
1853 d_mod CONSTANT VARCHAR2(100) := D_max_ord_qty_le_max_tot_qty;
1854 l_results_count NUMBER;
1855 BEGIN
1856
1857 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1858 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1859 RETURN;
1860 END IF;
1861
1862 IF p_line_id_tbl IS not null
1863 THEN
1864 IF PO_LOG.d_proc THEN
1865 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1866 PO_LOG.proc_begin(d_mod,'p_max_order_qty_tbl',p_max_order_qty_tbl);
1867 PO_LOG.proc_begin(d_mod,'p_max_tot_amount_tbl',p_max_tot_qty_tbl);
1868 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1869 END IF;
1870
1871 IF (x_results IS NULL) THEN
1872 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1873 END IF;
1874
1875 l_results_count := x_results.result_type.COUNT;
1876
1877 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1878 IF (p_max_order_qty_tbl(i) > p_max_tot_qty_tbl(i)) THEN
1879 x_results.add_result
1880 (
1881 p_entity_type => c_ENTITY_TYPE_LINE
1882 , p_entity_id => p_line_id_tbl(i)
1883 , p_column_name => c_CLM_MAX_ORDER_QUANTITY
1884 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_ORD_QTY_LE_MAX_TQTY
1885 );
1886 END IF;
1887 END LOOP;
1888
1889 IF (l_results_count < x_results.result_type.COUNT) THEN
1890 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1891 ELSE
1892 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1893 END IF;
1894 END IF;
1895
1896 END max_ord_qty_le_max_tot_qty;
1897
1898
1899 PROCEDURE max_ord_qty_ge_min_ord_qty
1900 (
1901 p_line_id_tbl IN PO_TBL_NUMBER
1902 , p_max_ord_qty_tbl IN PO_TBL_NUMBER
1903 , p_min_ord_qty_tbl IN PO_TBL_NUMBER
1904 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1905 , x_result_type OUT NOCOPY VARCHAR2
1906 )
1907 IS
1908 d_mod CONSTANT VARCHAR2(100) := D_max_ord_qty_ge_min_ord_qty;
1909 l_results_count NUMBER;
1910 BEGIN
1911
1912 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1913 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1914 RETURN;
1915 END IF;
1916
1917 IF p_line_id_tbl IS not null
1918 THEN
1919 IF PO_LOG.d_proc THEN
1920 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1921 PO_LOG.proc_begin(d_mod,'p_max_ord_qty_tbl',p_max_ord_qty_tbl);
1922 PO_LOG.proc_begin(d_mod,'p_min_ord_qty_tbl',p_min_ord_qty_tbl);
1923 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1924 END IF;
1925
1926 IF (x_results IS NULL) THEN
1927 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1928 END IF;
1929
1930 l_results_count := x_results.result_type.COUNT;
1931
1932 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1933 IF (p_max_ord_qty_tbl(i) < p_min_ord_qty_tbl(i)) THEN
1934 x_results.add_result
1935 (
1936 p_entity_type => c_ENTITY_TYPE_LINE
1937 , p_entity_id => p_line_id_tbl(i)
1938 , p_column_name => c_CLM_MAX_ORDER_QUANTITY
1939 , p_message_name => PO_MESSAGE_S.PO_IDV_MAX_ORD_QTY_GE_MIN_OQTY
1940 );
1941 END IF;
1942 END LOOP;
1943
1944 IF (l_results_count < x_results.result_type.COUNT) THEN
1945 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1946 ELSE
1947 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1948 END IF;
1949 END IF;
1950
1951 END max_ord_qty_ge_min_ord_qty;
1952
1953 ------------------------------------------------------------------------
1954 -- If the following is true:
1955 -- 1) Line has been saved
1956 -- 2) The new start date is greater than the existing start date
1957 -- 3) Document is an SPO
1958 -- 4) Line is rate based
1959 -- 5) Submitted or approved timecards exist for the line
1960 -- Then throw the error 'PO_CHNG_OTL_INVALID_START_DATE'.
1961 --
1962 ------------------------------------------------------------------------
1963 PROCEDURE otl_invalid_start_date_change(
1964 p_line_id_tbl IN PO_TBL_NUMBER
1965 , p_start_date_tbl IN PO_TBL_DATE
1966 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1967 , x_result_type OUT NOCOPY VARCHAR2
1968 )
1969 IS
1970 BEGIN
1971
1972 PO_VALIDATION_HELPER.no_timecards_exist(
1973 p_calling_module => D_otl_inv_start_date_change
1974 , p_line_id_tbl => p_line_id_tbl
1975 , p_start_date_tbl => p_start_date_tbl
1976 , p_expiration_date_tbl => NULL
1977 , p_column_name => c_START_DATE
1978 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_INVALID_START_DATE
1979 , x_results => x_results
1980 , x_result_type => x_result_type
1981 );
1982
1983 END otl_invalid_start_date_change;
1984
1985
1986 ------------------------------------------------------------------------
1987 -- If the following is true:
1988 -- 1) Line has been saved
1989 -- 2) The new end date is less than the existing end date
1990 -- 3) Document is an SPO
1991 -- 4) Line is rate based
1992 -- 5) Submitted or approved timecards exist for the line
1993 -- Then throw the error 'PO_CHNG_OTL_INVALID_END_DATE'.
1994 --
1995 ------------------------------------------------------------------------
1996 PROCEDURE otl_invalid_end_date_change(
1997 p_line_id_tbl IN PO_TBL_NUMBER
1998 , p_expiration_date_tbl IN PO_TBL_DATE
1999 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2000 , x_result_type OUT NOCOPY VARCHAR2
2001 )
2002 IS
2003 BEGIN
2004
2005 PO_VALIDATION_HELPER.no_timecards_exist(
2006 p_calling_module => D_otl_invalid_end_date_change
2007 , p_line_id_tbl => p_line_id_tbl
2008 , p_start_date_tbl => NULL
2009 , p_expiration_date_tbl => p_expiration_date_tbl
2010 , p_column_name => c_EXPIRATION_DATE
2011 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_INVALID_END_DATE
2012 , x_results => x_results
2013 , x_result_type => x_result_type
2014 );
2015
2016 END otl_invalid_end_date_change;
2017
2018
2019 -----------------------------------------------------------------------------
2020 -- Validates that the unit price is greater than or equal to zero
2021 -- for non-Fixed Price lines.
2022 -----------------------------------------------------------------------------
2023 PROCEDURE unit_price_ge_zero(
2024 p_line_id_tbl IN PO_TBL_NUMBER
2025 , p_unit_price_tbl IN PO_TBL_NUMBER
2026 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
2027 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
2028 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2029 , x_result_type OUT NOCOPY VARCHAR2
2030 )
2031 IS
2032 d_mod CONSTANT VARCHAR2(100) := D_unit_price_ge_zero;
2033
2034 l_line_id_tbl PO_TBL_NUMBER;
2035 l_unit_price_tbl PO_TBL_NUMBER;
2036 l_input_size NUMBER;
2037 l_count NUMBER;
2038 BEGIN
2039
2040 IF PO_LOG.d_proc THEN
2041 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2042 PO_LOG.proc_begin(d_mod,'p_unit_price_tbl',p_unit_price_tbl);
2043 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
2044 PO_LOG.proc_begin(d_mod,'p_clm_info_flag_tbl',p_clm_info_flag_tbl);
2045 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2046 END IF;
2047
2048 l_input_size := p_line_id_tbl.COUNT;
2049
2050 l_line_id_tbl := PO_TBL_NUMBER();
2051 l_line_id_tbl.extend(l_input_size);
2052 l_unit_price_tbl := PO_TBL_NUMBER();
2053 l_unit_price_tbl.extend(l_input_size);
2054
2055 l_count := 0;
2056
2057 FOR i IN 1 .. l_input_size LOOP
2058 IF (p_order_type_lookup_code_tbl(i) <> c_FIXED_PRICE) THEN
2059 IF p_clm_info_flag_tbl(i) <> 'Y' then
2060 l_count := l_count + 1;
2061 l_line_id_tbl(l_count) := p_line_id_tbl(i);
2062 l_unit_price_tbl(l_count) := p_unit_price_tbl(i);
2063 END IF;
2064 END IF;
2065 END LOOP;
2066
2067 l_line_id_tbl.trim(l_input_size-l_count);
2068 l_unit_price_tbl.trim(l_input_size-l_count);
2069
2070 PO_VALIDATION_HELPER.greater_or_equal_zero(
2071 p_calling_module => d_mod
2072 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
2073 , p_value_tbl => l_unit_price_tbl
2074 , p_entity_id_tbl => l_line_id_tbl
2075 , p_entity_type => c_ENTITY_TYPE_LINE
2076 , p_column_name => c_UNIT_PRICE
2077 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
2078 , x_results => x_results
2079 , x_result_type => x_result_type
2080 );
2081
2082 EXCEPTION
2083 WHEN OTHERS THEN
2084 IF PO_LOG.d_exc THEN
2085 PO_LOG.exc(d_mod,0,NULL);
2086 END IF;
2087 RAISE;
2088
2089 END unit_price_ge_zero;
2090
2091
2092 -----------------------------------------------------------------------------
2093 -- Validates that the list price per unit is greater than or equal to zero.
2094 -----------------------------------------------------------------------------
2095 PROCEDURE list_price_ge_zero(
2096 p_line_id_tbl IN PO_TBL_NUMBER
2097 , p_list_price_per_unit_tbl IN PO_TBL_NUMBER
2098 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2099 , x_result_type OUT NOCOPY VARCHAR2
2100 )
2101 IS
2102 BEGIN
2103
2104 PO_VALIDATION_HELPER.greater_or_equal_zero(
2105 p_calling_module => D_list_price_ge_zero
2106 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
2107 , p_value_tbl => p_list_price_per_unit_tbl
2108 , p_entity_id_tbl => p_line_id_tbl
2109 , p_entity_type => c_ENTITY_TYPE_LINE
2110 , p_column_name => c_LIST_PRICE_PER_UNIT
2111 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
2112 , x_results => x_results
2113 , x_result_type => x_result_type
2114 );
2115
2116 END list_price_ge_zero;
2117
2118
2119 -----------------------------------------------------------------------------
2120 -- Validates that the market price is greater than or equal to zero.
2121 -----------------------------------------------------------------------------
2122 PROCEDURE market_price_ge_zero(
2123 p_line_id_tbl IN PO_TBL_NUMBER
2124 , p_market_price_tbl IN PO_TBL_NUMBER
2125 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2126 , x_result_type OUT NOCOPY VARCHAR2
2127 )
2128 IS
2129 BEGIN
2130
2131 PO_VALIDATION_HELPER.greater_or_equal_zero(
2132 p_calling_module => D_market_price_ge_zero
2133 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
2134 , p_value_tbl => p_market_price_tbl
2135 , p_entity_id_tbl => p_line_id_tbl
2136 , p_entity_type => c_ENTITY_TYPE_LINE
2137 , p_column_name => c_MARKET_PRICE
2138 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
2139 , x_results => x_results
2140 , x_result_type => x_result_type
2141 );
2142
2143 END market_price_ge_zero;
2144
2145
2146
2147 -----------------------------------------------------------------------------
2148 -- Validates that the unit price may be changed, in that
2149 -- the price is not being used by another process that should
2150 -- prevent a price change.
2151 -----------------------------------------------------------------------------
2152 PROCEDURE validate_unit_price_change(
2153 p_line_id_tbl IN PO_TBL_NUMBER
2154 , p_unit_price_tbl IN PO_TBL_NUMBER
2155 , p_price_break_lookup_code_tbl IN PO_TBL_VARCHAR30
2156 , p_amt_changed_flag_tbl IN PO_TBL_VARCHAR1
2157 , x_result_set_id IN OUT NOCOPY NUMBER
2158 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2159 , x_result_type OUT NOCOPY VARCHAR2
2160 )
2161 IS
2162 d_mod CONSTANT VARCHAR2(100) := D_validate_unit_price_change;
2163
2164 l_data_key NUMBER;
2165 l_line_id_tbl PO_TBL_NUMBER;
2166 l_price_break_lookup_code_tbl PO_TBL_VARCHAR30;
2167 l_amount_changed_flag_tbl PO_TBL_VARCHAR1; -- <Bug 13503748: Encumbrance ER >--
2168
2169 BEGIN
2170
2171 IF PO_LOG.d_proc THEN
2172 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2173 PO_LOG.proc_begin(d_mod,'p_unit_price_tbl',p_unit_price_tbl);
2174 PO_LOG.proc_begin(d_mod,'p_price_break_lookup_code_tbl',p_price_break_lookup_code_tbl);
2175 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2176 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2177 END IF;
2178
2179 l_data_key := PO_CORE_S.get_session_gt_nextval();
2180
2181 FORALL i IN 1 .. p_line_id_tbl.COUNT
2182 INSERT INTO PO_SESSION_GT
2183 ( key
2184 , num1
2185 , num2
2186 , char1
2187 , char2 -- <Bug 13503748: Encumbrance ER >-
2188 )
2189 VALUES
2190 ( l_data_key
2191 , p_line_id_tbl(i)
2192 , p_unit_price_tbl(i)
2193 , p_price_break_lookup_code_tbl(i)
2194 , p_amt_changed_flag_tbl(i) -- <Bug 13503748: Encumbrance ER >--
2195 )
2196 ;
2197
2198
2199 SELECT
2200 SES.num1
2201 , SES.char1
2202 , SES.char2 -- <Bug 13503748: Encumbrance ER >--
2203 BULK COLLECT INTO
2204 l_line_id_tbl
2205 , l_price_break_lookup_code_tbl
2206 , l_amount_changed_flag_tbl -- <Bug 13503748: Encumbrance ER >--
2207 FROM
2208 PO_SESSION_GT SES
2209 , PO_LINES_ALL SAVED_LINE
2210 WHERE
2211 SES.key = l_data_key
2212 AND SAVED_LINE.po_line_id = SES.num1
2213 AND SAVED_LINE.order_type_lookup_code IN (c_QUANTITY, c_RATE)
2214 AND ( SES.num2 <> SAVED_LINE.unit_price
2215 OR (SES.num2 IS NULL AND SAVED_LINE.unit_price IS NOT NULL)
2216 OR (SES.num2 IS NOT NULL AND SAVED_LINE.unit_price IS NULL)
2217 )
2218 ;
2219
2220 IF (l_line_id_tbl.COUNT > 0) THEN
2221
2222 PO_VALIDATIONS.validate_unit_price_change(
2223 p_line_id_tbl => l_line_id_tbl
2224 , p_price_break_lookup_code_tbl => l_price_break_lookup_code_tbl
2225 , p_amount_changed_flag_tbl => l_amount_changed_flag_tbl --<Bug 13503748: Encumbrance ER >--
2226 , x_result_type => x_result_type
2227 , x_result_set_id => x_result_set_id
2228 , x_results => x_results
2229 );
2230
2231 ELSE
2232
2233 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2234
2235 END IF;
2236
2237
2238 IF PO_LOG.d_proc THEN
2239 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2240 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2241 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2242 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2243 END IF;
2244
2245 EXCEPTION
2246 WHEN OTHERS THEN
2247 IF PO_LOG.d_exc THEN
2248 PO_LOG.exc(d_mod,0,NULL);
2249 END IF;
2250 RAISE;
2251
2252 END validate_unit_price_change;
2253
2254
2255 -----------------------------------------------------------------------------
2256 -- Validates that the Expiration Date of the line
2257 -- is greater than or equal to the Effective From date
2258 -- of the Agreement.
2259 -- Agreements only.
2260 -----------------------------------------------------------------------------
2261 PROCEDURE expiration_ge_blanket_start(
2262 p_line_id_tbl IN PO_TBL_NUMBER
2263 , p_blanket_start_date_tbl IN PO_TBL_DATE
2264 , p_expiration_date_tbl IN PO_TBL_DATE
2265 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2266 , x_result_type OUT NOCOPY VARCHAR2
2267 )
2268 IS
2269 BEGIN
2270
2271 PO_VALIDATION_HELPER.start_date_le_end_date(
2272 p_calling_module => D_expiration_ge_blanket_start
2273 , p_start_date_tbl => p_blanket_start_date_tbl
2274 , p_end_date_tbl => p_expiration_date_tbl
2275 , p_entity_id_tbl => p_line_id_tbl
2276 , p_entity_type => c_entity_type_LINE
2277 , p_column_name => c_EXPIRATION_DATE
2278 , p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
2279 , p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
2280 , x_results => x_results
2281 , x_result_type => x_result_type
2282 );
2283
2284 END expiration_ge_blanket_start;
2285
2286
2287 -----------------------------------------------------------------------------
2288 -- Validates that the Expiration Date of the line
2289 -- is less than or equal to the Effective To date
2290 -- of the Agreement.
2291 -- Agreements only.
2292 -----------------------------------------------------------------------------
2293 PROCEDURE expiration_le_blanket_end(
2294 p_line_id_tbl IN PO_TBL_NUMBER
2295 , p_blanket_end_date_tbl IN PO_TBL_DATE
2296 , p_expiration_date_tbl IN PO_TBL_DATE
2297 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2298 , x_result_type OUT NOCOPY VARCHAR2
2299 )
2300 IS
2301 BEGIN
2302
2303 PO_VALIDATION_HELPER.start_date_le_end_date(
2304 p_calling_module => D_expiration_le_blanket_end
2305 , p_start_date_tbl => p_expiration_date_tbl
2306 , p_end_date_tbl => p_blanket_end_date_tbl
2307 , p_entity_id_tbl => p_line_id_tbl
2308 , p_entity_type => c_entity_type_LINE
2309 , p_column_name => c_EXPIRATION_DATE
2310 , p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
2311 , p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
2312 , x_results => x_results
2313 , x_result_type => x_result_type
2314 );
2315
2316 END expiration_le_blanket_end;
2317
2318
2319 -- <Complex Work R12 Start>
2320
2321 -- Bug 5072189 Start
2322 -------------------------------------------------------------------------
2323 -- The invoice close tolerance must be between 0 and 100, inclusive.
2324 -------------------------------------------------------------------------
2325 PROCEDURE recoupment_rate_range_check (
2326 p_line_id_tbl IN PO_TBL_NUMBER
2327 , p_recoupment_rate_tbl IN PO_TBL_NUMBER
2328 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2329 , x_result_type OUT NOCOPY VARCHAR2
2330 )
2331 IS
2332 BEGIN
2333
2334 PO_VALIDATION_HELPER.within_percentage_range(
2335 p_calling_module => D_recoupment_rate_range_check
2336 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
2337 , p_value_tbl => p_recoupment_rate_tbl
2338 , p_entity_id_tbl => p_line_id_tbl
2339 , p_entity_type => c_ENTITY_TYPE_LINE
2340 , p_column_name => c_RECOUPMENT_RATE
2341 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
2342 , x_results => x_results
2343 , x_result_type => x_result_type
2344 );
2345
2346 END recoupment_rate_range_check;
2347
2348 PROCEDURE retainage_rate_range_check (
2349 p_line_id_tbl IN PO_TBL_NUMBER
2350 , p_retainage_rate_tbl IN PO_TBL_NUMBER
2351 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2352 , x_result_type OUT NOCOPY VARCHAR2
2353 )
2354 IS
2355 BEGIN
2356
2357 PO_VALIDATION_HELPER.within_percentage_range(
2358 p_calling_module => D_retainage_rate_range_check
2359 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
2360 , p_value_tbl => p_retainage_rate_tbl
2361 , p_entity_id_tbl => p_line_id_tbl
2362 , p_entity_type => c_ENTITY_TYPE_LINE
2363 , p_column_name => c_RETAINAGE_RATE
2364 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
2365 , x_results => x_results
2366 , x_result_type => x_result_type
2367 );
2368
2369 END retainage_rate_range_check;
2370
2371 PROCEDURE prog_pay_rate_range_check (
2372 p_line_id_tbl IN PO_TBL_NUMBER
2373 , p_prog_pay_rate_tbl IN PO_TBL_NUMBER
2374 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2375 , x_result_type OUT NOCOPY VARCHAR2
2376 )
2377 IS
2378 BEGIN
2379
2380 PO_VALIDATION_HELPER.within_percentage_range(
2381 p_calling_module => D_prog_pay_rate_range_check
2382 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
2383 , p_value_tbl => p_prog_pay_rate_tbl
2384 , p_entity_id_tbl => p_line_id_tbl
2385 , p_entity_type => c_ENTITY_TYPE_LINE
2386 , p_column_name => c_PROGRESS_PAYMENT_RATE
2387 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
2388 , x_results => x_results
2389 , x_result_type => x_result_type
2390 );
2391
2392 END prog_pay_rate_range_check;
2393
2394 -- Bug 5072189 End
2395
2396 -----------------------------------------------------------------------------
2397 -- Validates that the line's quantity is greater than any of the
2398 -- quantity billed / quantity received of its quantity milestones.
2399 -- This check is only performed if quantity is being reduced below the
2400 -- current transaction quantity, since over-receiving is allowed.
2401 -----------------------------------------------------------------------------
2402 PROCEDURE qty_ge_qty_milestone_exec(
2403 p_line_id_tbl IN PO_TBL_NUMBER
2404 , p_quantity_tbl IN PO_TBL_NUMBER
2405 , x_result_set_id IN OUT NOCOPY NUMBER
2406 , x_result_type OUT NOCOPY VARCHAR2
2407 )
2408 IS
2409 d_mod CONSTANT VARCHAR2(100) := D_qty_ge_qty_milestone_exec;
2410 BEGIN
2411
2412 IF PO_LOG.d_proc THEN
2413 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2414 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
2415 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2416 END IF;
2417
2418 IF (x_result_set_id IS NULL) THEN
2419 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2420 END IF;
2421
2422 -- <Complex Work TODO>: Fill in the following messages
2423 -- PO_MESSAGE_S.PO_PO_QTY_LT_MILESTONE_BILL
2424 -- PO_MESSAGE_S.PO_PO_QTY_LT_MILESTONE_RECV
2425
2426 FORALL i IN 1 .. p_line_id_tbl.COUNT
2427 INSERT INTO PO_VALIDATION_RESULTS_GT
2428 ( result_set_id
2429 , entity_type
2430 , entity_id
2431 , column_name
2432 , column_val
2433 , message_name
2434 )
2435 SELECT
2436 x_result_set_id
2437 , c_ENTITY_TYPE_LINE
2438 , p_line_id_tbl(i)
2439 , c_QUANTITY
2440 , TO_CHAR(p_quantity_tbl(i))
2441 , (CASE
2442 WHEN POLL_TOTAL.max_qty_bill = POLL_TOTAL.max_qty_executed
2443 THEN 'CWPOTODOMESSAGE'
2444 ELSE 'CWPOTODOMESSAGE'
2445 END
2446 )
2447 FROM
2448 ( SELECT
2449 max_qty_recv,
2450 max_qty_bill,
2451 GREATEST(max_qty_recv, max_qty_bill) max_qty_executed
2452 FROM
2453 ( SELECT
2454 NVL(MAX(quantity_received), 0) max_qty_recv,
2455 NVL(MAX(quantity_billed), 0) max_qty_bill
2456 FROM PO_LINE_LOCATIONS_ALL PLL
2457 WHERE PLL.po_line_id = p_line_id_tbl(i)
2458 AND PLL.payment_type = c_MILESTONE
2459 AND PLL.value_basis = c_QUANTITY
2460 AND PLL.shipment_type = c_STANDARD
2461 )
2462 ) POLL_TOTAL, PO_LINES_ALL POL
2463 WHERE
2464 POL.po_line_id = p_line_id_tbl(i)
2465 AND p_quantity_tbl(i) IS NOT NULL
2466 AND p_quantity_tbl(i) < POL.quantity
2467 AND p_quantity_tbl(i) < POLL_TOTAL.max_qty_executed
2468 ;
2469
2470 IF (SQL%ROWCOUNT > 0) THEN
2471 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2472 ELSE
2473 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2474 END IF;
2475
2476 IF PO_LOG.d_proc THEN
2477 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2478 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2479 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2480 END IF;
2481
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 IF PO_LOG.d_exc THEN
2485 PO_LOG.exc(d_mod,0,NULL);
2486 END IF;
2487 RAISE;
2488
2489 END qty_ge_qty_milestone_exec;
2490
2491 -----------------------------------------------------------------------------
2492 -- Validates that the line's price is greater than the sum of prices
2493 -- on milestones that have been received or billed.
2494 -- This check is only performed if price is being reduced below the
2495 -- current transaction price, since over-receiving is allowed.
2496 -----------------------------------------------------------------------------
2497 PROCEDURE price_ge_price_milestone_exec(
2498 p_line_id_tbl IN PO_TBL_NUMBER
2499 , p_price_tbl IN PO_TBL_NUMBER
2500 , x_result_set_id IN OUT NOCOPY NUMBER
2501 , x_result_type OUT NOCOPY VARCHAR2
2502 )
2503 IS
2504 d_mod CONSTANT VARCHAR2(100) := D_price_ge_price_mstone_exec;
2505 BEGIN
2506
2507 IF PO_LOG.d_proc THEN
2508 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2509 PO_LOG.proc_begin(d_mod,'p_price_tbl',p_price_tbl);
2510 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2511 END IF;
2512
2513 IF (x_result_set_id IS NULL) THEN
2514 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2515 END IF;
2516
2517 -- <Complex Work TODO>: Fill in the following messages
2518 -- PO_MESSAGE_S.PO_PO_QTY_LT_MILESTONE_BILL
2519
2520 FORALL i IN 1 .. p_line_id_tbl.COUNT
2521 INSERT INTO PO_VALIDATION_RESULTS_GT
2522 ( result_set_id
2523 , entity_type
2524 , entity_id
2525 , column_name
2526 , column_val
2527 , message_name
2528 )
2529 SELECT
2530 x_result_set_id
2531 , c_ENTITY_TYPE_LINE
2532 , p_line_id_tbl(i)
2533 , c_UNIT_PRICE
2534 , TO_CHAR(p_price_tbl(i))
2535 , 'CWPOTODOMESSAGE'
2536 FROM
2537 ( SELECT
2538 NVL(SUM(CASE
2539 WHEN PLL.quantity_received > 0
2540 THEN NVL(PLL.price_override, 0)
2541 WHEN PLL.quantity_billed > 0
2542 THEN NVL(PLL.price_override, 0)
2543 ELSE 0
2544 END),0) sum_price_executed
2545 FROM PO_LINE_LOCATIONS_ALL PLL
2546 WHERE PLL.po_line_id = p_line_id_tbl(i)
2547 AND PLL.payment_type = c_MILESTONE
2548 AND PLL.value_basis = c_QUANTITY
2549 AND PLL.shipment_type = c_STANDARD
2550 ) POLL_TOTAL, PO_LINES_ALL POL
2551 WHERE
2552 POL.po_line_id = p_line_id_tbl(i)
2553 AND p_price_tbl(i) IS NOT NULL
2554 AND p_price_tbl(i) < POL.unit_price
2555 AND p_price_tbl(i) < POLL_TOTAL.sum_price_executed
2556 ;
2557
2558 IF (SQL%ROWCOUNT > 0) THEN
2559 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2560 ELSE
2561 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2562 END IF;
2563
2564 IF PO_LOG.d_proc THEN
2565 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2566 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2567 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2568 END IF;
2569
2570 EXCEPTION
2571 WHEN OTHERS THEN
2572 IF PO_LOG.d_exc THEN
2573 PO_LOG.exc(d_mod,0,NULL);
2574 END IF;
2575 RAISE;
2576
2577 END price_ge_price_milestone_exec;
2578
2579 -- <Complex Work R12 End>
2580
2581
2582 -------------------------------------------------------------------------------
2583 -- Ensures that the Unit of Measure is not null for non-FIXED PRICE lines.
2584 -------------------------------------------------------------------------------
2585 PROCEDURE unit_meas_not_null(
2586 p_line_id_tbl IN PO_TBL_NUMBER
2587 , p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30
2588 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
2589 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
2590 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2591 , x_result_type OUT NOCOPY VARCHAR2
2592 )
2593 IS
2594 d_mod CONSTANT VARCHAR2(100) := D_unit_meas_not_null;
2595 l_results_count NUMBER;
2596 BEGIN
2597
2598 IF PO_LOG.d_proc THEN
2599 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2600 PO_LOG.proc_begin(d_mod,'p_unit_meas_lookup_code_tbl',p_unit_meas_lookup_code_tbl);
2601 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
2602 PO_LOG.proc_begin(d_mod,'p_clm_info_flag_tbl',p_clm_info_flag_tbl);
2603 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2604 END IF;
2605
2606 IF (x_results IS NULL) THEN
2607 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2608 END IF;
2609
2610 l_results_count := x_results.result_type.COUNT;
2611
2612 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2613 IF ( ( p_order_type_lookup_code_tbl(i) <> c_FIXED_PRICE )
2614 AND p_unit_meas_lookup_code_tbl(i) IS NULL
2615 )
2616 THEN
2617 IF p_clm_info_flag_tbl(i) <> 'Y' then
2618 x_results.add_result(
2619 p_entity_type => c_ENTITY_TYPE_LINE
2620 , p_entity_id => p_line_id_tbl(i)
2621 , p_column_name => c_UNIT_MEAS_LOOKUP_CODE
2622 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2623 );
2624 end if;
2625 END IF;
2626 END LOOP;
2627
2628 IF (l_results_count < x_results.result_type.COUNT) THEN
2629 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2630 ELSE
2631 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2632 END IF;
2633
2634 IF PO_LOG.d_proc THEN
2635 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2636 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2637 END IF;
2638
2639 EXCEPTION
2640 WHEN OTHERS THEN
2641 IF PO_LOG.d_exc THEN
2642 PO_LOG.exc(d_mod,0,NULL);
2643 END IF;
2644 RAISE;
2645
2646 END unit_meas_not_null;
2647
2648
2649 -------------------------------------------------------------------------------
2650 -- Ensures that the Item Description is not null.
2651 -------------------------------------------------------------------------------
2652 PROCEDURE item_description_not_null(
2653 p_line_id_tbl IN PO_TBL_NUMBER
2654 , p_item_description_tbl IN PO_TBL_VARCHAR2000
2655 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2656 , x_result_type OUT NOCOPY VARCHAR2
2657 )
2658 IS
2659 BEGIN
2660
2661 PO_VALIDATION_HELPER.not_null(
2662 p_calling_module => D_item_description_not_null
2663 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_item_description_tbl)
2664 , p_entity_id_tbl => p_line_id_tbl
2665 , p_entity_type => c_entity_type_LINE
2666 , p_column_name => c_ITEM_DESCRIPTION
2667 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2668 , x_results => x_results
2669 , x_result_type => x_result_type
2670 );
2671
2672 END item_description_not_null;
2673
2674
2675 -------------------------------------------------------------------------------
2676 -- Ensures that the Category is not null.
2677 -------------------------------------------------------------------------------
2678 PROCEDURE category_id_not_null(
2679 p_line_id_tbl IN PO_TBL_NUMBER
2680 , p_category_id_tbl IN PO_TBL_NUMBER
2681 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
2682 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2683 , x_result_type OUT NOCOPY VARCHAR2
2684 )
2685 IS
2686
2687 l_line_id_tbl PO_TBL_NUMBER;
2688 l_category_id_tbl PO_TBL_NUMBER;
2689 l_input_size NUMBER;
2690 l_count NUMBER;
2691
2692 BEGIN
2693 -- CLM changes
2694 l_input_size := p_line_id_tbl.COUNT;
2695
2696 l_line_id_tbl := PO_TBL_NUMBER();
2697 l_line_id_tbl.extend(l_input_size);
2698 l_category_id_tbl := PO_TBL_NUMBER();
2699 l_category_id_tbl.extend(l_input_size);
2700
2701 l_count := 0;
2702
2703 FOR i IN 1 .. l_input_size LOOP
2704 IF (p_clm_info_flag_tbl(i) <> 'Y') THEN
2705 l_count := l_count + 1;
2706 l_line_id_tbl(l_count) := p_line_id_tbl(i);
2707 l_category_id_tbl(l_count) := p_category_id_tbl(i);
2708 END IF;
2709 END LOOP;
2710
2711 l_line_id_tbl.trim(l_input_size-l_count);
2712 l_category_id_tbl.trim(l_input_size-l_count);
2713
2714 PO_VALIDATION_HELPER.not_null(
2715 p_calling_module => D_category_id_not_null
2716 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(l_category_id_tbl)
2717 , p_entity_id_tbl => l_line_id_tbl
2718 , p_entity_type => c_entity_type_LINE
2719 , p_column_name => c_CATEGORY_ID
2720 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2721 , x_results => x_results
2722 , x_result_type => x_result_type
2723 );
2724
2725 END category_id_not_null;
2726
2727
2728 ---------------------------------------------------------------------------
2729 -- If order_type_lookup_code is Quantity and outside_operation flag is 'Y',
2730 -- then the item_id cannot be null.
2731 ---------------------------------------------------------------------------
2732 PROCEDURE item_id_not_null(
2733 p_id_tbl IN PO_TBL_NUMBER
2734 , p_item_id_tbl IN PO_TBL_NUMBER
2735 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
2736 , p_line_type_id_tbl IN PO_TBL_NUMBER
2737 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
2738 , p_message_name IN VARCHAR2
2739 , x_result_set_id IN OUT NOCOPY NUMBER
2740 , x_result_type OUT NOCOPY VARCHAR2
2741 )
2742 IS
2743 d_mod CONSTANT VARCHAR2(100) := D_item_id_not_null;
2744
2745 l_id_tbl PO_TBL_NUMBER;
2746 l_line_type_id_tbl PO_TBL_NUMBER;
2747 l_input_size NUMBER;
2748 l_count NUMBER;
2749
2750 BEGIN
2751
2752 IF PO_LOG.d_proc THEN
2753 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
2754 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
2755 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
2756 PO_LOG.proc_begin(d_mod,'p_line_type_id_tbl',p_line_type_id_tbl);
2757 PO_LOG.proc_begin(d_mod,'p_clm_info_flag_tbl',p_clm_info_flag_tbl);
2758 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
2759 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2760 END IF;
2761
2762 IF (x_result_set_id IS NULL) THEN
2763 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2764 END IF;
2765
2766 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2767
2768 l_input_size := p_id_tbl.COUNT;
2769
2770 l_id_tbl := PO_TBL_NUMBER();
2771 l_id_tbl.extend(l_input_size);
2772 l_line_type_id_tbl := PO_TBL_NUMBER();
2773 l_line_type_id_tbl.extend(l_input_size);
2774
2775 l_count := 0;
2776
2777 FOR i IN 1 .. l_input_size LOOP
2778 IF ( ( p_item_id_tbl(i) IS NULL
2779 AND p_order_type_lookup_code_tbl(i) = c_QUANTITY
2780 AND p_line_type_id_tbl(i) IS NOT NULL
2781 )
2782 OR ( p_item_id_tbl(i) IS NULL)
2783 )
2784 THEN
2785 if p_clm_info_flag_tbl(i)<> 'Y' then
2786 l_count := l_count + 1;
2787 l_id_tbl(l_count) := p_id_tbl(i);
2788 l_line_type_id_tbl(l_count) := p_line_type_id_tbl(i);
2789 end if;
2790 END IF;
2791 END LOOP;
2792
2793 IF (l_count > 0) THEN
2794
2795 l_id_tbl.trim(l_input_size-l_count);
2796 l_line_type_id_tbl.trim(l_input_size-l_count);
2797
2798 FORALL i IN 1 .. l_id_tbl.COUNT
2799 INSERT INTO PO_VALIDATION_RESULTS_GT
2800 ( result_set_id
2801 , entity_type
2802 , entity_id
2803 , column_name
2804 , message_name
2805 )
2806 SELECT
2807 x_result_set_id
2808 , c_ENTITY_TYPE_LINE
2809 , l_id_tbl(i)
2810 , c_ITEM_ID
2811 , p_message_name
2812 FROM
2813 PO_LINE_TYPES_B PLT
2814 WHERE
2815 PLT.line_type_id = l_line_type_id_tbl(i)
2816 AND PLT.outside_operation_flag = 'Y'
2817 ;
2818
2819 IF (SQL%ROWCOUNT > 0) THEN
2820 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2821 END IF;
2822
2823 END IF;
2824
2825 IF PO_LOG.d_proc THEN
2826 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2827 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2828 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2829 END IF;
2830
2831 EXCEPTION
2832 WHEN OTHERS THEN
2833 IF PO_LOG.d_exc THEN
2834 PO_LOG.exc(d_mod,0,NULL);
2835 END IF;
2836 RAISE;
2837
2838 END item_id_not_null;
2839
2840
2841 -------------------------------------------------------------------------------
2842 -- Ensures that the Job is not null for TEMP LABOR lines.
2843 -------------------------------------------------------------------------------
2844 PROCEDURE temp_labor_job_id_not_null(
2845 p_line_id_tbl IN PO_TBL_NUMBER
2846 , p_job_id_tbl IN PO_TBL_NUMBER
2847 , p_purchase_basis_tbl IN PO_TBL_VARCHAR30
2848 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2849 , x_result_type OUT NOCOPY VARCHAR2
2850 )
2851 IS
2852 d_mod CONSTANT VARCHAR2(100) := D_temp_labor_job_id_not_null;
2853 l_results_count NUMBER;
2854 BEGIN
2855
2856 IF PO_LOG.d_proc THEN
2857 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2858 PO_LOG.proc_begin(d_mod,'p_job_id_tbl',p_job_id_tbl);
2859 PO_LOG.proc_begin(d_mod,'p_purchase_basis_tbl',p_purchase_basis_tbl);
2860 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2861 END IF;
2862
2863 IF (x_results IS NULL) THEN
2864 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2865 END IF;
2866
2867 l_results_count := x_results.result_type.COUNT;
2868
2869 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2870 IF ( p_purchase_basis_tbl(i) = c_TEMP_LABOR
2871 AND p_job_id_tbl(i) IS NULL
2872 )
2873 THEN
2874 x_results.add_result(
2875 p_entity_type => c_ENTITY_TYPE_LINE
2876 , p_entity_id => p_line_id_tbl(i)
2877 , p_column_name => c_JOB_ID
2878 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2879 );
2880 END IF;
2881 END LOOP;
2882
2883 IF (l_results_count < x_results.result_type.COUNT) THEN
2884 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2885 ELSE
2886 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2887 END IF;
2888
2889 IF PO_LOG.d_proc THEN
2890 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2891 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2892 END IF;
2893
2894 EXCEPTION
2895 WHEN OTHERS THEN
2896 IF PO_LOG.d_exc THEN
2897 PO_LOG.exc(d_mod,0,NULL);
2898 END IF;
2899 RAISE;
2900
2901 END temp_labor_job_id_not_null;
2902
2903 -------------------------------------------------------------------------------
2904 -- Ensures that the Source Doc Line is not null if the Source Doc is not null
2905 -- and the Source Doc is not a contract.
2906 -------------------------------------------------------------------------------
2907 PROCEDURE src_doc_line_not_null(
2908 p_line_id_tbl IN PO_TBL_NUMBER
2909 , p_from_header_id_tbl IN PO_TBL_NUMBER
2910 , p_from_line_id_tbl IN PO_TBL_NUMBER
2911 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2912 , x_result_type OUT NOCOPY VARCHAR2
2913 )
2914 IS
2915 d_mod CONSTANT VARCHAR2(100) := D_src_doc_line_not_null;
2916 l_results_count NUMBER;
2917 BEGIN
2918
2919 IF (x_results IS NULL) THEN
2920 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2921 END IF;
2922
2923 IF PO_LOG.d_proc THEN
2924 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2925 PO_LOG.proc_begin(d_mod,'p_from_header_id_tbl',p_from_header_id_tbl);
2926 PO_LOG.proc_begin(d_mod,'p_from_line_id_tbl',p_from_line_id_tbl);
2927 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2928 END IF;
2929
2930 l_results_count := x_results.result_type.COUNT;
2931
2932 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2933 /* bug 9777541 Check if the given line is clm_enabled
2934 If so then we skip this check, the same check is handled
2935 as a part of submission checks. execute the chk only for
2936 non clm flows.*/
2937 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN --bug 9777541
2938
2939 IF (p_from_line_id_tbl(i) IS NULL
2940 AND p_from_header_id_tbl(i) IS NOT NULL
2941 )
2942 THEN
2943 x_results.add_result(
2944 p_entity_type => c_ENTITY_TYPE_LINE
2945 , p_entity_id => p_line_id_tbl(i)
2946 , p_column_name => c_FROM_LINE_ID
2947 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2948 );
2949 END IF;
2950 END IF;
2951
2952 END LOOP;
2953
2954 IF (l_results_count < x_results.result_type.COUNT) THEN
2955 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2956 ELSE
2957 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2958 END IF;
2959
2960 IF PO_LOG.d_proc THEN
2961 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2962 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2963 END IF;
2964
2965 EXCEPTION
2966 WHEN OTHERS THEN
2967 IF PO_LOG.d_exc THEN
2968 PO_LOG.exc(d_mod,0,NULL);
2969 END IF;
2970 RAISE;
2971 END src_doc_line_not_null;
2972
2973 -------------------------------------------------------------------------------
2974 -- Ensures that the Line Type is not null.
2975 -------------------------------------------------------------------------------
2976 PROCEDURE line_type_id_not_null(
2977 p_line_id_tbl IN PO_TBL_NUMBER
2978 , p_line_type_id_tbl IN PO_TBL_NUMBER
2979 , p_clm_info_flag_tbl IN PO_TBL_VARCHAR1 -- CLM changes
2980 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2981 , x_result_type OUT NOCOPY VARCHAR2
2982 )
2983 IS
2984
2985 l_line_id_tbl PO_TBL_NUMBER;
2986 l_line_type_id_tbl PO_TBL_NUMBER;
2987 l_input_size NUMBER;
2988 l_count NUMBER;
2989
2990 BEGIN
2991 -- CLM changes
2992 l_input_size := p_line_id_tbl.COUNT;
2993
2994 l_line_id_tbl := PO_TBL_NUMBER();
2995 l_line_id_tbl.extend(l_input_size);
2996 l_line_type_id_tbl := PO_TBL_NUMBER();
2997 l_line_type_id_tbl.extend(l_input_size);
2998
2999 l_count := 0;
3000
3001 FOR i IN 1 .. l_input_size LOOP
3002 IF (p_clm_info_flag_tbl(i) <> 'Y') THEN
3003 l_count := l_count + 1;
3004 l_line_id_tbl(l_count) := p_line_id_tbl(i);
3005 l_line_type_id_tbl(l_count) := p_line_type_id_tbl(i);
3006 END IF;
3007 END LOOP;
3008
3009 l_line_id_tbl.trim(l_input_size-l_count);
3010 l_line_type_id_tbl.trim(l_input_size-l_count);
3011
3012 PO_VALIDATION_HELPER.not_null(
3013 p_calling_module => D_line_type_id_not_null
3014 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(l_line_type_id_tbl)
3015 , p_entity_id_tbl => l_line_id_tbl
3016 , p_entity_type => c_entity_type_LINE
3017 , p_column_name => c_LINE_TYPE_ID
3018 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
3019 , x_results => x_results
3020 , x_result_type => x_result_type
3021 );
3022
3023 END line_type_id_not_null;
3024
3025 -------------------------------------------------------------------------------
3026 -- Ensures that the Start Date is not null for TEMP LABOR lines.
3027 -------------------------------------------------------------------------------
3028 PROCEDURE temp_lbr_start_date_not_null(
3029 p_line_id_tbl IN PO_TBL_NUMBER
3030 , p_start_date_tbl IN PO_TBL_DATE
3031 , p_purchase_basis_tbl IN PO_TBL_VARCHAR30
3032 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3033 , x_result_type OUT NOCOPY VARCHAR2
3034 )
3035 IS
3036 d_mod CONSTANT VARCHAR2(100) := D_temp_lbr_start_date_not_null;
3037 l_results_count NUMBER;
3038 BEGIN
3039
3040 IF PO_LOG.d_proc THEN
3041 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3042 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
3043 PO_LOG.proc_begin(d_mod,'p_purchase_basis_tbl',p_purchase_basis_tbl);
3044 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
3045 END IF;
3046
3047 IF (x_results IS NULL) THEN
3048 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3049 END IF;
3050
3051 l_results_count := x_results.result_type.COUNT;
3052
3053 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
3054 IF ( p_purchase_basis_tbl(i) = c_TEMP_LABOR
3055 AND p_start_date_tbl(i) IS NULL
3056 )
3057 THEN
3058 x_results.add_result(
3059 p_entity_type => c_ENTITY_TYPE_LINE
3060 , p_entity_id => p_line_id_tbl(i)
3061 , p_column_name => c_START_DATE
3062 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
3063 );
3064 END IF;
3065 END LOOP;
3066
3067 IF (l_results_count < x_results.result_type.COUNT) THEN
3068 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3069 ELSE
3070 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3071 END IF;
3072
3073 IF PO_LOG.d_proc THEN
3074 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3075 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
3076 END IF;
3077
3078 EXCEPTION
3079 WHEN OTHERS THEN
3080 IF PO_LOG.d_exc THEN
3081 PO_LOG.exc(d_mod,0,NULL);
3082 END IF;
3083 RAISE;
3084
3085 END temp_lbr_start_date_not_null;
3086
3087 -----------------------------------------------------------------------------
3088 -- OPM Integration R12
3089 -- Validates that secondary quantity is not null and greater than zero for
3090 -- an opm item.
3091 -----------------------------------------------------------------------------
3092 PROCEDURE line_sec_quantity_gt_zero(
3093 p_line_id_tbl IN PO_TBL_NUMBER
3094 , p_item_id_tbl IN PO_TBL_NUMBER
3095 , p_sec_quantity_tbl IN PO_TBL_NUMBER
3096 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3097 , x_result_type OUT NOCOPY VARCHAR2
3098 )
3099 IS
3100 d_mod CONSTANT VARCHAR2(100) := D_line_sec_quantity_gt_zero;
3101 l_inv_org_id_tbl PO_TBL_NUMBER;
3102 l_def_inv_org_id NUMBER;
3103 l_input_size NUMBER;
3104
3105 BEGIN
3106
3107 IF PO_LOG.d_proc THEN
3108 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3109 PO_LOG.proc_begin(d_mod,'p_sec_quantity_tbl',p_sec_quantity_tbl);
3110 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
3111 END IF;
3112
3113 -- SQL What : Get the default inv org id
3114 -- SQL Why : To pass to the Opm Validations
3115 select inventory_organization_id
3116 into l_def_inv_org_id
3117 from financials_system_parameters;
3118
3119 l_input_size := p_line_id_tbl.COUNT;
3120
3121 l_inv_org_id_tbl := PO_TBL_NUMBER();
3122 l_inv_org_id_tbl.extend(l_input_size);
3123
3124 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
3125 l_inv_org_id_tbl(i) := l_def_inv_org_id;
3126 END LOOP;
3127
3128 PO_VALIDATION_HELPER.gt_zero_opm_filter(
3129 p_calling_module => D_line_sec_quantity_gt_zero
3130 , p_value_tbl => p_sec_quantity_tbl
3131 , p_entity_id_tbl => p_line_id_tbl
3132 , p_item_id_tbl => p_item_id_tbl
3133 , p_inv_org_id_tbl => l_inv_org_id_tbl
3134 , p_entity_type => c_ENTITY_TYPE_LINE
3135 , p_column_name => c_SECONDARY_QUANTITY
3136 , x_results => x_results
3137 , x_result_type => x_result_type
3138 );
3139
3140 EXCEPTION
3141 WHEN OTHERS THEN
3142 IF PO_LOG.d_exc THEN
3143 PO_LOG.exc(d_mod,0,NULL);
3144 END IF;
3145 RAISE;
3146
3147 END line_sec_quantity_gt_zero;
3148
3149 -----------------------------------------------------------------------------
3150 -- OPM Integration R12
3151 -- Validates secondary quantity and the quantity combination for
3152 -- an opm item
3153 -----------------------------------------------------------------------------
3154 PROCEDURE line_qtys_within_deviation (
3155 p_line_id_tbl IN PO_TBL_NUMBER
3156 , p_item_id_tbl IN PO_TBL_NUMBER
3157 , p_quantity_tbl IN PO_TBL_NUMBER
3158 , p_primary_uom_tbl IN PO_TBL_VARCHAR30
3159 , p_sec_quantity_tbl IN PO_TBL_NUMBER
3160 , p_secondary_uom_tbl IN PO_TBL_VARCHAR30
3161 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3162 , x_result_type OUT NOCOPY VARCHAR2
3163 )
3164 IS
3165
3166 d_mod CONSTANT VARCHAR2(100) := D_line_qtys_within_deviation;
3167 l_inv_org_id_tbl PO_TBL_NUMBER;
3168 l_input_size NUMBER;
3169 l_def_inv_org_id NUMBER;
3170
3171 BEGIN
3172
3173 IF PO_LOG.d_proc THEN
3174 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3175 PO_LOG.proc_begin(d_mod,'p_sec_quantity_tbl',p_sec_quantity_tbl);
3176 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
3177 END IF;
3178
3179 -- SQL What : Get the default inv org id
3180 -- SQL Why : To pass to the Opm Validations
3181 select inventory_organization_id
3182 into l_def_inv_org_id
3183 from financials_system_parameters;
3184
3185 l_input_size := p_line_id_tbl.COUNT;
3186
3187 l_inv_org_id_tbl := PO_TBL_NUMBER();
3188 l_inv_org_id_tbl.extend(l_input_size);
3189
3190 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
3191 l_inv_org_id_tbl(i) := l_def_inv_org_id;
3192 END LOOP;
3193
3194 PO_VALIDATION_HELPER.qtys_within_deviation (
3195 p_calling_module => D_line_qtys_within_deviation
3196 , p_entity_id_tbl => p_line_id_tbl
3197 , p_item_id_tbl => p_item_id_tbl
3198 , p_inv_org_id_tbl => l_inv_org_id_tbl
3199 , p_quantity_tbl => p_quantity_tbl
3200 , p_primary_uom_tbl => p_primary_uom_tbl
3201 , p_sec_quantity_tbl => p_sec_quantity_tbl
3202 , p_secondary_uom_tbl => p_secondary_uom_tbl
3203 , p_column_name => c_QUANTITY
3204 , x_results => x_results
3205 , x_result_type => x_result_type
3206 );
3207
3208 EXCEPTION
3209 WHEN OTHERS THEN
3210 IF PO_LOG.d_exc THEN
3211 PO_LOG.exc(d_mod,0,NULL);
3212 END IF;
3213 RAISE;
3214
3215 END line_qtys_within_deviation;
3216
3217 -----------------------------------------------------------------------------
3218 -- Validates that source doc line is not null if the source document is filled
3219 -- in and the source document is a blanket or quotation
3220 -----------------------------------------------------------------------------
3221 PROCEDURE from_line_id_not_null (
3222 p_line_id_tbl IN PO_TBL_NUMBER
3223 , p_from_header_id_tbl IN PO_TBL_NUMBER
3224 , p_from_line_id_tbl IN PO_TBL_NUMBER
3225 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3226 , x_result_type OUT NOCOPY VARCHAR2
3227 )
3228 IS
3229
3230 d_mod CONSTANT VARCHAR2(100) := D_from_line_id_not_null;
3231 l_results_count NUMBER;
3232 l_src_doc_type_lookup_code VARCHAR2(30) := null;
3233 l_from_header_id NUMBER;
3234 BEGIN
3235
3236 IF PO_LOG.d_proc THEN
3237 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3238 PO_LOG.proc_begin(d_mod,'p_from_header_id_tbl',p_from_header_id_tbl);
3239 PO_LOG.proc_begin(d_mod,'p_from_line_id_tbl',p_from_line_id_tbl);
3240 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
3241 END IF;
3242
3243 IF (x_results IS NULL) THEN
3244 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3245 END IF;
3246
3247 l_results_count := x_results.result_type.COUNT;
3248
3249 FOR i IN 1 .. p_from_header_id_tbl.COUNT LOOP
3250
3251 l_from_header_id := p_from_header_id_tbl(i);
3252
3253 -- Only do validation if source document is chosen
3254 IF (l_from_header_id <> NULL)
3255 THEN
3256
3257 -- SQL What: Get type of source document
3258 -- SQL Why: To pass to validation
3259 SELECT type_lookup_code
3260 INTO l_src_doc_type_lookup_code
3261 FROM po_headers_all
3262 WHERE po_header_id = l_from_header_id;
3263
3264 -- Throw error if following is true:
3265 -- 1) Source doc is blanket or quotation
3266 -- 2) Source doc line is null
3267 IF ((l_src_doc_type_lookup_code = c_BLANKET
3268 OR l_src_doc_type_lookup_code = c_QUOTATION)
3269 AND p_from_line_id_tbl(i) IS NULL)
3270 THEN
3271 x_results.add_result(
3272 p_entity_type => c_ENTITY_TYPE_LINE
3273 , p_entity_id => p_line_id_tbl(i)
3274 , p_column_name => c_FROM_LINE_ID
3275 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
3276 );
3277 END IF; -- IF ((l_src_doc_type_lookup_code = c_BLANKET
3278
3279 END IF; -- IF (l_from_header_id <> NULL)
3280
3281 END LOOP;
3282
3283 IF (l_results_count < x_results.result_type.COUNT) THEN
3284 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3285 ELSE
3286 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3287 END IF;
3288
3289 IF PO_LOG.d_proc THEN
3290 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3291 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
3292 END IF;
3293
3294 EXCEPTION
3295 WHEN OTHERS THEN
3296 IF PO_LOG.d_exc THEN
3297 PO_LOG.exc(d_mod,0,NULL);
3298 END IF;
3299 RAISE;
3300
3301 END from_line_id_not_null;
3302
3303
3304 --Bug 5221843 START
3305 -------------------------------------------------------------------------------
3306 -- This procedure determines if Maximum Retainage Amount
3307 -- is greater than or equal to zero. If not, return a failure.
3308 -------------------------------------------------------------------------------
3309 PROCEDURE max_retain_amt_ge_zero(
3310 p_line_id_tbl IN PO_TBL_NUMBER
3311 , p_max_retain_amt_tbl IN PO_TBL_NUMBER
3312 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3313 , x_result_type OUT NOCOPY VARCHAR2
3314 )
3315 IS
3316 BEGIN
3317
3318 PO_VALIDATION_HELPER.greater_or_equal_zero(
3319 p_calling_module => D_max_retain_amt_ge_zero
3320 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
3321 , p_value_tbl => p_max_retain_amt_tbl
3322 , p_entity_id_tbl => p_line_id_tbl
3323 , p_entity_type => c_ENTITY_TYPE_LINE
3324 , p_column_name => c_MAX_RETAINAGE_AMOUNT
3325 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
3326 , x_results => x_results
3327 , x_result_type => x_result_type
3328 );
3329
3330 END max_retain_amt_ge_zero;
3331
3332 --Bug 5221843 END
3333
3334 --Bug 5453079 START
3335 -------------------------------------------------------------------------------
3336 -- This procedure determines if Maximum Retainage Amount
3337 -- is greater than already retained amount. If not, return a failure.
3338 -------------------------------------------------------------------------------
3339 PROCEDURE max_retain_amt_ge_retained(
3340 p_line_id_tbl IN PO_TBL_NUMBER
3341 , p_max_retain_amt_tbl IN PO_TBL_NUMBER
3342 , x_result_set_id IN OUT NOCOPY NUMBER
3343 , x_result_type OUT NOCOPY VARCHAR2
3344 )
3345 IS
3346 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_enc;
3347
3348 BEGIN
3349
3350 IF PO_LOG.d_proc THEN
3351 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3352 PO_LOG.proc_begin(d_mod,'p_max_retain_amt_tbl',p_max_retain_amt_tbl);
3353 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
3354 END IF;
3355
3356 IF (x_result_set_id IS NULL) THEN
3357 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
3358 END IF;
3359
3360 FORALL i IN 1 .. p_line_id_tbl.COUNT
3361 INSERT INTO PO_VALIDATION_RESULTS_GT
3362 ( result_set_id
3363 , entity_type
3364 , entity_id
3365 , column_name
3366 , column_val
3367 , message_name
3368 , token1_name
3369 , token1_value
3370 , token2_name
3371 , token2_value
3372 )
3373 SELECT
3374 x_result_set_id
3375 , c_ENTITY_TYPE_LINE
3376 , p_line_id_tbl(i)
3377 , c_MAX_RETAINAGE_AMOUNT
3378 , TO_CHAR(p_max_retain_amt_tbl(i))
3379 , PO_MESSAGE_S.PO_MAX_RET_AMT_GE_RETAINED
3380 , PO_MESSAGE_S.c_MAX_RET_AMT_token
3381 , TO_CHAR(p_max_retain_amt_tbl(i))
3382 , PO_MESSAGE_S.c_AMT_RETAINED_token
3383 , TO_CHAR(LOCATIONS_TOTAL.amount_retained)
3384 FROM
3385 ( SELECT NVL(SUM(POLL.retainage_withheld_amount),0) amount_retained
3386 FROM
3387 PO_LINE_LOCATIONS_ALL POLL
3388 WHERE
3389 POLL.po_line_id = p_line_id_tbl(i)
3390 ) LOCATIONS_TOTAL
3391 WHERE
3392 p_max_retain_amt_tbl(i) < LOCATIONS_TOTAL.amount_retained
3393 ;
3394
3395 IF (SQL%ROWCOUNT > 0) THEN
3396 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3397 ELSE
3398 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3399 END IF;
3400
3401 IF PO_LOG.d_proc THEN
3402 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
3403 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3404 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
3405 END IF;
3406
3407 EXCEPTION
3408 WHEN OTHERS THEN
3409 IF PO_LOG.d_exc THEN
3410 PO_LOG.exc(d_mod,0,NULL);
3411 END IF;
3412 RAISE;
3413
3414 END max_retain_amt_ge_retained;
3415 --Bug 5453079 END
3416
3417 PROCEDURE option_from_date_not_null (
3418 p_line_id_tbl IN PO_TBL_NUMBER
3419 , p_option_ind_tbl IN PO_TBL_VARCHAR1
3420 , p_option_from_date_tbl IN PO_TBL_DATE
3421 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3422 , x_result_type OUT NOCOPY VARCHAR2
3423 )
3424 IS
3425 d_mod CONSTANT VARCHAR2(100) := D_option_from_date_not_null;
3426 l_results_count NUMBER;
3427 BEGIN
3428
3429 IF PO_LOG.d_proc THEN
3430 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3431 PO_LOG.proc_begin(d_mod,'p_option_ind_tbl',p_option_ind_tbl);
3432 PO_LOG.proc_begin(d_mod,'p_option_from_date_tbl',p_option_from_date_tbl);
3433 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3434 END IF;
3435
3436 IF (x_results IS NULL) THEN
3437 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3438 END IF;
3439
3440 l_results_count := x_results.result_type.COUNT;
3441
3442 FOR i IN 1 .. p_line_id_tbl.count LOOP
3443
3444 IF (Nvl(p_option_ind_tbl(i),'B')='O') AND
3445 (p_option_from_date_tbl(i) IS NULL)
3446 THEN
3447 x_results.add_result(
3448 p_entity_type => c_ENTITY_TYPE_LINE
3449 , p_entity_id => p_line_id_tbl(i)
3450 , p_column_name => c_OPTION_FROM_DATE
3451 , p_message_name => 'PO_CLM_OPTN_FRM_DATE_NOT_NULL'
3452 );
3453 END IF;
3454 END LOOP;
3455 IF (l_results_count < x_results.result_type.COUNT) THEN
3456 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3457 ELSE
3458 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3459 END IF;
3460
3461 IF PO_LOG.d_proc THEN
3462 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3463 END IF;
3464
3465 EXCEPTION WHEN OTHERS THEN
3466 IF PO_LOG.d_exc THEN
3467 PO_LOG.exc(d_mod,0,NULL);
3468 END IF;
3469 RAISE;
3470 END option_from_date_not_null;
3471
3472 PROCEDURE option_to_date_not_null (
3473 p_line_id_tbl IN PO_TBL_NUMBER
3474 , p_option_ind_tbl IN PO_TBL_VARCHAR1
3475 ,p_option_to_Date_tbl IN PO_TBL_DATE
3476 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3477 , x_result_type OUT NOCOPY VARCHAR2
3478 )
3479 IS
3480 l_results_count NUMBER;
3481 d_mod CONSTANT VARCHAR2(100) := D_option_to_date_not_null;
3482 BEGIN
3483
3484 IF PO_LOG.d_proc THEN
3485 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3486 PO_LOG.proc_begin(d_mod,'p_option_ind_tbl',p_option_ind_tbl);
3487 PO_LOG.proc_begin(d_mod,'p_option_to_date_tbl',p_option_to_date_tbl);
3488 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3489 END IF;
3490
3491 IF (x_results IS NULL) THEN
3492 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3493 END IF;
3494
3495 l_results_count := x_results.result_type.COUNT;
3496
3497 FOR i IN 1 .. p_line_id_tbl.count LOOP
3498
3499 IF (Nvl(p_option_ind_tbl(i),'B')='O') AND
3500 (p_option_to_date_tbl(i) IS NULL)
3501 THEN
3502 x_results.add_result(
3503 p_entity_type => c_ENTITY_TYPE_LINE
3504 , p_entity_id => p_line_id_tbl(i)
3505 , p_column_name => c_OPTION_TO_DATE
3506 , p_message_name => 'PO_CLM_OPTION_TO_DATE_NOT_NULL'
3507 );
3508 END IF;
3509
3510 IF PO_LOG.d_proc THEN
3511 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3512 END IF;
3513
3514 END LOOP;
3515 IF (l_results_count < x_results.result_type.COUNT) THEN
3516 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3517 ELSE
3518 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3519 END IF;
3520 EXCEPTION WHEN OTHERS THEN
3521 IF PO_LOG.d_exc THEN
3522 PO_LOG.exc(d_mod,0,NULL);
3523 END IF;
3524 RAISE;
3525 END option_to_date_not_null;
3526
3527 PROCEDURE option_to_date_le_from_date (
3528 p_line_id_tbl IN PO_TBL_NUMBER
3529 , p_option_ind_tbl IN PO_TBL_VARCHAR1
3530 ,p_option_from_date_tbl IN PO_TBL_DATE
3531 ,p_option_to_Date_tbl IN PO_TBL_DATE
3532 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3533 , x_result_type OUT NOCOPY VARCHAR2
3534 )
3535 IS
3536 l_results_count NUMBER;
3537 d_mod CONSTANT VARCHAR2(100) := D_option_to_date_le_from_date;
3538
3539 BEGIN
3540 -- CLM changes
3541
3542 IF PO_LOG.d_proc THEN
3543 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3544 PO_LOG.proc_begin(d_mod,'p_option_ind_tbl',p_option_ind_tbl);
3545 PO_LOG.proc_begin(d_mod,'p_option_from_date_tbl',p_option_from_date_tbl);
3546 PO_LOG.proc_begin(d_mod,'p_option_to_date_tbl',p_option_to_date_tbl);
3547 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3548 END IF;
3549
3550 IF (x_results IS NULL) THEN
3551 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3552 END IF;
3553
3554 l_results_count := x_results.result_type.COUNT;
3555
3556 FOR i IN 1 .. p_line_id_tbl.count LOOP
3557
3558 IF (Nvl(p_option_ind_tbl(i),'B')='O') AND
3559 p_option_from_date_tbl(i) IS NOT NULL AND p_option_to_Date_tbl(i) IS NOT NULL
3560 THEN
3561 IF (p_option_from_date_tbl(i) > p_option_to_Date_tbl(i)) THEN
3562 x_results.add_result(
3563 p_entity_type => c_ENTITY_TYPE_LINE
3564 , p_entity_id => p_line_id_tbl(i)
3565 , p_column_name => c_OPTION_TO_DATE
3566 , p_message_name => 'PO_OPTION_TO_DATE_LE_FROM_DATE'
3567 );
3568 END IF;
3569 END IF;
3570 END LOOP;
3571 IF (l_results_count < x_results.result_type.COUNT) THEN
3572 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3573 ELSE
3574 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3575 END IF;
3576
3577 IF PO_LOG.d_proc THEN
3578 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3579 END IF;
3580
3581 EXCEPTION WHEN OTHERS THEN
3582 IF PO_LOG.d_exc THEN
3583 PO_LOG.exc(d_mod,0,NULL);
3584 END IF;
3585 RAISE;
3586 END option_to_date_le_from_date;
3587
3588 PROCEDURE option_from_date_le_sysdate (
3589 p_line_id_tbl IN PO_TBL_NUMBER
3590 , p_option_ind_tbl IN PO_TBL_VARCHAR1
3591 ,p_option_from_date_tbl IN PO_TBL_DATE
3592 , p_draft_id_tbl IN PO_TBL_NUMBER
3593 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3594 , x_result_type OUT NOCOPY VARCHAR2
3595 )
3596 IS
3597 l_results_count NUMBER;
3598 d_mod CONSTANT VARCHAR2(100) := D_option_from_date_le_sysdate;
3599 document_option_date DATE := NULL;
3600 mod_document_option_date DATE :=NULL;
3601 changeInOptionDate BOOLEAN := TRUE ;
3602 BEGIN
3603
3604 IF PO_LOG.d_proc THEN
3605 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3606 PO_LOG.proc_begin(d_mod,'p_option_ind_tbl',p_option_ind_tbl);
3607 PO_LOG.proc_begin(d_mod,'p_option_from_date_tbl',p_option_from_date_tbl);
3608 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3609 END IF;
3610
3611 IF (x_results IS NULL) THEN
3612 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3613 END IF;
3614
3615 l_results_count := x_results.result_type.COUNT;
3616
3617 FOR i IN 1 .. p_line_id_tbl.count LOOP
3618
3619 IF (Nvl(p_option_ind_tbl(i),'B')='O') AND
3620 p_option_from_date_tbl(i) IS NOT NULL
3621 THEN
3622
3623 IF (p_draft_id_tbl(i) IS NOT NULL AND p_draft_id_tbl(i) <> -1 ) THEN
3624 BEGIN
3625 SELECT CLM_OPTION_FROM_DATE
3626 INTO document_option_date
3627 FROM PO_LINES_MERGE_V
3628 WHERE (draft_id = -1
3629 OR draft_id IS NULL)
3630 AND po_line_id = p_line_id_tbl(i);
3631 EXCEPTION
3632 WHEN No_Data_Found THEN
3633 PO_LOG.exc(d_mod,0,NULL);
3634 END ;
3635
3636 mod_document_option_date := p_option_from_date_tbl(i);
3637
3638 IF(Trunc(document_option_date) = Trunc(mod_document_option_date)) THEN
3639 changeInOptionDate := FALSE;
3640 END IF ;
3641
3642 END IF;
3643
3644 IF (changeInOptionDate AND Trunc(p_option_from_date_tbl(i)) < Trunc(SYSDATE) ) THEN
3645 x_results.add_result(
3646 p_entity_type => c_ENTITY_TYPE_LINE
3647 , p_entity_id => p_line_id_tbl(i)
3648 , p_column_name => c_OPTION_FROM_DATE
3649 , p_message_name => 'PO_OPTION_FROM_DATE_LE_SYSDATE'
3650 );
3651 END IF;
3652 END IF;
3653 END LOOP;
3654 IF (l_results_count < x_results.result_type.COUNT) THEN
3655 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3656 ELSE
3657 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3658 END IF;
3659
3660 IF PO_LOG.d_proc THEN
3661 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3662 END IF;
3663
3664 EXCEPTION WHEN OTHERS THEN
3665 IF PO_LOG.d_exc THEN
3666 PO_LOG.exc(d_mod,0,NULL);
3667 END IF;
3668 RAISE;
3669
3670 END option_from_date_le_sysdate;
3671
3672 PROCEDURE option_fm_date_le_ex_date (
3673 p_line_id_tbl IN PO_TBL_NUMBER
3674 , p_exer_flag_tbl IN PO_TBL_VARCHAR1
3675 , p_option_from_date_tbl IN PO_TBL_DATE
3676 , p_option_exer_date_tbl IN PO_TBL_DATE
3677 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3678 , x_result_type OUT NOCOPY VARCHAR2
3679 )
3680 IS
3681 l_results_count NUMBER;
3682 d_mod CONSTANT VARCHAR2(100) := D_option_fm_date_le_ex_date;
3683
3684 BEGIN
3685 -- CLM changes
3686
3687 IF PO_LOG.d_proc THEN
3688 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3689 PO_LOG.proc_begin(d_mod,'p_exer_flag_tbl',p_exer_flag_tbl);
3690 PO_LOG.proc_begin(d_mod,'p_option_from_date_tbl',p_option_from_date_tbl);
3691 PO_LOG.proc_begin(d_mod,'p_option_exer_date_tbl',p_option_exer_date_tbl);
3692 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3693 END IF;
3694
3695 IF (x_results IS NULL) THEN
3696 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3697 END IF;
3698
3699 l_results_count := x_results.result_type.COUNT;
3700
3701 FOR i IN 1 .. p_line_id_tbl.count LOOP
3702
3703 IF (Nvl(p_exer_flag_tbl(i),'N')='Y') AND
3704 p_option_from_date_tbl(i) IS NOT NULL AND p_option_exer_date_tbl(i) IS NOT NULL
3705 THEN
3706 IF (p_option_from_date_tbl(i) > p_option_exer_date_tbl(i)) THEN
3707 x_results.add_result(
3708 p_entity_type => c_ENTITY_TYPE_LINE
3709 , p_entity_id => p_line_id_tbl(i)
3710 , p_column_name => c_EXERCISED_DATE
3711 , p_message_name => 'PO_OPTION_FM_DATE_LE_EX_DATE'
3712 );
3713 END IF;
3714 END IF;
3715 END LOOP;
3716 IF (l_results_count < x_results.result_type.COUNT) THEN
3717 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3718 ELSE
3719 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3720 END IF;
3721
3722 IF PO_LOG.d_proc THEN
3723 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3724 END IF;
3725
3726 EXCEPTION WHEN OTHERS THEN
3727 IF PO_LOG.d_exc THEN
3728 PO_LOG.exc(d_mod,0,NULL);
3729 END IF;
3730 RAISE;
3731 END option_fm_date_le_ex_date;
3732
3733 PROCEDURE line_num_disp_check (
3734 p_line_id_tbl IN PO_TBL_NUMBER
3735 , p_line_num_disp_tbl IN PO_TBL_VARCHAR100
3736 , p_group_line_id_tbl IN PO_TBL_NUMBER
3737 , p_clm_exhibit_name_tbl IN PO_TBL_VARCHAR20
3738 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
3739 , x_result_type OUT NOCOPY VARCHAR2
3740 )is
3741 d_mod CONSTANT VARCHAR2(100) := D_line_num_disp_check;
3742 l_results_count NUMBER;
3743 line_id_tbl_count NUMBER;
3744 l_line_num NUMBER;
3745 l_excep NUMBER := 0;
3746 BEGIN
3747
3748 line_id_tbl_count := p_line_id_tbl.COUNT;
3749
3750 IF PO_LOG.d_proc THEN
3751 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3752 PO_LOG.proc_begin(d_mod,'p_line_num_disp_tbl',p_line_num_disp_tbl);
3753 PO_LOG.proc_begin(d_mod,'x_result_type',x_result_type);
3754 END IF;
3755
3756 IF (x_results IS NULL) THEN
3757 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
3758 END IF;
3759
3760 l_results_count := x_results.result_type.COUNT;
3761
3762 FOR i IN 1 .. p_line_id_tbl.count LOOP
3763 ---- Added Check for exhibit. CLM Phase 4 - Elins project
3764 IF (p_group_line_id_tbl(i) IS NULL AND p_line_num_disp_tbl(i) IS NOT NULL AND p_clm_exhibit_name_tbl(i) IS NULL) THEN
3765 l_excep := 0;
3766 BEGIN
3767 l_line_num := TO_NUMBER(p_line_num_disp_tbl(i));
3768 EXCEPTION
3769 WHEN OTHERS THEN
3770 l_excep := 1;
3771 x_results.add_result(
3772 p_entity_type => c_ENTITY_TYPE_LINE
3773 , p_entity_id => p_line_id_tbl(i)
3774 , p_column_name => c_LINE_NUM_DISP
3775 , p_message_name => 'PO_INVALID_CLIN_NUM'
3776 );
3777 END;
3778
3779 IF (l_excep = 0) THEN
3780 ---- Added Check for exhibit. CLM Phase 4 - Elins project
3781 IF (p_clm_exhibit_name_tbl(i) IS NULL AND (p_group_line_id_tbl(i) IS NULL AND (LENGTH(p_line_num_disp_tbl(i)) <> 4 OR
3782 l_line_num > 9999))) THEN
3783 x_results.add_result(
3784 p_entity_type => c_ENTITY_TYPE_LINE
3785 , p_entity_id => p_line_id_tbl(i)
3786 , p_column_name => c_LINE_NUM_DISP
3787 , p_message_name => 'PO_INVALID_CLIN_NUM'
3788 );
3789
3790 END IF;
3791 END IF;
3792 END IF;
3793 -- Clm Phase 4 - Elins project
3794 IF (p_clm_exhibit_name_tbl(i) IS NOT NULL) THEN
3795 l_excep := 0;
3796 BEGIN
3797 l_line_num := TO_NUMBER(p_line_num_disp_tbl(i));
3798
3799 x_results.add_result(
3800 p_entity_type => c_ENTITY_TYPE_LINE
3801 , p_entity_id => p_line_id_tbl(i)
3802 , p_column_name => c_LINE_NUM_DISP
3803 , p_message_name => 'PO_INVALID_ELIN_NUM'
3804 );
3805
3806 l_excep := 1;
3807 EXCEPTION
3808 WHEN OTHERS THEN
3809 NULL;
3810 END;
3811
3812 IF (l_excep = 0) THEN
3813 ---- Added Check for exhibit. CLM Phase 4 - Elins project
3814 IF (LENGTH(p_line_num_disp_tbl(i)) <> 4)
3815 THEN
3816 x_results.add_result(
3817 p_entity_type => c_ENTITY_TYPE_LINE
3818 , p_entity_id => p_line_id_tbl(i)
3819 , p_column_name => c_LINE_NUM_DISP
3820 , p_message_name => 'PO_INVALID_ELIN_NUM'
3821 );
3822 l_excep := 1;
3823 END IF;
3824
3825 IF (l_excep = 0) THEN
3826 BEGIN
3827 SELECT 1 INTO l_excep FROM dual
3828 WHERE p_line_num_disp_tbl(i) LIKE p_clm_exhibit_name_tbl(i)||'%';
3829
3830 EXCEPTION
3831 WHEN No_Data_Found THEN
3832 x_results.add_result(
3833 p_entity_type => c_ENTITY_TYPE_LINE
3834 , p_entity_id => p_line_id_tbl(i)
3835 , p_column_name => c_LINE_NUM_DISP
3836 , p_message_name => 'PO_INVALID_ELIN_NUM'
3837 );
3838
3839 END;
3840 END IF;
3841 END IF;
3842 END IF;
3843
3844 END LOOP;
3845
3846 IF (l_results_count < x_results.result_type.COUNT) THEN
3847 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3848 ELSE
3849 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3850 END IF;
3851
3852 IF PO_LOG.d_proc THEN
3853 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3854 END IF;
3855
3856 EXCEPTION WHEN OTHERS THEN
3857 IF PO_LOG.d_exc THEN
3858 PO_LOG.exc(d_mod,0,NULL);
3859 END IF;
3860 RAISE;
3861
3862 END line_num_disp_check;
3863
3864 PROCEDURE mod_no_cancel_shp_complt(
3865 p_line_id_tbl IN PO_TBL_NUMBER
3866 ,p_control_action_tbl IN PO_TBL_VARCHAR30
3867 ,x_result_set_id IN OUT NOCOPY NUMBER
3868 ,x_result_type OUT NOCOPY VARCHAR2
3869 )
3870 IS
3871 d_mod CONSTANT VARCHAR2(100) := D_mod_no_cancel_shp_complt;
3872 BEGIN
3873
3874 IF PO_LOG.d_proc THEN
3875 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3876 PO_LOG.proc_begin(d_mod,'p_control_action_tbl',p_control_action_tbl);
3877 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
3878 END IF;
3879 IF (x_result_set_id IS NULL) THEN
3880 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
3881 END IF;
3882
3883 FORALL i IN 1..p_line_id_tbl.COUNT
3884 INSERT INTO po_validation_results_gt
3885 (result_set_id,
3886 entity_type,
3887 entity_id,
3888 message_name,
3889 column_name,
3890 column_val)
3891 SELECT x_result_set_id,
3892 c_entity_type_LINE
3893 ,p_line_id_tbl(i)
3894 ,PO_MESSAGE_S.PO_MOD_CANCEL_INVALID
3895 ,c_CONTROL_ACTION
3896 ,TO_CHAR(p_control_action_tbl(i))
3897 FROM DUAL
3898 WHERE nvl(p_control_action_tbl(i), '*') = 'CANCEL'
3899 AND not exists
3900 (SELECT 'Y'
3901 FROM PO_LINE_LOCATIONS_ALL
3902 WHERE po_line_id = p_line_id_tbl(i)
3903 AND quantity > quantity_received);
3904
3905 IF (SQL%ROWCOUNT > 0) THEN
3906 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
3907 ELSE
3908 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3909 END IF;
3910
3911
3912 IF PO_LOG.d_proc THEN
3913 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
3914 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
3915 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
3916 END IF;
3917
3918 EXCEPTION
3919 WHEN OTHERS THEN
3920 IF PO_LOG.d_exc THEN
3921 PO_LOG.exc(d_mod,0,NULL);
3922 END IF;
3923 RAISE;
3924
3925 END mod_no_cancel_shp_complt;
3926
3927 -- <Bug 9851804>
3928 -------------------------------------------------------------------------------
3929 -- This procedure validates:
3930 -- 1. line_num_display is unique.
3931 -- 2. SLIN numbers are in sync with CLIN numbers.
3932 -- NOTE: The logic has been copied from PO_VALIDATION_HELPER.child_num_unique.
3933 -------------------------------------------------------------------------------
3934 PROCEDURE clin_slin_num_check(
3935 p_header_id_tbl IN PO_TBL_NUMBER,
3936 p_draft_id_tbl IN PO_TBL_NUMBER,
3937 p_line_id_tbl IN PO_TBL_NUMBER,
3938 p_group_line_id_tbl IN PO_TBL_NUMBER,
3939 p_line_num_display_tbl IN PO_TBL_VARCHAR100,
3940 p_par_draft_id_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL, --<PAR Project>
3941 x_result_set_id IN OUT NOCOPY NUMBER,
3942 x_result_type OUT NOCOPY VARCHAR2
3943 )
3944 IS
3945 l_parent_id_tbl PO_TBL_NUMBER;
3946 l_data_key NUMBER;
3947 d_mod CONSTANT VARCHAR2(100) := D_clin_slin_num_check;
3948 l_par_draft_id_tbl po_tbl_number;
3949 BEGIN
3950 --<conc mod project>
3951 -- skip the validation for non-CLM documents
3952 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
3953 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
3954 RETURN;
3955 END IF;
3956
3957 IF PO_LOG.d_proc THEN
3958 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
3959 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
3960 END IF;
3961
3962 IF (x_result_set_id IS NULL) THEN
3963 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
3964 END IF;
3965
3966 -- Get a distinct list of parent ids.
3967 l_parent_id_tbl := PO_TBL_NUMBER() MULTISET UNION DISTINCT p_header_id_tbl;
3968 l_data_key := PO_CORE_S.get_session_gt_nextval();
3969
3970 FOR i IN 1 .. l_parent_id_tbl.COUNT LOOP
3971 -- Extract Par Line ids of this line
3972 PO_VALIDATION_HELPER.extract_par_draft_id_tbl(p_par_draft_id_tbl(i),
3973 l_par_draft_id_tbl);
3974 INSERT INTO PO_SESSION_GT
3975 ( key
3976 , index_num1
3977 , index_num2
3978 , num1
3979 , char1
3980 )
3981 SELECT distinct
3982 l_data_key
3983 , LINE.po_header_id
3984 , LINE.po_line_id
3985 , LINE.group_line_id
3986 , LINE.line_num_display
3987 FROM PO_LINES_MERGE_V LINE
3988 WHERE LINE.po_header_id =l_parent_id_tbl(i)
3989 AND LINE.draft_id <> p_draft_id_tbl(i)
3990 AND LINE.draft_id NOT IN (SELECT * FROM TABLE(l_par_draft_id_tbl))
3991 AND Nvl(LINE.mod_line_id, -999) <> p_line_id_tbl(i); -- <PAR Project>
3992 END LOOP;
3993
3994 -- Merge in the new data.
3995 FORALL i IN 1 .. p_line_id_tbl.COUNT
3996 MERGE INTO PO_SESSION_GT SES
3997 USING DUAL
3998 ON
3999 ( SES.key = l_data_key
4000 AND SES.index_num2 = p_line_id_tbl(i)
4001 AND SES.index_num1 = p_header_id_tbl(i)
4002 )
4003 WHEN MATCHED THEN UPDATE SET
4004 SES.num1 = p_group_line_id_tbl(i),
4005 SES.char1 = p_line_num_display_tbl(i),
4006 SES.index_char2 = c_NEW
4007 WHEN NOT MATCHED THEN
4008 INSERT
4009 ( key
4010 , index_num1
4011 , index_num2
4012 , num1
4013 , char1
4014 , index_char2
4015 )
4016 VALUES
4017 ( l_data_key
4018 , p_header_id_tbl(i)
4019 , p_line_id_tbl(i)
4020 , p_group_line_id_tbl(i)
4021 , p_line_num_display_tbl(i)
4022 , c_NEW
4023 );
4024
4025 -- validate uniqueness of line_num_display
4026 INSERT INTO PO_VALIDATION_RESULTS_GT
4027 ( result_set_id
4028 , entity_type
4029 , entity_id
4030 , column_name
4031 , column_val
4032 , message_name
4033 )
4034 SELECT
4035 x_result_set_id
4036 , c_entity_type_LINE
4037 , CHILD.index_num2
4038 , c_LINE_NUM_DISP
4039 , CHILD.char1
4040 , PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_LINE_NUM
4041 FROM
4042 PO_SESSION_GT CHILD
4043 WHERE
4044 CHILD.key = l_data_key
4045 AND CHILD.index_char2 = c_NEW -- check only new and modified records
4046 AND EXISTS
4047 ( SELECT null
4048 FROM PO_SESSION_GT SIBLING
4049 WHERE
4050 SIBLING.key = l_data_key
4051 AND SIBLING.index_num1 = CHILD.index_num1 -- header_id
4052 AND SIBLING.char1 = CHILD.char1 -- line num disp
4053 AND SIBLING.index_num2 <> CHILD.index_num2 -- line_id
4054 );
4055
4056 IF (SQL%ROWCOUNT > 0) THEN
4057 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4058 ELSE
4059 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4060 END IF;
4061
4062
4063 -- validate SLIN numbers are in sync with CLIN numbers
4064 INSERT INTO PO_VALIDATION_RESULTS_GT
4065 ( result_set_id
4066 , entity_type
4067 , entity_id
4068 , column_name
4069 , column_val
4070 , message_name
4071 )
4072 SELECT
4073 x_result_set_id
4074 , c_entity_type_LINE
4075 , SLIN.index_num2
4076 , c_LINE_NUM_DISP
4077 , SLIN.char1
4078 , 'PO_CLIN_SLIN_NUM_NOT_SYNC'
4079 FROM
4080 PO_SESSION_GT CLIN,
4081 PO_SESSION_GT SLIN
4082 WHERE
4083 CLIN.key = l_data_key
4084 AND SLIN.key = l_data_key
4085 AND CLIN.num1 IS NULL -- CLIN.group_line_id
4086 AND CLIN.index_num2 = SLIN.num1 -- CLIN.po_line_id = SLIN.group_line_id
4087 AND CLIN.char1 <> SUBSTR(SLIN.char1,0,4) -- line_num_display
4088 AND CLIN.index_char2 = c_NEW; -- check only new and modified CLINs
4089
4090 IF (SQL%ROWCOUNT > 0 OR x_result_type = PO_VALIDATIONS.c_result_type_FAILURE)
4091 THEN
4092 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4093 ELSE
4094 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4095 END IF;
4096
4097 IF PO_LOG.d_proc THEN
4098 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4099 END IF;
4100
4101 END clin_slin_num_check;
4102
4103
4104 --CLM PDOI Integration Starts
4105 -------------------------------------------------------------------------------
4106 -- This procedure validates:
4107 -- 1. Proper clin slin structure
4108 -- 2. Priced SLIN under a priced CLIN is not allowed.
4109 -------------------------------------------------------------------------------
4110
4111 PROCEDURE clin_slin_structure(
4112 p_line_id_tbl IN PO_TBL_NUMBER,
4113 --p_draft_id_tbl IN PO_TBL_NUMBER,
4114 p_intf_line_id_tbl IN PO_TBL_NUMBER,
4115 p_group_line_id_tbl IN PO_TBL_NUMBER,
4116 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
4117 x_result_type OUT NOCOPY VARCHAR2
4118 )
4119 IS
4120 d_mod CONSTANT VARCHAR2(100) := D_clin_slin_structure;
4121 l_results_count NUMBER := 0;
4122 l_line_count NUMBER := 0;
4123 BEGIN
4124
4125 -- skip the validation for non-CLM documents
4126 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4127 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4128 RETURN;
4129 END IF;
4130
4131 IF PO_LOG.d_proc THEN
4132 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4133 PO_LOG.proc_begin(d_mod,'p_group_line_id_tbl',p_group_line_id_tbl);
4134 END IF;
4135
4136 IF (x_results IS NULL) THEN
4137 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4138 END IF;
4139
4140 --l_results_count := x_results.result_type.COUNT;
4141
4142 FOR i IN 1 .. p_intf_line_id_tbl.count LOOP
4143 SELECT Count(interface_line_id)
4144 INTO l_line_count
4145 FROM po_lines_interface pclin
4146 WHERE pclin.interface_line_id = p_intf_line_id_tbl(i)
4147 AND pclin.group_line_id IS NULL /*clin*/
4148 AND Nvl(pclin.clm_info_flag,'N') = 'N' /*priced clin*/
4149 --AND Nvl(PLI.draft_id,-1) = Nvl(p_draft_id_tbl(i),-1)
4150 AND EXISTS ( --Priced SLIN
4151 SELECT interface_line_id
4152 FROM po_lines_interface
4153 WHERE interface_header_id = pclin.interface_header_id
4154 AND group_line_id IS NOT NULL
4155 AND Nvl(clm_info_flag,'N') = 'N'
4156 AND group_line_id = pclin.interface_line_id
4157 --AND Nvl(draft_id,-1) = Nvl(pclin.draft_id,-1)
4158 );
4159
4160 IF l_line_count > 0 THEN
4161 x_results.add_result(
4162 p_entity_type => c_ENTITY_TYPE_LINE
4163 , p_entity_id => p_intf_line_id_tbl(i)
4164 , p_column_name => c_CLM_INFO_FLAG
4165 , p_message_name => 'PO_CLM_CHG_TO_PRICE_LINE'
4166 );
4167
4168 END IF;
4169 l_results_count := l_results_count + l_line_count;
4170 END LOOP;
4171
4172 IF l_results_count > 0 THEN
4173 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4174 ELSE
4175 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4176 END IF;
4177
4178 IF PO_LOG.d_proc THEN
4179 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4180 END IF;
4181
4182 EXCEPTION
4183 WHEN OTHERS THEN
4184 IF PO_LOG.d_exc THEN
4185 PO_LOG.exc(d_mod,0,NULL);
4186 END IF;
4187 RAISE;
4188 END clin_slin_structure;
4189
4190
4191
4192 PROCEDURE validate_contract_type(
4193 p_intf_line_id_tbl IN PO_TBL_NUMBER
4194 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
4195 , p_contract_type_tbl IN PO_TBL_VARCHAR240
4196 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4197 , x_result_type OUT NOCOPY VARCHAR2
4198 ) IS
4199
4200 d_mod CONSTANT VARCHAR2(100) := D_validate_contract_type;
4201 l_results_count NUMBER := 0;
4202 l_line_count NUMBER := 0;
4203 is_contract_type_valid VARCHAR2(1) := NULL;
4204 BEGIN
4205
4206 -- skip the validation for non-CLM documents
4207 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4208 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4209 RETURN;
4210 END IF;
4211
4212 IF PO_LOG.d_proc THEN
4213 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4214 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
4215 END IF;
4216
4217 IF (x_results IS NULL) THEN
4218 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4219 END IF;
4220
4221 FOR i IN 1..p_intf_line_id_tbl.Count
4222 LOOP
4223
4224 IF p_contract_type_tbl(i) IS NULL
4225 THEN
4226 x_results.add_result(
4227 p_entity_type => c_ENTITY_TYPE_LINE
4228 , p_entity_id => p_intf_line_id_tbl(i)
4229 , p_column_name => c_CONTRACT_TYPE
4230 , p_message_name => 'PO_ALL_NOT_NULL'
4231 );
4232
4233 ELSE
4234 BEGIN
4235 SELECT 'Y' INTO is_contract_type_valid
4236 FROM dual
4237 WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
4238 WHERE lookup_type = Decode(p_order_type_lookup_code_tbl(i),
4239 'FIXED PRICE','PO_FEDERAL_CONTRACT_TYPES_AMT',
4240 'AMOUNT','PO_FEDERAL_CONTRACT_TYPES_AMT',
4241 'QUANTITY','PO_FEDERAL_CONTRACT_TYPES_QTY',
4242 '*')
4243 AND lookup_code = p_contract_type_tbl(i)
4244 );
4245 EXCEPTION WHEN No_Data_Found
4246 THEN
4247 x_results.add_result(
4248 p_entity_type => c_ENTITY_TYPE_LINE
4249 , p_entity_id => p_intf_line_id_tbl(i)
4250 , p_column_name => c_CONTRACT_TYPE
4251 , p_message_name => 'PO_CONTRACT_TYPE_INVALID'
4252 );
4253 l_results_count := l_results_count + 1;
4254 END;
4255
4256 END IF;
4257 END LOOP;
4258
4259 IF l_results_count > 0 THEN
4260 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4261 ELSE
4262 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4263 END IF;
4264
4265 IF PO_LOG.d_proc THEN
4266 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4267 END IF;
4268
4269 EXCEPTION
4270 WHEN OTHERS THEN
4271 IF PO_LOG.d_exc THEN
4272 PO_LOG.exc(d_mod,0,NULL);
4273 END IF;
4274
4275 RAISE;
4276
4277 END validate_contract_type;
4278
4279
4280
4281 PROCEDURE validate_cost_constraint(
4282 p_intf_line_id_tbl IN PO_TBL_NUMBER
4283 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30
4284 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4285 , x_result_type OUT NOCOPY VARCHAR2
4286 ) IS
4287
4288 d_mod CONSTANT VARCHAR2(100) := D_validate_cost_constraint;
4289 l_results_count NUMBER := 0;
4290 l_line_count NUMBER := 0;
4291 is_cost_constraint_valid VARCHAR2(1) := NULL;
4292 BEGIN
4293
4294 -- skip the validation for non-CLM documents
4295 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4296 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4297 RETURN;
4298 END IF;
4299
4300 IF PO_LOG.d_proc THEN
4301 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4302 --PO_LOG.proc_begin(d_mod,'p_cost_constraint_tbl',p_order_type_lookup_code_tbl);
4303 END IF;
4304
4305 IF (x_results IS NULL) THEN
4306 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4307 END IF;
4308
4309 FOR i IN 1..p_intf_line_id_tbl.Count
4310 LOOP
4311 IF p_cost_constraint_tbl(i) IS NOT NULL
4312 THEN
4313 BEGIN
4314 SELECT 'Y' INTO is_cost_constraint_valid
4315 FROM dual
4316 WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
4317 WHERE lookup_type = 'PO_FEDERAL_COST_CONSTRAINTS'
4318 AND lookup_code = p_cost_constraint_tbl(i)
4319 );
4320 EXCEPTION WHEN No_Data_Found
4321 THEN
4322 x_results.add_result(
4323 p_entity_type => c_ENTITY_TYPE_LINE
4324 , p_entity_id => p_intf_line_id_tbl(i)
4325 , p_column_name => c_COST_CONSTRAINT
4326 , p_message_name => 'PO_COST_CONSTRAINT_INVALID'
4327 );
4328 l_results_count := l_results_count + 1;
4329 END;
4330 END IF;
4331 END LOOP;
4332
4333 IF l_results_count > 0 THEN
4334 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4335 ELSE
4336 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4337 END IF;
4338
4339 IF PO_LOG.d_proc THEN
4340 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4341 END IF;
4342
4343 EXCEPTION
4344 WHEN OTHERS THEN
4345 IF PO_LOG.d_exc THEN
4346 PO_LOG.exc(d_mod,0,NULL);
4347 END IF;
4348 RAISE;
4349
4350 END validate_cost_constraint;
4351
4352
4353 PROCEDURE check_contract_num_and_ver(
4354 p_intf_line_id_tbl IN PO_TBL_NUMBER
4355 , p_oke_contract_header_id_tbl IN PO_TBL_NUMBER
4356 , p_oke_contract_header_num_tbl IN PO_TBL_VARCHAR240
4357 , p_oke_contract_version_id_tbl IN PO_TBL_NUMBER
4358 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4359 , x_result_type OUT NOCOPY VARCHAR2
4360 ) IS
4361
4362 d_mod CONSTANT VARCHAR2(100) := D_check_contract_num_and_ver;
4363 l_results_count NUMBER := 0;
4364 l_line_count NUMBER := 0;
4365 is_contract_valid VARCHAR2(1) := NULL;
4366 BEGIN
4367
4368 -- skip the validation for non-CLM documents
4369 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4370 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4371 RETURN;
4372 END IF;
4373
4374 IF PO_LOG.d_proc THEN
4375 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4376 --PO_LOG.proc_begin(d_mod,'p_oke_contract_header_num_tbl',p_oke_contract_header_num_tbl);
4377 END IF;
4378
4379 IF (x_results IS NULL) THEN
4380 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4381 END IF;
4382
4383 FOR i IN 1..p_intf_line_id_tbl.Count
4384 LOOP
4385 IF p_oke_contract_header_id_tbl(i) IS NULL
4386 THEN
4387 IF p_oke_contract_header_num_tbl(i) IS NOT NULL
4388 THEN
4389 x_results.add_result(
4390 p_entity_type => c_ENTITY_TYPE_LINE
4391 , p_entity_id => p_intf_line_id_tbl(i)
4392 , p_column_name => c_OKE_CONTRACT_HEADER_NUM
4393 , p_message_name => 'PO_CLM_CHG_TO_PRICE_LINE'
4394 );
4395 l_results_count := l_results_count + 1;
4396 END IF;
4397 ELSE
4398 BEGIN
4399 SELECT 'Y' INTO is_contract_valid
4400 FROM dual
4401 WHERE EXISTS ( SELECT 1 FROM okc_k_headers_b
4402 WHERE id = p_oke_contract_header_id_tbl(i)
4403 );
4404 EXCEPTION WHEN No_Data_Found
4405 THEN
4406 x_results.add_result(
4407 p_entity_type => c_ENTITY_TYPE_LINE
4408 , p_entity_id => p_intf_line_id_tbl(i)
4409 , p_column_name => c_OKE_CONTRACT_HEADER_ID
4410 , p_message_name => 'PO_CLM_CHG_TO_PRICE_LINE'
4411 );
4412 l_results_count := l_results_count + 1;
4413 END;
4414
4415 BEGIN
4416 SELECT 'Y' INTO is_contract_valid
4417 FROM dual
4418 WHERE EXISTS ( SELECT major_version FROM oke_k_vers_numbers_v
4419 WHERE chr_id = p_oke_contract_header_id_tbl(i)
4420 AND major_version = p_oke_contract_version_id_tbl(i)
4421 UNION
4422 SELECT major_version FROM okc_k_vers_numbers_h
4423 WHERE chr_id = p_oke_contract_header_id_tbl(i)
4424 AND major_version = p_oke_contract_version_id_tbl(i)
4425 );
4426 EXCEPTION WHEN No_Data_Found
4427 THEN
4428 x_results.add_result(
4429 p_entity_type => c_ENTITY_TYPE_LINE
4430 , p_entity_id => p_intf_line_id_tbl(i)
4431 , p_column_name => c_OKE_CONTRACT_VERSION_ID
4432 , p_message_name => 'PO_CLM_CHG_TO_PRICE_LINE'
4433 );
4434 l_results_count := l_results_count + 1;
4435 END;
4436 END IF;
4437 END LOOP;
4438 IF l_results_count > 0 THEN
4439 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4440 ELSE
4441 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4442 END IF;
4443
4444 IF PO_LOG.d_proc THEN
4445 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4446 END IF;
4447
4448 EXCEPTION
4449 WHEN OTHERS THEN
4450 IF PO_LOG.d_exc THEN
4451 PO_LOG.exc(d_mod,0,NULL);
4452 END IF;
4453 RAISE;
4454
4455 END check_contract_num_and_ver;
4456
4457
4458
4459 PROCEDURE validate_option_indicator(
4460 p_intf_line_id_tbl IN PO_TBL_NUMBER
4461 , p_clm_option_indicator_tbl IN PO_TBL_VARCHAR1
4462 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4463 , x_result_type OUT NOCOPY VARCHAR2
4464 ) IS
4465
4466 d_mod CONSTANT VARCHAR2(100) := D_validate_option_indicator;
4467 l_results_count NUMBER := 0;
4468 l_line_count NUMBER := 0;
4469
4470 BEGIN
4471
4472 -- skip the validation for non-CLM documents
4473 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4474 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4475 RETURN;
4476 END IF;
4477
4478 IF PO_LOG.d_proc THEN
4479 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4480 PO_LOG.proc_begin(d_mod,'p_clm_option_indicator_tbl',p_clm_option_indicator_tbl);
4481 END IF;
4482
4483 IF (x_results IS NULL) THEN
4484 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4485 END IF;
4486
4487 FOR i IN 1..p_intf_line_id_tbl.Count
4488 LOOP
4489 IF p_clm_option_indicator_tbl(i) IS NOT NULL
4490 THEN
4491 IF p_clm_option_indicator_tbl(i) NOT IN ('B','O')
4492 THEN
4493 x_results.add_result(
4494 p_entity_type => c_ENTITY_TYPE_LINE
4495 , p_entity_id => p_intf_line_id_tbl(i)
4496 , p_column_name => c_CLM_OPTION_INDICATOR
4497 , p_message_name => 'PO_OPTION_INDICATOR_INVALID'
4498 );
4499 l_results_count := l_results_count + 1;
4500 END IF;
4501 END IF;
4502 END LOOP;
4503
4504 IF l_results_count > 0 THEN
4505 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4506 ELSE
4507 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4508 END IF;
4509
4510 IF PO_LOG.d_proc THEN
4511 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4512 END IF;
4513
4514 EXCEPTION
4515 WHEN OTHERS THEN
4516 IF PO_LOG.d_exc THEN
4517 PO_LOG.exc(d_mod,0,NULL);
4518 END IF;
4519 RAISE;
4520
4521 END validate_option_indicator;
4522
4523
4524 PROCEDURE validate_clm_base_line_num(
4525 p_intf_line_id_tbl IN PO_TBL_NUMBER
4526 , p_clm_option_indicator_tbl IN PO_TBL_VARCHAR1
4527 , p_clm_base_line_num_tbl IN PO_TBL_NUMBER
4528 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4529 , x_result_type OUT NOCOPY VARCHAR2
4530 ) IS
4531
4532 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_base_line_num;
4533 l_results_count NUMBER := 0;
4534 l_line_count NUMBER := 0;
4535 l_intf_header_id NUMBER := NULL;
4536 l_clm_info_flag VARCHAR2(1) := NULL;
4537 l_clm_option_indicator VARCHAR2(1) := NULL;
4538 BEGIN
4539
4540 -- skip the validation for non-CLM documents
4541 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4542 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4543 RETURN;
4544 END IF;
4545
4546 IF PO_LOG.d_proc THEN
4547 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4548 --PO_LOG.proc_begin(d_mod,'validate_clm_base_line_num',validate_clm_base_line_num);
4549 END IF;
4550
4551 IF (x_results IS NULL) THEN
4552 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4553 END IF;
4554
4555 FOR i IN 1..p_intf_line_id_tbl.Count
4556 LOOP
4557 IF p_clm_base_line_num_tbl(i) IS NOT NULL
4558 THEN
4559 IF p_clm_option_indicator_tbl(i) = 'O'
4560 THEN
4561 BEGIN
4562 SELECT interface_header_id INTO l_intf_header_id
4563 FROM po_lines_interface
4564 WHERE interface_line_id = p_intf_line_id_tbl(i);
4565
4566 SELECT Nvl(clm_info_flag,'N'),
4567 Nvl(clm_option_indicator,'*')
4568 INTO l_clm_info_flag,
4569 l_clm_option_indicator
4570 FROM po_lines_interface
4571 WHERE interface_line_id = p_clm_base_line_num_tbl(i)
4572 AND interface_header_id = l_intf_header_id;
4573
4574 IF l_clm_info_flag <> 'N' --OR l_clm_option_indicator <> 'B'
4575 THEN
4576 x_results.add_result(
4577 p_entity_type => c_ENTITY_TYPE_LINE
4578 , p_entity_id => p_intf_line_id_tbl(i)
4579 , p_column_name => c_CLM_BASE_LINE_NUM
4580 , p_message_name => 'PO_BASE_LINE_NUM_INVALID'
4581 );
4582 l_results_count := l_results_count + 1;
4583 END IF;
4584 EXCEPTION WHEN No_Data_Found
4585 THEN
4586 x_results.add_result(
4587 p_entity_type => c_ENTITY_TYPE_LINE
4588 , p_entity_id => p_intf_line_id_tbl(i)
4589 , p_column_name => c_CLM_BASE_LINE_NUM
4590 , p_message_name => 'PO_BASE_LINE_NUM_INVALID'
4591 );
4592 l_results_count := l_results_count + 1;
4593 END;
4594 ELSE --clm_option_indicator has a calue other than 'O'
4595 x_results.add_result(
4596 p_entity_type => c_ENTITY_TYPE_LINE
4597 , p_entity_id => p_intf_line_id_tbl(i)
4598 , p_column_name => c_CLM_BASE_LINE_NUM
4599 , p_message_name => 'PO_BASE_LINE_NUM_INVALID'
4600 );
4601 l_results_count := l_results_count + 1;
4602 END IF;
4603 END IF;
4604 END LOOP;
4605
4606 IF l_results_count > 0 THEN
4607 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4608 ELSE
4609 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4610 END IF;
4611
4612 IF PO_LOG.d_proc THEN
4613 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4614 END IF;
4615
4616 EXCEPTION
4617 WHEN OTHERS THEN
4618 IF PO_LOG.d_exc THEN
4619 PO_LOG.exc(d_mod,0,NULL);
4620 END IF;
4621 RAISE;
4622
4623 END validate_clm_base_line_num;
4624
4625
4626
4627 PROCEDURE validate_clm_option_num(
4628 p_intf_line_id_tbl IN PO_TBL_NUMBER
4629 , p_clm_option_indicator_tbl IN PO_TBL_VARCHAR1
4630 , p_clm_base_line_num_tbl IN PO_TBL_NUMBER
4631 , p_clm_option_num_tbl IN PO_TBL_NUMBER
4632 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4633 , x_result_type OUT NOCOPY VARCHAR2
4634 ) IS
4635
4636 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_option_num;
4637 l_results_count NUMBER := 0;
4638 l_line_count NUMBER := 0;
4639 l_dup_option_num_count NUMBER := 0;
4640
4641 BEGIN
4642
4643 -- skip the validation for non-CLM documents
4644 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4645 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4646 RETURN;
4647 END IF;
4648
4649 IF PO_LOG.d_proc THEN
4650 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4651 --PO_LOG.proc_begin(d_mod,'validate_clm_base_line_num',validate_clm_base_line_num);
4652 END IF;
4653 IF (x_results IS NULL) THEN
4654 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4655 END IF;
4656
4657 FOR i IN 1..p_intf_line_id_tbl.Count
4658 LOOP
4659 IF p_clm_option_num_tbl(i) < 1
4660 THEN
4661 x_results.add_result(
4662 p_entity_type => c_ENTITY_TYPE_LINE
4663 , p_entity_id => p_intf_line_id_tbl(i)
4664 , p_column_name => c_CLM_OPTION_NUM
4665 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
4666 );
4667 l_results_count := l_results_count + 1;
4668 ELSIF p_clm_option_num_tbl(i) IS NOT NULL
4669 THEN
4670 IF p_clm_option_indicator_tbl(i) = 'O' AND p_clm_base_line_num_tbl(i) IS NOT NULL
4671 THEN
4672 l_dup_option_num_count := 0;
4673
4674 SELECT Count(interface_line_id)
4675 INTO l_dup_option_num_count
4676 FROM po_lines_interface
4677 WHERE clm_base_line_num = Nvl(p_clm_base_line_num_tbl(i),-1)
4678 AND clm_option_num = p_clm_option_num_tbl(i);
4679
4680 IF l_dup_option_num_count > 0
4681 THEN
4682 x_results.add_result(
4683 p_entity_type => c_ENTITY_TYPE_LINE
4684 , p_entity_id => p_intf_line_id_tbl(i)
4685 , p_column_name => c_CLM_OPTION_NUM
4686 , p_message_name => 'PO_DUPLICATE_OPTION_NUM'
4687 );
4688 l_results_count := l_results_count + 1;
4689 END IF; --dup_count
4690 ELSE
4691 x_results.add_result(
4692 p_entity_type => c_ENTITY_TYPE_LINE
4693 , p_entity_id => p_intf_line_id_tbl(i)
4694 , p_column_name => c_CLM_OPTION_NUM
4695 , p_message_name => 'PO_OPTION_NUM_INVALID'
4696 );
4697 l_results_count := l_results_count + 1;
4698 END IF;
4699 END IF;
4700 END LOOP;
4701
4702 IF l_results_count > 0 THEN
4703 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4704 ELSE
4705 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4706 END IF;
4707
4708 IF PO_LOG.d_proc THEN
4709 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4710 END IF;
4711
4712 EXCEPTION
4713 WHEN OTHERS THEN
4714 IF PO_LOG.d_exc THEN
4715 PO_LOG.exc(d_mod,0,NULL);
4716 END IF;
4717 RAISE;
4718
4719 END validate_clm_option_num;
4720
4721
4722
4723 PROCEDURE validate_clm_idc_type(
4724 p_intf_line_id_tbl IN PO_TBL_NUMBER
4725 , p_clm_idc_type_tbl IN PO_TBL_VARCHAR240
4726 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4727 , x_result_type OUT NOCOPY VARCHAR2
4728 ) IS
4729
4730 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_idc_type;
4731 l_results_count NUMBER := 0;
4732 l_line_count NUMBER := 0;
4733 is_valid_idc_type VARCHAR2(1) := NULL;
4734 BEGIN
4735
4736 -- skip the validation for non-CLM documents
4737 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4738 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4739 RETURN;
4740 END IF;
4741
4742 IF PO_LOG.d_proc THEN
4743 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4744 --PO_LOG.proc_begin(d_mod,'p_clm_option_indicator_tbl',p_clm_idc_type_tbl);
4745 END IF;
4746
4747 IF (x_results IS NULL) THEN
4748 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4749 END IF;
4750
4751 FOR i IN 1..p_intf_line_id_tbl.Count
4752 LOOP
4753 IF PO_PDOI_PARAMS.g_request.document_type <>
4754 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
4755 THEN
4756 BEGIN
4757 SELECT 'Y' INTO is_valid_idc_type
4758 FROM dual
4759 WHERE EXISTS ( SELECT 1 FROM PO_LOOKUP_CODES
4760 WHERE LOOKUP_TYPE = 'PO_FEDERAL_IDC_TYPES'
4761 AND lookup_code = p_clm_idc_type_tbl(i)
4762 );
4763 EXCEPTION WHEN No_Data_Found
4764 THEN
4765 x_results.add_result(
4766 p_entity_type => c_ENTITY_TYPE_LINE
4767 , p_entity_id => p_intf_line_id_tbl(i)
4768 , p_column_name => c_CLM_IDC_TYPE
4769 , p_message_name => 'PO_IDC_TYPE_INVALID'
4770 );
4771 l_results_count := l_results_count + 1;
4772 END;
4773 END IF;
4774 END LOOP;
4775
4776 IF l_results_count > 0 THEN
4777 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4778 ELSE
4779 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4780 END IF;
4781
4782 IF PO_LOG.d_proc THEN
4783 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4784 END IF;
4785
4786 EXCEPTION
4787 WHEN OTHERS THEN
4788 IF PO_LOG.d_exc THEN
4789 PO_LOG.exc(d_mod,0,NULL);
4790 END IF;
4791 RAISE;
4792
4793 END validate_clm_idc_type;
4794
4795
4796 PROCEDURE validate_clin_line_num_disp(
4797 p_intf_line_id_tbl IN PO_TBL_NUMBER
4798 , p_line_num_disp_tbl IN PO_TBL_VARCHAR100
4799 , p_group_line_id_tbl IN PO_TBL_NUMBER
4800 , p_intf_header_id_tbl IN PO_TBL_NUMBER
4801 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4802 , x_result_type OUT NOCOPY VARCHAR2
4803 ) IS
4804
4805 d_mod CONSTANT VARCHAR2(100) := D_validate_clin_line_num_disp;
4806 l_results_count NUMBER := 0;
4807 l_line_count NUMBER := 0;
4808 is_valid_clin_line_num_disp VARCHAR2(1) := NULL;
4809
4810 BEGIN
4811
4812 -- skip the validation for non-CLM documents
4813 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
4814 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4815 RETURN;
4816 END IF;
4817
4818 IF PO_LOG.d_proc THEN
4819 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4820 --PO_LOG.proc_begin(d_mod,'p_clm_option_indicator_tbl',p_clm_idc_type_tbl);
4821 END IF;
4822
4823 IF (x_results IS NULL) THEN
4824 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4825 END IF;
4826
4827 FOR i IN 1..p_intf_line_id_tbl.Count
4828 LOOP
4829 IF (p_group_line_id_tbl(i) IS NULL AND (LENGTH(p_line_num_disp_tbl(i)) <> 4 OR
4830 TO_NUMBER(p_line_num_disp_tbl(i)) > 9999 ))
4831 THEN
4832 x_results.add_result(
4833 p_entity_type => c_ENTITY_TYPE_LINE
4834 , p_entity_id => p_intf_line_id_tbl(i)
4835 , p_column_name => c_LINE_NUM_DISP
4836 , p_message_name => 'PO_CLM_LINE_TYPE_INVALID'
4837 );
4838 l_results_count := l_results_count + 1;
4839
4840 ELSE
4841
4842 FOR j IN 1 .. p_intf_line_id_tbl.Count LOOP
4843 IF (p_group_line_id_tbl(i) IS NULL AND p_group_line_id_tbl(j) IS NULL
4844 AND i <> j AND p_line_num_disp_tbl(i) = p_line_num_disp_tbl(j)
4845 AND p_intf_header_id_tbl(i) = p_intf_header_id_tbl(j)) THEN
4846 x_results.add_result(
4847 p_entity_type => c_ENTITY_TYPE_LINE
4848 , p_entity_id => p_intf_line_id_tbl(i)
4849 , p_column_name => c_LINE_NUM_DISP
4850 , p_message_name => 'PO_DUPLICATE_CLIN_NUM'
4851 );
4852 l_results_count := l_results_count + 1;
4853 EXIT;
4854 END IF;
4855 END LOOP;
4856 END IF;
4857
4858 END LOOP;
4859
4860 IF l_results_count > 0 THEN
4861 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4862 ELSE
4863 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4864 END IF;
4865
4866 IF PO_LOG.d_proc THEN
4867 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4868 END IF;
4869
4870 EXCEPTION
4871 WHEN OTHERS THEN
4872 IF PO_LOG.d_exc THEN
4873 PO_LOG.exc(d_mod,0,NULL);
4874 END IF;
4875 RAISE;
4876
4877 END validate_clin_line_num_disp;
4878
4879
4880 PROCEDURE idv_reference_equal_check(
4881 p_intf_line_id_tbl IN PO_TBL_NUMBER
4882 , p_from_header_id_tbl IN PO_TBL_NUMBER
4883 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4884 , x_result_type OUT NOCOPY VARCHAR2
4885 ) IS
4886
4887 d_mod CONSTANT VARCHAR2(100) := D_idv_reference_equal_check;
4888 l_results_count NUMBER := 0;
4889 l_line_count NUMBER := 0;
4890 l_intf_line_id_tbl PO_TBL_NUMBER;
4891 l_data_key NUMBER;
4892 BEGIN
4893
4894 IF PO_LOG.d_proc THEN
4895 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4896 --PO_LOG.proc_begin(d_mod,'p_clm_option_indicator_tbl',p_clm_idc_type_tbl);
4897 END IF;
4898
4899 IF (x_results IS NULL) THEN
4900 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4901 END IF;
4902
4903 l_data_key := PO_CORE_S.get_session_gt_nextval();
4904
4905 FORALL i IN 1..p_intf_line_id_tbl.Count
4906 INSERT INTO PO_SESSION_GT(
4907 KEY,
4908 num1)
4909 SELECT
4910 l_data_key,
4911 p_intf_line_id_tbl(i)
4912 FROM po_headers_draft_all phd, po_headers_interface phi, po_lines_interface pli
4913 WHERE phd.po_header_id = phi.po_header_id
4914 AND phd.type_lookup_code = 'STANDARD'
4915 AND phi.interface_header_id = PLI.interface_header_id
4916 AND PLI.interface_line_id = p_intf_line_id_tbl(i)
4917 AND Nvl(phd.clm_source_document_id,-1) <> Nvl(p_from_header_id_tbl(i),-1);
4918
4919 DELETE FROM po_session_gt
4920 WHERE key = l_data_key
4921 RETURNING num1 BULK COLLECT INTO l_intf_line_id_tbl;
4922
4923 FOR i IN 1..l_intf_line_id_tbl.Count
4924 LOOP
4925 x_results.add_result(
4926 p_entity_type => c_ENTITY_TYPE_LINE
4927 , p_entity_id => l_intf_line_id_tbl(i)
4928 , p_column_name => c_FROM_HEADER_ID
4929 , p_message_name => 'PO_IDV_REF_NOT_EQUAL'
4930 );
4931 END LOOP;
4932
4933 IF l_intf_line_id_tbl.count > 0 THEN
4934 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4935 ELSE
4936 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4937 END IF;
4938 IF PO_LOG.d_proc THEN
4939 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
4940 END IF;
4941
4942 EXCEPTION
4943 WHEN OTHERS THEN
4944 IF PO_LOG.d_exc THEN
4945 PO_LOG.exc(d_mod,0,NULL);
4946 END IF;
4947 RAISE;
4948
4949 END idv_reference_equal_check;
4950
4951
4952
4953
4954 PROCEDURE validate_clm_line_type_id(
4955 p_intf_line_id_tbl IN PO_TBL_NUMBER
4956 , p_line_type_id_tbl IN PO_TBL_NUMBER
4957 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
4958 , x_result_type OUT NOCOPY VARCHAR2
4959 ) IS
4960
4961 d_mod CONSTANT VARCHAR2(100) := D_validate_clm_line_type_id;
4962 l_results_count NUMBER := 0;
4963 l_line_count NUMBER := 0;
4964 l_intf_line_id_tbl PO_TBL_NUMBER;
4965 l_data_key NUMBER;
4966 BEGIN
4967 IF PO_LOG.d_proc THEN
4968 PO_LOG.proc_begin(d_mod,'p_intf_line_id_tbl',p_intf_line_id_tbl);
4969 --PO_LOG.proc_begin(d_mod,'p_clm_option_indicator_tbl',p_clm_idc_type_tbl);
4970 END IF;
4971
4972 IF (x_results IS NULL) THEN
4973 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4974 END IF;
4975
4976 l_data_key := PO_CORE_S.get_session_gt_nextval();
4977
4978 FORALL i IN 1..p_intf_line_id_tbl.Count
4979 INSERT INTO PO_SESSION_GT(
4980 KEY,
4981 num1)
4982 SELECT
4983 l_data_key,
4984 p_intf_line_id_tbl(i)
4985 FROM po_line_types
4986 WHERE p_line_type_id_tbl(i) IS NOT NULL
4987 AND line_type_id = p_line_type_id_tbl(i)
4988 AND order_type_lookup_code = 'RATE'
4989 AND purchase_basis = 'TEMP LABOR';
4990
4991 DELETE FROM po_session_gt
4992 WHERE key = l_data_key
4993 RETURNING num1 BULK COLLECT INTO l_intf_line_id_tbl;
4994
4995 FOR i IN 1..l_intf_line_id_tbl.Count
4996 LOOP
4997 x_results.add_result(
4998 p_entity_type => c_ENTITY_TYPE_LINE
4999 , p_entity_id => l_intf_line_id_tbl(i)
5000 , p_column_name => c_LINE_TYPE_ID
5001 , p_message_name => 'PO_CLM_LINE_TYPE_INVALID'
5002 );
5003 END LOOP;
5004
5005 IF l_intf_line_id_tbl.count > 0 THEN
5006 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
5007 ELSE
5008 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
5009 END IF;
5010 IF PO_LOG.d_proc THEN
5011 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
5012 END IF;
5013
5014 EXCEPTION
5015 WHEN OTHERS THEN
5016 IF PO_LOG.d_exc THEN
5017 PO_LOG.exc(d_mod,0,NULL);
5018 END IF;
5019 RAISE;
5020
5021 END validate_clm_line_type_id;
5022
5023 --CLM PDOI Integration Ends
5024
5025 --<PAR PROJECT>
5026 -----------------------------------------------------------------------
5027 --Start of Comments
5028 --Name: validate_comments_not_null
5029 --Pre-reqs: None
5030 --Modifies:
5031 --Locks:
5032 -- None
5033 --Function:
5034 -- validates that comments is not null
5035 --Parameters:
5036 --IN:
5037 --p_line_id_tbl :-line's po_line_id
5038 --p_draft_id_tbl: draft Id tbl
5039 --p_draft_type_tbl: draft_type of the document
5040 -- p_comments_tbl: comments column
5041 --IN OUT: x_results
5042 -- OUT: x_result_type
5043 --Notes:
5044 --Testing:
5045 --End of Comments
5046 ------------------------------------------------------------------------
5047 PROCEDURE validate_comments_not_null(
5048 p_line_id_tbl IN PO_TBL_NUMBER
5049 , p_draft_id_tbl IN PO_TBL_NUMBER
5050 , p_draft_type_tbl IN PO_TBL_VARCHAR30
5051 , p_comments_tbl IN PO_TBL_VARCHAR2000
5052 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
5053 , x_result_type OUT NOCOPY VARCHAR2
5054 )
5055 IS
5056 d_mod CONSTANT VARCHAR2(100) := D_validate_comments_not_null;
5057 l_draft_type VARCHAR2(10);
5058
5059 BEGIN
5060
5061 IF PO_LOG.d_proc THEN
5062 PO_LOG.proc_begin(d_mod);
5063 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
5064 PO_LOG.proc_begin(d_mod,'p_draft_id_tbl',p_draft_id_tbl);
5065 PO_LOG.proc_begin(d_mod,'p_draft_type_tbl',p_draft_type_tbl);
5066 PO_LOG.proc_begin(d_mod,'p_comments_tbl',p_comments_tbl);
5067 END IF;
5068
5069 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
5070
5071 -- Return if the document is base document
5072 FOR i IN 1..p_draft_id_tbl.Count() LOOP
5073 IF p_draft_id_tbl(i) = -1 THEN
5074 RETURN;
5075 END IF;
5076 END LOOP;
5077
5078 -- Return if the document is not PAR
5079 IF p_draft_type_tbl IS NULL THEN RETURN; END IF;
5080
5081 FOR i IN 1..p_draft_type_tbl.Count() LOOP
5082 IF p_draft_type_tbl(i) <>'PAR' THEN
5083 RETURN;
5084 END IF;
5085 END LOOP;
5086
5087 PO_VALIDATION_HELPER.not_null(
5088 p_calling_module => D_validate_comments_not_null
5089 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_comments_tbl)
5090 , p_entity_id_tbl => p_line_id_tbl
5091 , p_entity_type => c_entity_type_LINE
5092 , p_column_name => c_COMMENTS
5093 , p_message_name => PO_MESSAGE_S.PO_PAR_COMMENTS_NOT_NULL
5094 , x_results => x_results
5095 , x_result_type => x_result_type
5096 );
5097
5098 IF PO_LOG.d_proc THEN
5099 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
5100 END IF;
5101 END validate_comments_not_null;
5102
5103
5104
5105 -------------------------------------------------------------------------------
5106 -- This procedure validates:
5107 -- 1. Exhibit name is a valid name from the available lookups.
5108 -- 2. Exhibit should not have been used for CDRL for this document.
5109 -------------------------------------------------------------------------------
5110 PROCEDURE validate_exhibit_name(
5111 p_header_id_tbl IN PO_TBL_NUMBER,
5112 p_draft_id_tbl IN PO_TBL_NUMBER,
5113 p_line_id_tbl IN PO_TBL_NUMBER,
5114 p_exhibit_name_tbl IN PO_TBL_VARCHAR20,
5115 x_result_set_id IN OUT NOCOPY NUMBER,
5116 x_result_type OUT NOCOPY VARCHAR2
5117 )
5118 IS
5119
5120 l_data_key NUMBER;
5121 d_mod CONSTANT VARCHAR2(100) := D_clin_slin_num_check;
5122 BEGIN
5123 --<conc mod project>
5124 -- skip the validation for non-CLM documents
5125 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
5126 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
5127 RETURN;
5128 END IF;
5129
5130 IF PO_LOG.d_proc THEN
5131 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
5132 PO_LOG.proc_begin(d_mod,'p_draft_id_tbl',p_draft_id_tbl);
5133 PO_LOG.proc_begin(d_mod,'p_header_id_tbl',p_header_id_tbl);
5134 END IF;
5135
5136 IF (x_result_set_id IS NULL) THEN
5137 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
5138 END IF;
5139
5140 FORALL i IN 1 .. p_line_id_tbl.COUNT
5141 INSERT INTO PO_VALIDATION_RESULTS_GT
5142 ( result_set_id
5143 , entity_type
5144 , entity_id
5145 , column_name
5146 , column_val
5147 , message_name
5148 )
5149 SELECT
5150 x_result_set_id
5151 , c_ENTITY_TYPE_LINE
5152 , p_line_id_tbl(i)
5153 , c_CLM_EXHIBIT_NAME
5154 , TO_CHAR(p_exhibit_name_tbl(i))
5155 , PO_MESSAGE_S.PO_INVALID_EXHIBIT_NAME
5156
5157 FROM PO_LINES_MERGE_V POL
5158 WHERE NOT EXISTS (SELECT 1
5159 FROM FND_LOOKUP_VALUES LK
5160 WHERE LOOKUP_TYPE = 'PO_CLM_EXHIBIT_NUMBER'
5161 AND ENABLED_FLAG = 'Y'
5162 AND LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE
5163 AND START_DATE_ACTIVE <= SYSDATE
5164 AND (END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE >=SYSDATE )
5165 AND NOT EXISTS ( SELECT 1 FROM PO_EXHIBIT_DETAILS_MERGE_V POE
5166 WHERE NVL(POE.IS_CDRL,'N') = 'Y'
5167 AND POE.PO_HEADER_ID = p_header_id_tbl(i)
5168 AND POE.DRAFT_ID = p_draft_id_tbl(i)
5169 AND LK.LOOKUP_CODE = POE.EXHIBIT_NAME)
5170 AND LK.LOOKUP_CODE = p_exhibit_name_tbl(i)
5171 )
5172 AND p_exhibit_name_tbl(i) IS NOT NULL
5173 AND POL.PO_LINE_ID =p_line_id_tbl(i)
5174 AND POL.DRAFT_ID =p_draft_id_tbl(i) ;
5175
5176 IF (SQL%ROWCOUNT > 0 OR x_result_type = PO_VALIDATIONS.c_result_type_FAILURE)
5177 THEN
5178 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
5179 ELSE
5180 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
5181 END IF;
5182
5183 IF PO_LOG.d_proc THEN
5184 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
5185 END IF;
5186
5187 END validate_exhibit_name;
5188 -- bug 16461105
5189 PROCEDURE validate_undef_amt_le_line_amt(
5190 p_po_line_id IN PO_TBL_NUMBER,
5191 p_order_type_lookup_code IN PO_TBL_VARCHAR30,
5192 p_unit_price IN PO_TBL_NUMBER,
5193 p_quantity IN PO_TBL_NUMBER,
5194 p_old_quantity IN PO_TBL_NUMBER,
5195 p_old_unit_price IN PO_TBL_NUMBER,
5196 p_draft_id IN PO_TBL_NUMBER,
5197 p_undef_amount IN PO_TBL_NUMBER,
5198 p_po_line_uca_id IN PO_TBL_NUMBER,
5199 p_ucas_po_line_id IN PO_TBL_NUMBER,
5200 x_results IN OUT nocopy PO_VALIDATION_RESULTS_TYPE,
5201 x_result_type OUT nocopy VARCHAR2)
5202
5203 IS
5204 d_mod CONSTANT VARCHAR2(100) := D_mod_undef_amt_le_line_amt;
5205 l_undef_amt_tbl po_tbl_number;
5206 l_extended_price_tbl po_tbl_number;
5207 l_po_line_uca_id_tbl po_tbl_number;
5208 l_po_draft_id_tbl po_tbl_number; --bug 16385613
5209 l_results_count NUMBER; -- bug 16759650
5210 BEGIN
5211
5212 IF PO_LOG.d_proc THEN
5213 PO_LOG.proc_begin(d_mod,'p_po_line_uca_id',p_po_line_uca_id);
5214 END IF;
5215
5216 IF (x_results IS NULL) THEN
5217 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
5218 END IF;
5219
5220 l_results_count := x_results.result_type.COUNT; -- bug 16759650
5221
5222 -- SQL QUERY to formulate , PO_LINE_UCA_IDS, UNDEF_AMOUNTS,
5223 -- Extended price for quantity based lines for validation
5224 -- of undef amount less than or equal to line amount.
5225
5226
5227 SELECT undef_amt,
5228 greatest(changed_ext_price,0) changed_ext_price,
5229 po_line_uca_id,
5230 draft_id
5231 bulk collect INTO l_undef_amt_tbl, l_extended_price_tbl, l_po_line_uca_id_tbl,
5232 l_po_draft_id_tbl
5233 FROM (SELECT undef.undef_amt,
5234 uca_ids.po_line_uca_id,
5235 po_line_ids.po_line_id
5236 FROM (SELECT ROWNUM rn,
5237 column_value undef_amt
5238 FROM TABLE(p_undef_amount)) undef,
5239 (SELECT ROWNUM rn,
5240 column_value po_line_uca_id
5241 FROM TABLE(p_po_line_uca_id)) uca_ids,
5242 (SELECT ROWNUM rn,
5243 column_value po_line_id
5244 FROM TABLE(p_ucas_po_line_id)) po_line_ids
5245 WHERE uca_ids.rn = undef.rn
5246 AND po_line_ids.rn = undef.rn) ucas,
5247 (SELECT po_line_ids.po_line_id,
5248 otlc.order_type_lookup_code,
5249 podraftid.draft_id,
5250 ( CASE
5251 WHEN podraftid.draft_id = -1 THEN Nvl((
5252 qtys.quantity * ups.unit_price ), 0)
5253 ELSE Nvl(( qtys.quantity * ups.unit_price ), 0) - Nvl((
5254 oqtys.old_quantity * oups.old_unit_price ), 0)
5255 END ) changed_ext_price
5256 FROM (SELECT ROWNUM rn,
5257 column_value po_line_id
5258 FROM TABLE(p_po_line_id)) po_line_ids,
5259 (SELECT ROWNUM rn,
5260 column_value order_type_lookup_code
5261 FROM TABLE(p_order_type_lookup_code)) otlc,
5262 (SELECT ROWNUM rn,
5263 column_value unit_price
5264 FROM TABLE(p_unit_price)) ups,
5265 (SELECT ROWNUM rn,
5266 column_value quantity
5267 FROM TABLE(p_quantity)) qtys,
5268 (SELECT ROWNUM rn,
5269 column_value old_quantity
5270 FROM TABLE(p_old_quantity)) oqtys,
5271 (SELECT ROWNUM rn,
5272 column_value old_unit_price
5273 FROM TABLE(p_old_unit_price)) oups,
5274 (SELECT ROWNUM rn,
5275 column_value draft_id
5276 FROM TABLE(p_draft_id)) podraftid
5277 WHERE otlc.rn = po_line_ids.rn
5278 AND qtys.rn = otlc.rn
5279 AND oqtys.rn = otlc.rn
5280 AND oups.rn = otlc.rn
5281 AND ups.rn = otlc.rn
5282 AND podraftid.rn = otlc.rn)polines
5283 WHERE ucas.po_line_id = polines.po_line_id
5284 AND order_type_lookup_code = 'QUANTITY';
5285
5286 -- NOW CALLING VALIDATION API TO COMPARE THE NUMBERS
5287 --Bug 16385613 Begin
5288 IF l_po_line_uca_id_tbl.Count > 0 THEN
5289 IF l_po_draft_id_tbl(1) = -1 THEN -- IF BASE DOC
5290 PO_VALIDATION_HELPER.num1_less_or_equal_num2(
5291 p_calling_module => D_mod_undef_amt_le_line_amt
5292 , p_num1_tbl => l_undef_amt_tbl
5293 , p_num2_tbl => l_extended_price_tbl
5294 , p_entity_id_tbl => l_po_line_uca_id_tbl
5295 , p_entity_type => PO_VALIDATIONS.c_entity_type_LINE_UCAS
5296 , p_column_name => 'UNDEF_AMOUNT'
5297 , p_message_name => PO_MESSAGE_S.PO_UCA_UNDEF_LE_LINE_AMT
5298 , x_results => x_results
5299 , x_result_type => x_result_type);
5300
5301 ELSE --IF MOD DOC
5302 PO_VALIDATION_HELPER.num1_less_or_equal_num2(
5303 p_calling_module => D_mod_undef_amt_le_line_amt
5304 , p_num1_tbl => l_undef_amt_tbl
5305 , p_num2_tbl => l_extended_price_tbl
5306 , p_entity_id_tbl => l_po_line_uca_id_tbl
5307 , p_entity_type => PO_VALIDATIONS.c_entity_type_LINE_UCAS
5308 , p_column_name => 'UNDEF_AMOUNT'
5309 , p_message_name => 'PO_UCA_UNDEF_MOD_LE_LINE_AMT'
5310 , x_results => x_results
5311 , x_result_type => x_result_type);
5312 END IF;
5313 END IF;
5314
5315 IF (l_results_count < x_results.result_type.COUNT) THEN
5316 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
5317 ELSE
5318 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
5319 END IF;
5320
5321 IF PO_LOG.d_proc THEN
5322 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
5323 END IF;
5324
5325 EXCEPTION WHEN OTHERS THEN
5326 IF PO_LOG.d_exc THEN
5327 PO_LOG.exc(d_mod,0,NULL);
5328 END IF;
5329 RAISE;
5330
5331 END validate_undef_amt_le_line_amt;
5332 -- bug 16461105
5333 end;
5334