eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EGO_IMPORT_BATCH_COMPARE_PVT dependencies on DBMS_SQL

Line 1082: cr_msi_intf := DBMS_SQL.OPEN_CURSOR;

1078:
1079: -- Preaparing dynamic sql cursor from query l_msii_sql
1080: -- to get data from interface table
1081: Debug_Message('Preparing SQL to get data from interface table for item primary and operational attributes.');
1082: cr_msi_intf := DBMS_SQL.OPEN_CURSOR;
1083:
1084: -- if the batch is PDH batch
1085: -- we need to match inventory_item_id or item number in interface table
1086: -- with the p_item1 or p_ss_record

Line 1120: DBMS_SQL.PARSE(cr_msi_intf, l_msii_sql, DBMS_SQL.native);

1116: Debug_Message(SUBSTR(l_msii_sql, ((l-1)*1000) + 1, 1000));
1117: END LOOP; --FOR l in 1..(CEIL(LENGTH(l_msii_sql)/1000)) LOOP
1118:
1119: Debug_Message('Parsing the SQL');
1120: DBMS_SQL.PARSE(cr_msi_intf, l_msii_sql, DBMS_SQL.native);
1121: Debug_Message('Done Parsing the SQL');
1122:
1123: -- Defining columns for l_msii_sql
1124: -- First column will be number as l_msii_sql has inventory_item_id as first selected column always.

Line 1127: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf ,1 ,l_num_value);

1123: -- Defining columns for l_msii_sql
1124: -- First column will be number as l_msii_sql has inventory_item_id as first selected column always.
1125: -- depending on the metadata of primary and operational attributes, we define here the type of column
1126: Debug_Message('Defining columns of SQL');
1127: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf ,1 ,l_num_value);
1128: l_count := l_attr_data_tbl.LAST;
1129: Debug_Message('Total columns = '||TO_CHAR(l_count));
1130: FOR i IN 1..l_count LOOP
1131: l_fmt := l_attr_data_tbl(i).DATA_TYPE_CODE ;

Line 1133: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_num_value);

1129: Debug_Message('Total columns = '||TO_CHAR(l_count));
1130: FOR i IN 1..l_count LOOP
1131: l_fmt := l_attr_data_tbl(i).DATA_TYPE_CODE ;
1132: IF l_fmt = G_NUMBER_FORMAT THEN
1133: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_num_value);
1134: ELSIF l_fmt = G_CHAR_FORMAT THEN
1135: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_str_value, 4000);
1136: ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1137: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_date_value);

Line 1135: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_str_value, 4000);

1131: l_fmt := l_attr_data_tbl(i).DATA_TYPE_CODE ;
1132: IF l_fmt = G_NUMBER_FORMAT THEN
1133: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_num_value);
1134: ELSIF l_fmt = G_CHAR_FORMAT THEN
1135: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_str_value, 4000);
1136: ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1137: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_date_value);
1138: END IF; --IF l_fmt = G_NUMBER_FORMAT THEN
1139: END LOOP; --FOR i IN 1..l_count LOOP

Line 1137: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_date_value);

1133: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_num_value);
1134: ELSIF l_fmt = G_CHAR_FORMAT THEN
1135: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_str_value, 4000);
1136: ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1137: DBMS_SQL.DEFINE_COLUMN(cr_msi_intf, i + l_default_sel, l_date_value);
1138: END IF; --IF l_fmt = G_NUMBER_FORMAT THEN
1139: END LOOP; --FOR i IN 1..l_count LOOP
1140: Debug_Message('Done defining columns of SQL');
1141:

Line 1146: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);

1142: Debug_Message('Binding variables');
1143: -- Binding Variables to query.
1144: IF is_pdh_batch THEN
1145: -- For PDH item.
1146: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1147: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_batch_id);
1148: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);
1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1150: ELSE

Line 1147: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_batch_id);

1143: -- Binding Variables to query.
1144: IF is_pdh_batch THEN
1145: -- For PDH item.
1146: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1147: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_batch_id);
1148: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);
1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1150: ELSE
1151: -- Non PDH Item.

Line 1148: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);

1144: IF is_pdh_batch THEN
1145: -- For PDH item.
1146: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1147: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_batch_id);
1148: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);
1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1150: ELSE
1151: -- Non PDH Item.
1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);

Line 1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);

1145: -- For PDH item.
1146: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1147: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_batch_id);
1148: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);
1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1150: ELSE
1151: -- Non PDH Item.
1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);

Line 1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);

1148: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_item1);
1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1150: ELSE
1151: -- Non PDH Item.
1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);
1154: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);
1155: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);
1156: IF p_bundle_id IS NOT NULL THEN

Line 1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);

1149: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_ss_record);
1150: ELSE
1151: -- Non PDH Item.
1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);
1154: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);
1155: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);
1156: IF p_bundle_id IS NOT NULL THEN
1157: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':5', p_bundle_id);

Line 1154: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);

1150: ELSE
1151: -- Non PDH Item.
1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);
1154: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);
1155: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);
1156: IF p_bundle_id IS NOT NULL THEN
1157: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':5', p_bundle_id);
1158: END IF;

Line 1155: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);

1151: -- Non PDH Item.
1152: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':1', p_org_Id);
1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);
1154: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);
1155: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);
1156: IF p_bundle_id IS NOT NULL THEN
1157: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':5', p_bundle_id);
1158: END IF;
1159: END IF; --IF is_pdh_batch THEN

