[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
d_ip_category_id_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'IP_CATEGORY_ID_UPDATE');
d_uom_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'UOM_UPDATE');
d_item_desc_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ITEM_DESC_UPDATE');
d_negotiated_by_prep_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'NEGOTIATED_BY_PREPARER_UPDATE');
d_category_id_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CATEGORY_ID_UPDATE');
d_unit_price_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'UNIT_PRICE_UPDATE');
d_amount_update CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT_UPDATE');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_OVER_TOL_ERROR',
'OVER_TOLERANCE_ERROR_FLAG',
p_over_tolerance_err_flag_tbl(i),
'OVER_TOLERANCE_ERROR_FLAG',
p_over_tolerance_err_flag_tbl(i),
PO_VAL_CONSTANTS.c_over_tolerance_error_flag
FROM DUAL
WHERE p_over_tolerance_err_flag_tbl(i) IS NOT NULL AND
NOT EXISTS(
SELECT 1
FROM po_lookup_codes plc
WHERE plc.lookup_type = 'RECEIVING CONTROL LEVEL'
AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
AND plc.lookup_code = p_over_tolerance_err_flag_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_GA_OSP_NA',
PO_VAL_CONSTANTS.c_ga_flag_op
FROM DUAL
WHERE p_line_type_id_tbl(i) IS NOT NULL
AND EXISTS(
SELECT 1
FROM po_line_types_b plt
WHERE p_line_type_id_tbl(i) = plt.line_type_id
AND NVL(p_global_agreement_flag_tbl(i), 'N') = 'Y'
AND NVL(plt.outside_operation_flag, 'N') = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_INVALID_JOB',
'JOB_ID',
p_job_id_tbl(i),
'JOB_ID',
p_job_id_tbl(i),
'JOB_BG_ID',
p_job_business_group_id_tbl(i),
PO_VAL_CONSTANTS.c_job_id_valid
FROM DUAL
WHERE p_purchase_basis_tbl(i) = 'TEMP LABOR'
AND NOT EXISTS(
SELECT 1
FROM per_jobs_vl pj, financials_system_parameters fsp
WHERE pj.job_id = p_job_id_tbl(i)
AND pj.business_group_id = fsp.business_group_id
AND fsp.business_group_id = NVL(p_job_business_group_id_tbl(i),
fsp.business_group_id)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from),
TRUNC(SYSDATE))
AND NVL(TRUNC(pj.date_to),
TRUNC(SYSDATE)));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_INVALID_JOB',
'JOB_ID',
p_job_id_tbl(i),
'JOB_ID',
p_job_id_tbl(i),
'JOB_BG_ID',
p_job_business_group_id_tbl(i),
PO_VAL_CONSTANTS.c_job_id_valid
FROM DUAL
WHERE p_job_business_group_id_tbl(i) IS NOT NULL
AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
AND NOT EXISTS(
SELECT 1
FROM per_jobs_vl pj, per_business_groups_perf pbg
WHERE pj.job_id = p_job_id_tbl(i)
AND pj.business_group_id = p_job_business_group_id_tbl(i)
AND pj.business_group_id = pbg.business_group_id
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from),
TRUNC(SYSDATE))
AND NVL(TRUNC(pj.date_to),
TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pbg.date_from),
TRUNC(SYSDATE))
AND NVL(TRUNC(pbg.date_to),
TRUNC(SYSDATE)));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_INVALID_JOB_CAT',
'JOB_ID',
p_job_id_tbl(i),
PO_VAL_CONSTANTS.c_job_id_valid_cat
FROM DUAL
WHERE p_purchase_basis_tbl(i) = 'TEMP LABOR'
AND p_category_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_job_associations_b pja, per_jobs_vl pj
WHERE pja.job_id = p_job_id_tbl(i)
AND pja.category_id = p_category_id_tbl(i)
AND pja.job_id = pj.job_id
AND NVL(TRUNC(pja.inactive_date), TRUNC(sysdate)) >= TRUNC(sysdate)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from), TRUNC(SYSDATE))
AND NVL(TRUNC(pj.date_to), TRUNC(SYSDATE)));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_CANNOT_CROSS_BG',
'JOB_BUSINESS_GROUP_ID',
p_job_business_group_id_tbl(i),
PO_VAL_CONSTANTS.c_job_bg_id_not_cross_bg
FROM DUAL
WHERE p_job_business_group_id_tbl(i) IS NOT NULL
AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
AND NOT EXISTS(SELECT 1
FROM financials_system_parameters fsp
WHERE fsp.business_group_id = p_job_business_group_id_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_INVALID_BG',
'JOB_BUSINESS_GROUP_ID',
p_job_business_group_id_tbl(i),
PO_VAL_CONSTANTS.c_job_business_group_id_valid
FROM DUAL
WHERE p_job_business_group_id_tbl(i) IS NOT NULL
AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
AND NOT EXISTS(
SELECT 1
FROM per_business_groups_perf pbg
WHERE pbg.business_group_id = p_job_business_group_id_tbl(i)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pbg.date_from), TRUNC(SYSDATE))
AND NVL(TRUNC(pbg.date_to), TRUNC(SYSDATE)));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_UN_NUMBER_ID',
'UN_NUMBER_ID',
p_un_number_id_tbl(i),
'VALUE',
p_un_number_id_tbl(i),
PO_VAL_CONSTANTS.c_un_number_id_valid
FROM DUAL
WHERE p_un_number_id_tbl(i) IS NOT NULL
AND p_purchase_basis_tbl(i) <> 'TEMP LABOR'
AND NOT EXISTS(SELECT 1
FROM po_un_numbers_val_v pun
WHERE pun.un_number_id = p_un_number_id_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_HAZ_ID',
'HAZARD_CLASS_ID',
p_hazard_class_id_tbl(i),
'VALUE',
p_hazard_class_id_tbl(i),
PO_VAL_CONSTANTS.c_hazard_class_id_valid
FROM DUAL
WHERE p_hazard_class_id_tbl(i) IS NOT NULL
AND p_purchase_basis_tbl(i) <> 'TEMP LABOR'
AND NOT EXISTS(SELECT 'Y'
FROM po_hazard_classes_val_v phc
WHERE phc.hazard_class_id = p_hazard_class_id_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_ITEM_NOT_NULL',
'ITEM_ID',
p_item_id_tbl(i),
PO_VAL_CONSTANTS.c_item_id_not_null
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) = 'QUANTITY'
AND p_item_id_tbl(i) IS NULL
AND EXISTS(
SELECT 1
FROM po_line_types_b plt
WHERE p_line_type_id_tbl(i) IS NOT NULL
AND p_line_type_id_tbl(i) = plt.line_type_id
AND plt.outside_operation_flag = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
DECODE(plt.outside_operation_flag, 'N', 'PO_PDOI_INVALID_ITEM_ID', 'PO_PDOI_INVALID_OP_ITEM_ID'),
'ITEM_ID',
p_item_id_tbl(i),
'VALUE',
p_item_id_tbl(i),
DECODE(plt.outside_operation_flag, 'N', PO_VAL_CONSTANTS.c_item_id_valid,
PO_VAL_CONSTANTS.c_item_id_op_valid)
FROM po_line_types_b plt
WHERE p_item_id_tbl(i) IS NOT NULL
AND p_line_type_id_tbl(i) IS NOT NULL
AND p_line_type_id_tbl(i) = plt.line_type_id
AND plt.outside_operation_flag IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_inventory_org_id
AND msi.enabled_flag = 'Y'
AND msi.purchasing_item_flag = 'Y'
AND msi.purchasing_enabled_flag = 'Y'
AND msi.outside_operation_flag = plt.outside_operation_flag
AND TRUNC(NVL(msi.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(msi.end_date_active, SYSDATE)) >= TRUNC(SYSDATE));
p_create_or_update_item IN VARCHAR2,
p_inventory_org_id IN NUMBER,
x_result_set_id IN OUT NOCOPY NUMBER,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_item_description;
po_log.proc_begin(d_mod, 'p_create_or_update_item', p_create_or_update_item);
/* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
x_result_type := po_validations.c_result_type_success;
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
DECODE(p_item_description_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_DIFF_ITEM_DESC'),
'ITEM_DESCRIPTION',
p_item_description_tbl(i),
DECODE(p_item_description_tbl(i), NULL, 'COLUMN_NAME', NULL),
DECODE(p_item_description_tbl(i), NULL, 'ITEM_DESCRIPTION', NULL),
DECODE(p_item_description_tbl(i), NULL, PO_VAL_CONSTANTS.c_item_desc_not_null,
PO_VAL_CONSTANTS.c_item_desc_not_updatable)
FROM DUAL
WHERE p_item_description_tbl(i) IS NULL
OR ( p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
AND p_item_id_tbl(i) IS NOT NULL
AND EXISTS(
SELECT 1 FROM
mtl_system_items msi,mtl_system_items_tl mtl
where msi.inventory_item_id = p_item_id_tbl(i) AND
mtl.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_inventory_org_id
AND mtl.organization_id = msi.organization_id
AND msi.allow_item_desc_update_flag = 'N'
and mtl.language = USERENV('LANG')
AND p_item_description_tbl(i) <> mtl.description
AND p_create_or_update_item = 'N'));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val, -- bug5252804
token1_name,
token2_name,
token3_name,
token1_value,
token2_value,
token3_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_ITEM_RELATED_INFO',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i), -- bug5252804
'COLUMN_NAME',
'VALUE',
'ITEM',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
p_item_id_tbl(i),
PO_VAL_CONSTANTS.c_unit_meas_lookup_item
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
AND p_item_id_tbl(i) IS NOT NULL
AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM mtl_item_uoms_view miuv
WHERE miuv.inventory_item_id = p_item_id_tbl(i)
AND miuv.organization_id = p_inventory_org_id
AND miuv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_UOM_CODE',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
'VALUE',
p_unit_meas_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_unit_meas_lookup_valid
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
AND p_item_id_tbl(i) IS NULL
AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM po_units_of_measure_val_v pumv
WHERE pumv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token2_name,
token3_name,
token1_value,
token2_value,
token3_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_LINE_TYPE_INFO',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
'COLUMN_NAME',
'VALUE',
'LINE_TYPE',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
pltb.unit_of_measure,
PO_VAL_CONSTANTS.c_unit_meas_lookup_line_type
FROM PO_LINE_TYPES_B pltb
WHERE pltb.line_type_id = p_line_type_id_tbl(i)
AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
AND p_unit_meas_lookup_code_tbl(i) <> pltb.unit_of_measure;
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token2_name,
token1_value,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_INVALID_UOM',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
'COLUMN_NAME',
'VALUE',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_unit_meas_lookup_svc_valid
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) = 'RATE'
AND NOT EXISTS(SELECT 1
FROM mtl_units_of_measure_vl muomv
WHERE muomv.uom_class = l_service_uom_class
AND muomv.unit_of_measure = p_unit_meas_lookup_code_tbl(i)
AND TRUNC(sysdate) < NVL(muomv.disable_date, TRUNC(sysdate) + 1));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token2_name,
token3_name,
token1_value,
token2_value,
token3_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_ITEM_RELATED_INFO',
'ITEM_REVISION',
p_item_revision_tbl(i),
'COLUMN_NAME',
'VALUE',
'ITEM',
'item_revision',
p_item_revision_tbl(i),
p_item_id_tbl(i),
PO_VAL_CONSTANTS.c_item_revision_item
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
AND p_item_revision_tbl(i) IS NOT NULL
AND p_item_id_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM mtl_item_revisions mir
WHERE mir.inventory_item_id = p_item_id_tbl(i)
AND mir.revision = p_item_revision_tbl(i));
SELECT validate_flag,
category_set_id
INTO x_flag,
x_category_set_id
FROM mtl_category_sets_v
WHERE category_set_id = (SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2); /*** purchasing***/
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
token3_name,
token3_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_ITEM_RELATED_INFO',
'CATEGORY_ID',
p_category_id_tbl(i),
'COLUMN_NAME',
'CATEGORY_ID',
'VALUE',
p_category_id_tbl(i),
'ITEM',
p_item_id_tbl(i),
PO_VAL_CONSTANTS.c_category_id_item
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
AND p_item_id_tbl(i) IS NOT NULL
AND p_category_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM mtl_item_categories mic, mtl_categories mcs
WHERE mic.category_id = mcs.category_id
AND mic.category_set_id = x_category_set_id
AND mic.category_id = p_category_id_tbl(i)
AND mic.inventory_item_id = p_item_id_tbl(i)
AND mic.organization_id = p_inventory_org_id
AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
AND mcs.enabled_flag = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_CATEGORY_ID',
'CATEGORY_ID',
p_category_id_tbl(i),
'VALUE',
p_category_id_tbl(i),
PO_VAL_CONSTANTS.c_category_id_valid
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
AND p_item_id_tbl(i) IS NULL
AND p_category_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 'Y'
FROM mtl_categories_vl mcs, mtl_category_set_valid_cats mcsvc
WHERE mcs.category_id = p_category_id_tbl(i)
AND mcs.category_id = mcsvc.category_id
AND mcsvc.category_set_id = x_category_set_id
AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
AND mcs.enabled_flag = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_CATEGORY_ID',
'CATEGORY_ID',
p_category_id_tbl(i),
'VALUE',
p_category_id_tbl(i),
PO_VAL_CONSTANTS.c_category_id_valid
FROM DUAL
WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
AND p_item_id_tbl(i) IS NULL
AND p_category_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM mtl_categories_vl mcs
WHERE mcs.category_id = p_category_id_tbl(i)
AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
AND mcs.enabled_flag = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_IP_CATEGORY_ID',
'IP_CATEGORY_ID',
p_ip_category_id_tbl(i),
'VALUE',
p_ip_category_id_tbl(i),
PO_VAL_CONSTANTS.c_ip_category_id_valid
FROM DUAL
WHERE p_ip_category_id_tbl(i) IS NOT NULL
AND p_ip_category_id_tbl(i) <> -2
AND NOT EXISTS(
SELECT 'Y'
FROM icx_cat_categories_v
WHERE rt_category_id = p_ip_category_id_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token2_name,
token1_value,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_LINE_TYPE_INFO',
'UNIT_PRICE',
p_unit_price_tbl(i),
'COLUMN_NAME',
'VALUE',
'UNIT_PRICE',
p_unit_price_tbl(i),
PO_VAL_CONSTANTS.c_unit_price_line_type
FROM DUAL
WHERE p_line_type_id_tbl(i) IS NOT NULL
AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
AND NOT EXISTS(SELECT 1
FROM po_line_types_b plt
WHERE p_line_type_id_tbl(i) = plt.line_type_id
AND p_unit_price_tbl(i) = plt.unit_price);
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_LINE_NUM_UNIQUE',
'LINE_NUM',
p_line_num_tbl(i),
'VALUE',
p_line_num_tbl(i),
PO_VAL_CONSTANTS.c_line_num_unique
FROM DUAL
WHERE p_po_header_id_tbl(i) IS NOT NULL
AND p_line_num_tbl(i) IS NOT NULL
AND (EXISTS(SELECT 'Y'
FROM po_lines_all pln
WHERE pln.po_header_id = p_po_header_id_tbl(i)
AND pln.line_num = p_line_num_tbl(i)
AND NOT EXISTS (SELECT 'Y'
FROM po_lines_draft_all PLD
WHERE PLN.po_line_id = PLD.po_line_id
AND PLD.draft_id = p_draft_id_tbl(i)))
OR
EXISTS (SELECT 'Y'
FROM po_lines_draft_all PLD
WHERE PLD.draft_id = p_draft_id_tbl(i)
AND PLD.po_header_id = p_po_header_id_tbl(i)
AND PLD.line_num = p_line_num_tbl(i)
AND NVL(PLD.delete_flag, 'N') <> 'Y'));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
DECODE(p_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_LINE_ID_UNIQUE'),
'PO_LINE_ID',
p_po_line_id_tbl(i),
'COLUMN',
'PO_LINE_ID',
'VALUE',
p_po_line_id_tbl(i),
DECODE(p_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_line_id_not_null,
PO_VAL_CONSTANTS.c_po_line_id_unique)
FROM DUAL
WHERE p_po_line_id_tbl(i) IS NULL
OR ( p_po_header_id_tbl(i) IS NOT NULL
AND EXISTS(SELECT 1
FROM po_lines pln
WHERE pln.po_header_id = p_po_header_id_tbl(i)
AND pln.po_line_id = p_po_line_id_tbl(i)));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
DECODE(p_line_type_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_LINE_TYPE_ID'),
'LINE_TYPE_ID',
p_line_type_id_tbl(i),
'COLUMN_NAME',
'LINE_TYPE_ID',
'VALUE',
p_line_type_id_tbl(i),
DECODE(p_line_type_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_line_type_id_not_null,
PO_VAL_CONSTANTS.c_line_type_id_valid)
FROM DUAL
WHERE p_line_type_id_tbl(i) IS NULL OR
NOT EXISTS(SELECT 1
FROM po_line_types_val_v pltv
WHERE pltv.line_type_id = p_line_type_id_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_LINE_TYPE_ID_STYLE',
'LINE_TYPE_ID',
p_line_type_id_tbl(i),
'STYLE_ID',
p_style_id_tbl(i),
'LINE_TYPE_ID',
p_line_type_id_tbl(i),
PO_VAL_CONSTANTS.c_line_style_on_line_type
FROM po_doc_style_headers pdsh
WHERE p_style_id_tbl(i) IS NOT NULL AND
pdsh.style_id = p_style_id_tbl(i) AND
pdsh.line_type_allowed = 'SPECIFIED' AND
NOT EXISTS(SELECT 1
FROM po_doc_style_values pdv
WHERE pdv.style_id = pdsh.style_id
AND pdv.style_attribute_name = 'LINE_TYPES'
AND pdv.style_allowed_value = to_char(p_line_type_id_tbl(i))
AND nvl(pdv.enabled_flag, 'N') = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_PURCHASE_BASIS_STYLE',
'PURCHASE_BASIS',
p_purchase_basis_tbl(i),
'STYLE_ID',
p_style_id_tbl(i),
'PURCHASE_BASIS',
p_purchase_basis_tbl(i),
PO_VAL_CONSTANTS.c_line_style_on_purchase_basis
FROM DUAL
WHERE NOT EXISTS(SELECT 1
FROM po_doc_style_values pdsv
WHERE pdsv.style_id = p_style_id_tbl(i)
AND pdsv.style_attribute_name = 'PURCHASE_BASES'
AND pdsv.style_allowed_value = p_purchase_basis_tbl(i)
AND nvl(pdsv.enabled_flag, 'N') = 'Y');
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_PRICE_TYPE',
'PRICE_TYPE_LOOKUP_CODE',
p_price_type_lookup_code_tbl(i),
'VALUE',
p_price_type_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_price_type_lookup_code
FROM DUAL
WHERE p_price_type_lookup_code_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 'Y'
FROM po_lookup_codes plc
WHERE plc.lookup_type = 'PRICE TYPE'
AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
AND plc.lookup_code = p_price_type_lookup_code_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_ATO_ITEM_NA',
'ITEM_ID',
p_item_id_tbl(i),
'ITEM_ID',
p_item_id_tbl(i)
FROM DUAL
WHERE p_item_id_tbl(i) IS NOT NULL
AND p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
AND EXISTS(
SELECT 1
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_inventory_org_id
AND msi.bom_item_type IN(1, 2));
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_PRICE_BREAK',
'PRICE_BREAK_LOOKUP_CODE',
p_price_break_lookup_code_tbl(i),
'VALUE',
p_price_break_lookup_code_tbl(i)
FROM DUAL
WHERE p_price_break_lookup_code_tbl(i) IS NOT NULL
AND p_price_break_lookup_code_tbl(i) NOT IN
( SELECT lookup_code
FROM po_lookup_codes PLC
WHERE PLC.lookup_type = 'PRICE BREAK TYPE');
PROCEDURE ip_category_id_update(
p_id_tbl IN po_tbl_number,
p_ip_category_id_tbl IN po_tbl_number,
x_result_set_id IN OUT NOCOPY NUMBER,
x_results IN OUT NOCOPY po_validation_results_type,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_ip_category_id_update;
p_validation_id => PO_VAL_CONSTANTS.c_ip_cat_id_update_not_null);
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_IP_CATEGORY_ID',
'IP_CATEGORY_ID',
p_ip_category_id_tbl(i),
'VALUE',
p_ip_category_id_tbl(i),
PO_VAL_CONSTANTS.c_ip_cat_id_update_valid
FROM DUAL
WHERE p_ip_category_id_tbl(i) IS NOT NULL
AND p_ip_category_id_tbl(i) <> -2
AND NOT EXISTS(
SELECT 'Y'
FROM icx_cat_categories_v
WHERE rt_category_id = p_ip_category_id_tbl(i));
END ip_category_id_update;
PROCEDURE uom_update(
p_id_tbl IN po_tbl_number,
p_unit_meas_lookup_code_tbl IN po_tbl_varchar30,
p_order_type_lookup_code_tbl IN po_tbl_varchar30,
p_po_header_id_tbl IN po_tbl_number,
p_po_line_id_tbl IN po_tbl_number,
x_results IN OUT NOCOPY po_validation_results_type, x_result_set_id IN OUT NOCOPY NUMBER,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_uom_update;
p_validation_id => PO_VAL_CONSTANTS.c_uom_update_not_null);
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_UOM_CODE',
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
'UNIT_MEAS_LOOKUP_CODE',
p_unit_meas_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_uom_update_valid
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM po_lines_all pol,
po_units_of_measure_val_v pumv
WHERE po_header_id = p_po_header_id_tbl(i) AND
po_line_id = p_po_line_id_tbl(i) AND
p_unit_meas_lookup_code_tbl(i) IS NOT NULL AND
p_unit_meas_lookup_code_tbl(i) <> NVL(pol.unit_meas_lookup_code,
p_unit_meas_lookup_code_tbl(i)) AND
pumv.unit_of_measure <> p_unit_meas_lookup_code_tbl(i))
OR EXISTS(
SELECT 1
FROM po_lines_all pol,
po_units_of_measure_val_v pumv
WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE') AND
po_header_id = p_po_header_id_tbl(i) AND
po_line_id = p_po_line_id_tbl(i) AND
pol.unit_meas_lookup_code IS NULL AND
pumv.unit_of_measure <> p_unit_meas_lookup_code_tbl(i));
END uom_update;
PROCEDURE item_desc_update(
p_id_tbl IN po_tbl_number,
p_item_description_tbl IN po_tbl_varchar2000,
p_item_id_tbl IN po_tbl_number,
p_inventory_org_id IN NUMBER,
p_po_header_id_tbl IN po_tbl_number,
p_po_line_id_tbl IN po_tbl_number,
x_results IN OUT NOCOPY po_validation_results_type, x_result_set_id IN OUT NOCOPY NUMBER,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_item_desc_update;
p_validation_id => PO_VAL_CONSTANTS.c_item_desc_update_not_null);
/* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
FORALL i IN 1 .. p_id_tbl.COUNT
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_DIFF_ITEM_DESC',
'ITEM_DESCRIPTION',
p_item_description_tbl(i),
'ITEM_DESCRIPTION',
p_item_description_tbl(i),
PO_VAL_CONSTANTS.c_item_desc_update_unupdatable
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_system_items msi,
po_lines_all pol,
mtl_system_items_tl mtl
WHERE p_po_line_id_tbl(i) IS NOT NULL AND
p_item_id_tbl(i) IS NOT NULL AND
pol.po_header_id = nvl(p_po_header_id_tbl(i),pol.po_header_id) AND
pol.po_line_id = p_po_line_id_tbl(i) AND
msi.inventory_item_id = p_item_id_tbl(i) AND
msi.inventory_item_id = mtl.inventory_item_id AND
msi.organization_id = p_inventory_org_id AND
msi.organization_id = mtl.organization_id AND
msi.allow_item_desc_update_flag = 'N' AND
mtl.language = USERENV('LANG') AND
(p_item_description_tbl(i) <> mtl.description OR
p_item_description_tbl(i) <> pol.item_description))
OR EXISTS(
SELECT 1
FROM mtl_system_items msi,
mtl_system_items_tl mtl
WHERE p_po_line_id_tbl(i) IS NULL AND
p_item_id_tbl(i) IS NOT NULL AND
msi.inventory_item_id = p_item_id_tbl(i) AND
mtl.inventory_item_id = msi.inventory_item_id AND
msi.organization_id = p_inventory_org_id AND
mtl.organization_id = msi.organization_id AND
msi.allow_item_desc_update_flag = 'N' AND
mtl.language = USERENV('LANG') AND
p_item_description_tbl(i) <> mtl.description);
END item_desc_update;
PROCEDURE negotiated_by_prep_update(
p_id_tbl IN po_tbl_number,
p_negotiated_by_preparer_tbl IN po_tbl_varchar1,
x_results IN OUT NOCOPY po_validation_results_type,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_negotiated_by_prep_update;
p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_not_null);
p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_valid);
END negotiated_by_prep_update;
PROCEDURE category_id_update(
p_id_tbl IN po_tbl_number,
p_category_id_tbl IN po_tbl_number,
p_po_line_id_tbl IN po_tbl_number,
p_order_type_lookup_code_tbl IN po_tbl_varchar30,
p_item_id_tbl IN po_tbl_number,
p_job_id_tbl IN po_tbl_number,
p_inventory_org_id IN NUMBER,
x_result_set_id IN OUT NOCOPY NUMBER,
x_results IN OUT NOCOPY po_validation_results_type,
x_result_type OUT NOCOPY VARCHAR2)
IS
d_mod CONSTANT VARCHAR2(100) := d_category_id_update;
p_validation_id => PO_VAL_CONSTANTS.c_cat_id_update_not_null);
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_NO_PO_CAT_UPDATE',
'CATEGORY_ID',
p_category_id_tbl(i),
PO_VAL_CONSTANTS.c_cat_id_update_not_updatable
FROM DUAL
WHERE p_category_id_tbl(i) IS NOT NULL
AND (p_item_id_tbl(i) IS NOT NULL OR p_job_id_tbl(i) IS NOT NULL)
AND (EXISTS(SELECT 1
FROM po_lines_all pol
WHERE p_po_line_id_tbl(i) = pol.po_line_id
AND p_category_id_tbl(i) <> pol.category_id)
OR EXISTS(SELECT 1
FROM po_lines_draft_all pld
WHERE p_po_line_id_tbl(i) = pld.po_line_id
AND p_category_id_tbl(i) <> pld.category_id));
END category_id_update;
PROCEDURE unit_price_update
( p_id_tbl IN po_tbl_number,
p_po_line_id_tbl IN po_tbl_number, -- bug5008206
p_draft_id_tbl IN po_tbl_number,
p_unit_price_tbl IN po_tbl_number,
x_results IN OUT NOCOPY po_validation_results_type,
x_result_set_id IN OUT NOCOPY NUMBER, -- bug5008206
x_result_type OUT NOCOPY VARCHAR2
)
IS
d_mod CONSTANT VARCHAR2(100) := d_unit_price_update;
p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_not_null);
p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_ge_zero);
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_NO_PRICE',
'UNIT_PRICE',
p_unit_price_tbl(i),
PO_VAL_CONSTANTS.c_unit_price_null
FROM po_lines_all POL
WHERE POL.po_line_id = p_po_line_id_tbl(i)
AND POL.order_type_lookup_code = 'FIXED PRICE'
AND p_unit_price_tbl(i) IS NOT NULL
-- missin draft id
UNION
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_NO_PRICE',
'UNIT_PRICE',
p_unit_price_tbl(i),
PO_VAL_CONSTANTS.c_unit_price_null
FROM po_lines_draft_all POL
WHERE POL.po_line_id = p_po_line_id_tbl(i)
AND POL.draft_id = p_draft_id_tbl(i)
AND POL.order_type_lookup_code = 'FIXED PRICE'
AND p_unit_price_tbl(i) IS NOT NULL;
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
token1_name,
token1_value,
token2_name,
token2_value,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_INVALID_PRICE',
'VALUE',
POL.not_to_exceed_price,
'UNIT_PRICE',
p_unit_price_tbl(i),
'UNIT_PRICE',
p_unit_price_tbl(i),
PO_VAL_CONSTANTS.c_not_to_exceed_price_valid
FROM po_lines_all POL
WHERE POL.po_line_id = p_po_line_id_tbl(i)
AND POL.not_to_exceed_price < p_unit_price_tbl(i);
END unit_price_update;
PROCEDURE amount_update
( p_id_tbl IN po_tbl_number,
p_po_line_id_tbl IN po_tbl_number, -- bug5008206
p_draft_id_tbl IN po_tbl_number,
p_amount_tbl IN po_tbl_number,
x_results IN OUT NOCOPY po_validation_results_type,
x_result_set_id IN OUT NOCOPY NUMBER, -- bug5008206
x_result_type OUT NOCOPY VARCHAR2
)
IS
d_mod CONSTANT VARCHAR2(100) := d_amount_update;
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
entity_id,
message_name,
column_name,
column_val,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_BLKT_NO_AMT',
'AMOUNT',
p_amount_tbl(i),
PO_VAL_CONSTANTS.c_amount_blanket
FROM po_lines_all POL
WHERE POL.po_line_id = p_po_line_id_tbl(i)
AND POL.order_type_lookup_code <> 'FIXED PRICE'
AND p_amount_tbl(i) IS NOT NULL
-- missin draft id
UNION
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_line,
p_id_tbl(i),
'PO_PDOI_SVC_BLKT_NO_AMT',
'AMOUNT',
p_amount_tbl(i),
PO_VAL_CONSTANTS.c_amount_blanket
FROM po_lines_draft_all POL
WHERE POL.po_line_id = p_po_line_id_tbl(i)
AND POL.draft_id = p_draft_id_tbl(i)
AND POL.order_type_lookup_code <> 'FIXED PRICE'
AND p_amount_tbl(i) IS NOT NULL;
END amount_update;