[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
who_last_update_login number;
qa_core_pkg.dsql_add_text(' SELECT 1 ' ||
' FROM QA_SPEC_CHARS_V QSC, QA_CHARS QC ' ||
' WHERE QSC.CHAR_ID (+) = QC.CHAR_ID ' ||
' AND QC.CHAR_ID = ');
PROCEDURE UPDATE_MARKER ( COL_NAME VARCHAR2,
ERROR_COL_NAME VARCHAR2,
X_DATATYPE NUMBER,
X_CHAR_ID NUMBER,
X_GROUP_ID NUMBER,
X_USER_ID NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_PLAN_ID NUMBER,
ERROR_COL_LIST VARCHAR2,
X_LV_LOOKUP_VALUE VARCHAR2,
X_LV_OTHER_VALUE VARCHAR2,
X_HV_LOOKUP_VALUE VARCHAR2,
X_HV_OTHER_VALUE VARCHAR2,
X_OP_CODE NUMBER,
X_PCAT_ID NUMBER ) IS
l_col1 VARCHAR2(100);
qa_core_pkg.dsql_add_text(' UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE' ||
', LAST_UPDATE_LOGIN = ');
qa_core_pkg.dsql_add_bind(X_LAST_UPDATE_LOGIN);
qa_core_pkg.dsql_add_text(' , PROGRAM_UPDATE_DATE = SYSDATE ' ||
' , MARKER = ');
' (SELECT 1 ' ||
' FROM QA_INTERFACE_ERRORS QIE ' ||
' WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
' QRI.TRANSACTION_INTERFACE_ID ' ||
' AND QIE.ERROR_COLUMN IN ( ' );
END UPDATE_MARKER;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_PLAN_ID NUMBER,
ERROR_COL_LIST VARCHAR2,
X_LV_LOOKUP_VALUE VARCHAR2,
X_LV_OTHER_VALUE VARCHAR2,
X_HV_LOOKUP_VALUE VARCHAR2,
X_HV_OTHER_VALUE VARCHAR2,
X_OP_CODE NUMBER,
X_PCAT_ID NUMBER ) IS
l_col1 VARCHAR2(100);
qa_core_pkg.dsql_add_text(' INSERT INTO QA_INTERFACE_ERRORS ' ||
'( TRANSACTION_INTERFACE_ID, ' ||
' ERROR_COLUMN, ' ||
' ERROR_MESSAGE, ' ||
' LAST_UPDATE_DATE,' ||
' LAST_UPDATED_BY, ' ||
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' LAST_UPDATE_LOGIN, ' ||
' REQUEST_ID, ' ||
' PROGRAM_APPLICATION_ID, ' ||
' PROGRAM_ID, ' ||
' PROGRAM_UPDATE_DATE ) ' );
qa_core_pkg.dsql_add_text(' SELECT QRI.TRANSACTION_INTERFACE_ID, ');
qa_core_pkg.dsql_add_text(' SYSDATE, '); -- last_update_date
qa_core_pkg.dsql_add_bind(X_USER_ID); -- last_updated_by
qa_core_pkg.dsql_add_bind(X_LAST_UPDATE_LOGIN); -- last_update_login
' (SELECT 1 ' ||
' FROM QA_INTERFACE_ERRORS QIE ' ||
' WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
' QRI.TRANSACTION_INTERFACE_ID ' ||
' AND QIE.ERROR_COLUMN IN ( ');
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_PLAN_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
PCAT_ID_TABLE NUMBER_TABLE;
FOR ACTIONREC IN (SELECT
QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID,
QPCA.ACTION_ID,
QPCAT.OPERATOR,
QPCAT.LOW_VALUE_LOOKUP,
QPCAT.LOW_VALUE_OTHER,
QPCAT.HIGH_VALUE_LOOKUP,
QPCAT.HIGH_VALUE_OTHER
FROM
QA_PLAN_CHAR_ACTION_TRIGGERS QPCAT,
QA_PLAN_CHAR_ACTIONS QPCA,
QA_ACTIONS QA
WHERE
QPCAT.PLAN_ID = X_PLAN_ID AND
QPCAT.CHAR_ID = X_CHAR_ID AND
QPCA.PLAN_CHAR_ACTION_TRIGGER_ID = QPCAT.PLAN_CHAR_ACTION_TRIGGER_ID AND
QA.ACTION_ID = QPCA.ACTION_ID AND
QA.ENABLED_FLAG = 1
ORDER BY
QPCAT.TRIGGER_SEQUENCE) LOOP
I := I + 1;
UPDATE QA_RESULTS_INTERFACE QRI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
MARKER = 0
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND MARKER IS NOT NULL;
X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
X_PLAN_ID , ERROR_COL_LIST , LV_LOOKUP_TABLE(I),
LV_OTHER_TABLE(I), HV_LOOKUP_TABLE(I) ,
HV_OTHER_TABLE(I), OPERATOR_TABLE(I),
PCAT_ID_CURRENT);
UPDATE_MARKER (COL_NAME ,ERROR_COL_NAME, X_DATATYPE,
X_CHAR_ID, X_GROUP_ID, X_USER_ID,
X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
X_PLAN_ID , ERROR_COL_LIST , LV_LOOKUP_TABLE(I),
LV_OTHER_TABLE(I), HV_LOOKUP_TABLE(I) ,
HV_OTHER_TABLE(I), OPERATOR_TABLE(I),
PCAT_ID_CURRENT);
* validate_mandatory. inserts an error into the errors table when a
* revision is not entered for an item under revision control. also
* inserts an errors when a revision is entered for an item not under
* revision control.
*/
PROCEDURE VALIDATE_REVISION(COL_NAME VARCHAR2,
ERROR_COL_NAME VARCHAR2,
X_GROUP_ID NUMBER,
X_USER_ID NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
PARENT_COL_NAME VARCHAR2,
ERROR_COL_LIST VARCHAR2,
X_MANDATORY NUMBER) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_NEED_REV, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND EXISTS ' ||
'(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
'WHERE QRI.' || COL_NAME || ' IS NULL ' ||
' AND QRI.' || REV_COLUMN || ' = 2)';
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_CANT_HAVE_REV, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND EXISTS ' ||
'(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
'WHERE QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND QRI.' || REV_COLUMN || ' = 1)';
X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
PARENT_COL_NAME VARCHAR2,
ERROR_COL_LIST VARCHAR2,
X_MANDATORY NUMBER) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_MANDATORY, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.' || COL_NAME || ' IS NULL ' ||
' AND :LOT_CNTRL_CODE = (SELECT MSI.LOT_CONTROL_CODE ' ||
' FROM MTL_SYSTEM_ITEMS MSI ' ||
' WHERE MSI.INVENTORY_ITEM_ID = QRI.' || ITEM_ID_COL ||
' AND MSI.ORGANIZATION_ID = QRI.ORGANIZATION_ID)';
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5,2;
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.'|| COL_NAME || ' IS NOT NULL '||
' AND :LOT_CNTRL_CODE = (SELECT MSI.LOT_CONTROL_CODE ' ||
' FROM MTL_SYSTEM_ITEMS MSI ' ||
' WHERE MSI.INVENTORY_ITEM_ID = QRI.'|| ITEM_ID_COL ||
' AND MSI.ORGANIZATION_ID = QRI.ORGANIZATION_ID)';
X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5,1;
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.'|| COL_NAME || ' IS NOT NULL '||
' AND NOT EXISTS (SELECT MLN.LOT_NUMBER ' ||
' FROM MTL_LOT_NUMBERS MLN ' ||
' WHERE MLN.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
' AND MLN.INVENTORY_ITEM_ID = QRI.' || ITEM_ID_COL ||
' AND MLN.LOT_NUMBER = QRI.' || COL_NAME ||
' AND (MLN.DISABLE_FLAG = :DB OR MLN.DISABLE_FLAG IS NULL))';
X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5,2;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
PARENT_COL_NAME VARCHAR2,
ERROR_COL_LIST VARCHAR2,
X_MANDATORY NUMBER) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_MANDATORY, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.' || COL_NAME || ' IS NULL ' ||
' AND :SERIAL_CNTRL_CODE <> (SELECT MSI.SERIAL_NUMBER_CONTROL_CODE ' ||
' FROM MTL_SYSTEM_ITEMS MSI ' ||
' WHERE MSI.INVENTORY_ITEM_ID = QRI.' || ITEM_ID_COL ||
' AND MSI.ORGANIZATION_ID = QRI.ORGANIZATION_ID)';
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5,1;
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.'|| COL_NAME || ' IS NOT NULL '||
' AND :SERIAL_CNTRL_CODE = (SELECT MSI.SERIAL_NUMBER_CONTROL_CODE ' ||
' FROM MTL_SYSTEM_ITEMS MSI ' ||
' WHERE MSI.INVENTORY_ITEM_ID = QRI.' || ITEM_ID_COL ||
' AND MSI.ORGANIZATION_ID = QRI.ORGANIZATION_ID)';
X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5,1;
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.'|| COL_NAME || ' IS NOT NULL '||
' AND NOT EXISTS (SELECT MSN.SERIAL_NUMBER ' ||
' FROM MTL_SERIAL_NUMBERS MSN ' ||
' WHERE MSN.SERIAL_NUMBER = QRI.' || COL_NAME ||
' AND MSN.CURRENT_ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
' AND MSN.INVENTORY_ITEM_ID = QRI.' || ITEM_ID_COL ||
' AND (QRI.'||LOT_COL||' IS NULL OR MSN.LOT_NUMBER = QRI.'||LOT_COL||')' ||
' AND (QRI.'||REV_COL||' IS NULL OR MSN.REVISION = QRI.'||REV_COL||'))';
X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_DATATYPE NUMBER) IS
SQL_STATEMENT VARCHAR2(2000);
'SELECT TRANSACTION_INTERFACE_ID, ' || COL_NAME ||
' FROM QA_RESULTS_INTERFACE QRI ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN ( :ERROR_COL_NAME, NULL))' ||
' AND to_char(QRI.' || COL_NAME || ') NOT IN (to_char(:NULL_NUM), :NULL_CHAR, to_char(:null_date, ''DD-MON-YYYY HH24:MI:SS''))' ;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERRMSG,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_DATATYPE NUMBER,
X_DECIMAL_PRECISION NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
COL_NAME || ' = ';
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))'||
' AND to_char(QRI.' || COL_NAME || ') NOT IN (to_char(:null_num), :null_char, to_char(:null_date, ''DD-MON-YYYY HH24:MI:SS''))' ;
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5,
NULL_NUM,
NULL_CHAR,
NULL_DATE;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'SELECT QRI.TRANSACTION_INTERFACE_ID, ' ||
'QRI.' || COL_NAME || ', ' ||
'QRI.ORGANIZATION_ID, ' ||
'QRI.' || COMP_TEXT || 'SUB_LOCATOR_TYPE, ' ||
'QRI.' || COMP_TEXT || 'LOCATION_CONTROL_CODE, ' ||
'QRI.' || COMP_TEXT || 'RESTRICT_LOCATORS_CODE, ' ||
'QRI.' || COMP_TEXT || 'SUBINVENTORY, ' ||
'MP.STOCK_LOCATOR_CONTROL_CODE, ' ||
'MP.NEGATIVE_INV_RECEIPT_CODE, ' ||
'QRI.' || COMP_TEXT || 'ITEM_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI, ' ||
' MTL_PARAMETERS MP ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN ( :ERROR_COL_NAME, NULL)) '||
' AND QRI.ORGANIZATION_ID = MP.ORGANIZATION_ID';
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_CANT_HAVE_LOC,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
COMP_TEXT || 'GEN_LOC_CTRL_CODE = :GEN_LOC_CTRL_TABLE ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
GEN_LOC_CTRL_TABLE(I),
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
'(SELECT SECONDARY_LOCATOR FROM MTL_SECONDARY_LOCATORS ' ||
'WHERE INVENTORY_ITEM_ID = ' || TO_CHAR(ITEM_ID_TABLE(I)) ||
' AND ORGANIZATION_ID = ' || TO_CHAR(ORG_ID_TABLE(I)) ||
' AND SUBINVENTORY_CODE = ' || SUBINV_TABLE(I) || ')';
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
ID_FIELD || ' = :CID ' ||
', ' || COMP_TEXT || 'GEN_LOC_CTRL_CODE = :GEN_LOC_CTRL_TABLE ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
CID,
GEN_LOC_CTRL_TABLE(I),
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'SELECT QRI.TRANSACTION_INTERFACE_ID, ' ||
'QRI.' || COL_NAME || ', ' ||
'QRI.ORGANIZATION_ID, ' ||
'QRI.TO_SUB_LOCATOR_TYPE, ' ||
'QRI.LOCATION_CONTROL_CODE, ' ||
'QRI.RESTRICT_LOCATORS_CODE, ' ||
'QRI.TO_SUBINVENTORY, ' ||
'MP.STOCK_LOCATOR_CONTROL_CODE, ' ||
'MP.NEGATIVE_INV_RECEIPT_CODE, ' ||
'QRI.ITEM_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI, ' ||
' MTL_PARAMETERS MP ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN ( :ERROR_COL_NAME, NULL))' ||
' AND QRI.ORGANIZATION_ID = MP.ORGANIZATION_ID';
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_CANT_HAVE_LOC,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
'GEN_LOC_CTRL_CODE = :GEN_LOC_CTRL_TABLE ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
GEN_LOC_CTRL_TABLE(I),
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
'(SELECT SECONDARY_LOCATOR FROM MTL_SECONDARY_LOCATORS ' ||
'WHERE INVENTORY_ITEM_ID = ' || TO_CHAR(ITEM_ID_TABLE(I)) ||
' AND ORGANIZATION_ID = ' || TO_CHAR(ORG_ID_TABLE(I)) ||
' AND SUBINVENTORY_CODE = ' || TO_SUBINV_TABLE(I) || ')';
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
ID_FIELD || ' = :CID ' ||
', GEN_LOC_CTRL_CODE = :GEN_LOC_CTRL_TABLE ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
CID,
GEN_LOC_CTRL_TABLE(I),
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
CURSOR C IS SELECT RESTRICT_SUBINVENTORIES_CODE,
RESTRICT_LOCATORS_CODE,
LOCATION_CONTROL_CODE,
REVISION_QTY_CONTROL_CODE
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = CID
AND ORGANIZATION_ID = X_ORG_ID;
'SELECT TRANSACTION_INTERFACE_ID, ' || COL_NAME ||
', ORGANIZATION_ID, LINE_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN ( :ERROR_COL_NAME, NULL))';
'((SELECT PRIMARY_ITEM_ID FROM WIP_REP_ASSY_VAL_V ' ||
'WHERE ORGANIZATION_ID = ' || TO_CHAR(ORG_ID_TABLE(I)) ||
' AND LINE_ID = ' || TO_CHAR(LINE_ID_TABLE(I)) || ')' ||
'UNION' ||
'(SELECT ASSEMBLY_ITEM_ID FROM BOM_OPERATIONAL_ROUTINGS ' ||
'WHERE ORGANIZATION_ID = ' || TO_CHAR(ORG_ID_TABLE(I)) ||
'AND LINE_ID = ' || TO_CHAR(LINE_ID_TABLE(I)) || '))';
' ( SELECT COMPONENT_ITEM_ID ' ||
' FROM BOM_INVENTORY_COMPONENTS BIC, BOM_BILL_OF_MATERIALS BOM ' ||
' WHERE BOM.ORGANIZATION_ID = ' || TO_CHAR(ORG_ID_TABLE(I)) ||
' AND BOM.ASSEMBLY_ITEM_ID = ' || TO_CHAR(G_ITEM_ID) ||
' AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID ' ||
' AND BIC.EFFECTIVITY_DATE <= SYSDATE ' ||
' AND NVL(BIC.DISABLE_DATE, SYSDATE+1) > SYSDATE )';*/
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
ID_FIELD || ' = :CID ' ||
', ' || COMP_TEXT || 'RESTRICT_SUBINV_CODE = :BIND_RST_SINV ' ||
', ' || COMP_TEXT || 'RESTRICT_LOCATORS_CODE = :BIND_RST_LOC ' ||
', ' || COMP_TEXT || 'LOCATION_CONTROL_CODE = :BIND_LOC_CTRL ' ||
', ' || COMP_TEXT || 'REVISION_QTY_CONTROL_CODE = :BIND_REV_CTRL ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
CID,
ITM_RST_SINV,
ITM_RST_LOC,
ITM_LOC_CTRL,
REV_CTRL_CODE,
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_MANDATORY, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = :PROCESS_STATUS ' ||
' AND NOT EXISTS ' ||
'(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
' AND QRI.' || ID_FIELD || ' IS NOT NULL ' ||
' AND QRI.ITEM_ID IS NOT NULL' ||
' AND NOT EXISTS '||
'(SELECT COMPONENT_ITEM_ID ' ||
' FROM BOM_INVENTORY_COMPONENTS BIC, BOM_BILL_OF_MATERIALS BOM ' ||
' WHERE BOM.ORGANIZATION_ID = QRI.ORGANIZATION_ID' ||
' AND BOM.ASSEMBLY_ITEM_ID = QRI.ITEM_ID ' ||
' AND BIC.COMPONENT_ITEM_ID = QRI.COMP_ITEM_ID' ||
' AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID ' ||
' AND BIC.EFFECTIVITY_DATE <= SYSDATE ' ||
' AND NVL(BIC.DISABLE_DATE, SYSDATE+1) > SYSDATE )';
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_GROUP_ID,2,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'SELECT TRANSACTION_INTERFACE_ID, ' || COL_NAME ||
', ORGANIZATION_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN ( :ERROR_COL_NAME, NULL)) ';
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
ID_FIELD || ' = :CID ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
CID,
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'SELECT TRANSACTION_INTERFACE_ID, ' || COL_NAME ||
', ORGANIZATION_ID, LINE_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN ( :ERROR_COL_NAME, NULL))';
'(SELECT PRIMARY_ITEM_ID FROM WIP_REP_ASSY_VAL_V ' ||
'WHERE ORGANIZATION_ID = ' || TO_CHAR(ORG_ID_TABLE(I)) ||
' AND LINE_ID = ' || TO_CHAR(LINE_ID_TABLE(I)) || ')';
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
ID_FIELD || ' = :CID '||
' WHERE QRI.GROUP_ID = :GROUP_ID '||
' AND QRI.TRANSACTION_INTERFACE_ID = :INTERFACE_ID_TABLE ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
CID,
X_GROUP_ID,
INTERFACE_ID_TABLE(I),
l_col1, l_col2, l_col3, l_col4, l_col5;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) VALUES
(INTERFACE_ID_TABLE(I), ERROR_COL_NAME, ERROR_INVALID_VALUE,
SYSDATE, X_USER_ID, SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, SYSDATE);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
FROM_CLAUSE VARCHAR2,
WHERE_CLAUSE VARCHAR2,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, :REQUEST_ID, ' ||
':PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
'AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
'AND NOT EXISTS ' ||
--
-- bug 13454868
-- Added hint to improve performance
--
'(SELECT /*+ NO_UNNEST PUSH_SUBQ */ ''X'' ' ||
'FROM ' || FROM_CLAUSE ||
' WHERE (' || WHERE_CLAUSE || '))';
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, :REQUEST_ID, ' ||
':PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
'AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
'AND NOT EXISTS ' ||
'(SELECT ''X'' ' ||
'FROM MTL_SUBINVENTORIES_VAL_V MSVV ' ||
'WHERE MSVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
' AND QRI.' || COMP_STRING || 'RESTRICT_SUBINV_CODE = 2 ' ||
' AND MSVV.SECONDARY_INVENTORY_NAME = QRI.' || COL_NAME ||
' UNION ' ||
'SELECT ''X'' ' ||
'FROM MTL_ITEM_SUB_VAL_V MISVV ' ||
'WHERE MISVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
' AND QRI.' || COMP_STRING || 'RESTRICT_SUBINV_CODE = 1 ' ||
' AND MISVV.INVENTORY_ITEM_ID = ' ||
'QRI.' || COMP_STRING || 'ITEM_ID ' ||
' AND MISVV.SECONDARY_INVENTORY_NAME = QRI.' || COL_NAME || ')';
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
COMP_STRING || 'SUB_LOCATOR_TYPE = ' ||
'(SELECT LOCATOR_TYPE ' ||
' FROM MTL_SUBINVENTORIES_VAL_V ' ||
' WHERE SECONDARY_INVENTORY_NAME = QRI.' || COMP_STRING ||
'SUBINVENTORY ' ||
' AND ORGANIZATION_ID = QRI.ORGANIZATION_ID) ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, :REQUEST_ID, ' ||
':PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
'AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
'AND NOT EXISTS ' ||
'(SELECT ''X'' ' ||
'FROM MTL_SUBINVENTORIES_VAL_V MSVV ' ||
'WHERE MSVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
' AND QRI.RESTRICT_SUBINV_CODE = 2 ' ||
' AND MSVV.SECONDARY_INVENTORY_NAME = QRI.' || COL_NAME ||
' UNION ' ||
'SELECT ''X'' ' ||
'FROM MTL_ITEM_SUB_VAL_V MISVV ' ||
'WHERE MISVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
' AND QRI.RESTRICT_SUBINV_CODE = 1 ' ||
' AND MISVV.INVENTORY_ITEM_ID = ' ||
'QRI.ITEM_ID ' ||
' AND MISVV.SECONDARY_INVENTORY_NAME = QRI.' || COL_NAME || ')';
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, TO_SUB_LOCATOR_TYPE = ' ||
'(SELECT LOCATOR_TYPE ' ||
' FROM MTL_SUBINVENTORIES_VAL_V ' ||
' WHERE SECONDARY_INVENTORY_NAME = QRI.TO_SUBINVENTORY ' ||
' AND ORGANIZATION_ID = QRI.ORGANIZATION_ID) ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
SELECT name
FROM qa_chars qc, qa_plan_chars qpc, qa_results_interface qri
WHERE qc.char_id = qpc.char_id
AND qpc.plan_id = qri.plan_id
AND qpc.result_column_name = p_col_name
AND qri.group_id = p_group_id;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_SQL_VALIDATION_STRING VARCHAR2,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_INVALID_VALUE, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, :REQUEST_ID, ' ||
':PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
'AND QRI.' || COL_NAME || ' IS NOT NULL ' ||
'AND NOT EXISTS (SELECT ''x'' FROM DUAL WHERE QRI.' ||
COL_NAME || ' IN ' || '(' || FORMATTED_SQL_STRING || ') )'||
' AND to_char(QRI.' || COL_NAME || ') NOT IN (to_char(:null_num), :null_char, to_char(:null_date, ''DD-MON-YYYY HH24:MI:SS''))';
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5,
null_num,
null_char,
null_date;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ID_ASSIGNMENT VARCHAR2,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
ID_ASSIGNMENT || '
WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'UPDATE QA_RESULTS_INTERFACE QRI ' ||
'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
'WIP_ENTITY_ID = (SELECT WIP_ENTITY_ID FROM ' ||
'WIP_REPETITIVE_ENTITIES_V WREV ' ||
'WHERE WREV.PRIMARY_ITEM_ID = QRI.ITEM_ID ' ||
' AND WREV.ORGANIZATION_ID = QRI.ORGANIZATION_ID) ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5;
X_PROGRAM_ID NUMBER, X_LAST_UPDATE_LOGIN NUMBER,
X_COLUMN_NAME VARCHAR2 DEFAULT NULL) IS
BEGIN
QLTTRAMB.qa_import_log('Procedure SET_ERROR_STATUS');
UPDATE QA_RESULTS_INTERFACE qri
SET PROCESS_STATUS = 3,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE qri.GROUP_ID = X_GROUP_ID AND EXISTS
(SELECT 1
FROM qa_interface_errors qie
WHERE qie.transaction_interface_id = qri.transaction_interface_id);
UPDATE QA_RESULTS_INTERFACE QRI
SET PROCESS_STATUS = 3,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE GROUP_ID = X_GROUP_ID
AND EXISTS
(SELECT TRANSACTION_INTERFACE_ID, ERROR_COLUMN
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN = X_COLUMN_NAME);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
ERROR_COL_LIST VARCHAR2) IS
SQL_STATEMENT VARCHAR2(2000);
'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, ' ||
':ERROR_OUTSIDE_LIMITS, SYSDATE, ' ||
':USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID ' ||
'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5))
AND NOT EXISTS ' ||
'(SELECT ''X'' ' ||
'FROM QA_SPEC_CHARS_V QSC, ' ||
'QA_CHARS QC ' ||
'WHERE ((QRI.' || COL_NAME || ' IS NULL) ' ||
'OR (((DECODE(QSC.CHAR_ID, NULL, ' ||
'QC.LOWER_REASONABLE_LIMIT, ' ||
'QSC.LOWER_REASONABLE_LIMIT) IS NULL) ' ||
'OR ';
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
l_col1, l_col2, l_col3, l_col4, l_col5,
X_CHAR_ID;
X_LAST_UPDATE_LOGIN NUMBER, X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER, X_PROGRAM_ID NUMBER,
FROM_CLAUSE VARCHAR2, WHERE_CLAUSE VARCHAR2,
ID_ASSIGN VARCHAR2, X_CHAR_ID NUMBER,
X_CHAR_NAME VARCHAR2, X_DATATYPE NUMBER, X_DECIMAL_PRECISION NUMBER,
X_PLAN_ID NUMBER, X_VALUES_EXIST_FLAG NUMBER,
X_READ_ONLY_FLAG NUMBER,
X_SQL_VALIDATION_STRING VARCHAR2 DEFAULT NULL,
PARENT_COL VARCHAR2 DEFAULT NULL, GRANDPARENT_COL VARCHAR2 DEFAULT NULL,
GREAT_GRANDPARENT_COL VARCHAR2 DEFAULT NULL) IS
ERROR_COL_LIST VARCHAR2(200);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, PARENT_COL, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, PARENT_COL, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, PARENT_COL, ERROR_COL_LIST, X_MANDATORY_FLAG);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, PARENT_COL, ERROR_COL_LIST, X_MANDATORY_FLAG);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, PARENT_COL, ERROR_COL_LIST, X_MANDATORY_FLAG);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, PARENT_COL, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_DATATYPE);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, X_DATATYPE, X_DECIMAL_PRECISION, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
X_CHAR_ID, X_PLAN_ID, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
X_SQL_VALIDATION_STRING, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, FROM_CLAUSE, WHERE_CLAUSE, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, ID_ASSIGN, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
X_PLAN_ID, ERROR_COL_LIST);
DERIVE_JOB(X_GROUP_ID, X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, ERROR_COL_LIST);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_COL_NAME VARCHAR2,
X_CHAR_ID NUMBER,
X_TXN_TYPE NUMBER
) IS
SQL_STATEMENT VARCHAR2(2000);
'UPDATE QA_RESULTS_INTERFACE QRI ' || 'SET LAST_UPDATE_DATE = SYSDATE, ' ||
'LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN ' ||
', REQUEST_ID = :REQUEST_ID ' ||
', PROGRAM_APPLICATION_ID = :PROGRAM_APPLICATION_ID ' ||
', PROGRAM_ID = :PROGRAM_ID ' ||
', PROGRAM_UPDATE_DATE = SYSDATE, ' ||
X_COL_NAME || ' = :NEW_SEQUENCE ' ||
' WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
'AND NOT EXISTS (SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID )';
EXECUTE IMMEDIATE SQL_STATEMENT USING X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NEW_SEQUENCE,
X_GROUP_ID;
P_LAST_UPDATE_LOGIN NUMBER,
P_REQUEST_ID NUMBER,
P_PROGRAM_APPLICATION_ID NUMBER,
P_PROGRAM_ID NUMBER,
P_CHAR_NAME VARCHAR2,
P_TXN_TYPE NUMBER
) IS
--L_MATCHING_ELEMENTS VARCHAR2(1000);
THEN -- this is insert, the element must be null
MUST_BE_NULL := TRUE;
ELSE -- this is update, the element must be null unless it is a matching element
--SELECT MATCHING_ELEMENTS
--INTO L_MATCHING_ELEMENTS
--FROM QA_RESULTS_INTERFACE
--WHERE GROUP_ID = P_GROUP_ID;
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_REQUEST_ID => P_REQUEST_ID,
P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => P_PROGRAM_ID,
P_PARENT_COL_NAME => NULL,
P_ERROR_COL_LIST => ERROR_COL_LIST);
FUNCTION VALIDATE_UPDATE_TYPE_RECORDS (X_GROUP_ID NUMBER,
X_PLAN_ID NUMBER,
CHAR_NAME_TABLE CHAR30_TABLE,
DEVELOPER_NAME_TABLE CHAR30_TABLE,
HARDCODED_COLUMN_TABLE CHAR30_TABLE,
DATATYPE_TABLE NUMBER_TABLE,
NUM_ELEMS BINARY_INTEGER,
X_USER_ID NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER) RETURN VARCHAR2 IS
SELECT_STMT VARCHAR2(10000);
QLTTRAMB.qa_import_log('Start of Procedure VALIDATE_UPDATE_TYPE_RECORDS');
SELECT MATCHING_ELEMENTS, TRANSACTION_INTERFACE_ID
INTO X_MATCHING_ELEMENTS, X_TRANSACTION_INTERFACE_ID
FROM QA_RESULTS_INTERFACE
WHERE GROUP_ID = X_GROUP_ID;
QLTTRAMB.qa_import_log('Error - The records to be updated doesn''t have valid matching_elements');
SELECT NAME
INTO X_PLAN_NAME
FROM QA_PLANS
WHERE PLAN_ID = X_PLAN_ID;
SELECT import_view_name
INTO l_importviewname
FROM QA_PLANS
WHERE PLAN_ID = X_PLAN_ID;
SELECT view_name
INTO l_viewname
FROM QA_PLANS
WHERE PLAN_ID = X_PLAN_ID;
SELECT_STMT := 'SELECT V.ROW_ID ' ||
'FROM "'|| l_importviewname ||'" QI,' ||
' "'|| l_viewname ||'" V ' ||
'WHERE QI.PROCESS_STATUS = 2 ';
SELECT_STMT := 'SELECT V.ROW_ID ' ||
'FROM "Q_'||translate(X_PLAN_NAME, ' ''', '__')||'_IV" QI,' ||
' "Q_'||translate(X_PLAN_NAME, ' ''', '__')||'_V" V ' ||
'WHERE QI.PROCESS_STATUS = 2 ';
-- insert into date_test(d) values('2-JAN-2001');
-- select to_char(to_date(d, 'DD-MON-YYYY'), 'YYYY/MM/DD')
-- from date_test;
-- SELECT_STMT := SELECT_STMT ||
-- ' AND V.' || CONDITIONS_TABLE(I) ||
-- ' = QI.' || NVL(DEVELOPER_TABLE(I),CONDITIONS_TABLE(I));
SELECT_STMT := SELECT_STMT || ' AND V.' || CONDITIONS_TABLE(I)
|| ' = qltdate.any_to_date(QI.' || CONDITIONS_TABLE(I) ||')';
SELECT_STMT := SELECT_STMT || ' AND V.' || CONDITIONS_TABLE(I)
|| ' = qltdate.any_to_date(QI.' ||
NVL(DEVELOPER_TABLE(I),CONDITIONS_TABLE(I)) ||')';
SELECT_STMT := SELECT_STMT || ' AND V.' || CONDITIONS_TABLE(I)
|| ' = qltdate.any_to_date(QI.' || DEVELOPER_TABLE(I) ||')';
SELECT_STMT := SELECT_STMT || ' AND V.' || CONDITIONS_TABLE(I)
|| ' = qltdate.any_to_datetime(QI.' || CONDITIONS_TABLE(I) ||')';
SELECT_STMT := SELECT_STMT || ' AND V.' || CONDITIONS_TABLE(I)
|| ' = qltdate.any_to_datetime(QI.' ||
NVL(DEVELOPER_TABLE(I),CONDITIONS_TABLE(I)) ||')';
SELECT_STMT := SELECT_STMT || ' AND V.' || CONDITIONS_TABLE(I)
|| ' = qltdate.any_to_datetime(QI.' || DEVELOPER_TABLE(I) ||')';
SELECT_STMT := SELECT_STMT ||
' AND V."' || CONDITIONS_TABLE(I) ||
'" = QI."' || CONDITIONS_TABLE(I) || '"';
SELECT_STMT := SELECT_STMT ||
' AND V."' || CONDITIONS_TABLE(I) ||
'" = QI."' || NVL(DEVELOPER_TABLE(I),CONDITIONS_TABLE(I)) || '"';
SELECT_STMT := SELECT_STMT ||
' AND V."' || CONDITIONS_TABLE(I) ||
'" = QI."' || DEVELOPER_TABLE(I) || '"';
SELECT_STMT := SELECT_STMT || ' and QI.TRANSACTION_INTERFACE_ID = :X_INTERFACE_ID';
DBMS_SQL.PARSE(CURSOR_HANDLE, SELECT_STMT, DBMS_SQL.NATIVE);
QLTTRAMB.qa_import_log('Completed procedure VALIDATE_UPDATE_TYPE_RECORDS');
INSERT INTO QA_INTERFACE_ERRORS
(TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'GROUP_ID',
FND_MESSAGE.GET_STRING('QA', 'QA_INTERFACE_INVALID_VALUE'),
SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2;
X_LAST_UPDATE_LOGIN, 'GROUP_ID');
INSERT INTO QA_INTERFACE_ERRORS
(TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'MATCHING_ELEMENTS',
FND_MESSAGE.GET_STRING('QA', 'QA_INTERFACE_INVALID_VALUE'),
SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2;
X_LAST_UPDATE_LOGIN, 'MATCHING_ELEMENTS');
INSERT INTO QA_INTERFACE_ERRORS
(TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'MATCHING_ELEMENTS',
FND_MESSAGE.GET_STRING('QA', 'QA_INTERFACE_NO_MATCH'),
SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2;
X_LAST_UPDATE_LOGIN, 'MATCHING_ELEMENTS');
INSERT INTO QA_INTERFACE_ERRORS
(TRANSACTION_INTERFACE_ID,
ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'MATCHING_ELEMENTS',
FND_MESSAGE.GET_STRING('QA', 'QA_INTERFACE_MANY_MATCHES'),
SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2;
X_LAST_UPDATE_LOGIN, 'MATCHING_ELEMENTS');
END VALIDATE_UPDATE_TYPE_RECORDS;
PROCEDURE RETRIEVE_UPDATE_RECORDS(X_GROUP_ID NUMBER,
STMT_OF_ROWIDS VARCHAR2,
DEVELOPER_NAME CHAR30_TABLE,
RESULT_COLUMN_NAME CHAR30_TABLE,
DATATYPE_TABLE NUMBER_TABLE,
CHAR_NAME_TABLE CHAR30_TABLE,
NUM_ELEMS NUMBER,
X_PLAN_ID NUMBER,
READ_ONLY_FLAG_TABLE NUMBER_TABLE,
ENABLED_FLAG_TABLE NUMBER_TABLE) IS
UPDATE_STMT VARCHAR2(10000);
QLTTRAMB.qa_import_log('Start of procedure RETRIEVE_UPDATE_RECORDS');
SELECT NAME INTO X_PLAN_NAME
FROM QA_PLANS
WHERE PLAN_ID = X_PLAN_ID;
SELECT view_name
INTO l_viewname
FROM QA_PLANS
WHERE PLAN_ID = X_PLAN_ID;
UPDATE_STMT := 'UPDATE QA_RESULTS_INTERFACE QRI ' || 'SET (';
UPDATE_STMT := UPDATE_STMT || RESULT_COLUMN_NAME(I);
UPDATE_STMT := UPDATE_STMT || RESULT_COLUMN_NAME(I);
UPDATE_STMT := UPDATE_STMT || RESULT_COLUMN_NAME(I);
UPDATE_STMT := UPDATE_STMT || DEVELOPER_NAME(I);
UPDATE_STMT := UPDATE_STMT || ',';
QLTTRAMB.qa_import_log('Building the update command for update');
UPDATE_STMT := UPDATE_STMT || ') = (SELECT';
UPDATE_STMT := UPDATE_STMT||' NVL(QRI.'||RESULT_COLUMN_NAME(I)||
',' || VALUE_STRING;
UPDATE_STMT := UPDATE_STMT||' NVL(QRI.'||RESULT_COLUMN_NAME(I)||
',qltdate.date_to_canon(' || VALUE_STRING || ')';
UPDATE_STMT := UPDATE_STMT||' NVL(QRI.'||RESULT_COLUMN_NAME(I)||
',qltdate.date_to_canon_dt(' || VALUE_STRING || ')';
UPDATE_STMT := UPDATE_STMT||' NVL(QRI.'||RESULT_COLUMN_NAME(I)||
',null)';
UPDATE_STMT := UPDATE_STMT||' NVL(QRI.'||RESULT_COLUMN_NAME(I)||
',' || VALUE_STRING;
UPDATE_STMT := UPDATE_STMT||' NVL(QRI.'||DEVELOPER_NAME(I)||
',' || VALUE_STRING;
UPDATE_STMT := UPDATE_STMT || ',';
UPDATE_STMT := UPDATE_STMT || ' FROM "'|| l_viewname ||
'" V ' || 'WHERE V.ROW_ID = :BIND_ROWID ' ||
') WHERE QRI.GROUP_ID = :GROUP_ID ';
QLTTRAMB.qa_import_log('Built the update statement for update');
QLTTRAMB.qa_import_log('UPDATE_STMT='|| substr(UPDATE_STMT,1,1000));
QLTTRAMB.qa_import_log(nvl(substr(UPDATE_STMT,1001,1000), 'SQL completed'));
QLTTRAMB.qa_import_log(nvl(substr(UPDATE_STMT,2002,1000), 'SQL completed'));
QLTTRAMB.qa_import_log(nvl(substr(UPDATE_STMT,3003,1000), 'SQL completed'));
QLTTRAMB.qa_import_log(nvl(substr(UPDATE_STMT,4004,1000), 'SQL completed'));
QLTTRAMB.qa_import_log(nvl(substr(UPDATE_STMT,5005,1000), 'SQL completed'));
QLTTRAMB.qa_import_log(nvl(substr(UPDATE_STMT,6006,1000), 'SQL completed'));
EXECUTE IMMEDIATE UPDATE_STMT USING L_ROWID,X_GROUP_ID;
UPDATE_STMT := UPDATE_STMT || ' FROM "Q_'||
translate(X_PLAN_NAME, ' ''', '__') ||
'_V" V ' || 'WHERE V.ROW_ID IN ' || STMT_OF_ROWIDS ||
') WHERE QRI.GROUP_ID = '||X_GROUP_ID;
QLTTRAMB.qa_import_log('Procedure RETRIEVE_UPDATE_RECORDS completed');
END RETRIEVE_UPDATE_RECORDS;
X_LAST_UPDATE_LOGIN NUMBER;
COPY_STMT_OF_ROWIDS VARCHAR2(10000); -- For update capabilities
SELECT organization_id
FROM qa_results_interface qri
WHERE qri.group_id = p_group_id
AND qri.process_status = 2;
X_LAST_UPDATE_LOGIN := who_last_update_login;
SELECT USER_NAME
INTO X_USER_NAME
FROM FND_USER_VIEW
WHERE USER_ID = X_USER_ID;
UPDATE QA_RESULTS_INTERFACE
SET QA_CREATED_BY = X_USER_ID,
QA_CREATED_BY_NAME = X_USER_NAME,
QA_LAST_UPDATED_BY = X_USER_ID,
QA_LAST_UPDATED_BY_NAME = X_USER_NAME,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = X_USER_ID,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2
AND QA_CREATED_BY_NAME IS NULL;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ERROR_COLUMN,
ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'QA_CREATED_BY_NAME',
ERROR_INVALID_VALUE, SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND QRI.INSERT_TYPE <> 2 -- added for 3663648
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('QA_CREATED_BY_NAME', NULL))
AND NOT EXISTS
(SELECT 'X'
FROM FND_USER_VIEW FU2
WHERE QRI.QA_CREATED_BY_NAME = FU2.USER_NAME);
UPDATE QA_RESULTS_INTERFACE QRI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
QA_CREATED_BY =
(SELECT MIN(FU2.USER_ID)
FROM FND_USER_VIEW FU2
WHERE FU2.USER_NAME = QRI.QA_CREATED_BY_NAME),
QA_LAST_UPDATED_BY =
(SELECT MIN(FU2.USER_ID)
FROM FND_USER_VIEW FU2
WHERE FU2.USER_NAME = QRI.QA_CREATED_BY_NAME)
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND QRI.INSERT_TYPE <> 2 -- added for 3663648
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('QA_CREATED_BY_NAME', NULL));
UPDATE QA_RESULTS_INTERFACE
SET QA_LAST_UPDATED_BY = X_USER_ID,
QA_LAST_UPDATED_BY_NAME = X_USER_NAME
WHERE GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2
AND QA_LAST_UPDATED_BY_NAME IS NULL;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ERROR_COLUMN,
ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'QA_LAST_UPDATED_BY_NAME',
ERROR_INVALID_VALUE, SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND QRI.INSERT_TYPE = 2 -- added for 3663648
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('QA_LAST_UPDATED_BY_NAME', NULL))
AND NOT EXISTS
(SELECT 'X'
FROM FND_USER_VIEW FU2
WHERE QRI.QA_LAST_UPDATED_BY_NAME = FU2.USER_NAME);
QLTTRAMB.qa_import_log('Error in last updated by Error count :' ||sql%rowcount);
UPDATE QA_RESULTS_INTERFACE QRI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
QA_LAST_UPDATED_BY=
(SELECT MIN(FU2.USER_ID)
FROM FND_USER_VIEW FU2
WHERE FU2.USER_NAME = QRI.QA_LAST_UPDATED_BY_NAME)
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND QRI.INSERT_TYPE = 2 -- added for 3663648
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('QA_LAST_UPDATED_BY_NAME', NULL));
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ERROR_COLUMN,
ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'ORGANIZATION_CODE',
ERROR_INVALID_VALUE, SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('ORGANIZATION_CODE', NULL))
AND NOT EXISTS
(SELECT 'X'
FROM MTL_PARAMETERS OOD
WHERE QRI.ORGANIZATION_CODE = OOD.ORGANIZATION_CODE);
UPDATE QA_RESULTS_INTERFACE QRI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
ORGANIZATION_ID =
(SELECT MIN(OOD.ORGANIZATION_ID)
FROM MTL_PARAMETERS OOD
WHERE OOD.ORGANIZATION_CODE = QRI.ORGANIZATION_CODE)
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('ORGANIZATION_CODE', NULL));
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, X_LAST_UPDATE_LOGIN,
'ORGANIZATION_CODE');
UPDATE qa_results_interface qri
SET last_update_date = sysdate,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = sysdate,
operating_unit_id =
(SELECT ou.organization_id
FROM hr_operating_units ou
WHERE ou.name = qri.operating_unit AND
(ou.date_from IS NULL OR ou.date_from <= sysdate) AND
(ou.date_to IS NULL OR ou.date_to >= sysdate))
WHERE qri.group_id = x_group_id AND
qri.process_status = 2 AND
qri.operating_unit IS NOT NULL AND
qri.operating_unit_id IS NULL;
INSERT INTO qa_interface_errors(
transaction_interface_id,
error_column,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT qri.transaction_interface_id,
'OPERATING_UNIT',
ERROR_INVALID_VALUE,
sysdate,
x_user_id,
sysdate,
x_user_id,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
sysdate
FROM qa_results_interface qri
WHERE qri.group_id = x_group_id AND
qri.process_status = 2 AND
qri.operating_unit IS NOT NULL AND
qri.operating_unit_id IS NULL;
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ERROR_COLUMN,
ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'PLAN_NAME',
ERROR_INVALID_VALUE, SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('PLAN_NAME', NULL))
AND NOT EXISTS
(SELECT 'X'
FROM QA_PLANS_VAL_V QPVV
WHERE QRI.PLAN_NAME = QPVV.NAME
AND QRI.ORGANIZATION_ID = QPVV.ORGANIZATION_ID);
UPDATE QA_RESULTS_INTERFACE QRI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
PLAN_ID =
(SELECT MIN(QPVV.PLAN_ID)
FROM QA_PLANS_VAL_V QPVV
WHERE QPVV.NAME = QRI.PLAN_NAME
AND QPVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID)
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('PLAN_NAME', NULL));
INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ERROR_COLUMN,
ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT QRI.TRANSACTION_INTERFACE_ID, 'SPEC_NAME',
ERROR_INVALID_VALUE, SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('SPEC_NAME', NULL))
AND (QRI.SPEC_NAME IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM QA_SPECS QSVV
WHERE QRI.SPEC_NAME = QSVV.SPEC_NAME
AND QRI.ORGANIZATION_ID = QSVV.ORGANIZATION_ID
AND trunc(sysdate) BETWEEN
nvl(trunc(qsvv.effective_from), trunc(sysdate)) AND
nvl(trunc(qsvv.effective_to), trunc(sysdate))));
UPDATE QA_RESULTS_INTERFACE QRI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
SPEC_ID =
(SELECT MIN(QSVV.SPEC_ID)
FROM QA_SPECS QSVV
WHERE QSVV.SPEC_NAME = QRI.SPEC_NAME
AND QSVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID
AND trunc(sysdate) BETWEEN
nvl(trunc(qsvv.effective_from), trunc(sysdate)) AND
nvl(trunc(qsvv.effective_to), trunc(sysdate)))
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('SPEC_NAME', NULL));
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, X_LAST_UPDATE_LOGIN,
'PLAN_NAME');
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, X_LAST_UPDATE_LOGIN,
'SPEC_NAME');
SELECT MAX(PLAN_ID)
INTO X_PLAN_ID
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID =
QRI.TRANSACTION_INTERFACE_ID
AND QIE.ERROR_COLUMN IN ('ORGANIZATION_CODE', 'PLAN_NAME',
'SPEC_NAME', NULL));
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, X_LAST_UPDATE_LOGIN);
FOR CHARREC IN (SELECT QPCV.CHAR_ID, QC.DEVELOPER_NAME,
QPCV.RESULT_COLUMN_NAME, QPCV.ENABLED_FLAG,
QPCV.MANDATORY_FLAG, QC.DATATYPE, QPCV.DECIMAL_PRECISION,
QC.SQL_VALIDATION_STRING, QPCV.VALUES_EXIST_FLAG,
UPPER(REPLACE(QC.NAME, ' ', '_')) CHAR_NAME, READ_ONLY_FLAG
FROM QA_CHARS QC, QA_PLAN_CHARS_V QPCV
WHERE QPCV.PLAN_ID = X_PLAN_ID
AND QPCV.CHAR_ID = QC.CHAR_ID) LOOP
I := I + 1;
SELECT
qpc.char_id,
upper(replace(qc.name, ' ', '_')),
qc.hardcoded_column,
qc.developer_name,
qpc.result_column_name,
qpc.enabled_flag,
qpc.mandatory_flag,
qc.datatype,
nvl(qpc.decimal_precision,qc.decimal_precision),
qc.sql_validation_string,
qpc.values_exist_flag,
qpc.read_only_flag
BULK COLLECT INTO
char_id_table,
char_name_table,
hardcoded_column_table,
developer_name_table,
result_column_name_table,
enabled_flag_table,
mandatory_flag_table,
datatype_table,
decimal_precision_table,
sql_validation_string_table,
values_exist_flag_table,
read_only_flag_table
FROM
qa_chars qc,
qa_plan_chars qpc
WHERE
qpc.plan_id = x_plan_id AND
qpc.char_id = qc.char_id;
QLTTRAMB.qa_import_log('calling validate update_type records');
COPY_STMT_OF_ROWIDS := VALIDATE_UPDATE_TYPE_RECORDS(X_GROUP_ID,
X_PLAN_ID,
CHAR_NAME_TABLE,
DEVELOPER_NAME_TABLE,
HARDCODED_COLUMN_TABLE, -- Bug 4254876
DATATYPE_TABLE,
NUM_ELEMS,
X_USER_ID,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID);
QLTTRAMB.qa_import_log('Got stmt_of_rowids as string which has all rowids to be updated');
RETRIEVE_UPDATE_RECORDS(X_GROUP_ID,
COPY_STMT_OF_ROWIDS,
DEVELOPER_NAME_TABLE,
RESULT_COLUMN_NAME_TABLE,
DATATYPE_TABLE,
CHAR_NAME_TABLE,
NUM_ELEMS,
X_PLAN_ID,
READ_ONLY_FLAG_TABLE,
ENABLED_FLAG_TABLE);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'WIP_LINES_VAL_V WL',
'QRI.PRODUCTION_LINE = WL.LINE_CODE AND ' ||
'QRI.ORGANIZATION_ID = WL.ORGANIZATION_ID',
'LINE_ID = (SELECT MIN(WL.LINE_ID) ' ||
'FROM WIP_LINES_VAL_V WL ' ||
'WHERE WL.LINE_CODE = QRI.PRODUCTION_LINE ' ||
'AND WL.ORGANIZATION_ID = QRI.ORGANIZATION_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'BOM_DEPARTMENTS_VAL_V BD',
'QRI.DEPARTMENT = BD.DEPARTMENT_CODE AND ' ||
'QRI.ORGANIZATION_ID = BD.ORGANIZATION_ID',
'DEPARTMENT_ID = (SELECT MIN(BD.DEPARTMENT_ID) ' ||
'FROM BOM_DEPARTMENTS_VAL_V BD ' ||
'WHERE BD.DEPARTMENT_CODE = QRI.DEPARTMENT ' ||
'AND BD.ORGANIZATION_ID = QRI.ORGANIZATION_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'BOM_DEPARTMENTS_VAL_V BD',
'QRI.TO_DEPARTMENT = BD.DEPARTMENT_CODE AND ' ||
'QRI.ORGANIZATION_ID = BD.ORGANIZATION_ID',
'TO_DEPARTMENT_ID = (SELECT MIN(BD.DEPARTMENT_ID) ' ||
'FROM BOM_DEPARTMENTS_VAL_V BD ' ||
'WHERE BD.DEPARTMENT_CODE = QRI.TO_DEPARTMENT ' ||
'AND BD.ORGANIZATION_ID = QRI.ORGANIZATION_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'BOM_RESOURCES_VAL_V BR',
'QRI.RESOURCE_CODE = BR.RESOURCE_CODE AND ' ||
'QRI.ORGANIZATION_ID = BR.ORGANIZATION_ID',
'RESOURCE_ID = (SELECT MIN(BR.RESOURCE_ID) ' ||
'FROM BOM_RESOURCES_VAL_V BR ' ||
'WHERE BR.RESOURCE_CODE = QRI.RESOURCE_CODE ' ||
'AND BR.ORGANIZATION_ID = QRI.ORGANIZATION_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, ' WIP_DISCRETE_JOBS_ALL_V WE ',
'QRI.JOB_NAME = WE.WIP_ENTITY_NAME AND ' ||
'QRI.ORGANIZATION_ID = WE.ORGANIZATION_ID',
'WIP_ENTITY_ID = (SELECT MIN(WE.WIP_ENTITY_ID) ' ||
'FROM WIP_DISCRETE_JOBS_ALL_V WE ' ||
'WHERE WE.WIP_ENTITY_NAME = QRI.JOB_NAME ' ||
'AND WE.ORGANIZATION_ID = QRI.ORGANIZATION_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'WIP_ENTITIES WO',
'QRI.WORK_ORDER = WO.WIP_ENTITY_NAME AND ' || 'QRI.ORGANIZATION_ID = WO.ORGANIZATION_ID',
'WORK_ORDER_ID = (SELECT WE.WIP_ENTITY_ID ' || 'FROM WIP_ENTITIES WE ' ||
'WHERE WE.WIP_ENTITY_NAME = QRI.WORK_ORDER ' ||
'AND WE.ORGANIZATION_ID = QRI.ORGANIZATION_ID AND WE.ENTITY_TYPE IN (6, 7))',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'PO_VENDORS PV',
'QRI.VENDOR_NAME = PV'||g_period||'VENDOR_NAME', -- bug 3554899.
'VENDOR_ID = (SELECT MIN(PV.VENDOR_ID) ' ||
'FROM PO_VENDORS PV ' ||
'WHERE PV.VENDOR_NAME = QRI.VENDOR_NAME)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'PO_POS_ALL_TRX_V PH',
'QRI.PO_NUMBER = PH.SEGMENT1',
'PO_HEADER_ID = (SELECT PH.PO_HEADER_ID ' ||
'FROM PO_POS_ALL_TRX_V ph ' ||
'WHERE ph.segment1 = qri.po_number AND ' ||
'(qri.operating_unit_id IS NULL ' ||
' OR qri.operating_unit_id = ph.org_id) AND ' ||
'rownum = 1)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'PO_RELEASES PR',
'PR.PO_HEADER_ID = QRI.PO_HEADER_ID AND PR.RELEASE_NUM = QRI.PO_RELEASE_NUM',
'PO_RELEASE_ID = (SELECT MIN(PR.PO_RELEASE_ID) ' ||
'FROM PO_RELEASES PR ' ||
'WHERE PR.PO_HEADER_ID = QRI.PO_HEADER_ID AND PR.RELEASE_NUM = QRI.PO_RELEASE_NUM)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I) );
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'QA_CUSTOMERS_LOV_V RC',
'QRI.CUSTOMER_NAME = RC.CUSTOMER_NAME AND ' ||
'RC.STATUS = ''A'' AND ' ||
'NVL(RC.CUSTOMER_PROSPECT_CODE, ''CUSTOMER'') = ' ||
'''CUSTOMER''',
'CUSTOMER_ID = (SELECT MIN(RC.CUSTOMER_ID) ' ||
'FROM QA_CUSTOMERS_LOV_V RC ' ||
'WHERE RC.CUSTOMER_NAME = QRI.CUSTOMER_NAME ' ||
'AND RC.STATUS = ''A'' AND ' ||
'NVL(RC.CUSTOMER_PROSPECT_CODE, ''CUSTOMER'') = ' ||
'''CUSTOMER'')',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
non-pjm enabled orgs).
rkaza, 11/10/2001.
*/
--
-- Bug 5249078. Changed pjm_projects_all_v to
-- pjm_projects_v for MOAC compliance.
-- bso Thu Jun 1 10:46:50 PDT 2006
--
I := POSITION_IN_TABLE('PROJECT_NUMBER', DEVELOPER_NAME_TABLE, NUM_ELEMS);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'PJM_PROJECTS_V PPAV',
'QRI.PROJECT_NUMBER = PPAV.PROJECT_NUMBER ',
'PROJECT_ID = (SELECT MIN(PPAV.PROJECT_ID) ' ||
'FROM PJM_PROJECTS_ALL_V PPAV ' ||
'WHERE PPAV.PROJECT_NUMBER = QRI.PROJECT_NUMBER ) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'PA_TASKS PT',
'QRI.TASK_NUMBER = PT.TASK_NUMBER AND '||
'QRI.PROJECT_ID = PT.PROJECT_ID ',
'TASK_ID = (SELECT MIN(PT.TASK_ID) ' ||
'FROM PA_TASKS PT ' ||
'WHERE PT.TASK_NUMBER = QRI.TASK_NUMBER '||
'AND PT.PROJECT_ID = ( Select MIN ( PPAV.PROJECT_ID) ' ||
'FROM PJM_PROJECTS_ALL_V PPAV ' ||
'WHERE PPAV.PROJECT_NUMBER = QRI.PROJECT_NUMBER ) ) ' ,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL,'PROJECT_NUMBER' );
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'GME_BATCH_HEADER GBH',
'QRI.PROCESS_BATCH_NUM = GBH.BATCH_NO AND QRI.ORGANIZATION_ID = GBH.ORGANIZATION_ID ',
'PROCESS_BATCH_ID = (SELECT MIN(GBH.BATCH_ID) ' ||
'FROM GME_BATCH_HEADER GBH ' ||
'WHERE GBH.BATCH_NO = QRI.PROCESS_BATCH_NUM AND '||
'GBH.ORGANIZATION_ID = QRI.ORGANIZATION_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'GME_BATCH_STEPS GBS',
'QRI.PROCESS_BATCHSTEP_NUM = GBS.BATCHSTEP_NO AND QRI.PROCESS_BATCH_ID = GBS.BATCH_ID ',
'PROCESS_BATCHSTEP_ID = (SELECT MIN (GBS.BATCHSTEP_ID) FROM GME_BATCH_STEPS GBS '||
'WHERE GBS.BATCHSTEP_NO = QRI.PROCESS_BATCHSTEP_NUM ' ||
'AND GBS.BATCH_ID = QRI.PROCESS_BATCH_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL,'PROCESS_BATCH_NUM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'GMO_BATCH_STEPS_V GBSV',
'QRI.PROCESS_OPERATION= GBSV.OPERATION AND '||
'QRI.PROCESS_BATCH_ID = GBSV.BATCH_ID AND '||
--
-- bug 5343944
-- the view GSBV has the column batchstep_id and
-- not batch_step_id. Similarly the table QRI has
-- column process_batchstep_id and not batch_step_id
-- Made necessary corrections
-- ntungare
--
-- 'QRI.BATCH_STEP_ID = GBSV.BATCH_STEP_ID ',
'QRI.PROCESS_BATCHSTEP_ID = GBSV.BATCHSTEP_ID ',
'PROCESS_OPERATION_ID = (SELECT MIN (GBSV.OPRN_ID) FROM '||
'GMO_BATCH_STEPS_V GBSV WHERE GBSV.OPERATION = '||
'QRI.PROCESS_OPERATION AND GBSV.BATCH_ID = QRI.PROCESS_BATCH_ID '||
'AND GBSV.BATCHSTEP_ID = QRI.PROCESS_BATCHSTEP_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),NULL,
'PROCESS_BATCH_NUM', 'PROCESS_BATCHSTEP_NUM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'GME_BATCH_STEP_ACTIVITIES GBSA',
'QRI.PROCESS_ACTIVITY = GBSA.ACTIVITY AND '||
'QRI.PROCESS_BATCH_ID = GBSA.BATCH_ID AND '||
'QRI.PROCESS_BATCHSTEP_ID = GBSA.BATCHSTEP_ID ',
'PROCESS_ACTIVITY_ID = (SELECT MIN (GBSA.BATCHSTEP_ACTIVITY_ID) '||
'FROM GME_BATCH_STEP_ACTIVITIES GBSA WHERE GBSA.ACTIVITY = '||
'QRI.PROCESS_ACTIVITY AND GBSA.BATCH_ID = QRI.PROCESS_BATCH_ID '||
'AND GBSA.BATCHSTEP_ID = QRI.PROCESS_BATCHSTEP_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),NULL,
'PROCESS_BATCH_NUM', 'PROCESS_BATCHSTEP_NUM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'GME_BATCH_STEP_RESOURCES GBSR',
'QRI.PROCESS_RESOURCE = GBSR.RESOURCES AND QRI.PROCESS_BATCH_ID = '||
'GBSR.BATCH_ID AND QRI.PROCESS_BATCHSTEP_ID = GBSR.BATCHSTEP_ID '||
'AND QRI.PROCESS_ACTIVITY_ID = GBSR.BATCHSTEP_ACTIVITY_ID ',
'PROCESS_RESOURCE_ID = (SELECT MIN (GBSR.BATCHSTEP_RESOURCE_ID) '||
'FROM GME_BATCH_STEP_RESOURCES GBSR WHERE GBSR.RESOURCES = '||
'QRI.PROCESS_RESOURCE AND GBSR.BATCH_ID = QRI.PROCESS_BATCH_ID '||
'AND GBSR.BATCHSTEP_ID = QRI.PROCESS_BATCHSTEP_ID AND '||
'GBSR.BATCHSTEP_ACTIVITY_ID = QRI.PROCESS_ACTIVITY_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),NULL,
'PROCESS_BATCH_NUM', 'PROCESS_BATCHSTEP_NUM', 'PROCESS_ACTIVITY');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE',
'QRI.PROCESS_PARAMETER = GP.PARAMETER_NAME AND GP.PARAMETER_ID = '||
'GE.PARAMETER_ID AND GE.BATCHSTEP_RESOURCE_ID = QRI.PROCESS_RESOURCE_ID ',
'PROCESS_PARAMETER_ID = (SELECT MIN (PARAMETER_ID) FROM '||
'GME_PROCESS_PARAMETERS GE WHERE '||
'GE.BATCHSTEP_RESOURCE_ID = '||
'QRI.PROCESS_RESOURCE_ID)',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),NULL,
'PROCESS_RESOURCE');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'WMS_LICENSE_PLATE_NUMBERS WLPN',
'QRI.LICENSE_PLATE_NUMBER = WLPN.LICENSE_PLATE_NUMBER ',
'LPN_ID = (SELECT WLPN.LPN_ID ' ||
'FROM WMS_LICENSE_PLATE_NUMBERS WLPN '||
'WHERE WLPN.LICENSE_PLATE_NUMBER = QRI.LICENSE_PLATE_NUMBER'
|| ' AND ROWNUM = 1) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'WMS_LICENSE_PLATE_NUMBERS WLPN1',
'QRI.XFR_LICENSE_PLATE_NUMBER = WLPN1.LICENSE_PLATE_NUMBER ',
'XFR_LPN_ID = (SELECT WLPN1.LPN_ID ' ||
'FROM WMS_LICENSE_PLATE_NUMBERS WLPN1 '||
'WHERE WLPN1.LICENSE_PLATE_NUMBER = QRI.XFR_LICENSE_PLATE_NUMBER'
|| ' AND ROWNUM = 1) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'OKE_K_HEADERS_LOV_V OKEH',
'QRI.CONTRACT_NUMBER = OKEH.K_NUMBER ',
'CONTRACT_ID = (SELECT OKEH.K_HEADER_ID ' ||
'FROM OKE_K_HEADERS_LOV_V OKEH '||
'WHERE OKEH.K_NUMBER = QRI.CONTRACT_NUMBER ' ||
'AND ROWNUM = 1) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'OKE_K_LINES_FULL_V OKEL',
'QRI.CONTRACT_LINE_NUMBER = OKEL.LINE_NUMBER ',
'CONTRACT_LINE_ID = (SELECT OKEL.K_LINE_ID ' ||
'FROM OKE_K_LINES_FULL_V OKEL '||
'WHERE OKEL.LINE_NUMBER = QRI.CONTRACT_LINE_NUMBER ' ||
'AND ROWNUM = 1) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
null, 'CONTRACT_NUMBER');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'OKE_K_DELIVERABLES_VL OKED',
'QRI.DELIVERABLE_NUMBER = OKED.DELIVERABLE_NUM ',
'DELIVERABLE_ID = (SELECT OKED.DELIVERABLE_ID ' ||
'FROM OKE_K_DELIVERABLES_VL OKED '||
'WHERE OKED.DELIVERABLE_NUM = QRI.DELIVERABLE_NUMBER ' ||
'AND ROWNUM = 1) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
null, 'CONTRACT_LINE_NUMBER', 'CONTRACT_NUMBER');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'qa_sales_orders_lov_v SH',
'to_char(QRI.SALES_ORDER) = SH.ORDER_NUMBER ',
'SO_HEADER_ID = (select sales_order_id from qa_sales_orders_lov_v'||
' where to_char(qri.sales_order) = order_number and rownum = 1) ',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'oe_order_headers SH2',
'QRI.RMA_NUMBER = SH2.ORDER_NUMBER AND ' ||
'SH2.order_category_code in (''RETURN'',''MIXED'')',
'RMA_HEADER_ID = (SELECT MIN(SH2.HEADER_ID) ' ||
'FROM oe_order_headers SH2 ' ||
'WHERE SH2.ORDER_NUMBER = QRI.RMA_NUMBER ' ||
'AND SH2.order_category_code in (''RETURN'',''MIXED''))',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'RCV_RECEIPTS_ALL_V RRA',
'QRI.RECEIPT_NUM = RRA.RECEIPT_NUM', NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));*/
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, '(SELECT DISTINCT RCVSH.RECEIPT_NUM
FROM RCV_SHIPMENT_HEADERS RCVSH, PO_VENDORS POV, RCV_TRANSACTIONS RT
WHERE RCVSH.RECEIPT_SOURCE_CODE in (''VENDOR'',''CUSTOMER'') AND
RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID) RRA',
'QRI.RECEIPT_NUM = RRA.RECEIPT_NUM', NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'HZ_PARTIES HP',
'QRI.PARTY_NAME = HP.PARTY_NAME AND ' ||
'HP.STATUS = ''A'' AND ' ||
'PARTY_TYPE IN (''ORGANIZATION'',''PERSON'')',
'PARTY_ID = (SELECT MIN(HP.PARTY_ID) ' ||
'FROM HZ_PARTIES HP ' ||
'WHERE HP.PARTY_NAME = QRI.PARTY_NAME ' ||
'AND HP.STATUS = ''A'' AND ' ||
'PARTY_TYPE IN (''ORGANIZATION'',''PERSON''))',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'mtl_system_items_b_kfv msikfv, mtl_parameters mp',
'qri.asset_group = msikfv.concatenated_segments' ||
' and msikfv.organization_id = mp.organization_id' ||
' and msikfv.eam_item_type in (1,3) ' ||
' and mp.maint_organization_id = qri.organization_id',
'qri.asset_group_id = (SELECT msikfv.inventory_item_id ' ||
' FROM mtl_system_items_b_kfv msikfv ' ||
' WHERE msikfv.concatenated_segments = qri.asset_group ' ||
' and rownum=1)', --multiple identical ids may belong to same asset group
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),
NULL,
NULL);
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'csd_repairs CR',
'qri.repair_order_number = CR.repair_number',
'repair_line_id = (SELECT cr.repair_line_id
FROM csd_repairs CR
WHERE cr.repair_number = qri.repair_order_number
AND cr.status not in (''C'', ''H''))',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'jtf_tasks_vl jtv',
'qri.jtf_task_number = jtv.task_number',
'jtf_task_id = (SELECT jtv.task_id
FROM jtf_tasks_vl jtv
WHERE jtv.task_number = qri.jtf_task_number)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),
NULL,
NULL);
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'csi_item_instances cii ',
'qri.asset_instance_number = cii.instance_number ',
'qri.asset_instance_id = (SELECT cii.instance_id FROM ' ||
'csi_item_instances cii' ||
' WHERE cii.instance_number = qri.asset_instance_number)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),
NULL,
NULL);
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I),
NULL,
NULL);
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'qa_csi_item_instances cii',
'qri.item_instance = cii.instance_number',
'csi_instance_id = (SELECT cii.instance_id FROM ' ||
'qa_csi_item_instances cii, mtl_system_items_kfv msik ' ||
'WHERE cii.inventory_item_id = msik.inventory_item_id AND ' ||
'cii.last_vld_organization_id = msik.organization_id AND ' ||
'cii.instance_number = qri.item_instance)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'cs_counters cc',
'qri.counter_name = cc.name',
'counter_id = (SELECT cc.counter_id FROM ' ||
'cs_counters cc, cs_counter_groups ccg WHERE ' ||
'cc.counter_group_id = ccg.counter_group_id AND ' ||
'ccg.template_flag = ''N'' AND cc.name = qri.counter_name)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'qa_ahl_mr amr',
'qri.maintenance_requirement = amr.title AND qri.version_number = amr.version_number',
'ahl_mr_id = (SELECT amr.mr_header_id FROM ' ||
'qa_ahl_mr amr WHERE qri.maintenance_requirement = amr.title ' ||
'AND qri.version_number = amr.version_number)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'cs_incidents ci',
'qri.service_request = ci.incident_number',
'cs_incident_id = (SELECT ci.incident_id FROM ' ||
'cs_incidents ci WHERE ' ||
'ci.incident_number = qri.service_request)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I),
READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'wip_discrete_jobs_all_v we2',
'qri.rework_job = we2.wip_entity_name AND ' ||
'qri.organization_id = we2.organization_id',
'wip_rework_id = (SELECT we2.wip_entity_id FROM ' ||
'wip_discrete_jobs_all_v we2 WHERE ' ||
'qri.rework_job = we2.wip_entity_name ' ||
'AND qri.organization_id = we2.organization_id)',
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
NULL,
NULL,
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'oe_order_lines_all oel, oe_order_headers_all oeha',
' oel.header_id = oeha.header_id and '||
' oeha.order_number = qri.sales_order and '||
' oel.line_number = nvl(qri.CHARACTER'|| TO_CHAR(J)||',-1) ',
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL);
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
' oe_order_lines oel, so_order_types sot, oe_order_headers sh',
' sh.order_type_id = sot.order_type_id and '||
' oel.header_id = sh.header_id and '||
' oel.line_category_code IN (''RETURN'', ''MIXED'') and '||
' sh.order_number = qri.rma_number and '||
' oel.line_number = nvl(qri.CHARACTER'|| TO_CHAR(J)||',-1)',
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
P_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
P_REQUEST_ID => X_REQUEST_ID,
P_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => X_PROGRAM_ID,
P_CHAR_NAME => CHAR_NAME_TABLE(I),
P_TXN_TYPE => TYPE_OF_TXN);
DERIVE_SEQUENCE(X_GROUP_ID, X_USER_ID, X_LAST_UPDATE_LOGIN, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, 'SEQUENCE'||to_char(J),
CHAR_ID_TABLE(I), TYPE_OF_TXN);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
null,'ITEM',ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, ' PO_LINES_VAL_TRX_V PLVV',
'QRI.PO_LINE_NUM = PLVV.LINE_NUM AND ' ||
'QRI.PO_HEADER_ID = PLVV.PO_HEADER_ID AND ' ||
'QRI.PO_LINE_NUM IS NOT NULL',
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'PO_NUMBER');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_ITEM_UOMS_VIEW MIUV2',
'QRI.COMP_UOM = MIUV2.UOM_CODE AND ' ||
'QRI.COMP_ITEM_ID = MIUV2.INVENTORY_ITEM_ID AND ' ||
'QRI.ORGANIZATION_ID = MIUV2.ORGANIZATION_ID',
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'COMP_ITEM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_ITEM_REVISIONS MIR',
'QRI.COMP_REVISION = MIR.REVISION AND ' ||
'QRI.COMP_ITEM_ID = MIR.INVENTORY_ITEM_ID AND ' ||
'QRI.ORGANIZATION_ID = MIR.ORGANIZATION_ID',
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'COMP_ITEM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL,
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'COMP_ITEM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL, CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'COMP_ITEM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL, CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'COMP_ITEM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'WIP_OPERATIONS_ALL_V WOAV',
'WOAV.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
'AND WOAV.WIP_ENTITY_ID = QRI.WIP_ENTITY_ID ' ||
'AND WOAV.OPERATION_SEQ_NUM = QRI.FROM_OP_SEQ_NUM ' ||
'AND (QRI.LINE_ID IS NULL OR ' ||
'WOAV.REPETITIVE_SCHEDULE_ID = ' ||
'(SELECT REPETITIVE_SCHEDULE_ID ' ||
'FROM WIP_FIRST_OPEN_SCHEDULE_V ' ||
'WHERE ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
'AND WIP_ENTITY_ID = QRI.WIP_ENTITY_ID ' ||
'AND LINE_ID = QRI.LINE_ID))',
NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'JOB_NAME', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'WIP_OPERATIONS_ALL_V WOAV',
'WOAV.ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
'AND WOAV.WIP_ENTITY_ID = QRI.WIP_ENTITY_ID ' ||
'AND WOAV.OPERATION_SEQ_NUM = QRI.TO_OP_SEQ_NUM ' ||
'AND (QRI.LINE_ID IS NULL OR ' ||
'WOAV.REPETITIVE_SCHEDULE_ID = ' ||
'(SELECT REPETITIVE_SCHEDULE_ID ' ||
'FROM WIP_FIRST_OPEN_SCHEDULE_V ' ||
'WHERE ORGANIZATION_ID = QRI.ORGANIZATION_ID ' ||
'AND WIP_ENTITY_ID = QRI.WIP_ENTITY_ID ' ||
'AND LINE_ID = QRI.LINE_ID))',
NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'JOB_NAME', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_ITEM_UOMS_VIEW MIUV2',
'QRI.UOM = MIUV2.UOM_CODE AND ' ||
'QRI.ITEM_ID = MIUV2.INVENTORY_ITEM_ID AND ' ||
'QRI.ORGANIZATION_ID = MIUV2.ORGANIZATION_ID',
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_ITEM_REVISIONS MIR',
'QRI.REVISION = MIR.REVISION AND ' ||
'QRI.ITEM_ID = MIR.INVENTORY_ITEM_ID AND ' ||
'QRI.ORGANIZATION_ID = MIR.ORGANIZATION_ID',
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL,
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL,
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL, CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL, CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_MATERIAL_STATUSES MMS, MTL_LOT_NUMBERS MLN',
'MMS.STATUS_CODE = QRI.LOT_STATUS AND MMS.ENABLED_FLAG = 1 '||
' AND MLN.LOT_NUMBER = QRI.LOT_NUMBER AND MLN.STATUS_ID = MMS.STATUS_ID',
'LOT_STATUS_ID = (SELECT MMS.STATUS_ID '||
' FROM MTL_LOT_NUMBERS MLN,MTL_MATERIAL_STATUSES MMS '||
' WHERE MLN.STATUS_ID = MMS.STATUS_ID '||
' AND MLN.LOT_NUMBER = QRI.LOT_NUMBER AND MMS.ENABLED_FLAG = 1 '||
' AND MLN.INVENTORY_ITEM_ID = QRI.ITEM_ID )',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_MATERIAL_STATUSES MMS, MTL_SERIAL_NUMBERS MSN',
'MMS.STATUS_CODE = QRI.SERIAL_STATUS AND MMS.ENABLED_FLAG = 1 '||
' AND MSN.SERIAL_NUMBER = QRI.SERIAL_NUMBER AND MSN.STATUS_ID = MMS.STATUS_ID',
'SERIAL_STATUS_ID = (SELECT MMS.STATUS_ID '||
' FROM MTL_SERIAL_NUMBERS MSN,MTL_MATERIAL_STATUSES MMS '||
' WHERE MSN.STATUS_ID = MMS.STATUS_ID '||
' AND MSN.SERIAL_NUMBER = QRI.SERIAL_NUMBER AND MMS.ENABLED_FLAG = 1 '||
' AND MSN.INVENTORY_ITEM_ID = QRI.ITEM_ID )',
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL,
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'COMP_SUBINVENTORY', 'COMP_ITEM');
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'PO_SHIPMENTS_ALL_V PSAV',
'QRI.PO_SHIPMENT_NUM = PSAV.SHIPMENT_NUM AND ' ||
'PSAV.PO_LINE_ID = (SELECT PO_LINE_ID FROM ' ||
'PO_LINES_VAL_TRX_V WHERE LINE_NUM = QRI.PO_LINE_NUM ' ||
'AND PO_HEADER_ID = QRI.PO_HEADER_ID)',
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'PO_LINE_NUM', 'PO_NUMBER');
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
'WIP_OPERATIONS_ALL_V WOAV',
'woav.organization_id = qri.organization_id AND ' ||
'woav.wip_entity_id = qri.work_order_id AND ' ||
'woav.operation_seq_num = qri.maintenance_op_seq',
NULL,
CHAR_ID_TABLE(I),
CHAR_NAME_TABLE(I),
DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I),
X_PLAN_ID,
VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL,
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'SUBINVENTORY', 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL,
NULL, CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
NULL, 'TO_SUBINVENTORY', 'ITEM', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I),
'FROM_OP_SEQ_NUM', 'JOB_NAME', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, NULL, NULL, NULL,
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),
SQL_VALIDATION_STRING_TABLE(I),
'TO_OP_SEQ_NUM', 'JOB_NAME', ITEM_PARENT);
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_ITEM_REVISIONS MIR',
'MIR.REVISION = QRI.'||RESULT_COLUMN_NAME_TABLE(I)||
' AND MIR.INVENTORY_ITEM_ID = QRI.BILL_REFERENCE_ID'||
' AND MIR.ORGANIZATION_ID = QRI.ORGANIZATION_ID',
RESULT_COLUMN_NAME_TABLE(I)|| '= QRI.'||RESULT_COLUMN_NAME_TABLE(I),
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I));
X_LAST_UPDATE_LOGIN, X_REQUEST_ID, X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, 'MTL_RTG_ITEM_REVISIONS MRIR',
'MRIR.PROCESS_REVISION = QRI.'||RESULT_COLUMN_NAME_TABLE(I)||
' AND MRIR.INVENTORY_ITEM_ID = QRI.ROUTING_REFERENCE_ID'||
' AND MRIR.ORGANIZATION_ID = QRI.ORGANIZATION_ID',
RESULT_COLUMN_NAME_TABLE(I)|| '= QRI.'||RESULT_COLUMN_NAME_TABLE(I),
CHAR_ID_TABLE(I), CHAR_NAME_TABLE(I), DATATYPE_TABLE(I),
DECIMAL_PRECISION_TABLE(I), X_PLAN_ID, VALUES_EXIST_FLAG_TABLE(I), READ_ONLY_FLAG_TABLE(I),NULL);
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID, X_LAST_UPDATE_LOGIN);
X_LAST_UPDATE_LOGIN NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER) IS
X_PLAN_ID NUMBER;
SELECT MAX(PLAN_ID)
INTO X_PLAN_ID
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
FOR RESREC IN (SELECT QPC.CHAR_ID, QPC.RESULT_COLUMN_NAME, QC.DATATYPE
FROM QA_PLAN_CHARS QPC, qa_chars qc
WHERE QPC.PLAN_ID = X_PLAN_ID
AND QC.CHAR_ID = QPC.CHAR_ID ) LOOP
NUM_COLS := NUM_COLS + 1;
SQL_STATEMENT := 'INSERT INTO QA_RESULTS_UPDATE_HISTORY ' ||
'(OCCURRENCE,UPDATE_ID,CREATION_DATE,CREATED_BY,' ||
' LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,' ||
' TXN_HEADER_ID,CHAR_ID,OLD_VALUE,REQUEST_ID,' ||
' PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE) ' ||
'SELECT QR.OCCURRENCE, QA_RESULTS_UPDATE_HISTORY_S.nextval, ' ||
'sysdate, :USER_ID, sysdate, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
':TXN_HEADER_ID, :RESULT_COLUMN_ID' ||
', QR.' || RESULT_COLUMN_NAME_TABLE(J) || ', ' ||
':REQUEST_ID, :PROGRAM_APPLICATION_ID, ' ||
':PROGRAM_ID, sysdate ' ||
'FROM QA_RESULTS QR, ' ||
'QA_RESULTS_INTERFACE QRI ' ||
'WHERE QR.ROWID = :BIND_ROWID ' ||
' AND QRI.GROUP_ID = :GROUP_ID ' ||
' AND (QR.' || RESULT_COLUMN_NAME_TABLE(J) || ' <> QRI.' || RESULT_COLUMN_NAME_TABLE(J)||
' OR (QR.' || RESULT_COLUMN_NAME_TABLE(J)|| ' IS NOT NULL AND ' ||
'QRI.' || RESULT_COLUMN_NAME_TABLE(J)|| ' IS NULL )' ||
' OR (QRI.' || RESULT_COLUMN_NAME_TABLE(J)|| ' IS NOT NULL AND ' ||
'QR.' || RESULT_COLUMN_NAME_TABLE(J)|| ' IS NULL ))';
X_LAST_UPDATE_LOGIN,
X_TXN_HEADER_ID,
RESULT_COLUMN_ID_TABLE(J),
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
L_ROWID,
X_GROUP_ID;
PROCEDURE UPDATE_VALID_ROWS(X_GROUP_ID NUMBER,
X_USER_ID NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_TXN_HEADER_ID NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
STMT_OF_ROWIDS VARCHAR2) IS
X_PLAN_ID NUMBER;
SELECT result_column_name
FROM qa_plan_chars
WHERE plan_id = (SELECT plan_id from qa_results_interface
WHERE TRANSACTION_INTERFACE_ID = X_TRANSACTION_INTERFACE_ID)
AND (result_column_name like 'CHARACTER%' OR
result_column_name like 'COMMENT%');
qri_update_str_tab DBMS_SQL.VARCHAR2S;
update_qri_cur PLS_INTEGER;
update_qri_status NUMBER;
SELECT MAX(TRANSACTION_INTERFACE_ID)
INTO X_TRANSACTION_INTERFACE_ID
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
SELECT MAX(PLAN_ID)
INTO X_PLAN_ID
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.TRANSACTION_INTERFACE_ID = X_TRANSACTION_INTERFACE_ID
AND PROCESS_STATUS = 2;
'SELECT TRANSACTION_INTERFACE_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID )';
UPDATE QA_RESULTS_INTERFACE QRI
SET SEQUENCE1 = G_SEQ_TAB1(J),
SEQUENCE2 = G_SEQ_TAB2(J),
SEQUENCE3 = G_SEQ_TAB3(J),
SEQUENCE4 = G_SEQ_TAB4(J),
SEQUENCE5 = G_SEQ_TAB5(J),
SEQUENCE6 = G_SEQ_TAB6(J),
SEQUENCE7 = G_SEQ_TAB7(J),
SEQUENCE8 = G_SEQ_TAB8(J),
SEQUENCE9 = G_SEQ_TAB9(J),
SEQUENCE10 = G_SEQ_TAB10(J),
SEQUENCE11 = G_SEQ_TAB11(J),
SEQUENCE12 = G_SEQ_TAB12(J),
SEQUENCE13 = G_SEQ_TAB13(J),
SEQUENCE14 = G_SEQ_TAB14(J),
SEQUENCE15 = G_SEQ_TAB15(J)
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.TRANSACTION_INTERFACE_ID = INTERFACE_ID_TABLE(J)
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID);
FOR COLUMN_NAMES_REC IN (SELECT QPC.RESULT_COLUMN_NAME
FROM QA_PLAN_CHARS QPC,
QA_CHARS QC
WHERE QPC.PLAN_ID = X_PLAN_ID
AND QPC.CHAR_ID = QC.CHAR_ID) LOOP
I := I + 1;
COLUMN_LIST := 'LAST_UPDATE_DATE, QA_LAST_UPDATE_DATE, ' ||
'LAST_UPDATED_BY, QA_LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ' ||
'TXN_HEADER_ID, REQUEST_ID, ' ||
'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,' ||
'MTI_TRANSACTION_HEADER_ID,' ||
'MTI_TRANSACTION_INTERFACE_ID,' ||
'MMT_TRANSACTION_ID,' ||
'WJSI_GROUP_ID,' ||
'WMTI_GROUP_ID,' ||
'WMT_TRANSACTION_ID,' ||
'RTI_INTERFACE_TRANSACTION_ID '
;
'NVL(qri.QA_LAST_UPDATED_BY,qr.QA_LAST_UPDATED_BY),' ||
':LAST_UPDATE_LOGIN, :TXN_HEADER_ID, :REQUEST_ID, :PROGRAM_APPLICATION_ID, ' ||
':PROGRAM_ID, SYSDATE, ' ||
'NVL(qri.MTI_TRANSACTION_HEADER_ID, qr.MTI_TRANSACTION_HEADER_ID), ' ||
'NVL(qri.MTI_TRANSACTION_INTERFACE_ID, qr.MTI_TRANSACTION_INTERFACE_ID), ' ||
'NVL(qri.MMT_TRANSACTION_ID, qr.MMT_TRANSACTION_ID), ' ||
'NVL(qri.WJSI_GROUP_ID, qr.WJSI_GROUP_ID), ' ||
'NVL(qri.WMTI_GROUP_ID, qr.WMTI_GROUP_ID), ' ||
'NVL(qri.WMT_TRANSACTION_ID, qr.WMT_TRANSACTION_ID), ' ||
'NVL(qri.RTI_INTERFACE_TRANSACTION_ID, qr.RTI_INTERFACE_TRANSACTION_ID) '
;
SQL_STATEMENT := 'UPDATE QA_RESULTS QR SET (' || COLUMN_LIST || ') = ' ||
'(SELECT ' || VALUE_LIST ||
' FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.TRANSACTION_INTERFACE_ID = :TRANSACTION_INTERFACE_ID ' ||
') WHERE ROWID = :BIND_ROWID';
SELECT 1 INTO DUMMY FROM qa_results where rowid = l_rowid FOR UPDATE NOWAIT;
SELECT 1 INTO DUMMY FROM qa_results
WHERE '('''||rowid||''')' = stmt_of_rowids
FOR UPDATE NOWAIT;
X_LAST_UPDATE_LOGIN,
X_TXN_HEADER_ID,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_TRANSACTION_INTERFACE_ID,
L_ROWID;
qri_update_str_tab(1) := 'UPDATE qa_results SET ';
qri_update_str_tab(cntr+1) := sc_elements_tab(cntr) ||
' = CASE WHEN '||sc_elements_tab(cntr)||
' in (to_char(:null_num), :null_char, to_char(:null_date, ''DD-MON-YYYY HH24:MI:SS'')) then null else '||
sc_elements_tab(cntr) ||' end ';
qri_update_str_tab(cntr+1) := ' , ' || sc_elements_tab(cntr) ||
' = CASE WHEN '||sc_elements_tab(cntr)||
' in (to_char(:null_num), :null_char, to_char(:null_date, ''DD-MON-YYYY HH24:MI:SS'')) then null else '||
sc_elements_tab(cntr) ||' end ';
qri_update_str_tab(NVL(qri_update_str_tab.LAST, 0)+1) := ' where ROWID = :BIND_ROWID';
update_qri_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(update_qri_cur, qri_update_str_tab, qri_update_str_tab.FIRST, qri_update_str_tab.LAST, TRUE, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE( update_qri_cur, ':null_num' , null_num );
DBMS_SQL.BIND_VARIABLE( update_qri_cur, ':null_char', null_char );
DBMS_SQL.BIND_VARIABLE( update_qri_cur, ':null_date', null_date );
DBMS_SQL.BIND_VARIABLE( update_qri_cur, ':BIND_ROWID', L_ROWID );
update_qri_status := DBMS_SQL.EXECUTE( update_qri_cur );
DBMS_SQL.CLOSE_CURSOR(update_qri_cur);
INSERT INTO QA_INTERFACE_ERRORS
(TRANSACTION_INTERFACE_ID, ERROR_MESSAGE, ERROR_COLUMN,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT TRANSACTION_INTERFACE_ID, ERROR_BUSY, NULL,
SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = X_TRANSACTION_INTERFACE_ID;
QLTTRAMB.qa_import_log('Exception in update valid rows. Resource busy Error count :' ||sql%rowcount);
UPDATE QA_RESULTS_INTERFACE
SET PROCESS_STATUS = 3
WHERE TRANSACTION_INTERFACE_ID = X_TRANSACTION_INTERFACE_ID;
END UPDATE_VALID_ROWS;
PROCEDURE update_no_validate(x_group_id IN NUMBER,
type_of_txn IN NUMBER,
stmt_of_rowids OUT NOCOPY VARCHAR2) AS
X_USER_ID NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_LOGIN := who_last_update_login;
SELECT USER_NAME
INTO X_USER_NAME
FROM FND_USER_VIEW
WHERE USER_ID = X_USER_ID;
UPDATE QA_RESULTS_INTERFACE QRI
SET QA_CREATED_BY = X_USER_ID,
QA_CREATED_BY_NAME = X_USER_NAME,
QA_LAST_UPDATED_BY = X_USER_ID,
QA_LAST_UPDATED_BY_NAME = X_USER_NAME,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = X_USER_ID,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
ORGANIZATION_ID =
(SELECT MIN(OOD.ORGANIZATION_ID)
FROM MTL_PARAMETERS OOD
WHERE OOD.ORGANIZATION_CODE = QRI.ORGANIZATION_CODE)
WHERE GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
UPDATE QA_RESULTS_INTERFACE QRI
SET -- plan_id
PLAN_ID =
(SELECT MIN(QP.PLAN_ID)
FROM QA_PLANS QP
WHERE QP.NAME = QRI.PLAN_NAME
AND QP.ORGANIZATION_ID = QRI.ORGANIZATION_ID),
-- spec_id
SPEC_ID =
(SELECT MIN(QSVV.SPEC_ID)
FROM QA_SPECS QSVV
WHERE QSVV.SPEC_NAME = QRI.SPEC_NAME
AND QSVV.ORGANIZATION_ID = QRI.ORGANIZATION_ID
AND trunc(sysdate) BETWEEN
nvl(trunc(qsvv.effective_from), trunc(sysdate)) AND
nvl(trunc(qsvv.effective_to), trunc(sysdate)))
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.PROCESS_STATUS = 2;
SELECT MAX(PLAN_ID)
INTO X_PLAN_ID
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID;
SELECT
qpc.char_id,
upper(replace(qc.name, ' ', '_')),
qc.hardcoded_column,
qc.developer_name,
qpc.result_column_name,
qpc.enabled_flag,
qpc.mandatory_flag,
qc.datatype,
nvl(qpc.decimal_precision,qc.decimal_precision),
qc.sql_validation_string,
qpc.values_exist_flag,
qpc.read_only_flag
BULK COLLECT INTO
char_id_table,
char_name_table,
hardcoded_column_table,
developer_name_table,
result_column_name_table,
enabled_flag_table,
mandatory_flag_table,
datatype_table,
decimal_precision_table,
sql_validation_string_table,
values_exist_flag_table,
read_only_flag_table
FROM
qa_chars qc,
qa_plan_chars qpc
WHERE
qpc.plan_id = x_plan_id AND
qpc.char_id = qc.char_id;
COPY_STMT_OF_ROWIDS := VALIDATE_UPDATE_TYPE_RECORDS(X_GROUP_ID,
X_PLAN_ID,
CHAR_NAME_TABLE,
DEVELOPER_NAME_TABLE,
HARDCODED_COLUMN_TABLE,
DATATYPE_TABLE,
NUM_ELEMS,
X_USER_ID,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID);
RETRIEVE_UPDATE_RECORDS(X_GROUP_ID,
COPY_STMT_OF_ROWIDS,
DEVELOPER_NAME_TABLE,
RESULT_COLUMN_NAME_TABLE,
DATATYPE_TABLE,
CHAR_NAME_TABLE,
NUM_ELEMS,
X_PLAN_ID,
READ_ONLY_FLAG_TABLE,
ENABLED_FLAG_TABLE);
END update_no_validate;
X_LAST_UPDATE_LOGIN NUMBER,
X_TXN_HEADER_ID NUMBER,
X_REQUEST_ID NUMBER,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER) IS
X_PLAN_ID NUMBER;
SELECT MAX(PLAN_ID)
INTO X_PLAN_ID
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
'SELECT TRANSACTION_INTERFACE_ID ' ||
'FROM QA_RESULTS_INTERFACE QRI ' ||
'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
' AND QRI.PROCESS_STATUS = 2 ' ||
' AND NOT EXISTS
(SELECT ''X'' ' ||
'FROM QA_INTERFACE_ERRORS QIE ' ||
'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
'QRI.TRANSACTION_INTERFACE_ID )';
UPDATE QA_RESULTS_INTERFACE QRI
SET SEQUENCE1 = G_SEQ_TAB1(J),
SEQUENCE2 = G_SEQ_TAB2(J),
SEQUENCE3 = G_SEQ_TAB3(J),
SEQUENCE4 = G_SEQ_TAB4(J),
SEQUENCE5 = G_SEQ_TAB5(J),
SEQUENCE6 = G_SEQ_TAB6(J),
SEQUENCE7 = G_SEQ_TAB7(J),
SEQUENCE8 = G_SEQ_TAB8(J),
SEQUENCE9 = G_SEQ_TAB9(J),
SEQUENCE10 = G_SEQ_TAB10(J),
SEQUENCE11 = G_SEQ_TAB11(J),
SEQUENCE12 = G_SEQ_TAB12(J),
SEQUENCE13 = G_SEQ_TAB13(J),
SEQUENCE14 = G_SEQ_TAB14(J),
SEQUENCE15 = G_SEQ_TAB15(J)
WHERE QRI.GROUP_ID = X_GROUP_ID
AND QRI.TRANSACTION_INTERFACE_ID = INTERFACE_ID_TABLE(J)
AND NOT EXISTS
(SELECT 'X'
FROM QA_INTERFACE_ERRORS QIE
WHERE QIE.TRANSACTION_INTERFACE_ID = QRI.TRANSACTION_INTERFACE_ID);
FOR CHARREC IN (SELECT RESULT_COLUMN_NAME
FROM QA_PLAN_CHARS
WHERE PLAN_ID = X_PLAN_ID) LOOP
I := I + 1;
COLUMN_LIST := 'COLLECTION_ID, OCCURRENCE, LAST_UPDATE_DATE, ' ||
'QA_LAST_UPDATE_DATE, LAST_UPDATED_BY, QA_LAST_UPDATED_BY, ' ||
'CREATION_DATE, QA_CREATION_DATE, CREATED_BY, QA_CREATED_BY, ' ||
'LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, ' ||
'PROGRAM_ID, PROGRAM_UPDATE_DATE, ' ||
'TXN_HEADER_ID, ' ||
'ORGANIZATION_ID, PLAN_ID, SPEC_ID,' ||
'MTI_TRANSACTION_HEADER_ID,' ||
'MTI_TRANSACTION_INTERFACE_ID,' ||
'MMT_TRANSACTION_ID,' ||
'WJSI_GROUP_ID,' ||
'WMTI_GROUP_ID,' ||
'WMT_TRANSACTION_ID,' ||
'RTI_INTERFACE_TRANSACTION_ID ' ;
'SYSDATE, :USER_ID, QA_LAST_UPDATED_BY, ' ||
'SYSDATE, SYSDATE, :USER_ID2, QA_CREATED_BY, ' ||
':LAST_UPDATE_LOGIN, :REQUEST_ID, :PROGRAM_APPLICATION_ID, ' ||
':PROGRAM_ID, SYSDATE,:TXN_HEADER_ID ' ||
', ORGANIZATION_ID, PLAN_ID, NVL(SPEC_ID, 0),' ||
'MTI_TRANSACTION_HEADER_ID,' ||
'MTI_TRANSACTION_INTERFACE_ID,' ||
'MMT_TRANSACTION_ID,' ||
'WJSI_GROUP_ID,' ||
'WMTI_GROUP_ID,' ||
'WMT_TRANSACTION_ID,' ||
'RTI_INTERFACE_TRANSACTION_ID ' ;
SQL_STATEMENT := 'INSERT INTO QA_RESULTS (' || COLUMN_LIST ||
') SELECT ' || VALUE_LIST || ' FROM QA_RESULTS_INTERFACE ' ||
'WHERE GROUP_ID = :GROUP_ID ' ||
' AND PROCESS_STATUS = 2';
QLTTRAMB.qa_import_log('Insert SQL :'||Substr(SQL_STATEMENT,1,3900));
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_TXN_HEADER_ID,
X_GROUP_ID;
QLTTRAMB.qa_import_log('After executing insert');
X_LAST_UPDATE_LOGIN NUMBER;
STMT_OF_ROWIDS VARCHAR2(10000); -- For update capabilities.
CURSOR C IS SELECT MARKER FROM QA_RESULTS_INTERFACE
WHERE GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2
AND MARKER IS NOT NULL;
X_LAST_UPDATE_LOGIN := who_last_update_login;
UPDATE QA_RESULTS_INTERFACE
SET PROCESS_STATUS = 2,
REQUEST_ID = X_REQUEST_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = X_USER_ID,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE GROUP_ID = X_GROUP_ID;
QLTTRAMB.qa_import_log('Updated to running status for group_id = '||X_GROUP_ID);
DELETE FROM QA_INTERFACE_ERRORS
WHERE TRANSACTION_INTERFACE_ID IN
(SELECT TRANSACTION_INTERFACE_ID
FROM QA_RESULTS_INTERFACE
WHERE GROUP_ID = X_GROUP_ID);
update_no_validate(X_GROUP_ID,TYPE_OF_TXN,STMT_OF_ROWIDS);
QLTTRAMB.qa_import_log('type of transaction = insert, setting who columns');
SELECT QA_COLLECTION_ID_S.NEXTVAL INTO X_COLLECTION_ID FROM DUAL;
UPDATE QA_RESULTS_INTERFACE
SET COLLECTION_ID = NVL(COLLECTION_ID, X_COLLECTION_ID),
QA_CREATED_BY = NVL(QA_CREATED_BY, X_USER_ID),
QA_LAST_UPDATED_BY = NVL(QA_LAST_UPDATED_BY, X_USER_ID)
WHERE GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
QLTTRAMB.qa_import_log('type of transaction = update, setting who columns');
UPDATE QA_RESULTS_INTERFACE
SET QA_CREATED_BY = NVL(QA_CREATED_BY, X_USER_ID),
QA_LAST_UPDATED_BY = NVL(QA_LAST_UPDATED_BY, X_USER_ID)
WHERE GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
QLTTRAMB.qa_import_log('validation completed, inserting data now');
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL INTO X_TXN_HEADER_ID FROM DUAL;
STMT_OF_ROWIDS, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,X_PROGRAM_APPLICATION_ID,X_PROGRAM_ID);
QLTTRAMB.qa_import_log('Calling UPDATE_VALID_ROWS');
UPDATE_VALID_ROWS(X_GROUP_ID, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_TXN_HEADER_ID, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID, STMT_OF_ROWIDS);
TRANSFER_VALID_ROWS(X_GROUP_ID, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_TXN_HEADER_ID, X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID);
select 1
into action_child_flag
from QA_PC_PLAN_RELATIONSHIP ppr,
qa_pc_criteria qpct,
qa_plan_char_actions pca,
qa_plan_char_action_triggers qpcat
where ppr.parent_plan_id = qpcat.plan_id
and ppr.plan_relationship_id = qpct.plan_relationship_id
and ppr.data_entry_mode in (2,4)
and qpct.char_id = pca.assigned_char_id
and pca.plan_char_action_trigger_id = qpcat.plan_char_action_trigger_id
and qpcat.plan_id in (SELECT DISTINCT plan_id
FROM qa_results_interface qri
WHERE qri.group_id = x_group_id
AND qri.process_status = 2)
and pca.action_id = 24
and ROWNUM =1;
SELECT DISTINCT PLAN_ID
BULK COLLECT INTO PLAN_ID_TABLE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
UPDATE_DELETE_QRI (X_REQUEST_ID,
X_USER_ID,
X_LAST_UPDATE_LOGIN,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
X_DEBUG);
INSERT_AUTO_HIST_CHILD (TYPE_OF_TXN,
PLAN_ID_TABLE(i),
X_TXN_HEADER_ID);
SELECT DISTINCT PLAN_ID
BULK COLLECT INTO PLAN_ID_TABLE
FROM QA_RESULTS_INTERFACE QRI
WHERE QRI.GROUP_ID = X_GROUP_ID
AND PROCESS_STATUS = 2;
SELECT to_number(argument1)
INTO x_txn_header_id
FROM FND_CONCURRENT_REQUESTS
WHERE priority_request_id = l_request_id
AND request_id <> priority_request_id;
UPDATE_DELETE_QRI (X_REQUEST_ID,
X_USER_ID,
X_LAST_UPDATE_LOGIN,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_GROUP_ID,
X_DEBUG);
INSERT_AUTO_HIST_CHILD (TYPE_OF_TXN,
PLAN_ID_TABLE(i),
X_TXN_HEADER_ID);
INSERT INTO qa_interface_errors
(transaction_interface_id, error_message, error_column,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
request_id, program_application_id, program_id,
program_update_date)
SELECT transaction_interface_id, substr(l_error_message,1, 240),
g_col_name, sysdate, x_user_id, sysdate, x_user_id,
x_last_update_login, x_request_id, x_program_application_id,
x_program_id, sysdate
FROM qa_results_interface
WHERE group_id = x_group_id;
UPDATE qa_results_interface
SET process_status = 3
WHERE group_id = x_group_id;
INSERT INTO QA_INTERFACE_ERRORS
(TRANSACTION_INTERFACE_ID, ERROR_MESSAGE, ERROR_COLUMN,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT TRANSACTION_INTERFACE_ID, ERROR_CRITICAL, NULL,
SYSDATE, X_USER_ID,
SYSDATE, X_USER_ID, X_LAST_UPDATE_LOGIN,
X_REQUEST_ID, X_PROGRAM_APPLICATION_ID, X_PROGRAM_ID,
SYSDATE
FROM QA_RESULTS_INTERFACE
WHERE GROUP_ID = X_GROUP_ID;
UPDATE QA_RESULTS_INTERFACE
SET PROCESS_STATUS = 3
WHERE GROUP_ID = X_GROUP_ID;
FUNCTION TRANSACTION_UPDATE(G_ID NUMBER,
VAL_FLAG NUMBER,
DEBUG VARCHAR2,
TYPE_OF_TXN NUMBER) RETURN BOOLEAN IS
NEW_GROUP_ID NUMBER;
CURSOR c1 IS SELECT rowid
FROM QA_RESULTS_INTERFACE
WHERE GROUP_ID = G_ID;
SELECT QA_GROUP_S.NEXTVAL INTO NEW_GROUP_ID FROM DUAL;
UPDATE QA_RESULTS_INTERFACE
SET GROUP_ID = NEW_GROUP_ID
WHERE rowid = rec.rowid;
-- Result will be set to true if ALL updates succeed.
-- (All updates will be processed even if some of them fail.)
RESULT := RESULT and TRANSACTION_WORKER(NEW_GROUP_ID,
VAL_FLAG, DEBUG, TYPE_OF_TXN);
END TRANSACTION_UPDATE;
who_last_update_login := to_number(argument7);
IF TYPE_OF_TXN = 1 THEN -- perform insert
X_RETURN := TRANSACTION_WORKER(TO_NUMBER(ARGUMENT1),
TO_NUMBER(ARGUMENT2), ARGUMENT3, TYPE_OF_TXN);
ELSE -- perform update
X_RETURN := TRANSACTION_UPDATE(TO_NUMBER(ARGUMENT1),
TO_NUMBER(ARGUMENT2), ARGUMENT3, TYPE_OF_TXN);
PROCEDURE UPDATE_DELETE_QRI(P_REQUEST_ID IN NUMBER,
P_USER_ID IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
P_PROGRAM_APPLICATION_ID IN NUMBER,
P_PROGRAM_ID IN NUMBER,
P_GROUP_ID IN NUMBER,
P_DEBUG IN VARCHAR2) IS
BEGIN
-- update process status to 4 for successful rows
UPDATE QA_RESULTS_INTERFACE
SET PROCESS_STATUS = 4,
REQUEST_ID = P_REQUEST_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID,
PROGRAM_ID = P_PROGRAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE GROUP_ID = P_GROUP_ID
AND PROCESS_STATUS = 2;
DELETE FROM QA_INTERFACE_ERRORS
WHERE TRANSACTION_INTERFACE_ID IN
(SELECT TRANSACTION_INTERFACE_ID
FROM QA_RESULTS_INTERFACE
WHERE PROCESS_STATUS = 4);
DELETE FROM QA_RESULTS_INTERFACE WHERE PROCESS_STATUS = 4;
END UPDATE_DELETE_QRI;
PROCEDURE INSERT_AUTO_HIST_CHILD (P_TYPE_OF_TXN IN NUMBER,
P_PLAN_ID IN NUMBER,
P_TXN_HEADER_ID IN NUMBER) IS
--
-- bug 13418140
-- Cursor to update the child records post
-- action firing
--
CURSOR res_cur IS
SELECT collection_id,occurrence,organization_id
FROM qa_results
WHERE plan_id = P_PLAN_ID
AND txn_header_id = P_TXN_HEADER_ID;
QA_PARENT_CHILD_PKG.insert_history_auto_rec(P_PLAN_ID, P_TXN_HEADER_ID, 1, 4) ;
QA_PARENT_CHILD_PKG.insert_history_auto_rec(P_PLAN_ID, P_TXN_HEADER_ID, 1, 2) ;
QA_PARENT_CHILD_PKG.insert_history_auto_rec(P_PLAN_ID, P_TXN_HEADER_ID, 1, 4) ;
l_ret_val := QA_PARENT_CHILD_PKG.update_all_children(P_PLAN_ID,
import_rec.collection_id,
import_rec.occurrence);
END INSERT_AUTO_HIST_CHILD;