Line 1157: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':5', p_bundle_id);

1153: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':2', p_ss_code);
1154: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':3', p_ss_record);
1155: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':4', p_batch_id);
1156: IF p_bundle_id IS NOT NULL THEN
1157: DBMS_SQL.BIND_VARIABLE(cr_msi_intf, ':5', p_bundle_id);
1158: END IF;
1159: END IF; --IF is_pdh_batch THEN
1160: Debug_Message('Done Binding variables');
1161:

Line 1162: l_ignore := DBMS_SQL.EXECUTE(cr_msi_intf);

1158: END IF;
1159: END IF; --IF is_pdh_batch THEN
1160: Debug_Message('Done Binding variables');
1161:
1162: l_ignore := DBMS_SQL.EXECUTE(cr_msi_intf);
1163: Debug_Message('Query Execution Complete');
1164:
1165: ------------------------------------------------------------------------------------
1166: -- While finding Privileges of the user upon items, do find privilege one time --

Line 1182: WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP

1178: -- keep required meta data for the attributes for which source --
1179: -- system data is not NULL. --
1180: -----------------------------------------------------------------
1181: Debug_Message('Fetching the Rows');
1182: WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP
1183: -- for each column defined previously, get the value of column in l_disp_val
1184:
1185: -- Inserting rows for Supplier and SupplierSite in case we are showing
1186: -- any supplier and supplier site information

Line 1236: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_num_value);

1232:
1233: FOR i IN 1..l_count LOOP
1234: l_fmt := l_attr_data_tbl(i).DATA_TYPE_CODE ;
1235: IF l_fmt = G_NUMBER_FORMAT THEN
1236: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_num_value);
1237: l_disp_val := TO_CHAR(l_num_value);
1238: ELSIF l_fmt = G_CHAR_FORMAT THEN
1239: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i +l_default_sel, l_str_value);
1240: l_disp_val := l_str_value;

Line 1239: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i +l_default_sel, l_str_value);

1235: IF l_fmt = G_NUMBER_FORMAT THEN
1236: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_num_value);
1237: l_disp_val := TO_CHAR(l_num_value);
1238: ELSIF l_fmt = G_CHAR_FORMAT THEN
1239: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i +l_default_sel, l_str_value);
1240: l_disp_val := l_str_value;
1241: ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1242: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_date_value);
1243: l_disp_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');

Line 1242: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_date_value);

1238: ELSIF l_fmt = G_CHAR_FORMAT THEN
1239: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i +l_default_sel, l_str_value);
1240: l_disp_val := l_str_value;
1241: ELSIF l_fmt = G_TIME_FORMAT OR l_fmt = G_DATE_TIME_FORMAT THEN
1242: DBMS_SQL.COLUMN_VALUE(cr_msi_intf, i+l_default_sel, l_date_value);
1243: l_disp_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1244: END IF; -- IF l_fmt = G_NUMBER_FORMAT THEN
1245:
1246: IF l_disp_val IS NOT NULL THEN

Line 1346: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP

1342: l_compare_tbl(l_compare_tbl.LAST) := l_compare_rec;
1343: END IF; --IF l_disp_val IS NOT NULL THEN
1344: END LOOP; --FOR i IN 1..l_count LOOP
1345: Debug_Message('Completed Entering records for Source System');
1346: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP
1347: DBMS_SQL.close_cursor(cr_msi_intf);
1348:
1349: Debug_Message('Done processing Item attributes (Primary and Operational) for Source System');
1350: Debug_Message('Processing Item attributes (Primary and Operational) for Production Items');

Line 1347: DBMS_SQL.close_cursor(cr_msi_intf);

1343: END IF; --IF l_disp_val IS NOT NULL THEN
1344: END LOOP; --FOR i IN 1..l_count LOOP
1345: Debug_Message('Completed Entering records for Source System');
1346: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_intf) > 0 LOOP
1347: DBMS_SQL.close_cursor(cr_msi_intf);
1348:
1349: Debug_Message('Done processing Item attributes (Primary and Operational) for Source System');
1350: Debug_Message('Processing Item attributes (Primary and Operational) for Production Items');
1351: -- Building query to get values from production table MTL_SYSTEM_ITEMS

Line 1411: cr_msi_attr := dbms_sql.open_cursor;

1407: Debug_Message(SUBSTR(l_sql_msi, ((l-1)*1000) + 1, 1000));
1408: END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_msi)/1000)) LOOP
1409:
1410: -- Opening a Dynamic Cursor for handling Query l_sql_msi
1411: cr_msi_attr := dbms_sql.open_cursor;
1412: Debug_Message('Parsing the SQL');
1413: DBMS_SQL.PARSE(cr_msi_attr, l_sql_msi, DBMS_SQL.native);
1414: Debug_Message('Done parsing the SQL');
1415:

Line 1413: DBMS_SQL.PARSE(cr_msi_attr, l_sql_msi, DBMS_SQL.native);

1409:
1410: -- Opening a Dynamic Cursor for handling Query l_sql_msi
1411: cr_msi_attr := dbms_sql.open_cursor;
1412: Debug_Message('Parsing the SQL');
1413: DBMS_SQL.PARSE(cr_msi_attr, l_sql_msi, DBMS_SQL.native);
1414: Debug_Message('Done parsing the SQL');
1415:
1416: Debug_Message('Binding variables');
1417: -- Binding the variables

Line 1419: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);

