[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ACTION_LOG(X_PLAN_ID NUMBER,
X_COLLECTION_ID NUMBER,
X_CREATION_DATE DATE,
X_CHAR_ID NUMBER,
X_OPERATOR NUMBER,
X_LOW_VALUE VARCHAR2,
X_HIGH_VALUE VARCHAR2,
X_MESSAGE VARCHAR2,
X_RESULT VARCHAR2,
X_CONCURRENT NUMBER) IS
user_id NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_LOGIN := FND_PROFILE.VALUE('CONC_LOGIN_ID');
insert into qa_action_log (LOG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PLAN_ID,
COLLECTION_ID,
TRANSACTION_DATE,
CHAR_ID,
OPERATOR,
LOW_VALUE,
HIGH_VALUE,
ACTION_LOG_MESSAGE,
RESULT_VALUE)
values (qa_action_log_s.NEXTVAL,
SYSDATE,
user_id,
SYSDATE,
user_id,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
SYSDATE,
X_PLAN_ID,
X_COLLECTION_ID,
X_CREATION_DATE,
X_CHAR_ID,
X_OPERATOR,
X_LOW_VALUE,
X_HIGH_VALUE,
X_MESSAGE,
X_RESULT);
insert into qa_action_log (LOG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_ID,
COLLECTION_ID,
TRANSACTION_DATE,
CHAR_ID,
OPERATOR,
LOW_VALUE,
HIGH_VALUE,
ACTION_LOG_MESSAGE,
RESULT_VALUE)
values (qa_action_log_s.NEXTVAL,
SYSDATE,
user_id,
SYSDATE,
user_id,
X_PLAN_ID,
X_COLLECTION_ID,
X_CREATION_DATE,
X_CHAR_ID,
X_OPERATOR,
X_LOW_VALUE,
X_HIGH_VALUE,
X_MESSAGE,
X_RESULT);
end INSERT_ACTION_LOG;
SELECT qpc.char_id CHAR_ID,
qc.datatype TYPE,
qc.fk_lookup_type FK_LOOKUP_TYPE,
NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
qpcat.operator OPERATOR,
decode(X_SPEC_ID, 0,
decode(qpcat.low_value_lookup,
7,qc.lower_reasonable_limit,
6,qc.lower_spec_limit,
5,qc.lower_user_defined_limit,
4,qc.target_value,
3,qc.upper_user_defined_limit,
2,qc.upper_spec_limit,
1,qc.upper_reasonable_limit,
NULL,qpcat.low_value_other),
decode(qpcat.low_value_lookup,
7,qscqs.lower_reasonable_limit,
6,qscqs.lower_spec_limit,
5,qscqs.lower_user_defined_limit,
4,qscqs.target_value,
3,qscqs.upper_user_defined_limit,
2,qscqs.upper_spec_limit,
1,qscqs.upper_reasonable_limit,
NULL,qpcat.low_value_other)) LOW_VALUE,
decode(X_SPEC_ID, 0,
decode(qpcat.high_value_lookup,
7,qc.lower_reasonable_limit,
6,qc.lower_spec_limit,
5,qc.lower_user_defined_limit,
4,qc.target_value,
3,qc.upper_user_defined_limit,
2,qc.upper_spec_limit,
1,qc.upper_reasonable_limit,
NULL,qpcat.high_value_other),
decode(qpcat.high_value_lookup,
7,qscqs.lower_reasonable_limit,
6,qscqs.lower_spec_limit,
5,qscqs.lower_user_defined_limit,
4,qscqs.target_value,
3,qscqs.upper_user_defined_limit,
2,qscqs.upper_spec_limit,
1,qscqs.upper_reasonable_limit,
NULL,qpcat.high_value_other)) HIGH_VALUE,
qpca.action_id ACTION,
qpca.plan_char_action_id PCA_ID,
qpcat.trigger_sequence SEQ_ID,
qpcat.plan_char_action_trigger_id PCAT_ID,
nvl(qscqs.uom_code, qc.uom_code) SPEC_CHAR_UOM,
nvl(qpc.uom_code, qc.uom_code) PLAN_CHAR_UOM,
qpc.decimal_precision DECIMAL_PRECISION
FROM qa_chars qc,
qa_plan_chars qpc,
qa_plan_char_action_triggers qpcat,
qa_plan_char_actions qpca,
qa_actions qa,
(select
qsc.CHAR_ID,
qsc.ENABLED_FLAG,
qsc.TARGET_VALUE,
qsc.UPPER_SPEC_LIMIT,
qsc.LOWER_SPEC_LIMIT,
qsc.UPPER_REASONABLE_LIMIT,
qsc.LOWER_REASONABLE_LIMIT,
qsc.UPPER_USER_DEFINED_LIMIT,
qsc.LOWER_USER_DEFINED_LIMIT,
qsc.UOM_CODE
from
qa_spec_chars qsc,
qa_specs qs
where
qsc.spec_id = qs.common_spec_id and
qs.spec_id = X_SPEC_ID
) qscqs
WHERE qpc.plan_id = X_PLAN_ID
and qpc.char_id = qc.char_id
and qc.char_id = qscqs.char_id (+)
and qpcat.plan_id (+) = X_PLAN_ID
and qpcat.char_id (+) = qpc.char_id
and qpca.plan_char_action_trigger_id (+) =
qpcat.plan_char_action_trigger_id
and qpca.action_id = qa.action_id
and (qa.online_flag = 2 or qa.action_id = 24)
and qa.enabled_flag = 1
ORDER BY qpc.prompt_sequence, qpcat.trigger_sequence,
qpcat.plan_char_action_trigger_id;
select MESSAGE, ASSIGN_TYPE from qa_plan_char_actions
where PLAN_CHAR_ACTION_ID = X_PCA_ID;
select MESSAGE from qa_plan_char_actions
where PLAN_CHAR_ACTION_ID = X_PCA_ID;
Cursor WF_ITEMTYPE_SELECTOR(WORKFLOW_ITEMTYPE VARCHAR2) IS
select wf_selector
from wf_item_types
where name = WORKFLOW_ITEMTYPE;
select count (*)
from wf_runnable_processes_v
where item_type = WORKFLOW_ITEMTYPE;
select STATUS_CODE from qa_plan_char_actions
where PLAN_CHAR_ACTION_ID = X_PCA_ID;
SELECT to_number(org_information3)
FROM hr_organization_information
WHERE organization_id = ORG_ID
AND org_information_context = 'Accounting Information';
SELECT
operating_unit
FROM inv_organization_info_v
WHERE organization_id = ORG_ID ;
Select Operating_Unit from org_organization_definitions
Where Organization_id = ORG_ID ;
SELECT
result_column_name
FROM qa_plan_chars
WHERE plan_id = x_plan_id
AND char_id = 23;
select RESULT_COLUMN_NAME from qa_plan_chars
where PLAN_ID = X_PLAN_ID
and CHAR_ID in (select char_id
from qa_chars
where developer_name =
'TO_INTRAOPERATION_STEP');
select lookup_code from mfg_lookups
where meaning = X_MEANING
and lookup_type = 'WIP_INTRAOPERATION_STEP';
select ASSIGNED_CHAR_ID
from QA_PLAN_CHAR_ACTIONS
where PLAN_CHAR_ACTION_ID = X_PCA_ID
and ACTION_ID = X_ACTION_ID;
SELECT
NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
qc.developer_name DEV_NAME
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qpc.plan_id = X_PLAN_ID
AND qc.char_id = qpc.char_id
AND qc.char_context_flag <> 3
ORDER BY qc.char_id;
SELECT NVL(qpcv.hardcoded_column,qpcv.result_column_name) Q_COLUMN,
qpcv.developer_name DEV_NAME
FROM qa_plan_chars_v qpcv
WHERE qpcv.plan_id = X_PLAN_ID
AND qpcv.char_context_flag <> 3
ORDER BY qpcv.char_id;
SELECT REASON_ID FROM MTL_TRANSACTION_REASONS_VAL_V
WHERE REASON_NAME = X_REASON_CODE;
SELECT EMPLOYEE_ID FROM HR_EMPLOYEES_CURRENT_V
WHERE FULL_NAME = X_EMPLOYEE;
select result_column_name
from qa_plan_chars
where char_id = elem_id and
plan_id = col_plan_id;
select qpca.message
from qa_plan_char_actions qpca, qa_plan_char_action_triggers qpcat
where qpcat.char_id = elem_id and
qpcat.plan_id = col_plan_id and
qpcat.plan_char_action_trigger_id = qpca.plan_char_action_trigger_id and
qpca.action_id = act_id;
select lookup_code
from mfg_lookups
where lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' and
meaning = value;
select auto_firm_flag
from wip_eam_parameters
where organization_id = org_id ;
select meaning
from mfg_lookups
where lookup_type = 'QA_OPERATOR' and
lookup_code = operator_number;
SELECT STATUS_ID FROM QA_PLAN_CHAR_ACTIONS
WHERE PLAN_CHAR_ACTION_ID = X_PCA_ID;
X_LAST_UPDATED_BY NUMBER;
X_WORKFLOW_ITEMTYPE_SELECTOR VARCHAR2(240);
fail_po_insertion EXCEPTION;
wf_missing_selector EXCEPTION;
fail_serial_insertion EXCEPTION;
fail_lot_insertion EXCEPTION;
update_column numtable; -- Added for update capabilities.
total_updates number := -1; -- Added for update capabilities.
txn_type varchar2(6); -- Added for update capabilities.
SELECT primary_unit_of_measure
FROM mtl_system_items_b
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT max(interface_transaction_id)
FROM rcv_transactions_interface
WHERE group_id = grp_id
AND parent_transaction_id = txn_id;
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,' ',
' ',X_message,
' ',X_CONCURRENT);
FOR UPDATED_RES_COLUMNS IN
(SELECT CHAR_ID
FROM QA_RESULTS_UPDATE_HISTORY
WHERE TXN_HEADER_ID = X_TXN_HEADER_ID
AND OCCURRENCE = X_OCCURRENCE) LOOP
I := I + 1;
UPDATE_COLUMN(I) := UPDATED_RES_COLUMNS.CHAR_ID;
TOTAL_UPDATES := I;
IF TOTAL_UPDATES = 0
THEN SELECT DECODE(INSERT_TYPE,2,'UPDATE','INSERT')
INTO TXN_TYPE
FROM QA_RESULTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = X_TXN_HEADER_ID;
IF NVL(TXN_TYPE,'INSERT') = 'UPDATE'
THEN TOTAL_UPDATES := 0;
ELSE TOTAL_UPDATES := -1;
TOTAL_UPDATES := -1;
(TOTAL_UPDATES <> 0) THEN
BEGIN
FOR prec IN action_triggers(Y_PLAN_ID, Y_SPEC_ID) LOOP
i := i + 1;
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
IF total_updates > -1
THEN FOR j IN 1..(total_updates+1) LOOP
IF ( j = (total_updates+1) )THEN done := TRUE;
ELSIF (update_column(j) = char_id_tab(i)) THEN EXIT;
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
X_message := 'ACTION PROCESS DBG, ACTION LOG INSERT';
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,
X_COLLECTION_ID,
X_CREATION_DATE,
char_id_tab(i),
operator_tab(i),
low_value_tab(i),
high_value_tab(i),
X_message,
result,
X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
INV_MATERIAL_STATUS_GRP.UPDATE_STATUS(
p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_update_method => 4,
p_status_id => x_status_id,
p_organization_id => x_org_id,
p_inventory_item_id => x_item_id,
p_lot_number => x_lot_number,
p_object_type => 'O');
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
INV_MATERIAL_STATUS_GRP.UPDATE_STATUS(
p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_update_method => 4,
p_status_id => x_status_id,
p_organization_id => x_org_id,
p_inventory_item_id => x_item_id,
p_serial_number => x_serial_number,
p_object_type => 'S');
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
Update PO_VENDOR_SITES
SET HOLD_ALL_PAYMENTS_FLAG = 'Y'
WHERE VENDOR_ID = x_vendor_id
AND ORG_ID = NVL ( X_OP_UNIT, ORG_ID);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
SELECT DISTINCT wrs.wip_entity_id INTO output
FROM wip_repetitive_schedules wrs,
wip_entities we
WHERE wrs.organization_id = x_org_id
AND wrs.line_id = output2
AND we.wip_entity_id = wrs.wip_entity_id
AND we.organization_id = wrs.organization_id
AND we.primary_item_id = to_number(QLTNINRB.NAME_IN('ITEM_ID'));
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
X_MESSAGE := 'SELECT ' || X_MESSAGE || ' FROM dual';
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
X_LAST_UPDATED_BY := QLTNINRB.NAME_IN('LAST_UPDATED_BY');
X_LOGIN := QLTNINRB.NAME_IN('LAST_UPDATE_LOGIN');
RCV_INSPECTION_GRP.INSERT_INSPECTION(
p_api_version => 1.1,
p_init_msg_list => NULL,
p_commit => 'F',
p_validation_level => NULL,
p_created_by => X_LAST_UPDATED_BY,
p_last_updated_by => X_LAST_UPDATED_BY,
p_last_update_login => X_LOGIN,
p_employee_id => X_EMPLOYEE_ID,
p_group_id => X_GROUP_ID,
p_transaction_id => X_TRANSACTION_ID,
p_transaction_type => X_TXN_TYPE,
p_processing_mode => X_PO_TXN_PROCESSOR_MODE,
p_quantity => X_QUANTITY,
p_uom => X_UOM,
p_quality_code => X_QUALITY_CODE,
p_transaction_date => X_TRANSACTION_DATE,
p_comments => X_COMMENTS,
p_reason_id => X_REASON_ID,
p_vendor_lot => X_VENDOR_LOT,
p_lpn_id => X_LPN_ID,
p_transfer_lpn_id => X_XFR_LPN_ID,
p_qa_collection_id => X_COLLECTION_ID,
p_return_status => X_RETURN_STATUS,
p_msg_count => X_MSG_COUNT,
p_msg_data => X_MSG_DATA,
p_subinventory => X_RTI_SUB_CODE,
p_locator_id => X_RTI_LOC_ID,
p_from_subinventory => X_RTI_SUB_CODE,
p_from_locator_id => X_RTI_LOC_ID);
RAISE fail_po_insertion;
/*-- Bug 3225280. Moved the Lot and serial insertion code after RCV
-- insert_inspection API because, we want the interface_transaction_id
-- of the ACCEPT and REJECT transactions to be passed to the WMS APIs
-- as product_transaction_id.
--
-- For this, first we need to find the interface_transaction_id of the
-- inspection record inserted by RCV API. The logic here is to fetch the
-- max(interface_transaction_id) from rti for the parent_transaction_id
-- and group_id combination. Since we are implementing this just after
-- RCV API call, it will fetch the interface_transaction_id of the
-- inspection record just inserted.
-- kabalakr. Wed Oct 29 23:19:22 PST 2003.
--
OPEN int_txn(X_GROUP_ID, X_TRANSACTION_ID);
INV_RCV_INTEGRATION_APIS.INSERT_MTLI
(p_api_version => 1.0,
p_init_msg_lst => NULL,
x_return_status => X_RETURN_STATUS,
x_msg_count => X_MSG_COUNT,
x_msg_data => X_MSG_DATA,
p_transaction_interface_id => l_int_txn_id,
p_transaction_quantity => X_QUANTITY,
p_primary_quantity => l_primary_qty,
p_organization_id => X_ORG_ID,
p_inventory_item_id => X_ITEM_ID,
p_lot_number => X_LOT_NUMBER,
p_expiration_date => NULL,
p_status_id => NULL,
x_serial_transaction_temp_id => l_ser_txn_id,
p_product_code => 'RCV',
p_product_transaction_id => l_rti_int_txn_id);
RAISE fail_lot_insertion;
INV_RCV_INTEGRATION_APIS.INSERT_MSNI
(p_api_version => 1.0,
p_init_msg_lst => NULL,
x_return_status => X_RETURN_STATUS,
x_msg_count => X_MSG_COUNT,
x_msg_data => X_MSG_DATA,
p_transaction_interface_id => l_int_txn_id,
p_fm_serial_number => X_SERIAL_NUMBER,
p_to_serial_number => X_SERIAL_NUMBER,
p_organization_id => X_ORG_ID,
p_inventory_item_id => X_ITEM_ID,
p_status_id => NULL,
p_product_code => 'RCV',
p_product_transaction_id => l_rti_int_txn_id);
RAISE fail_serial_insertion;
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
WHEN fail_po_insertion THEN
--dbms_output.put_line('x progress is '||to_char(x_progress));
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
WHEN fail_serial_insertion THEN
X_ERROR := FALSE;
FND_MESSAGE.SET_NAME('QA', 'QA_WMS_SER_INSERT_FAIL');
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
WHEN fail_lot_insertion THEN
X_ERROR := FALSE;
FND_MESSAGE.SET_NAME('QA', 'QA_WMS_LOT_INSERT_FAIL');
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
END; -- insert inspection
OPEN wf_itemtype_selector(X_WORKFLOW_ITEMTYPE);
FETCH wf_itemtype_selector
INTO X_WORKFLOW_ITEMTYPE_SELECTOR;
CLOSE wf_itemtype_selector;
AND X_WORKFLOW_ITEMTYPE_SELECTOR IS NULL THEN
raise wf_missing_selector;
WHEN WF_MISSING_SELECTOR THEN
X_ERROR := FALSE;
X_message := 'Found no SELECTOR for ' || X_WORKFLOW_ITEMTYPE;
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
l_work_order_rec.last_update_date := fnd_date.chardt_to_date(QLTNINRB.NAME_IN('QA_CREATION_DATE'));
l_work_order_rec.last_updated_by := QLTNINRB.NAME_IN('LAST_UPDATED_BY');
l_work_order_rec.last_update_login := QLTNINRB.NAME_IN('LAST_UPDATE_LOGIN');
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,0,
0,'',
'',X_message,
'',X_CONCURRENT);
PO_ASL_SV.Update_Vendor_Status (x_org_id, x_vendor_id,
X_Status, NULL, x_item_id,'N', NULL, x_asl_return);
INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
X_CREATION_DATE,char_id_tab(i),
operator_tab(i),low_value_tab(i),
high_value_tab(i),X_message,
result,X_CONCURRENT);
SELECT NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
qpcao.token_name TOKEN_NAME,
qc.fk_lookup_type LOOKUP_TYPE,
qc.char_id CHAR_ID,
qc.datatype DATATYPE
from qa_chars qc,
qa_plan_chars qpc,
qa_plan_char_action_outputs qpcao,
qa_plan_char_actions qpca,
qa_plan_char_action_triggers qpcat
where qc.char_id = qpcao.char_id
and qpc.char_id = qpcao.char_id
and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
and qpc.plan_id = qpcat.plan_id
and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
and qc.char_context_flag <> 3
UNION SELECT qc.hardcoded_column Q_COLUMN,
qpcao.token_name TOKEN_NAME,
qc.fk_lookup_type LOOKUP_TYPE,
qc.char_id CHAR_ID,
qc.datatype DATATYPE
from qa_chars qc,
qa_plan_char_action_outputs qpcao,
qa_plan_char_actions qpca,
qa_plan_char_action_triggers qpcat
where qc.char_id = qpcao.char_id
and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
and qc.char_context_flag = 3;
SELECT name
FROM wf_item_attributes wia
WHERE wia.item_type = X_WF_ITEMTYPE;
SELECT
qc.datatype
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qc.char_id = t_char_id
AND qpc.char_id = qc.char_id
AND qpc.plan_id = X_PLAN_ID;
SELECT qpcv.datatype
FROM qa_plan_chars_v qpcv
WHERE qpcv.char_id = t_char_id
AND qpcv.plan_id = X_PLAN_ID;
select qa_action_workflow_s.nextval
from dual;
select process_name
from wf_runnable_processes_v
where item_type = X_WF_ITEMTYPE;
SELECT NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
qpcao.token_name TOKEN_NAME,
qc.fk_lookup_type LOOKUP_TYPE,
qc.char_id CHAR_ID
from qa_chars qc,
qa_plan_chars qpc,
qa_plan_char_action_outputs qpcao,
qa_plan_char_actions qpca,
qa_plan_char_action_triggers qpcat
where qc.char_id = qpcao.char_id
and qpc.char_id = qpcao.char_id
and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
and qpc.plan_id = qpcat.plan_id
and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
and qc.char_context_flag <> 3
UNION SELECT qc.hardcoded_column Q_COLUMN,
qpcao.token_name TOKEN_NAME,
qc.fk_lookup_type LOOKUP_TYPE,
qc.char_id CHAR_ID
from qa_chars qc,
qa_plan_char_action_outputs qpcao,
qa_plan_char_actions qpca,
qa_plan_char_action_triggers qpcat
where qc.char_id = qpcao.char_id
and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
and qc.char_context_flag = 3;
SELECT aas.NAME
FROM QA_PLAN_CHAR_ACTIONS qpca,
ALR_ACTION_SETS aas
WHERE qpca.PLAN_CHAR_ACTION_ID = X_PCA_ID
AND qpca.ALR_ACTION_SET_ID = aas.ACTION_SET_ID
AND aas.APPLICATION_ID = 250;
SELECT plan_id FROM qa_plan_char_action_triggers
WHERE plan_char_action_trigger_id = (select PLAN_CHAR_ACTION_TRIGGER_ID
FROM QA_PLAN_CHAR_ACTIONS WHERE PLAN_CHAR_ACTION_ID = l_pca_id);
SELECT result_column_name FROM qa_plan_chars
WHERE plan_id = l_plan_id AND char_id = ( SELECT char_id
FROM qa_plan_char_action_triggers WHERE
plan_char_action_trigger_id = (SELECT plan_char_action_trigger_id
FROM qa_plan_char_actions WHERE plan_char_action_id = l_pca_id));
occurrence(l_occurrence) for every update. The values will be passed
to alerts which takes the combination of l_result_column_value and
l_occurrence and fires the action if that particular combination is
not already available. But before this some mandatory steps are needed
in Oracle Alerts which is specified in the ARU readme.
Bug 3162828.suramasw.
*/
OPEN CPLAN(x_pca_id);
l_sql_string := 'SELECT '|| l_result_column_name ||' FROM QA_RESULTS '||
'WHERE PLAN_ID = :l_plan_id AND OCCURRENCE = :l_occurrence';
SELECT NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
qpcao.token_name TOKEN_NAME,
qc.fk_lookup_type LOOKUP_TYPE,
qc.char_id CHAR_ID
from qa_chars qc,
qa_plan_chars qpc,
qa_plan_char_actions qpca,
qa_plan_char_action_triggers qpcat,
qa_plan_char_action_outputs qpcao
where qc.char_id = qpcao.char_id
and qpc.char_id = qpcao.char_id
and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
and qpc.plan_id = qpcat.plan_id
and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
and qc.char_context_flag <> 3
UNION SELECT qc.hardcoded_column Q_COLUMN,
qpcao.token_name TOKEN_NAME,
qc.fk_lookup_type LOOKUP_TYPE,
qc.char_id CHAR_ID
from qa_chars qc,
qa_plan_char_actions qpca,
qa_plan_char_action_triggers qpcat,
qa_plan_char_action_outputs qpcao
where qc.char_id = qpcao.char_id
and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
and qc.char_context_flag = 3;
SELECT
NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
qc.datatype,
DECODE(qpc.decimal_precision, NULL, qc.decimal_precision, qpc.decimal_precision)
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qc.char_id = qpc.char_id
AND qc.char_id = t_char_id
AND qpc.plan_id = X_PLAN_ID;
SELECT NVL(qpcv.hardcoded_column,qpcv.result_column_name) Q_COLUMN,
qpcv.datatype, qpcv.decimal_precision
FROM qa_plan_chars_v qpcv
WHERE qpcv.char_id = t_char_id
AND qpcv.plan_id = X_PLAN_ID;
update_stmt VARCHAR2(2500);
update_stmt := 'UPDATE qa_results SET ' || y_column || ' = ' ;
update_stmt := update_stmt || bind_var_name || ' WHERE plan_id = :PLAN_ID AND '||
'collection_id = :COLL_ID AND '||
'occurrence = :OCCURRENCE';
EXECUTE IMMEDIATE update_stmt USING return_value_num, X_PLAN_ID, X_COLLECTION_ID, X_OCCURRENCE;
EXECUTE IMMEDIATE update_stmt USING
return_value_date,
X_PLAN_ID,
X_COLLECTION_ID,
X_OCCURRENCE;
EXECUTE IMMEDIATE update_stmt USING return_value_char, X_PLAN_ID, X_COLLECTION_ID, X_OCCURRENCE;
SELECT lpn_context
, subinventory_code
, locator_id
INTO l_xfer_lpn_ctxt
, l_xfer_lpn_sub
, l_xfer_lpn_loc_id
FROM wms_license_plate_numbers
WHERE lpn_id = x_xfr_lpn_id;
SELECT subinventory_code
, locator_id
INTO l_lpn_sub
, l_lpn_loc_id
FROM wms_license_plate_numbers
WHERE lpn_id = x_lpn_id;
SELECT TRIM(token_name) tokenName,
'CHARID' || char_id bulk collect
INTO token_rec_tab
FROM qa_plan_char_action_outputs
WHERE plan_char_action_id = p_plan_char_action_id
order by length(tokenName) desc ;
SELECT REGEXP_REPLACE(l_message_str,
'&' || token_rec_tab(cntr).token_name,
'&' || token_rec_tab(cntr).char_name || ';',