[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
'PO_LINE_LOCATIONS_DRAFT_ALL',
p_id_tbl(i),
'PO_PDOI_SHIPMENT_NUM_UNIQUE',
'SHIPMENT_NUM',
p_shipment_num_tbl(i),
'VALUE',
p_shipment_num_tbl(i),
PO_VAL_CONSTANTS.c_shipment_num_unique
FROM DUAL
WHERE p_shipment_num_tbl(i) IS NOT NULL AND
p_po_header_id_tbl(i) IS NOT NULL AND
p_po_line_id_tbl(i) IS NOT NULL AND
p_shipment_type_tbl(i) IS NOT NULL AND
(EXISTS(SELECT 1
FROM po_line_locations_all
WHERE po_header_id = p_po_header_id_tbl(i)
AND po_line_id = p_po_line_id_tbl(i)
AND shipment_num = p_shipment_num_tbl(i)
AND p_doc_type = 'BLANKET' -- bug 4642348
AND shipment_type = 'PRICE BREAK') -- Bug#16501849
OR EXISTS(SELECT 1
FROM po_line_locations_draft_all
WHERE po_header_id = p_po_header_id_tbl(i)
AND po_line_id = p_po_line_id_tbl(i)
AND draft_id = p_draft_id_tbl(i) -- bug 4642348
AND shipment_num = p_shipment_num_tbl(i)
AND NVL(delete_flag, 'N') = 'N')); -- bug 4642348
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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_SHIP_TO_ORG_ID',
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i),
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i),
PO_VAL_CONSTANTS.c_ship_to_organization_id
FROM DUAL
WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
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)
AND mir.organization_id = p_ship_to_organization_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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_SHIP_TO_ORG_ID',
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i),
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i),
PO_VAL_CONSTANTS.c_ship_to_organization_id
FROM DUAL
WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
AND p_item_revision_tbl(i) IS NULL
AND p_item_id_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM mtl_system_items msi --Bug7513119
WHERE msi.inventory_item_id = p_item_id_tbl(i)
AND msi.organization_id = p_ship_to_organization_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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_SHIP_TO_ORG_ID',
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i),
'SHIP_TO_ORGANIZATION_ID',
p_ship_to_organization_id_tbl(i),
PO_VAL_CONSTANTS.c_ship_to_organization_id
FROM DUAL
WHERE p_ship_to_organization_id_tbl(i) IS NOT NULL
AND p_item_id_tbl(i) IS NULL
AND NOT EXISTS(
SELECT 1
FROM org_organization_definitions ood
WHERE ood.organization_id = p_ship_to_organization_id_tbl(i)
AND SYSDATE < NVL(ood.disable_date, SYSDATE + 1)
AND ood.inventory_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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_SHIP_TO_LOC_ID',
'SHIP_TO_LOCATION_ID',
p_ship_to_location_id_tbl(i),
'SHIP_TO_LOCATION_ID',
p_ship_to_location_id_tbl(i),
PO_VAL_CONSTANTS.c_loc_ship_to_loc_id_valid
FROM DUAL
WHERE p_ship_to_location_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_locations_val_v PLV
WHERE PLV.location_id = p_ship_to_location_id_tbl(i)
AND ship_to_site_flag = 'Y'
AND ( PLV.inventory_organization_id IS NULL
OR PLV.inventory_organization_id = p_ship_to_organization_id_tbl(i)
OR p_ship_to_organization_id_tbl(i) IS 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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_RCV_EXCEP_CD',
'QTY_RCV_EXCEPTION_CODE',
p_qty_rcv_exception_code_tbl(i),
'QTY_RCV_EXCEPTION_CODE',
p_qty_rcv_exception_code_tbl(i),
PO_VAL_CONSTANTS.c_qty_ecv_exception_code
FROM DUAL
WHERE p_qty_rcv_exception_code_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_lookup_codes plc
WHERE p_qty_rcv_exception_code_tbl(i) = plc.lookup_code
AND plc.lookup_type = 'RECEIVING CONTROL LEVEL'
AND SYSDATE < NVL(plc.inactive_date, 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,
token1_value)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
'PO_LINE_LOCATIONS_DRAFT_ALL',
p_id_tbl(i),
'PO_PDOI_INVALID_ROUTING_ID',
'RECEIVING_ROUTING_ID',
p_receiving_routing_id_tbl(i),
'RECEIVING_ROUTING_ID',
p_receiving_routing_id_tbl(i)
FROM DUAL
WHERE p_receiving_routing_id_tbl(i) IS NOT NULL
AND p_shipment_type_tbl(i) = 'STANDARD'
AND NOT EXISTS(SELECT 1
FROM rcv_routing_headers rrh
WHERE rrh.routing_header_id = p_receiving_routing_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_location,
p_id_tbl(i),
'PO_PDOI_PRICE_BREAK_STYLE',
'STYLE_ID',
p_style_id_tbl(i),
'STYLE_ID',
p_style_id_tbl(i),
PO_VAL_CONSTANTS.c_loc_style_related_info
FROM DUAL
WHERE EXISTS(SELECT 1
FROM po_doc_style_headers pdsh
WHERE pdsh.style_id = p_style_id_tbl(i) AND
NVL(pdsh.price_breaks_flag, 'N') = 'N');
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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_TAX_NAME',
'TAX_NAME',
p_tax_name_tbl(i),
'VALUE',
p_tax_name_tbl(i),
PO_VAL_CONSTANTS.c_tax_name
FROM DUAL
WHERE p_tax_name_tbl(i) IS NOT NULL
AND p_tax_code_id_tbl(i) IS NULL
AND NOT EXISTS(SELECT 'Y'
FROM ZX_INPUT_CLASSIFICATIONS_V zicv
WHERE zicv.lookup_code = p_tax_name_tbl(i)
AND zicv.org_id in (p_operating_unit, -99)
AND zicv.enabled_flag = 'Y'
AND NVL(p_need_by_date_tbl(i),SYSDATE) BETWEEN
NVL(zicv.start_date_active, SYSDATE) AND
COALESCE(zicv.end_date_active,
p_need_by_date_tbl(i),
SYSDATE)
AND p_allow_tax_code_override = '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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_TAX_NAME',
'TAX_NAME',
p_tax_name_tbl(i),
'VALUE',
p_tax_name_tbl(i),
PO_VAL_CONSTANTS.c_tax_name
FROM DUAL
WHERE p_tax_code_id_tbl(i) IS NOT NULL
AND p_tax_name_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 'Y'
FROM ZX_ID_TCC_MAPPING
WHERE tax_rate_code_id = p_tax_code_id_tbl(i)
AND tax_classification_code = p_tax_name_tbl(i)
AND NVL(p_need_by_date_tbl(i),SYSDATE) BETWEEN
NVL(effective_from, SYSDATE) AND
COALESCE(effective_to,
p_need_by_date_tbl(i),
SYSDATE)
AND tax_class = 'INPUT'
AND org_id IN (p_operating_unit, -99)
AND source = 'AP'
AND active_flag = 'Y'
AND p_allow_tax_code_override = '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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_FOB',
'FOB_LOOKUP_CODE',
p_fob_lookup_code_tbl(i),
'VALUE',
p_fob_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_loc_fob_lookup_code
FROM DUAL
WHERE p_fob_lookup_code_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM PO_LOOKUP_CODES
WHERE lookup_type = 'FOB' AND
sysdate < nvl(inactive_date, sysdate + 1) AND
lookup_code = p_fob_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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_FREIGHT_TERMS',
'FREIGHT_TERMS',
p_freight_terms_tbl(i),
'VALUE',
p_freight_terms_tbl(i),
PO_VAL_CONSTANTS.c_loc_freight_terms
FROM DUAL
WHERE p_freight_terms_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM PO_LOOKUP_CODES
WHERE lookup_type = 'FREIGHT TERMS' AND
sysdate < nvl(inactive_date, sysdate + 1) AND
lookup_code = p_freight_terms_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_location,
p_id_tbl(i),
'PO_PDOI_INVALID_FREIGHT_CARR',
'FREIGHT_CARRIER',
p_freight_carrier_tbl(i),
'VALUE',
p_freight_carrier_tbl(i),
PO_VAL_CONSTANTS.c_loc_freight_carrier
FROM DUAL
WHERE p_freight_carrier_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM ORG_FREIGHT
WHERE freight_code = p_freight_carrier_tbl(i) AND
organization_id = p_inventory_org_id AND
nvl(disable_date, sysdate + 1) > sysdate);