1415:
1416: Debug_Message('Binding variables');
1417: -- Binding the variables
1418: IF is_pdh_batch THEN
1419: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1420: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);
1421: ELSE
1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);

Line 1420: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);

1416: Debug_Message('Binding variables');
1417: -- Binding the variables
1418: IF is_pdh_batch THEN
1419: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1420: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);
1421: ELSE
1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);

Line 1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);

1418: IF is_pdh_batch THEN
1419: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1420: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);
1421: ELSE
1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);
1425: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);
1426: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);

Line 1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);

1419: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1420: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);
1421: ELSE
1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);
1425: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);
1426: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);
1427: END IF; --IF is_pdh_batch THEN

Line 1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);

1420: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_org_id);
1421: ELSE
1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);
1425: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);
1426: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);
1427: END IF; --IF is_pdh_batch THEN
1428: Debug_Message('Done binding variables');

Line 1425: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);

1421: ELSE
1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);
1425: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);
1426: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);
1427: END IF; --IF is_pdh_batch THEN
1428: Debug_Message('Done binding variables');
1429:

Line 1426: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);

1422: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':1',p_item1);
1423: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':2',p_item2);
1424: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':3',p_item3);
1425: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':4',p_item4);
1426: DBMS_SQL.BIND_VARIABLE(cr_msi_attr,':5',p_org_id);
1427: END IF; --IF is_pdh_batch THEN
1428: Debug_Message('Done binding variables');
1429:
1430: -- First columnn is inventory item id.

Line 1432: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, 1, l_num_value);

1428: Debug_Message('Done binding variables');
1429:
1430: -- First columnn is inventory item id.
1431: Debug_Message('Defining columns for SQL');
1432: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, 1, l_num_value);
1433: -- Defining Columns for Dynamic Cursor
1434: Debug_Message('Total columns = '||TO_CHAR(l_col_idx + 1));
1435: FOR i in 2..l_col_idx LOOP
1436: IF G_META(i) = G_CHAR_FORMAT THEN

Line 1437: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_str_value, 4000);

1433: -- Defining Columns for Dynamic Cursor
1434: Debug_Message('Total columns = '||TO_CHAR(l_col_idx + 1));
1435: FOR i in 2..l_col_idx LOOP
1436: IF G_META(i) = G_CHAR_FORMAT THEN
1437: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_str_value, 4000);
1438: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1439: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_num_value);
1440: ELSIF G_META(i) in(G_DATE_FORMAT, G_TIME_FORMAT, G_DATE_TIME_FORMAT) THEN
1441: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_date_value);

Line 1439: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_num_value);

1435: FOR i in 2..l_col_idx LOOP
1436: IF G_META(i) = G_CHAR_FORMAT THEN
1437: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_str_value, 4000);
1438: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1439: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_num_value);
1440: ELSIF G_META(i) in(G_DATE_FORMAT, G_TIME_FORMAT, G_DATE_TIME_FORMAT) THEN
1441: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_date_value);
1442: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1443: END LOOP; --FOR i in 2..l_col_idx LOOP

Line 1441: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_date_value);

1437: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_str_value, 4000);
1438: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1439: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_num_value);
1440: ELSIF G_META(i) in(G_DATE_FORMAT, G_TIME_FORMAT, G_DATE_TIME_FORMAT) THEN
1441: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_date_value);
1442: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1443: END LOOP; --FOR i in 2..l_col_idx LOOP
1444:
1445: l_ignore := DBMS_SQL.EXECUTE(cr_msi_attr);

Line 1445: l_ignore := DBMS_SQL.EXECUTE(cr_msi_attr);

1441: DBMS_SQL.DEFINE_COLUMN(cr_msi_attr, i, l_date_value);
1442: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1443: END LOOP; --FOR i in 2..l_col_idx LOOP
1444:
1445: l_ignore := DBMS_SQL.EXECUTE(cr_msi_attr);
1446: Debug_Message('Done Execution of the Query');
1447:
1448: Debug_Message('Fetching rows');
1449: WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP

Line 1449: WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP

1445: l_ignore := DBMS_SQL.EXECUTE(cr_msi_attr);
1446: Debug_Message('Done Execution of the Query');
1447:
1448: Debug_Message('Fetching rows');
1449: WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP
1450: -- first column is inventory_item_id
1451: Debug_Message('Getting value for inventory_item_id');
1452: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, 1, l_item_id);
1453: Debug_Message('Selected Row and started entering into Compare View table for item : ' || TO_CHAR(l_item_id));

Line 1452: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, 1, l_item_id);

1448: Debug_Message('Fetching rows');
1449: WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP
1450: -- first column is inventory_item_id
1451: Debug_Message('Getting value for inventory_item_id');
1452: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, 1, l_item_id);
1453: Debug_Message('Selected Row and started entering into Compare View table for item : ' || TO_CHAR(l_item_id));
1454:
1455: FOR i IN 2..l_col_idx LOOP
1456: -- for each column get the value into appropriate variable depending upon the format of column

Line 1458: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_str_value);

1454:
1455: FOR i IN 2..l_col_idx LOOP
1456: -- for each column get the value into appropriate variable depending upon the format of column
1457: IF G_META(i) = G_CHAR_FORMAT THEN
1458: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_str_value);
1459: l_val := l_str_value;
1460: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1461: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_num_value);
1462: l_val := TO_CHAR(l_num_value);

Line 1461: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_num_value);

