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 1081: cr_msi_intf := DBMS_SQL.OPEN_CURSOR;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1346: DBMS_SQL.close_cursor(cr_msi_intf);

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

Line 1410: cr_msi_attr := dbms_sql.open_cursor;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 1513: DBMS_SQL.close_cursor(cr_msi_attr);

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

Line 1608: cr_ud_attr := dbms_sql.open_cursor;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2006: DBMS_SQL.close_cursor(cr_ud_attr);

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

Line 2062: cr_ud_attr := dbms_sql.open_cursor;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2481: DBMS_SQL.CLOSE_CURSOR(cr_ud_attr);

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

Line 2801: cr_dd_attr := DBMS_SQL.OPEN_CURSOR;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2891: DBMS_SQL.CLOSE_CURSOR(cr_dd_attr);

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