[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)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_DEL_LOCATION',
'DELIVER_TO_LOCATION_ID',
p_deliver_to_location_id_tbl(i),
'DELIVER_TO_LOCATION_ID',
p_deliver_to_location_id_tbl(i)
FROM DUAL
WHERE p_deliver_to_location_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM hr_locations
WHERE NVL(inventory_organization_id, p_ship_to_org_id_tbl(i)) = p_ship_to_org_id_tbl(i)
AND NVL(inactive_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND location_id = p_deliver_to_location_id_tbl(i))
AND NOT EXISTS(
SELECT 1
FROM hz_locations
WHERE NVL(address_expiration_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND location_id = p_deliver_to_location_id_tbl(i));
SELECT FSP.BUSINESS_GROUP_ID
INTO l_fsp_business_group_id
FROM FINANCIALS_SYSTEM_PARAMETERS FSP;
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_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_DEL_PERSON',
'DELIVER_TO_PERSON_ID',
p_deliver_to_person_id_tbl(i),
'DELIVER_TO_PERSON',
p_deliver_to_person_id_tbl(i)
FROM DUAL
WHERE NOT EXISTS(
SELECT
/*+ INDEX(P PER_PEOPLE_F_PK) */
1
FROM PER_PEOPLE_F P,
PER_ASSIGNMENTS_F A
WHERE P.person_id = p_deliver_to_person_id_tbl(i)
AND A.person_id = P.person_id
AND A.primary_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN P.effective_start_date AND P.effective_end_date
AND TRUNC(SYSDATE) BETWEEN A.effective_start_date AND A.effective_end_date
AND (NVL(current_employee_flag,'N') = 'Y'
OR NVL(current_npw_flag,'N') = 'Y')
AND P.business_group_id = l_fsp_business_group_id
AND A.assignment_type IN ('E',l_assignment_type));
INSERT INTO po_session_gt(key, num1, char1)
SELECT l_key,
l_num_list_tbl(i),
decode(msi.outside_operation_flag,'Y','O', decode(msi.stock_enabled_flag,'Y','E','D'))
FROM mtl_system_items msi
WHERE p_dest_type_code_tbl(i) IS NOT NULL
AND msi.organization_id = p_ship_to_org_id_tbl(i)
AND msi.inventory_item_id = p_item_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, char1
BULK COLLECT INTO l_index_tbl, l_result_tbl;
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_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_DEST_TYPE',
'DESTINATION_TYPE_CODE',
p_dest_type_code_tbl(i),
'DESTINATION_TYPE',
p_dest_type_code_tbl(i)
FROM DUAL
WHERE p_dest_type_code_tbl(i) IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM po_lookup_codes
WHERE lookup_type = 'DESTINATION TYPE'
AND ((nvl(l_item_status_tbl(i),'D') = 'D'
/* AND lookup_code = 'EXPENSE') commented and added below 7644072*/
AND lookup_code <> 'INVENTORY') -- bug 7644072
OR (nvl(l_item_status_tbl(i),'D') = 'E'
AND lookup_code <> 'SHOP FLOOR')
OR (nvl(l_item_status_tbl(i),'D') = 'O'
AND lookup_code = 'SHOP FLOOR')
/* commenting the below and adding new conditions bug 7644072
OR (p_value_basis_tbl(i) = 'FIXED PRICE' -- EAM Integration Enhancement R12
AND lookup_code = 'SHOP FLOOR')*/
OR (p_value_basis_tbl(i) = 'FIXED PRICE' -- EAM Integration Enhancement R12
AND p_purchase_basis_tbl(i) = 'TEMP LABOR' --bug7644072
AND lookup_code = 'EXPENSE') --bug7644072
)
AND ((nvl(p_accrue_on_receipt_flag_tbl(i),'N') = 'N' AND lookup_code = 'EXPENSE')
OR p_txn_flow_header_id_tbl(i) is NOT NULL
OR (nvl(p_accrue_on_receipt_flag_tbl(i),'N') = 'Y'
AND ((p_expense_accrual_code = 'PERIOD END' AND lookup_code <> 'EXPENSE')
OR p_expense_accrual_code <> 'PERIOD END')
)
)
AND lookup_code= p_dest_type_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_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_DEST_SUBINV',
'DESTINATION_SUBINVENTORY',
p_destination_subinv_tbl(i),
'DESTINATION_SUBINVENTORY',
p_destination_subinv_tbl(i)
FROM DUAL
WHERE p_dest_type_code_tbl(i) = 'INVENTORY'
AND p_destination_subinv_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = NVL(p_ship_to_org_id_tbl(i), msub.organization_id)
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND ( p_item_id_tbl(i) IS NULL
OR ( p_item_id_tbl(i) IS NOT NULL
AND EXISTS(
SELECT NULL
FROM mtl_system_items msi
WHERE msi.organization_id = NVL(p_ship_to_org_id_tbl(i), msi.organization_id)
AND msi.inventory_item_id = p_item_id_tbl(i)
AND ( msi.restrict_subinventories_code = 2
OR ( msi.restrict_subinventories_code = 1
AND EXISTS(
SELECT NULL
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id =
NVL(p_ship_to_org_id_tbl(i), mis.organization_id)
AND mis.inventory_item_id = msi.inventory_item_id
AND mis.secondary_inventory = msub.secondary_inventory_name))))))
AND msub.secondary_inventory_name = p_destination_subinv_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_distribution,
p_id_tbl(i),
'PO_PDOI_SHIKYU_DEST_SUBINV',
'DESTINATION_SUBINVENTORY',
p_destination_subinv_tbl(i),
'DESTINATION_SUBINVENTORY',
p_destination_subinv_tbl(i)
FROM DUAL
WHERE p_destination_subinv_tbl(i) IS NOT NULL
AND p_dest_type_code_tbl(i) = 'INVENTORY'
AND p_loc_outsourced_assembly_tbl(i) = 1 /* SHIKYU item */
AND EXISTS(
SELECT 1
FROM mtl_secondary_inventories msub
WHERE msub.organization_id = NVL(p_ship_to_org_id_tbl(i), msub.organization_id)
AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msub.asset_inventory = 2 /* Not asset subinventory */
AND msub.secondary_inventory_name = p_destination_subinv_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_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_WIP_SCHED',
'WIP_REPETITIVE_SCHEDULE_ID',
p_wip_rep_schedule_id_tbl(i),
'WIP_REPETITIVE_SCHEDULE_ID',
p_wip_rep_schedule_id_tbl(i)
FROM DUAL
WHERE p_dest_type_code_tbl(i) = 'SHOP FLOOR'
AND p_wip_entity_id_tbl(i) IS NOT NULL
AND p_wip_rep_schedule_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM wip_repetitive_schedules wrs
WHERE wrs.organization_id = p_destination_org_id_tbl(i)
AND wrs.wip_entity_id = p_wip_entity_id_tbl(i)
AND wrs.repetitive_schedule_id = p_wip_rep_schedule_id_tbl(i)
AND wrs.status_type IN(3, 4, 6));
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_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_WIP_ENTITY',
'WIP_ENTITY_ID',
p_wip_entity_id_tbl(i),
'WIP_ENTITY_ID',
p_wip_entity_id_tbl(i)
FROM DUAL
WHERE p_dest_type_code_tbl(i) = 'SHOP FLOOR'
AND p_wip_entity_id_tbl(i) IS NOT NULL
AND p_wip_rep_schedule_id_tbl(i) IS NULL
AND NOT EXISTS(
SELECT 1
FROM wip_discrete_jobs wdj
WHERE wdj.organization_id = p_destination_org_id_tbl(i)
AND wdj.wip_entity_id = p_wip_entity_id_tbl(i)
AND wdj.status_type IN(3, 4, 6));
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_distribution,
p_id_tbl(i),
decode(p_charge_account_id_tbl(i), NULL, 'PO_PDOI_NO_CHG_ACCT', 'PO_PDOI_INVALID_CHG_ACCOUNT'),
'CHARGE_ACCOUNT_ID',
p_charge_account_id_tbl(i),
decode(p_charge_account_id_tbl(i), NULL, NULL, 'CHARGE_ACCOUNT'),
decode(p_charge_account_id_tbl(i), NULL, NULL, p_charge_account_id_tbl(i))
FROM DUAL
WHERE (p_charge_account_id_tbl(i) IS NULL OR
(p_charge_account_id_tbl(i) IS NOT NULL AND
NOT EXISTS(
SELECT NULL
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_charge_account_id_tbl(i)
AND gcc.enabled_flag = 'Y'
AND TRUNC(NVL(p_gl_date_tbl(i), SYSDATE)) BETWEEN TRUNC(NVL(start_date_active,
NVL(p_gl_date_tbl(i), SYSDATE)))
AND TRUNC(NVL(end_date_active,
NVL(p_gl_date_tbl(i), SYSDATE)))
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.chart_of_accounts_id = p_chart_of_account_id
AND gcc.summary_flag = '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)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_distribution,
p_id_tbl(i),
'PO_PDOI_INVALID_BUDGET_ACCT',
'BUDGET_ACCOUNT_ID',
p_budget_account_id_tbl(i),
'BUDGET_ACCOUNT',
p_budget_account_id_tbl(i)
FROM DUAL
WHERE (p_po_encumberance_flag = 'Y' AND
p_dest_type_code_tbl(i) <> 'SHOP FLOOR' AND
p_budget_account_id_tbl(i) IS NULL)
OR ( p_budget_account_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT NULL
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_budget_account_id_tbl(i)
AND gcc.enabled_flag = 'Y'
AND TRUNC(NVL(p_gl_date_tbl(i), SYSDATE)) BETWEEN TRUNC(NVL(start_date_active,
NVL(p_gl_date_tbl(i), SYSDATE)))
AND TRUNC(NVL(end_date_active,
NVL(p_gl_date_tbl(i), SYSDATE)))
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.chart_of_accounts_id = p_chart_of_account_id
AND gcc.summary_flag = '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)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_distribution,
p_id_tbl(i),
p_message_name,
p_column_name,
p_account_id_tbl(i),
p_token_name,
p_account_id_tbl(i)
FROM DUAL
WHERE p_account_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT NULL
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_account_id_tbl(i)
AND gcc.enabled_flag = 'Y'
AND TRUNC(NVL(p_gl_date_tbl(i), SYSDATE)) BETWEEN TRUNC(NVL(start_date_active,
NVL(p_gl_date_tbl(i), SYSDATE)))
AND TRUNC(NVL(end_date_active,
NVL(p_gl_date_tbl(i), SYSDATE)))
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.chart_of_accounts_id = p_chart_of_account_id
AND gcc.summary_flag = 'N');