1457: IF G_META(i) = G_CHAR_FORMAT THEN
1458: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_str_value);
1459: l_val := l_str_value;
1460: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1461: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_num_value);
1462: l_val := TO_CHAR(l_num_value);
1463: ELSIF G_META(i) = G_DATE_FORMAT THEN
1464: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_date_value);
1465: l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');

Line 1464: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_date_value);

1460: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1461: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_num_value);
1462: l_val := TO_CHAR(l_num_value);
1463: ELSIF G_META(i) = G_DATE_FORMAT THEN
1464: DBMS_SQL.COLUMN_VALUE(cr_msi_attr, i, l_date_value);
1465: l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1466: END IF; -- IF G_META(i) = G_CHAR_FORMAT THEN
1467:
1468: -- To populate into the Compare View Table

Line 1513: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP

1509: p_item2 => p_item2 ,
1510: p_item3 => p_item3 ,
1511: p_item4 => p_item4);
1512: END LOOP; --FOR i IN 2..l_col_idx LOOP
1513: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP
1514: DBMS_SQL.close_cursor(cr_msi_attr);
1515: Debug_Message('Completed inserting information for primary and operational attributes for all items');
1516: END IF; --IF (l_compare_tbl.LAST > 0) THEN
1517: Debug_Message('Done processing Item attributes (Primary and Operational) for Production Items');

Line 1514: DBMS_SQL.close_cursor(cr_msi_attr);

1510: p_item3 => p_item3 ,
1511: p_item4 => p_item4);
1512: END LOOP; --FOR i IN 2..l_col_idx LOOP
1513: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_msi_attr) > 0 LOOP
1514: DBMS_SQL.close_cursor(cr_msi_attr);
1515: Debug_Message('Completed inserting information for primary and operational attributes for all items');
1516: END IF; --IF (l_compare_tbl.LAST > 0) THEN
1517: Debug_Message('Done processing Item attributes (Primary and Operational) for Production Items');
1518:

Line 1609: cr_ud_attr := dbms_sql.open_cursor;

1605: l_sql_query := NULL;
1606: l_temp_query := NULL;
1607: l_idx := 1;
1608: l_start := NVL(l_compare_tbl.LAST, 0) ;
1609: cr_ud_attr := dbms_sql.open_cursor;
1610:
1611: --R12C: Finding attr Group display name with prefix to identify
1612: -- the Attribute group data level.
1613: IF rec_attr.DATA_LEVEL_ID = G_ITEM_LEVEL_ID THEN

Line 1904: DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);

1900: END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
1901:
1902: -- Define Dynamic SQL for querying for other Items.
1903: Debug_Message('Parsing SQL');
1904: DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);
1905: Debug_Message('Done Parsing SQL');
1906:
1907: Debug_Message('Defining columns for SQL');
1908: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1, l_num_value);

Line 1908: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1, l_num_value);

1904: DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);
1905: Debug_Message('Done Parsing SQL');
1906:
1907: Debug_Message('Defining columns for SQL');
1908: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1, l_num_value);
1909: -- Defining columns in Dynamic Cursor
1910: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
1911: FOR i IN 2..l_idx LOOP
1912: IF G_META(i) = G_CHAR_FORMAT THEN

Line 1913: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value,4000);

1909: -- Defining columns in Dynamic Cursor
1910: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
1911: FOR i IN 2..l_idx LOOP
1912: IF G_META(i) = G_CHAR_FORMAT THEN
1913: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value,4000);
1914: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1915: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
1916: ELSIF G_META(i) = G_DATE_FORMAT THEN
1917: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);

Line 1915: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);

1911: FOR i IN 2..l_idx LOOP
1912: IF G_META(i) = G_CHAR_FORMAT THEN
1913: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value,4000);
1914: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1915: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
1916: ELSIF G_META(i) = G_DATE_FORMAT THEN
1917: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);
1918: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1919: END LOOP; --FOR i IN 2..l_idx LOOP

Line 1917: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);

1913: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value,4000);
1914: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1915: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
1916: ELSIF G_META(i) = G_DATE_FORMAT THEN
1917: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);
1918: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1919: END LOOP; --FOR i IN 2..l_idx LOOP
1920:
1921: Debug_Message('Binding variables');

Line 1922: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);

1918: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1919: END LOOP; --FOR i IN 2..l_idx LOOP
1920:
1921: Debug_Message('Binding variables');
1922: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
1923: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_org_id);
1924: --Bug#5043002
1925: --R12C: BEGIN
1926: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN

Line 1923: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_org_id);

1919: END LOOP; --FOR i IN 2..l_idx LOOP
1920:
1921: Debug_Message('Binding variables');
1922: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
1923: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_org_id);
1924: --Bug#5043002
1925: --R12C: BEGIN
1926: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
1927: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

Line 1927: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

1923: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_org_id);
1924: --Bug#5043002
1925: --R12C: BEGIN
1926: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
1927: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1928: END IF;
1929:
1930: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
1931: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);

Line 1931: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);

1927: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1928: END IF;
1929:
1930: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
1931: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1932: END IF;
1933:
1934: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1935: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

Line 1935: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

1931: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1932: END IF;
1933:
1934: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1935: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1936: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1937: END IF;
1938: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
1939: --R12C: END

Line 1936: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);

1932: END IF;
1933:
1934: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1935: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1936: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1937: END IF;
1938: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
1939: --R12C: END
1940:

Line 1938: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);

