[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
d_last_updated_by CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'LAST_UPDATED_BY');
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_header,
p_id_tbl(i),
DECODE(p_po_header_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_PO_HDR_ID_UNIQUE'),
'PO_HEADER_ID',
p_po_header_id_tbl(i),
'COLUMN_NAME',
'PO_HEADER_ID',
'VALUE',
p_po_header_id_tbl(i),
DECODE(p_po_header_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_header_id_not_null,
PO_VAL_CONSTANTS.c_po_header_id_unique)
FROM DUAL
WHERE p_id_tbl(i) IS NULL OR EXISTS(SELECT 1
FROM po_headers_all poh
WHERE p_po_header_id_tbl(i) = poh.po_header_id);
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_header,
p_id_tbl(i),
'PO_PDOI_DOC_NUM_UNIQUE',
'DOCUMENT_NUM',
p_document_num_tbl(i),
'VALUE',
p_document_num_tbl(i),
PO_VAL_CONSTANTS.c_document_num_unique
FROM DUAL
WHERE p_document_num_tbl(i) IS NOT NULL AND
EXISTS(SELECT 1
FROM po_headers
WHERE segment1 = p_document_num_tbl(i)
AND ((p_type_lookup_code_tbl(i) IN ('BLANKET', 'STANDARD')
AND
type_lookup_code IN ('BLANKET', 'CONTRACT',
'PLANNED', 'STANDARD'))
OR
(p_type_lookup_code_tbl(i) = 'QUOTATION' AND
type_lookup_code = p_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,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
DECODE(p_currency_code_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_CURRENCY'),
'CURRENCY_CODE',
p_currency_code_tbl(i),
'COLUMN_NAME',
'CURRENCY_CODE',
'VALUE',
p_currency_code_tbl(i),
DECODE(p_currency_code_tbl(i), NULL, PO_VAL_CONSTANTS.c_currency_code_not_null,
PO_VAL_CONSTANTS.c_currency_code_valid)
FROM DUAL
WHERE p_currency_code_tbl(i) IS NULL
OR NOT EXISTS(
SELECT 1
FROM fnd_currencies cur
WHERE p_currency_code_tbl(i) = cur.currency_code
AND cur.enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(cur.start_date_active, SYSDATE - 1)
AND NVL(cur.end_date_active, 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,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
'PO_PDOI_INVALID_RATE_TYPE',
'RATE_TYPE',
p_rate_type_tbl(i),
'VALUE',
p_rate_type_tbl(i),
PO_VAL_CONSTANTS.c_rate_type_valid
FROM DUAL
WHERE p_func_currency_code <> NVL(p_currency_code_tbl(i), ' ')
AND p_rate_type_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM gl_daily_conversion_types_v dct
WHERE p_rate_type_tbl(i) = dct.conversion_type);
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_header,
p_id_tbl(i),
DECODE(p_agent_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_BUYER'),
'AGENT_ID',
p_agent_id_tbl(i),
'COLUMN_NAME',
'AGENT_ID',
'VALUE',
p_agent_id_tbl(i),
DECODE(p_agent_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_agent_id_not_null,
PO_VAL_CONSTANTS.c_agent_id_valid)
FROM DUAL
WHERE p_agent_id_tbl(i) IS NULL
OR NOT EXISTS(
SELECT 1
FROM po_agents poa
WHERE p_agent_id_tbl(i) = poa.agent_id
AND SYSDATE BETWEEN NVL(poa.start_date_active, SYSDATE - 1)
AND NVL(poa.end_date_active, 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,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
DECODE(p_vendor_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_VENDOR'),
'VENDOR_ID',
p_vendor_id_tbl(i),
'COLUMN_NAME',
'VENDOR_ID',
'VALUE',
p_vendor_id_tbl(i),
DECODE(p_vendor_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_not_null,
PO_VAL_CONSTANTS.c_vendor_valid)
FROM DUAL
WHERE p_vendor_id_tbl(i) IS NULL
OR NOT EXISTS(SELECT 1
FROM po_suppliers_val_v psv
WHERE p_vendor_id_tbl(i) = psv.vendor_id);
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_header,
p_id_tbl(i),
DECODE(p_vendor_site_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_VENDOR_SITE'),
'VENDOR_SITE_ID',
p_vendor_site_id_tbl(i),
'COLUMN_NAME',
'VENDOR_SITE_ID',
'VALUE',
p_vendor_site_id_tbl(i),
DECODE(p_vendor_site_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_site_not_null,
PO_VAL_CONSTANTS.c_vendor_site_valid)
FROM DUAL
WHERE p_vendor_id_tbl(i) IS NOT NULL
AND ( p_vendor_site_id_tbl(i) IS NULL
OR NOT EXISTS(SELECT 1
FROM po_supplier_sites_val_v pssv
WHERE p_vendor_id_tbl(i) = pssv.vendor_id
AND p_vendor_site_id_tbl(i) = pssv.vendor_site_id));
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_header,
p_id_tbl(i),
'PO_PDOI_INVALID_VDR_CNTCT',
'VENDOR_CONTACT_ID',
p_vendor_contact_id_tbl(i),
'VALUE',
p_vendor_contact_id_tbl(i),
PO_VAL_CONSTANTS.c_vendor_contact_valid
FROM DUAL
WHERE p_vendor_id_tbl(i) IS NOT NULL
AND p_vendor_site_id_tbl(i) IS NOT NULL
AND p_vendor_contact_id_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM po_vendor_contacts pvc
WHERE p_vendor_site_id_tbl(i) = pvc.vendor_site_id
AND p_vendor_contact_id_tbl(i) = pvc.vendor_contact_id);
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_header,
p_id_tbl(i),
DECODE(p_ship_to_location_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_SHIP_LOC_ID'),
'SHIP_TO_LOCATION_ID',
p_ship_to_location_id_tbl(i),
'COLUMN_NAME',
'SHIP_TO_LOCATION_ID',
'VALUE',
p_ship_to_location_id_tbl(i),
DECODE(p_ship_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_ship_to_location_id_not_null,
PO_VAL_CONSTANTS.c_ship_to_location_id_valid)
FROM DUAL
WHERE (p_ship_to_location_id_tbl(i) IS NULL
-- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
AND p_type_lookup_code_tbl(i) <> 'QUOTATION')
OR (p_ship_to_location_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM hr_locations hrl
WHERE hrl.ship_to_site_flag = 'Y'
AND p_ship_to_location_id_tbl(i) = hrl.location_id
AND SYSDATE < NVL(hrl.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,
token2_name,
token2_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
DECODE(p_bill_to_location_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_BILL_LOC_ID'),
'BILL_TO_LOCATION_ID',
p_bill_to_location_id_tbl(i),
'COLUMN_NAME',
'BILL_TO_LOCATION_ID',
'VALUE',
p_bill_to_location_id_tbl(i),
DECODE(p_bill_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_bill_to_location_id_not_null,
PO_VAL_CONSTANTS.c_bill_to_location_id_valid)
FROM DUAL
WHERE (p_bill_to_location_id_tbl(i) IS NULL
-- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
AND p_type_lookup_code_tbl(i) <> 'QUOTATION')
OR (p_bill_to_location_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM hr_locations hrl
WHERE hrl.bill_to_site_flag = 'Y'
AND p_bill_to_location_id_tbl(i) = hrl.location_id
AND SYSDATE < NVL(hrl.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,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
'PO_PDOI_INVALID_FREIGHT_CARR',
'SHIP_VIA_LOOKUP_CODE',
p_ship_via_lookup_code_tbl(i),
'VALUE',
p_ship_via_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_ship_via_lookup_code
FROM DUAL
WHERE p_ship_via_lookup_code_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM org_freight ofr
WHERE p_ship_via_lookup_code_tbl(i) = ofr.freight_code
AND NVL(ofr.disable_date, SYSDATE + 1) > SYSDATE
AND ofr.organization_id = p_inventory_org_id);
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_header,
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_fob_lookup_code
FROM DUAL
WHERE p_fob_lookup_code_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_lookup_codes plc
WHERE p_fob_lookup_code_tbl(i) = plc.lookup_code
AND plc.lookup_type = 'FOB'
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,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
'PO_PDOI_INVALID_FREIGHT_TERMS',
'FREIGHT_TERMS_LOOKUP_CODE',
p_freight_terms_lookup_tbl(i),
'VALUE',
p_freight_terms_lookup_tbl(i),
PO_VAL_CONSTANTS.c_freight_terms_lookup_code
FROM DUAL
WHERE p_freight_terms_lookup_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_lookup_codes plc
WHERE p_freight_terms_lookup_tbl(i) = plc.lookup_code
AND plc.lookup_type = 'FREIGHT TERMS'
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,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
'PO_PDOI_INVALID_SHIPPING_CTRL',
'SHIPPING_CONTROL',
p_shipping_control_tbl(i),
'VALUE',
p_shipping_control_tbl(i),
PO_VAL_CONSTANTS.c_shipping_control
FROM DUAL
WHERE p_shipping_control_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_lookup_codes plc
WHERE p_shipping_control_tbl(i) = plc.lookup_code
AND plc.lookup_type = 'SHIPPING CONTROL'
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,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
p_id_tbl(i),
'PO_PDOI_INVALID_STYLE_ID',
'STYLE_ID',
p_style_id_tbl(i),
'STYLE_ID',
p_style_id_tbl(i),
PO_VAL_CONSTANTS.c_style_id_valid
FROM DUAL
WHERE NOT EXISTS(SELECT 1
FROM po_doc_style_headers pdsh
WHERE pdsh.style_id = p_style_id_tbl(i) AND
pdsh.status = 'ACTIVE');
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_header,
p_id_tbl(i),
'PO_PDOI_COMPLEX_WORK_STYLE',
'STYLE_ID',
p_style_id_tbl(i),
'STYLE_ID',
p_style_id_tbl(i),
PO_VAL_CONSTANTS.c_style_id_complex_work
FROM DUAL
WHERE EXISTS(SELECT 1
FROM po_doc_style_headers pdsh
WHERE pdsh.style_id = p_style_id_tbl(i) AND
pdsh.progress_payment_flag = 'Y');