[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT interface_header_id
, org_id
FROM PO_HEADERS_INTERFACE
WHERE batch_id = p_batch_id
AND process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_NEW;
UPDATE PO_HEADERS_INTERFACE
SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE interface_header_id = l_interface_header_ids(i)
AND (l_org_ids(i) IS NULL
OR l_org_ids(i) = g_OU_REQD -- where g_OU_REQD is -2 (iProc defined this value)
OR NOT EXISTS
(SELECT 'ORG_ID exists'
FROM HR_ALL_ORGANIZATION_UNITS HAOU
WHERE HAOU.organization_id = l_org_ids(i)
))
RETURNING interface_header_id, org_id
BULK COLLECT INTO l_err_interface_header_ids, l_err_org_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
UPDATE PO_HEADERS_INTERFACE
SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE interface_header_id = l_interface_header_ids(i)
-- ECO bug 5584556: Skip the validations covered above
AND l_org_ids(i) IS NOT NULL
AND l_org_ids(i) <> g_OU_REQD -- where g_OU_REQD is -2 (iProc defined this value)
AND EXISTS
(SELECT 'ORG_ID exists'
FROM HR_ALL_ORGANIZATION_UNITS HAOU
WHERE HAOU.organization_id = l_org_ids(i)
)
-- ECO bug 5584556: End
AND (
NOT EXISTS
(SELECT 'Valid Operating Unit ID'
FROM HR_OPERATING_UNITS HROU
WHERE HROU.organization_id = l_org_ids(i)
-- Bug 5060582: Dont need the date checks
--AND sysdate BETWEEN
-- nvl(HROU.date_from, sysdate-1)
-- AND nvl(HROU.date_to, sysdate+1)
)
-- ECO bug 5584556: Add new messages
-- This check is done as part of UT'ing this ECO.
-- We found a case where the PSP row does not exist for the org_id.
-- The migration program would die for such cases. The following query
-- is copied from init_sys_parameters() where it was failing.
OR
NOT EXISTS
( SELECT 'Valid OU references in FSP, PSP, SOB and RCV'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
GL_SETS_OF_BOOKS SOB,
PO_SYSTEM_PARAMETERS_ALL PSPA,
RCV_PARAMETERS RCV
WHERE FSPA.set_of_books_id = SOB.set_of_books_id
AND RCV.organization_id (+) = FSPA.inventory_organization_id
AND PSPA.org_id = l_org_ids(i)
AND FSPA.org_id = l_org_ids(i)
)
-- ECO bug 5584556: End
)
RETURNING interface_header_id, org_id
BULK COLLECT INTO l_err_interface_header_ids, l_err_org_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
SELECT name
INTO l_ou_name
FROM HR_ALL_ORGANIZATION_UNITS_TL HAOUTL
WHERE HAOUTL.organization_id = l_err_org_ids(i)
AND language = userenv('LANG');
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
index_char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.currency_code(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
-- ECO bug 5584556: Start
-- The following validation check is already done above.
-- So adding these additional where-clauses to skip those cases.
AND p_headers_rec.currency_code(i) IS NOT NULL
-- ECO bug 5584556: End
AND NOT EXISTS(SELECT 1
FROM FND_CURRENCIES CUR
WHERE p_headers_rec.currency_code(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));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, index_char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_currency_code_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.vendor_id(i)
, p_headers_rec.vendor_name(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND (p_headers_rec.vendor_id(i) IS NULL
OR p_headers_rec.vendor_id(i) = g_NULL_COLUMN_VALUE -- -2
OR NOT EXISTS(SELECT 1
FROM PO_VENDORS PV
WHERE p_headers_rec.vendor_id(i) = PV.vendor_id));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_id_list, l_vendor_name_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.vendor_id(i)
, p_headers_rec.vendor_name(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
-- ECO bug 5584556: Start
-- The following 3 validation checks are already done above.
-- So adding these additional where-clauses to skip those cases.
AND p_headers_rec.vendor_id(i) IS NOT NULL
AND p_headers_rec.vendor_id(i) <> g_NULL_COLUMN_VALUE -- -2
AND EXISTS(SELECT 1
FROM PO_VENDORS PV
WHERE p_headers_rec.vendor_id(i) = PV.vendor_id)
-- ECO bug 5584556: End
AND NOT EXISTS(SELECT 1
FROM PO_SUPPLIERS_VAL_V PSV
WHERE p_headers_rec.vendor_id(i) = PSV.vendor_id);
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_id_list, l_vendor_name_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.vendor_site_id(i)
, p_headers_rec.vendor_site_code(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE')
AND
(
(
-- Handle null Supplier Sites for Create action only
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
AND (p_headers_rec.vendor_site_id(i) IS NULL
OR p_headers_rec.vendor_site_id(i) = g_NULL_COLUMN_VALUE)
)
OR
(
-- Handle invalid Supplier Site id for Create/update action
p_headers_rec.vendor_site_id(i) IS NOT NULL
AND p_headers_rec.vendor_site_id(i) <> g_NULL_COLUMN_VALUE
AND NOT EXISTS( SELECT 1
FROM PO_VENDOR_SITES_ALL PVSA
WHERE p_headers_rec.vendor_site_id(i) = PVSA.vendor_site_id)
)
);
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_vendor_site_id_list, l_vendor_site_code_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.vendor_site_id(i)
, p_headers_rec.vendor_site_code(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE')
-- ECO bug 5584556: Start
-- The following 3 validation checks are already done above.
-- So adding these additional where-clauses to skip those cases.
AND p_headers_rec.vendor_site_id(i) IS NOT NULL
AND p_headers_rec.vendor_site_id(i) <> g_NULL_COLUMN_VALUE
AND EXISTS(SELECT 1
FROM PO_VENDOR_SITES_ALL PVSA
WHERE p_headers_rec.vendor_site_id(i) = PVSA.vendor_site_id)
-- ECO bug 5584556: End
AND NOT EXISTS(SELECT 1
FROM PO_SUPPLIER_SITES_VAL_V PSSV
WHERE p_headers_rec.vendor_site_id(i) = PSSV.vendor_site_id);
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_vendor_site_id_list, l_vendor_site_code_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.vendor_contact_id(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE')
AND p_headers_rec.vendor_id(i) IS NOT NULL
AND p_headers_rec.vendor_site_id(i) IS NOT NULL
AND p_headers_rec.vendor_contact_id(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM PO_VENDOR_CONTACTS PVC
WHERE p_headers_rec.vendor_site_id(i) = PVC.vendor_site_id
AND p_headers_rec.vendor_contact_id(i) = PVC.vendor_contact_id);
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_contact_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE') --AND
--p_headers_rec.has_errors(i) = 'N'
) THEN
l_progress := '180';
SELECT location_code
INTO x_location_name
FROM HR_LOCATIONS_ALL_TL
WHERE location_id = p_location_id
AND language = userenv('LANG');
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.ship_to_location_id(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND (p_headers_rec.ship_to_location_id(i) IS NULL OR
NOT EXISTS(SELECT 1
FROM HR_LOCATIONS_ALL HRLA
WHERE p_headers_rec.ship_to_location_id(i) = HRLA.location_id));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_to_loc_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.ship_to_location_id(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
-- ECO bug 5584556: Start
-- The following validation checks are already done above.
-- So adding these additional where-clauses to skip those cases.
AND p_headers_rec.ship_to_location_id(i) IS NOT NULL
AND EXISTS(SELECT 'ID exists'
FROM HR_LOCATIONS_ALL HRLA
WHERE p_headers_rec.ship_to_location_id(i) = HRLA.location_id)
-- ECO bug 5584556: End
AND NOT EXISTS(SELECT 'Active ship-to-location'
FROM HR_LOCATIONS HRL
WHERE HRL.ship_to_site_flag = 'Y'
AND p_headers_rec.ship_to_location_id(i) = HRL.location_id
AND SYSDATE < NVL(HRL.inactive_date, SYSDATE + 1));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_to_loc_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.bill_to_location_id(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND (p_headers_rec.bill_to_location_id(i) IS NULL OR
NOT EXISTS(SELECT 1
FROM HR_LOCATIONS_ALL HRLA
WHERE p_headers_rec.bill_to_location_id(i) = HRLA.location_id));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_bill_to_loc_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.bill_to_location_id(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
-- ECO bug 5584556: Start
-- The following validation checks are already done above.
-- So adding these additional where-clauses to skip those cases.
AND p_headers_rec.bill_to_location_id(i) IS NOT NULL
AND EXISTS(SELECT 'ID exists'
FROM HR_LOCATIONS_ALL HRLA
WHERE p_headers_rec.bill_to_location_id(i) = HRLA.location_id)
-- ECO bug 5584556: End
AND NOT EXISTS(SELECT 1
FROM HR_LOCATIONS HRL
WHERE HRL.bill_to_site_flag = 'Y'
AND p_headers_rec.bill_to_location_id(i) = HRL.location_id
AND SYSDATE < NVL(HRL.inactive_date, SYSDATE + 1));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_bill_to_loc_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
SELECT displayed_field
INTO x_lookup_meaning
FROM PO_LOOKUP_CODES
WHERE lookup_code = p_po_lookup_code
AND lookup_type = p_po_lookup_type;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.fob(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND p_headers_rec.fob(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM PO_LOOKUP_CODES PLC
WHERE p_headers_rec.fob(i) = PLC.lookup_code
AND PLC.lookup_type = 'FOB'
AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_fob_lookup_code_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.freight_carrier(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND p_headers_rec.freight_carrier(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM ORG_FREIGHT OFR
WHERE p_headers_rec.freight_carrier(i) = OFR.freight_code
AND NVL(OFR.disable_date, SYSDATE + 1) > SYSDATE
AND OFR.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id);
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_via_luc_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.freight_terms(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND p_headers_rec.freight_terms(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM PO_LOOKUP_CODES PLC
WHERE p_headers_rec.freight_terms(i) = PLC.lookup_code
AND PLC.lookup_type = 'FREIGHT TERMS'
AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_freight_terms_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
SELECT name
INTO x_terms_name
FROM AP_TERMS_TL
WHERE term_id = p_terms_id
AND language = userenv('LANG');
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.terms_id(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND p_headers_rec.terms_id(i) IS NOT NULL
AND NOT EXISTS
(SELECT 1
FROM AP_TERMS APT
WHERE p_headers_rec.terms_id(i) = APT.term_id
AND sysdate BETWEEN
nvl(APT.start_date_active, sysdate - 1) AND
nvl(APT.end_date_active, sysdate + 1));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_terms_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.shipping_control(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND p_headers_rec.shipping_control(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM PO_LOOKUP_CODES PLC
WHERE p_headers_rec.shipping_control(i) = PLC.lookup_code
AND PLC.lookup_type = 'SHIPPING CONTROL'
AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_shipping_control_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
char1)
SELECT p_key
, l_subscript_array(i)
, p_headers_rec.interface_header_id(i)
, p_headers_rec.rate_type_code(i)
FROM DUAL
WHERE --p_headers_rec.has_errors(i) = 'N'
p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
AND p_headers_rec.currency_code(i) <> PO_R12_CAT_UPG_PVT.g_sys.currency_code
AND p_headers_rec.rate_type_code(i) IS NOT NULL
AND NOT EXISTS(
SELECT 'Rate type exists'
FROM GL_DAILY_CONVERSION_TYPES_V GLDCT
WHERE GLDCT.conversion_type = p_headers_rec.rate_type_code(i));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_rate_type_code_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
SELECT PO_SESSION_GT_S.nextval
INTO l_key
FROM DUAL;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
num4)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.item_id(i)
FROM PO_LINE_TYPES_B PLT
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND p_lines_rec.item_id(i) is not null
AND p_lines_rec.line_type_id(i) is not null
AND p_lines_rec.line_type_id(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_lines_rec.item_id(i)
AND MSI.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_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));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, num4
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids, l_item_id_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
char1)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.item_description(i)
FROM DUAL
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND (p_lines_rec.item_description(i) is null OR
(p_lines_rec.item_id(i) is not null AND
EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.inventory_item_id = p_lines_rec.item_id(i)
AND MSI.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
AND MSI.allow_item_desc_update_flag = 'N'
AND p_lines_rec.item_description(i) <> MSI.description)));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids, l_item_description_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
(p_lines_rec.action(i) = 'UPDATE' AND
p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
)
AND
p_lines_rec.uom_code(i) IS NULL) THEN
l_progress := '060';
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
p_lines_rec.unit_of_measure(i) IS NULL AND
p_lines_rec.uom_code(i) IS NOT NULL) THEN
l_progress := '030';
SELECT unit_of_measure
INTO p_lines_rec.unit_of_measure(i)
FROM PO_UNITS_OF_MEASURE_VAL_V
WHERE uom_code = p_lines_rec.uom_code(i)
-- ECO bug 5584556: This validation is already done above.
-- So skip these cases.
AND uom_code IS NOT NULL;
(p_lines_rec.action(i) = 'UPDATE' AND
p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
)
AND p_lines_rec.unit_of_measure(i) IS NULL
-- ECO bug 5584556: This validation is already done above.
-- So skip these cases.
AND p_lines_rec.uom_code(i) IS NOT NULL) THEN
-- ECO bug 5584556: End
l_progress := '060';
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
char1,
char2)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.unit_of_measure(i)
, p_lines_rec.uom_code(i)
FROM DUAL
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND p_lines_rec.unit_of_measure(i) is not null
AND p_lines_rec.uom_code(i) is not null
AND ( (p_lines_rec.item_id(i) is not null
AND NOT EXISTS (SELECT 1
FROM MTL_ITEM_UOMS_VIEW MIUV
WHERE MIUV.inventory_item_id = p_lines_rec.item_id(i)
AND MIUV.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
AND MIUV.unit_of_measure = p_lines_rec.unit_of_measure(i)))
OR
(p_lines_rec.item_id(i) is null
AND NOT EXISTS (SELECT 1
FROM MTL_UNITS_OF_MEASURE MUOM
WHERE MUOM.unit_of_measure = p_lines_rec.unit_of_measure(i)
AND sysdate < NVL(disable_date, sysdate+1))));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, char1, char2
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids, l_unit_of_measure_list, l_uom_code_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
p_lines_rec.item_id(i) IS NULL AND
p_lines_rec.item_revision(i) IS NOT NULL) THEN
l_progress := '030';
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
num4,
char1)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.item_id(i)
, p_lines_rec.item_revision(i)
FROM DUAL
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND p_lines_rec.item_revision(i) is not null
AND p_lines_rec.item_id(i) is not null
AND NOT EXISTS (SELECT 1
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.inventory_item_id = p_lines_rec.item_id(i)
AND MIR.revision = p_lines_rec.item_revision(i));
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, num4, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids, l_item_id_list, l_item_revision_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
SELECT concatenated_segments
INTO x_category_name
FROM MTL_CATEGORIES_B_KFV
WHERE category_id = p_category_id;
SELECT category_name
INTO x_ip_category_name
FROM ICX_CAT_CATEGORIES_V
WHERE rt_category_id = p_ip_category_id
AND language = userenv('LANG');
(p_lines_rec.action(i) = 'UPDATE' AND
p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
)
AND
(p_lines_rec.category_id(i) IS NULL
OR p_lines_rec.category_id(i) = g_NULL_COLUMN_VALUE)) -- -2
THEN
l_progress := '030';
SELECT validate_flag, category_set_id
INTO l_validate_flag, l_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_SESSION_GT(key,
num1,
num2,
num3,
num4,
num5,
char1)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.item_id(i)
, p_lines_rec.category_id(i)
, p_lines_rec.category(i) -- category name
FROM DUAL
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND p_lines_rec.item_id(i) is not null
AND p_lines_rec.category_id(i) is not null
AND p_lines_rec.category_id(i) <> g_NULL_COLUMN_VALUE -- -2
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 = l_category_set_id
AND MIC.category_id = p_lines_rec.category_id(i)
AND MIC.inventory_item_id = p_lines_rec.item_id(i)
AND MIC.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
AND sysdate < nvl(MCS.disable_date, sysdate+1)
AND MCS.enabled_flag = 'Y');
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, num4, num5, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids, l_item_id_list,
l_category_id_list, l_category_name_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
num4,
char1)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.category_id(i)
, p_lines_rec.category(i) -- category name
FROM DUAL
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND p_lines_rec.item_id(i) is null
AND p_lines_rec.category_id(i) is not null
AND p_lines_rec.category_id(i) <> g_NULL_COLUMN_VALUE -- -2
AND NOT EXISTS
(SELECT 'Y'
FROM MTL_CATEGORIES_VL MCS,
MTL_CATEGORY_SET_VALID_CATS MCSVC
WHERE MCS.category_id = p_lines_rec.category_id(i)
AND MCS.category_id = MCSVC.category_id
AND MCSVC.category_set_id = l_category_set_id
AND sysdate < nvl(MCS.disable_date, sysdate+1)
AND MCS.enabled_flag = 'Y');
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, num4, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids,
l_category_id_list, l_category_name_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
INSERT INTO PO_SESSION_GT(key,
num1,
num2,
num3,
num4,
char1)
SELECT p_key
, l_subscript_array(i)
, p_lines_rec.interface_header_id(i)
, p_lines_rec.interface_line_id(i)
, p_lines_rec.category_id(i)
, p_lines_rec.category(i) -- category name
FROM DUAL
WHERE --p_lines_rec.has_errors(i) = 'N'
p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
AND p_lines_rec.item_id(i) is null
AND p_lines_rec.category_id(i) is not null
AND p_lines_rec.category_id(i) <> g_NULL_COLUMN_VALUE -- -2
AND NOT EXISTS
(SELECT 1
FROM MTL_CATEGORIES_VL MCS
WHERE MCS.category_id = p_lines_rec.category_id(i)
AND sysdate < nvl(MCS.disable_date, sysdate+1)
AND MCS.enabled_flag = 'Y');
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
DELETE FROM PO_SESSION_GT
WHERE key = p_key
RETURNING num1, num2, num3, num4, char1
BULK COLLECT INTO l_indexes, l_interface_header_ids,
l_interface_line_ids,
l_category_id_list, l_category_name_list;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
(p_lines_rec.action(i) = 'UPDATE' AND
p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
)
AND
(p_lines_rec.unit_price(i) IS NULL OR
p_lines_rec.unit_price(i) < 0)) THEN
l_progress := '030';
SELECT PO_SESSION_GT_S.nextval
INTO l_key
FROM DUAL;
UPDATE PO_LINES_INTERFACE POLI
SET POLI.processing_id = -POLI.processing_id,
POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POLI.action = 'ORIGINAL'
RETURNING POLI.interface_line_id, POLI.interface_header_id
BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
l_err_attr_values_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
AND (
POAI.interface_line_id IS NULL
OR
-- the following are NOT NULL columns in the TXN table, so they must
-- have a not null value in the INTF table as well.
(
--POAI.po_line_id IS NULL OR
POAI.req_template_name IS NULL OR
POAI.req_template_line_num IS NULL OR
POAI.org_id IS NULL OR
POAI.inventory_item_id IS NULL OR
POAI.ip_category_id IS NULL
)
)
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
l_err_attr_values_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
AND (EXISTS
(SELECT 'Invalid relationship with Line Level'
FROM PO_LINES_INTERFACE POLI
WHERE POLI.interface_line_id = POAI.interface_line_id
AND ( -- Attribute Row for GBPA must have appr action at line level
(POAI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE AND
POAI.req_template_line_num = g_NOT_REQUIRED_ID AND
POLI.action NOT IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE', 'DELETE'))
)))
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
l_err_attr_values_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
l_err_attr_values_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
AND (EXISTS
(SELECT 'Invalid relationship with Line Level'
FROM PO_LINES_INTERFACE POLI
WHERE POLI.interface_line_id = POAI.interface_line_id
AND ( -- Attribute Row for ReqTemplate must have action as REQTEMPALTE at line level
((POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE OR
POAI.req_template_line_num <> g_NOT_REQUIRED_ID) AND
POLI.action NOT IN ('REQTEMPLATE'))
)))
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
l_err_attr_values_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
AND (
( -- For ReqTemplates, ALL the 3 columns must be given
(POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
(POAI.req_template_line_num = g_NOT_REQUIRED_ID OR
POAI.org_id = g_NOT_REQUIRED_ID))
OR
(POAI.req_template_line_num <> g_NOT_REQUIRED_ID AND
(POAI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE OR
POAI.org_id = g_NOT_REQUIRED_ID))
)
)
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
l_err_attr_values_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
AND (
(-- For ReqTemplates, the ReqTemplate Line must exist in Txn tables
POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
POAI.req_template_line_num <> g_NOT_REQUIRED_ID AND
POAI.org_id <> g_NOT_REQUIRED_ID AND
NOT EXISTS
(SELECT 'ReqTemplate Line in txn tables'
FROM PO_REQEXPRESS_LINES_ALL PORT
WHERE PORT.express_name = POAI.req_template_name
AND PORT.sequence_num = POAI.req_template_line_num
AND PORT.org_id = POAI.org_id)
)
)
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
l_err_attr_values_ids.DELETE;
l_err_attr_values_tlp_ids.DELETE;
l_err_line_ids.DELETE;
l_err_header_ids.DELETE;
UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
SET POATI.processing_id = -POATI.processing_id,
POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POATI.action = PO_R12_CAT_UPG_PVT.g_action_tlp_create
AND (
POATI.language IS NULL
OR
POATI.interface_line_id IS NULL
OR
-- the following are NOT NULL columns in the TXN table, so they must
-- have a not null value in the INTF table as well.
(
--POATI.po_line_id IS NULL OR
POATI.req_template_name IS NULL OR
POATI.req_template_line_num IS NULL OR
POATI.org_id IS NULL OR
POATI.inventory_item_id IS NULL OR
POATI.ip_category_id IS NULL OR
POATI.description IS NULL
)
OR
(EXISTS
(SELECT 'Invalid relationship with Line Level'
FROM PO_LINES_INTERFACE POLI
WHERE POLI.interface_line_id = POATI.interface_line_id
AND ( -- Attribute Row for GBPA must have appr action at line level
(POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE AND
POATI.req_template_line_num = g_NOT_REQUIRED_ID AND
POLI.action NOT IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE', 'DELETE'))
OR
-- Attribute Row for ReqTemplate must have action as REQTEMPALTE at line level
((POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE OR
POATI.req_template_line_num <> g_NOT_REQUIRED_ID) AND
POLI.action NOT IN ('REQTEMPLATE'))
)
)
)
OR
( -- For ReqTemplates, ALL the 3 columns must be given
(POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
(POATI.req_template_line_num = g_NOT_REQUIRED_ID OR
POATI.org_id = g_NOT_REQUIRED_ID))
OR
(POATI.req_template_line_num <> g_NOT_REQUIRED_ID AND
(POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE OR
POATI.org_id = g_NOT_REQUIRED_ID))
)
OR
(-- For ReqTemplates, the ReqTemplate Line must exist in Txn tables
POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
POATI.req_template_line_num <> g_NOT_REQUIRED_ID AND
POATI.org_id <> g_NOT_REQUIRED_ID AND
NOT EXISTS
(SELECT 'ReqTemplate Line in txn tables'
FROM PO_REQEXPRESS_LINES_ALL PORT
WHERE PORT.express_name = POATI.req_template_name
AND PORT.sequence_num = POATI.req_template_line_num
AND PORT.org_id = POATI.org_id)
)
)
RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
UPDATE PO_HEADERS_INTERFACE POHI
SET POHI.processing_id = -POHI.processing_id,
POHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POHI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POHI.action = 'ADD'
RETURNING POHI.interface_header_id
BULK COLLECT INTO l_err_header_ids;
UPDATE PO_LINES_INTERFACE POLI
SET POLI.processing_id = -POLI.processing_id,
POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POLI.action = PO_R12_CAT_UPG_PVT.g_action_line_create -- ADD
AND (POLI.interface_header_id IS NULL OR
(POLI.po_header_id IS NOT NULL AND
NOT EXISTS
(SELECT 'PO_HEADER_ID points to valid doc'
FROM PO_HEADERS_ALL POH
WHERE POH.po_header_id = POLI.po_header_id)))
RETURNING POLI.interface_line_id, POLI.interface_header_id
BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = 'ORIGINAL'
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
SET POATI.processing_id = -POATI.processing_id,
POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POATI.action = 'ORIGINAL'
RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
PROCEDURE validate_update_action
(
p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_update_action';
UPDATE PO_HEADERS_INTERFACE POHI
SET POHI.processing_id = -POHI.processing_id,
POHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POHI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POHI.action = 'UPDATE'
AND (POHI.po_header_id is null
OR NOT EXISTS
(SELECT 'document exists'
FROM PO_HEADERS_ALL POH
WHERE POH.po_header_id = POHI.po_header_id))
RETURNING POHI.interface_header_id
BULK COLLECT INTO l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Header level='||SQL%rowcount); END IF;
UPDATE PO_LINES_INTERFACE POLI
SET POLI.processing_id = -POLI.processing_id,
POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POLI.action = 'UPDATE'
AND (POLI.po_line_id is null
OR NOT EXISTS
(SELECT 'line exists'
FROM PO_LINES_ALL POL
WHERE POL.po_line_id = POLI.po_line_id))
RETURNING POLI.interface_line_id, POLI.interface_header_id
BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Line level='||SQL%rowcount); END IF;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = 'UPDATE'
AND (
-- the following are NOT NULL columns in the TXN table, so they must
-- have a not null value in the INTF table as well.
(
POAI.po_line_id IS NULL OR
POAI.req_template_name IS NULL OR
POAI.req_template_line_num IS NULL OR
POAI.org_id IS NULL OR
POAI.inventory_item_id IS NULL
--OR POAI.ip_category_id IS NULL
)
OR
-- If all 3 Primary Keys are -2, its an error
-- We need at least one PK reference to update in txn tables
(POAI.po_line_id = g_NOT_REQUIRED_ID
AND (POAI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE
OR POAI.req_template_line_num = g_NOT_REQUIRED_ID))
---------------------------------------------------
-- If PO_LINE_ID is given then RT keys must be null
OR
(POAI.po_line_id <> g_NOT_REQUIRED_ID
AND (POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
OR POAI.req_template_line_num <> g_NOT_REQUIRED_ID))
---------------------------------------------------
-- IF PO_LINE_ID is given, then Attr for line must exist
OR
(POAI.po_line_id <> g_NOT_REQUIRED_ID
AND NOT EXISTS
(SELECT 'Attribute row exists for PO Line'
FROM PO_ATTRIBUTE_VALUES POAV
WHERE POAV.po_line_id = POAI.po_line_id))
-- IF RT keys is given, then Attr for RT line must exist
OR
(POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
AND POAI.req_template_line_num <> g_NOT_REQUIRED_ID
AND NOT EXISTS
(SELECT 'Attribute row exists for Req Template Line'
FROM PO_ATTRIBUTE_VALUES POAV
WHERE POAV.req_template_name = POAI.req_template_name
AND POAV.req_template_line_num = POAI.req_template_line_num))
)
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Attr level='||SQL%rowcount); END IF;
UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
SET POATI.processing_id = -POATI.processing_id,
POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POATI.action = 'UPDATE'
AND (
-- the following are NOT NULL columns in the TXN table, so they must
-- have a not null value in the INTF table as well.
(
POATI.po_line_id IS NULL OR
POATI.req_template_name IS NULL OR
POATI.req_template_line_num IS NULL OR
POATI.org_id IS NULL OR
POATI.inventory_item_id IS NULL OR
POATI.ip_category_id IS NULL --OR
--POATI.description IS NULL
)
OR POATI.language IS NULL
OR
-- If all 3 Primary Keys are NULL, its an error
-- We need at least one PK reference to update in txn tables
(POATI.po_line_id = g_NOT_REQUIRED_ID
AND (POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE
OR POATI.req_template_line_num = g_NOT_REQUIRED_ID))
---------------------------------------------------
-- If PO_LINE_ID is given then RT keys must be null
OR
(POATI.po_line_id <> g_NOT_REQUIRED_ID
AND (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
OR POATI.req_template_line_num <> g_NOT_REQUIRED_ID))
---------------------------------------------------
-- IF PO_LINE_ID is given, then Attr for line must exist
OR
(POATI.po_line_id <> g_NOT_REQUIRED_ID
AND NOT EXISTS
(SELECT 'Attribute TLP row exists for PO Line'
FROM PO_ATTRIBUTE_VALUES_TLP POTLP
WHERE POTLP.po_line_id = POATI.po_line_id
AND POTLP.language = POATI.language))
-- IF RT keys is given, then:
-- 1. ORG_ID must be given
-- 2. TLP row for RT line must exist
OR
(POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
AND POATI.req_template_line_num <> g_NOT_REQUIRED_ID
AND (POATI.org_id = g_NOT_REQUIRED_ID OR
NOT EXISTS
(SELECT 'Attribute row exists for Req Template Line'
FROM PO_ATTRIBUTE_VALUES_TLP POTLP
WHERE POTLP.req_template_name = POATI.req_template_name
AND POTLP.req_template_line_num = POATI.req_template_line_num
AND POTLP.org_id = POATI.org_id
AND POTLP.language = POATI.language)))
)
RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at TLP level='||SQL%rowcount); END IF;
END validate_update_action;
PROCEDURE validate_delete_action
(
p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_action';
UPDATE PO_HEADERS_INTERFACE POHI
SET POHI.processing_id = -POHI.processing_id,
POHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POHI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POHI.action = 'DELETE'
AND (POHI.po_header_id is null
OR EXISTS
(SELECT 'document not created by migration program'
FROM PO_HEADERS_ALL POH
WHERE POH.po_header_id = POHI.po_header_id
AND POH.created_by <> PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER))
RETURNING POHI.interface_header_id
BULK COLLECT INTO l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Header level='||SQL%rowcount); END IF;
UPDATE PO_LINES_INTERFACE POLI
SET POLI.processing_id = -POLI.processing_id,
POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POLI.action = 'DELETE'
AND (POLI.po_line_id is null
OR EXISTS
(SELECT 'line not created by migration program'
FROM PO_LINES_ALL POL
WHERE POL.po_line_id = POLI.po_line_id
AND POL.created_by <> PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER))
RETURNING POLI.interface_line_id, POLI.interface_header_id
BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Line level='||SQL%rowcount); END IF;
UPDATE PO_ATTR_VALUES_INTERFACE POAI
SET POAI.processing_id = -POAI.processing_id,
POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POAI.action = 'DELETE'
RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Attr level='||SQL%rowcount); END IF;
UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
SET POATI.processing_id = -POATI.processing_id,
POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
AND POATI.action = 'DELETE'
AND (
-- the following are NOT NULL columns in the TXN table, so they must
-- have a not null value in the INTF table as well.
(
POATI.po_line_id IS NULL OR
POATI.req_template_name IS NULL OR
POATI.req_template_line_num IS NULL OR
POATI.org_id IS NULL
)
OR POATI.language IS NULL
OR
-- If all 3 Primary Keys are NULL, its an error
-- We need at least one PK reference to update in txn tables
(POATI.po_line_id = g_NOT_REQUIRED_ID
AND (POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE
OR POATI.req_template_line_num = g_NOT_REQUIRED_ID))
---------------------------------------------------
-- If PO_LINE_ID is given then RT keys must be null
OR
(POATI.po_line_id <> g_NOT_REQUIRED_ID
AND (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
OR POATI.req_template_line_num <> g_NOT_REQUIRED_ID))
---------------------------------------------------
-- IF PO_LINE_ID is given, then Attr for line must exist
OR
(POATI.po_line_id <> g_NOT_REQUIRED_ID
AND NOT EXISTS
(SELECT 'Attribute TLP row exists for PO Line'
FROM PO_ATTRIBUTE_VALUES_TLP POTLP
WHERE POTLP.po_line_id = POATI.po_line_id
AND POTLP.language = POATI.language))
-- IF RT keys is given, then:
-- 1. ORG_ID must be given
-- 2. TLP row for RT line must exist
OR
(POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
AND POATI.req_template_line_num <> g_NOT_REQUIRED_ID
AND (POATI.org_id = g_NOT_REQUIRED_ID OR
NOT EXISTS
(SELECT 'Attribute row exists for Req Template Line'
FROM PO_ATTRIBUTE_VALUES_TLP POTLP
WHERE POTLP.req_template_name = POATI.req_template_name
AND POTLP.req_template_line_num = POATI.req_template_line_num
AND POTLP.org_id = POATI.org_id
AND POTLP.language = POATI.language)))
---------------------------------------------------
-- For GBPA/BPA/Quotations, you can not delete TLP row for created_language
OR (POATI.po_line_id <> g_NOT_REQUIRED_ID
AND EXISTS
(SELECT 'TLP row for deletion is specified for created_lang'
FROM PO_LINES_ALL POL,
PO_HEADERS_ALL POH
WHERE POL.po_line_id = POATI.po_line_id
AND POH.po_header_id = POL.po_header_id
AND POH.created_language = POATI.language))
-- For ReqTemplates, you can not delete TLP row for base_language
OR (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
POATI.req_template_line_num <> g_NOT_REQUIRED_ID AND
POATI.language = l_base_language)
)
RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at TLP level='||SQL%rowcount); END IF;
END validate_delete_action;