1934: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
1935: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
1936: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
1937: END IF;
1938: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
1939: --R12C: END
1940:
1941: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);
1942:

Line 1941: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);

1937: END IF;
1938: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
1939: --R12C: END
1940:
1941: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);
1942:
1943: -- Execution of the Query (Cursor) for UD attrs for Items
1944: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
1945: Debug_Message('Executed SQL, fetching rows');

Line 1944: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);

1940:
1941: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);
1942:
1943: -- Execution of the Query (Cursor) for UD attrs for Items
1944: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
1945: Debug_Message('Executed SQL, fetching rows');
1946: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
1947: l_cnt := l_start + 1;
1948: l_item_id := NULL;

Line 1946: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP

1942:
1943: -- Execution of the Query (Cursor) for UD attrs for Items
1944: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
1945: Debug_Message('Executed SQL, fetching rows');
1946: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
1947: l_cnt := l_start + 1;
1948: l_item_id := NULL;
1949: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);
1950: FOR i IN 2..l_idx LOOP

Line 1949: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);

1945: Debug_Message('Executed SQL, fetching rows');
1946: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
1947: l_cnt := l_start + 1;
1948: l_item_id := NULL;
1949: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);
1950: FOR i IN 2..l_idx LOOP
1951: l_str_value := NULL;
1952: l_num_value := NULL;
1953: l_date_value := NULL;

Line 1955: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);

1951: l_str_value := NULL;
1952: l_num_value := NULL;
1953: l_date_value := NULL;
1954: IF G_META(i) = G_CHAR_FORMAT THEN
1955: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);
1956: l_int_val := l_str_value;
1957: ELSIF G_META(i) = G_DATE_FORMAT THEN
1958: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
1959: l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');

Line 1958: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);

1954: IF G_META(i) = G_CHAR_FORMAT THEN
1955: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);
1956: l_int_val := l_str_value;
1957: ELSIF G_META(i) = G_DATE_FORMAT THEN
1958: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
1959: l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1960: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1961: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);
1962: l_int_val := TO_CHAR(l_num_value);

Line 1961: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);

1957: ELSIF G_META(i) = G_DATE_FORMAT THEN
1958: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
1959: l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
1960: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
1961: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);
1962: l_int_val := TO_CHAR(l_num_value);
1963: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
1964:
1965: -- if a value set is associated, then get the display value

Line 2006: END LOOP; -- WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP

2002: ,p_item3 => p_item3
2003: ,p_item4 => p_item4);
2004: l_cnt := l_cnt + 1;
2005: END LOOP; --FOR i IN 2..l_idx LOOP
2006: END LOOP; -- WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2007: DBMS_SQL.close_cursor(cr_ud_attr);
2008: Debug_Message('Done Getting values from production table');
2009: END IF; -- IF DATA_LEVEL CHECK AGIANST p_supplier_id, p_supplier_site_id
2010: END LOOP; --FOR rec_attr IN cr_attr_groups_pdh(l_revision) LOOP

Line 2007: DBMS_SQL.close_cursor(cr_ud_attr);

2003: ,p_item4 => p_item4);
2004: l_cnt := l_cnt + 1;
2005: END LOOP; --FOR i IN 2..l_idx LOOP
2006: END LOOP; -- WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2007: DBMS_SQL.close_cursor(cr_ud_attr);
2008: Debug_Message('Done Getting values from production table');
2009: END IF; -- IF DATA_LEVEL CHECK AGIANST p_supplier_id, p_supplier_site_id
2010: END LOOP; --FOR rec_attr IN cr_attr_groups_pdh(l_revision) LOOP
2011: Debug_Message('Done getting User Defined Attributes for Items ');

Line 2063: cr_ud_attr := dbms_sql.open_cursor;

2059: l_sql_query := NULL;
2060: l_temp_query := NULL;
2061: l_idx := 1;
2062: l_start := NVL(l_compare_tbl.LAST,0);
2063: cr_ud_attr := dbms_sql.open_cursor;
2064: l_sql_query :=' FROM EGO_MTL_SY_ITEMS_EXT_VL I, EGO_ATTR_GROUPS_DL_V AG '||
2065: ' WHERE AG.APPLICATION_ID = 431 '||
2066: ' AND NVL(AG.ATTR_GROUP_TYPE, ''EGO_ITEMMGMT_GROUP'') = ''EGO_ITEMMGMT_GROUP'' '||
2067: ' AND AG.ATTR_GROUP_ID = I.ATTR_GROUP_ID'||

Line 2364: DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);

2360:
2361: Debug_Message('Entering User Defined Attributes for Items ');
2362: -- Define Dynamic SQL for querying for other Items.
2363: Debug_Message('Parsing SQL');
2364: DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);
2365: Debug_Message('Done parsing SQL');
2366:
2367: Debug_Message('Defining columns for SQL');
2368: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1 , l_num_value); --inventory_item_id

Line 2368: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1 , l_num_value); --inventory_item_id

2364: DBMS_SQL.PARSE(cr_ud_attr, l_sql_query, DBMS_SQL.native);
2365: Debug_Message('Done parsing SQL');
2366:
2367: Debug_Message('Defining columns for SQL');
2368: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1 , l_num_value); --inventory_item_id
2369: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
2370: FOR i IN 2..l_idx LOOP
2371: IF G_META(i) = G_CHAR_FORMAT THEN
2372: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value, 4000);

Line 2372: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value, 4000);

2368: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, 1 , l_num_value); --inventory_item_id
2369: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
2370: FOR i IN 2..l_idx LOOP
2371: IF G_META(i) = G_CHAR_FORMAT THEN
2372: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value, 4000);
2373: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2374: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
2375: ELSIF G_META(i) = G_DATE_FORMAT THEN
2376: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);

Line 2374: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);

2370: FOR i IN 2..l_idx LOOP
2371: IF G_META(i) = G_CHAR_FORMAT THEN
2372: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value, 4000);
2373: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2374: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
2375: ELSIF G_META(i) = G_DATE_FORMAT THEN
2376: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);
2377: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2378: END LOOP; --FOR i IN 2..l_idx LOOP

Line 2376: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);

2372: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_str_value, 4000);
2373: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2374: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_num_value);
2375: ELSIF G_META(i) = G_DATE_FORMAT THEN
2376: DBMS_SQL.DEFINE_COLUMN(cr_ud_attr, i, l_date_value);
2377: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2378: END LOOP; --FOR i IN 2..l_idx LOOP
2379:
2380: Debug_Message('Binding variables');

Line 2382: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);

2378: END LOOP; --FOR i IN 2..l_idx LOOP
2379:
2380: Debug_Message('Binding variables');
2381: IF rec_attr.REVISION IS NULL THEN
2382: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
2383: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);
2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);

Line 2383: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);

2379:
2380: Debug_Message('Binding variables');
2381: IF rec_attr.REVISION IS NULL THEN
2382: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
2383: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);
2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);
2387: ELSE

Line 2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);

2380: Debug_Message('Binding variables');
2381: IF rec_attr.REVISION IS NULL THEN
2382: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
2383: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);
2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);
2387: ELSE
2388: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_org_id);

Line 2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);

2381: IF rec_attr.REVISION IS NULL THEN
2382: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
2383: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);
2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);
2387: ELSE
2388: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_org_id);
2389: END IF; --IF rec_attr.REVISION IS NULL THEN

Line 2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);

2382: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_item1);
2383: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':2', p_item2);
2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);
2387: ELSE
2388: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_org_id);
2389: END IF; --IF rec_attr.REVISION IS NULL THEN
2390:

Line 2388: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_org_id);

2384: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':3', p_item3);
2385: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':4', p_item4);
2386: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':5', p_org_id);
2387: ELSE
2388: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':1', p_org_id);
2389: END IF; --IF rec_attr.REVISION IS NULL THEN
2390:
2391: --R12C: BEGIN
2392: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN

Line 2393: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

2389: END IF; --IF rec_attr.REVISION IS NULL THEN
2390:
2391: --R12C: BEGIN
2392: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_LEVEL_ID THEN
2393: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2394: END IF;
2395:
2396: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
2397: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);

Line 2397: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);

2393: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2394: END IF;
2395:
2396: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPPLIER_SITE_LEVEL_ID THEN
2397: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2398: END IF;
2399:
2400: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2401: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

Line 2401: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);

2397: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2398: END IF;
2399:
2400: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2401: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2402: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2403: END IF;
2404: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
2405: --R12C: END

Line 2402: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);

2398: END IF;
2399:
2400: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2401: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2402: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2403: END IF;
2404: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
2405: --R12C: END
2406:

Line 2404: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);

2400: IF rec_attr.DATA_LEVEL_ID = G_ITEM_SUPSITEORG_LEVEL_ID THEN
2401: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':96', p_supplier_id);
2402: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':97', p_supplier_site_id);
2403: END IF;
2404: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
2405: --R12C: END
2406:
2407: --Bug#5043002
2408: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);

Line 2408: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);

2404: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':98', rec_attr.DATA_LEVEL_ID);
2405: --R12C: END
2406:
2407: --Bug#5043002
2408: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':99', rec_attr.ATTR_GROUP_INT_NAME);
2409:
2410: -- Bug#5043002
2411: IF nvl(l_inv_rev_id_tbl.LAST,0)>0 THEN
2412: FOR j in l_inv_rev_id_tbl.FIRST..l_inv_rev_id_tbl.LAST LOOP

Line 2413: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':'||j, l_inv_rev_id_tbl(j));

2409:
2410: -- Bug#5043002
2411: IF nvl(l_inv_rev_id_tbl.LAST,0)>0 THEN
2412: FOR j in l_inv_rev_id_tbl.FIRST..l_inv_rev_id_tbl.LAST LOOP
2413: DBMS_SQL.BIND_VARIABLE(cr_ud_attr, ':'||j, l_inv_rev_id_tbl(j));
2414: END LOOP ;
2415: END IF ;
2416: -- Bug#5043002
2417: Debug_Message('Done binding variables');

Line 2419: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);

2415: END IF ;
2416: -- Bug#5043002
2417: Debug_Message('Done binding variables');
2418:
2419: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
2420: Debug_Message('Executed SQL, fetching rows');
2421: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2422: l_cnt := l_start + 1 ;
2423: l_item_id := NULL;

Line 2421: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP

2417: Debug_Message('Done binding variables');
2418:
2419: l_ignore := DBMS_SQL.EXECUTE(cr_ud_attr);
2420: Debug_Message('Executed SQL, fetching rows');
2421: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2422: l_cnt := l_start + 1 ;
2423: l_item_id := NULL;
2424: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);
2425: FOR i IN 2..l_idx LOOP

Line 2424: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);

2420: Debug_Message('Executed SQL, fetching rows');
2421: WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2422: l_cnt := l_start + 1 ;
2423: l_item_id := NULL;
2424: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, 1, l_item_id);
2425: FOR i IN 2..l_idx LOOP
2426: l_str_value := NULL;
2427: l_num_value := NULL;
2428: l_date_value := NULL;

Line 2430: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);

2426: l_str_value := NULL;
2427: l_num_value := NULL;
2428: l_date_value := NULL;
2429: IF G_META(i) = G_CHAR_FORMAT THEN
2430: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);
2431: l_int_val := l_str_value;
2432: ELSIF G_META(i) = G_DATE_FORMAT THEN
2433: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
2434: l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');

Line 2433: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);

2429: IF G_META(i) = G_CHAR_FORMAT THEN
2430: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_str_value);
2431: l_int_val := l_str_value;
2432: ELSIF G_META(i) = G_DATE_FORMAT THEN
2433: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
2434: l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
2435: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2436: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);
2437: l_int_val := TO_CHAR(l_num_value);

Line 2436: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);

2432: ELSIF G_META(i) = G_DATE_FORMAT THEN
2433: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_date_value);
2434: l_int_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
2435: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2436: DBMS_SQL.COLUMN_VALUE(cr_ud_attr, i, l_num_value);
2437: l_int_val := TO_CHAR(l_num_value);
2438: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2439:
2440: -- if a value set is associated, then get the display value

Line 2480: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP

2476: ,p_item3 => p_item3
2477: ,p_item4 => p_item4);
2478: l_cnt := l_cnt + 1;
2479: END LOOP; --FOR i IN 2..l_idx LOOP
2480: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2481:
2482: DBMS_SQL.CLOSE_CURSOR(cr_ud_attr);
2483: Debug_Message('Done Getting values from production table');
2484: END IF; -- IF DATA_LEVEL check against p_supplier_site_id and p_supplier_id

Line 2482: DBMS_SQL.CLOSE_CURSOR(cr_ud_attr);

2478: l_cnt := l_cnt + 1;
2479: END LOOP; --FOR i IN 2..l_idx LOOP
2480: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_ud_attr) > 0 LOOP
2481:
2482: DBMS_SQL.CLOSE_CURSOR(cr_ud_attr);
2483: Debug_Message('Done Getting values from production table');
2484: END IF; -- IF DATA_LEVEL check against p_supplier_site_id and p_supplier_id
2485: END LOOP; --FOR rec_attr IN cr_attr_groups(l_revision) LOOP
2486: Debug_Message('Done getting User Defined Attributes for Items ');

Line 2802: cr_dd_attr := DBMS_SQL.OPEN_CURSOR;

2798: Debug_Message(SUBSTR(l_sql_query, ((l-1)*1000) + 1, 1000));
2799: END LOOP; --FOR l in 1..(CEIL(LENGTH(l_sql_query)/1000)) LOOP
2800:
2801: -- Defining a Dynamic Cursor -
2802: cr_dd_attr := DBMS_SQL.OPEN_CURSOR;
2803: Debug_Message('Parsing SQL');
2804: DBMS_SQL.PARSE(cr_dd_attr, l_sql_query, DBMS_SQL.native);
2805: Debug_Message('Done Parsing SQL');
2806: Debug_Message('Defining columns');

Line 2804: DBMS_SQL.PARSE(cr_dd_attr, l_sql_query, DBMS_SQL.native);

2800:
2801: -- Defining a Dynamic Cursor -
2802: cr_dd_attr := DBMS_SQL.OPEN_CURSOR;
2803: Debug_Message('Parsing SQL');
2804: DBMS_SQL.PARSE(cr_dd_attr, l_sql_query, DBMS_SQL.native);
2805: Debug_Message('Done Parsing SQL');
2806: Debug_Message('Defining columns');
2807: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, 1 , l_num_value); --inventory _item _id
2808: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));

Line 2807: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, 1 , l_num_value); --inventory _item _id

2803: Debug_Message('Parsing SQL');
2804: DBMS_SQL.PARSE(cr_dd_attr, l_sql_query, DBMS_SQL.native);
2805: Debug_Message('Done Parsing SQL');
2806: Debug_Message('Defining columns');
2807: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, 1 , l_num_value); --inventory _item _id
2808: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
2809: FOR i IN 2..l_idx LOOP
2810: IF G_META(i) = G_CHAR_FORMAT THEN
2811: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_str_value, 4000);

Line 2811: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_str_value, 4000);

2807: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, 1 , l_num_value); --inventory _item _id
2808: Debug_Message('Total columns: '||TO_CHAR(l_idx + 1));
2809: FOR i IN 2..l_idx LOOP
2810: IF G_META(i) = G_CHAR_FORMAT THEN
2811: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_str_value, 4000);
2812: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2813: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_num_value);
2814: ELSIF G_META(i) = G_DATE_FORMAT THEN
2815: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_date_value);

Line 2813: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_num_value);

2809: FOR i IN 2..l_idx LOOP
2810: IF G_META(i) = G_CHAR_FORMAT THEN
2811: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_str_value, 4000);
2812: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2813: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_num_value);
2814: ELSIF G_META(i) = G_DATE_FORMAT THEN
2815: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_date_value);
2816: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2817: END LOOP; --FOR i IN 2..l_idx LOOP

Line 2815: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_date_value);

2811: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_str_value, 4000);
2812: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2813: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_num_value);
2814: ELSIF G_META(i) = G_DATE_FORMAT THEN
2815: DBMS_SQL.DEFINE_COLUMN(cr_dd_attr, i, l_date_value);
2816: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2817: END LOOP; --FOR i IN 2..l_idx LOOP
2818:
2819: Debug_Message('Binding variables');

Line 2821: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);

2817: END LOOP; --FOR i IN 2..l_idx LOOP
2818:
2819: Debug_Message('Binding variables');
2820: IF is_pdh_batch THEN
2821: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2822: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);
2823: ELSE
2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);

Line 2822: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);

2818:
2819: Debug_Message('Binding variables');
2820: IF is_pdh_batch THEN
2821: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2822: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);
2823: ELSE
2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);

Line 2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);

2820: IF is_pdh_batch THEN
2821: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2822: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);
2823: ELSE
2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);
2827: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);
2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);

Line 2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);

2821: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2822: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);
2823: ELSE
2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);
2827: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);
2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);
2829: END IF; --IF is_pdh_batch THEN

Line 2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);

2822: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_org_id);
2823: ELSE
2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);
2827: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);
2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);
2829: END IF; --IF is_pdh_batch THEN
2830: Debug_Message('Done binding variables');

Line 2827: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);

2823: ELSE
2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);
2827: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);
2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);
2829: END IF; --IF is_pdh_batch THEN
2830: Debug_Message('Done binding variables');
2831:

Line 2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);

2824: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':1', p_item1);
2825: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':2', p_item2);
2826: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':3', p_item3);
2827: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':4', p_item4);
2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);
2829: END IF; --IF is_pdh_batch THEN
2830: Debug_Message('Done binding variables');
2831:
2832: l_ignore := DBMS_SQL.EXECUTE(cr_dd_attr);

Line 2832: l_ignore := DBMS_SQL.EXECUTE(cr_dd_attr);

2828: DBMS_SQL.BIND_VARIABLE(cr_dd_attr, ':5', p_org_id);
2829: END IF; --IF is_pdh_batch THEN
2830: Debug_Message('Done binding variables');
2831:
2832: l_ignore := DBMS_SQL.EXECUTE(cr_dd_attr);
2833: Debug_Message('Executed SQL, fetching rows');
2834: WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP
2835: l_cnt := l_start + 1;
2836: FOR i IN 2..l_idx LOOP

Line 2834: WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP

2830: Debug_Message('Done binding variables');
2831:
2832: l_ignore := DBMS_SQL.EXECUTE(cr_dd_attr);
2833: Debug_Message('Executed SQL, fetching rows');
2834: WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP
2835: l_cnt := l_start + 1;
2836: FOR i IN 2..l_idx LOOP
2837: l_str_value := NULL;
2838: l_num_value := NULL;

Line 2841: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_str_value);

2837: l_str_value := NULL;
2838: l_num_value := NULL;
2839: l_date_value := NULL;
2840: IF G_META(i) = G_CHAR_FORMAT THEN
2841: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_str_value);
2842: l_val := l_str_value;
2843: ELSIF G_META(i) = G_DATE_FORMAT THEN
2844: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_date_value);
2845: l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');

Line 2844: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_date_value);

2840: IF G_META(i) = G_CHAR_FORMAT THEN
2841: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_str_value);
2842: l_val := l_str_value;
2843: ELSIF G_META(i) = G_DATE_FORMAT THEN
2844: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_date_value);
2845: l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
2846: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2847: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_num_value);
2848: l_val := TO_CHAR(l_num_value);

Line 2847: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_num_value);

2843: ELSIF G_META(i) = G_DATE_FORMAT THEN
2844: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_date_value);
2845: l_val := TO_CHAR(l_date_value, 'MM/DD/YYYY HH24:MI:SS');
2846: ELSIF G_META(i) = G_NUMBER_FORMAT THEN
2847: DBMS_SQL.COLUMN_VALUE(cr_dd_attr, i, l_num_value);
2848: l_val := TO_CHAR(l_num_value);
2849: END IF; --IF G_META(i) = G_CHAR_FORMAT THEN
2850:
2851: -- if a value set is associated, then get the display value

Line 2891: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP

2887: p_item3 => p_item3 ,
2888: p_item4 => p_item4);
2889: l_cnt := l_cnt + 1;
2890: END LOOP; --FOR i IN 2..l_idx LOOP
2891: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP
2892: DBMS_SQL.CLOSE_CURSOR(cr_dd_attr);
2893: Debug_Message('Done getting attribute values for production items.');
2894: END IF; --IF l_sel_clause IS NOT NULL THEN
2895: Debug_Message('Done Processing GDSN attributes.');

Line 2892: DBMS_SQL.CLOSE_CURSOR(cr_dd_attr);

2888: p_item4 => p_item4);
2889: l_cnt := l_cnt + 1;
2890: END LOOP; --FOR i IN 2..l_idx LOOP
2891: END LOOP; --WHILE DBMS_SQL.FETCH_ROWS(cr_dd_attr) > 0 LOOP
2892: DBMS_SQL.CLOSE_CURSOR(cr_dd_attr);
2893: Debug_Message('Done getting attribute values for production items.');
2894: END IF; --IF l_sel_clause IS NOT NULL THEN
2895: Debug_Message('Done Processing GDSN attributes.');
2896: Debug_Message('Done GET_COMPARED_DATA Successfully at - '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));