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_ITEM_BULKLOAD_PKG dependencies on DBMS_SQL

Line 670: l_item_num_table DBMS_SQL.VARCHAR2_TABLE;

666:
667: -----------------------------------------------------------------------
668: -- Variables used to query Item Number Generation Method
669: -----------------------------------------------------------------------
670: l_item_num_table DBMS_SQL.VARCHAR2_TABLE;
671: l_item_num_cursor INTEGER;
672: l_item_num_exec INTEGER;
673: l_item_num_rows_cnt NUMBER;
674:

Line 680: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;

676: -----------------------------------------------------------------------
677:
678: BEGIN
679:
680: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;
681: --Developer_Debug('l_item_num_sql => '||l_item_num_sql);
682:
683: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);
684:

Line 683: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);

679:
680: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;
681: --Developer_Debug('l_item_num_sql => '||l_item_num_sql);
682:
683: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);
684:
685: DBMS_SQL.DEFINE_ARRAY(
686: c => l_item_num_cursor -- cursor --
687: , position => 1 -- select position --

Line 685: DBMS_SQL.DEFINE_ARRAY(

681: --Developer_Debug('l_item_num_sql => '||l_item_num_sql);
682:
683: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);
684:
685: DBMS_SQL.DEFINE_ARRAY(
686: c => l_item_num_cursor -- cursor --
687: , position => 1 -- select position --
688: , c_tab => l_item_num_table -- table of chars --
689: , cnt => 10000 -- rows requested --

Line 693: DBMS_SQL.BIND_VARIABLE(l_item_num_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

689: , cnt => 10000 -- rows requested --
690: , lower_bound => 1 -- start at --
691: );
692:
693: DBMS_SQL.BIND_VARIABLE(l_item_num_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
694:
695: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
696: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
697:

Line 695: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);

691: );
692:
693: DBMS_SQL.BIND_VARIABLE(l_item_num_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
694:
695: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
696: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
697:
698: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
699: -- Bug : 4099546

Line 696: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);

692:
693: DBMS_SQL.BIND_VARIABLE(l_item_num_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
694:
695: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
696: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
697:
698: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
699: -- Bug : 4099546
700: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);

Line 698: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);

694:
695: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
696: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
697:
698: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
699: -- Bug : 4099546
700: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);
701: FND_MESSAGE.SET_NAME('EGO','EGO_NUM_OF_ITEMS_PROCD');
702: l_msg := FND_MESSAGE.GET;

Line 700: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);

696: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
697:
698: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
699: -- Bug : 4099546
700: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);
701: FND_MESSAGE.SET_NAME('EGO','EGO_NUM_OF_ITEMS_PROCD');
702: l_msg := FND_MESSAGE.GET;
703:
704: Developer_Debug(l_msg||' '||To_char(l_item_num_rows_cnt));

Line 731: l_item_num_table DBMS_SQL.VARCHAR2_TABLE;

727: -----------------------------------------------------------------------
728: PROCEDURE Log_created_Items (REQUEST_ID IN NUMBER)
729: IS
730: l_item_num_sql VARCHAR2(10000);
731: l_item_num_table DBMS_SQL.VARCHAR2_TABLE;
732: l_transaction_id_table DBMS_SQL.NUMBER_TABLE;
733: l_item_num_cursor INTEGER;
734: l_item_num_exec INTEGER;
735: l_item_num_rows_cnt NUMBER;

Line 732: l_transaction_id_table DBMS_SQL.NUMBER_TABLE;

728: PROCEDURE Log_created_Items (REQUEST_ID IN NUMBER)
729: IS
730: l_item_num_sql VARCHAR2(10000);
731: l_item_num_table DBMS_SQL.VARCHAR2_TABLE;
732: l_transaction_id_table DBMS_SQL.NUMBER_TABLE;
733: l_item_num_cursor INTEGER;
734: l_item_num_exec INTEGER;
735: l_item_num_rows_cnt NUMBER;
736: l_msg fnd_new_messages.message_text%TYPE;

Line 748: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;

744: || ' WHERE REQUEST_ID = '||REQUEST_ID
745: || ' AND PROCESS_FLAG = '||G_INTF_STATUS_SUCCESS
746: || ' AND TRANSACTION_TYPE = '''||G_CREATE||'''';
747:
748: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;
749: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);
750: DBMS_SQL.DEFINE_ARRAY(
751: c => l_item_num_cursor
752: , position => 1

Line 749: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);

745: || ' AND PROCESS_FLAG = '||G_INTF_STATUS_SUCCESS
746: || ' AND TRANSACTION_TYPE = '''||G_CREATE||'''';
747:
748: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;
749: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);
750: DBMS_SQL.DEFINE_ARRAY(
751: c => l_item_num_cursor
752: , position => 1
753: , c_tab => l_item_num_table

Line 750: DBMS_SQL.DEFINE_ARRAY(

746: || ' AND TRANSACTION_TYPE = '''||G_CREATE||'''';
747:
748: l_item_num_cursor := DBMS_SQL.OPEN_CURSOR;
749: DBMS_SQL.PARSE(l_item_num_cursor, l_item_num_sql, DBMS_SQL.NATIVE);
750: DBMS_SQL.DEFINE_ARRAY(
751: c => l_item_num_cursor
752: , position => 1
753: , c_tab => l_item_num_table
754: , cnt => 10000

Line 758: DBMS_SQL.DEFINE_ARRAY(

754: , cnt => 10000
755: , lower_bound => 1
756: );
757:
758: DBMS_SQL.DEFINE_ARRAY(
759: c => l_item_num_cursor
760: , position => 2
761: , n_tab => l_transaction_id_table
762: , cnt => 10000

Line 766: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);

762: , cnt => 10000
763: , lower_bound => 1
764: );
765:
766: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
767: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
768: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
769: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 2, l_transaction_id_table);
770: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);

Line 767: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);

763: , lower_bound => 1
764: );
765:
766: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
767: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
768: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
769: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 2, l_transaction_id_table);
770: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);
771: IF (l_item_num_rows_cnt > 0) THEN

Line 768: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);

764: );
765:
766: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
767: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
768: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
769: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 2, l_transaction_id_table);
770: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);
771: IF (l_item_num_rows_cnt > 0) THEN
772: FOR i IN 1..l_item_num_rows_cnt LOOP

Line 769: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 2, l_transaction_id_table);

765:
766: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
767: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
768: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
769: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 2, l_transaction_id_table);
770: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);
771: IF (l_item_num_rows_cnt > 0) THEN
772: FOR i IN 1..l_item_num_rows_cnt LOOP
773: Developer_Debug(l_msg||' ['||i||'] = '||l_item_num_table(i));

Line 770: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);

766: l_item_num_exec := DBMS_SQL.EXECUTE(l_item_num_cursor);
767: l_item_num_rows_cnt := DBMS_SQL.FETCH_ROWS(l_item_num_cursor);
768: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 1, l_item_num_table);
769: DBMS_SQL.COLUMN_VALUE(l_item_num_cursor, 2, l_transaction_id_table);
770: DBMS_SQL.CLOSE_CURSOR(l_item_num_cursor);
771: IF (l_item_num_rows_cnt > 0) THEN
772: FOR i IN 1..l_item_num_rows_cnt LOOP
773: Developer_Debug(l_msg||' ['||i||'] = '||l_item_num_table(i));
774: Error_Handler.Add_Error_Message

Line 823: l_org_id_table DBMS_SQL.VARCHAR2_TABLE;

819:
820: -----------------------------------------------------------------------
821: -- Variables used to query Item Number Generation Method
822: -----------------------------------------------------------------------
823: l_org_id_table DBMS_SQL.VARCHAR2_TABLE;
824: l_org_id_cursor INTEGER;
825: l_org_id_exec INTEGER;
826: l_org_id_rows_cnt NUMBER;
827:

Line 837: l_org_id_cursor := DBMS_SQL.OPEN_CURSOR;

833: -----------------------------------------------------------------------
834:
835: BEGIN
836:
837: l_org_id_cursor := DBMS_SQL.OPEN_CURSOR;
838: --Write_Debug('l_org_id_sql => '||l_org_id_sql);
839:
840: DBMS_SQL.PARSE(l_org_id_cursor, l_org_id_sql, DBMS_SQL.NATIVE);
841:

Line 840: DBMS_SQL.PARSE(l_org_id_cursor, l_org_id_sql, DBMS_SQL.NATIVE);

836:
837: l_org_id_cursor := DBMS_SQL.OPEN_CURSOR;
838: --Write_Debug('l_org_id_sql => '||l_org_id_sql);
839:
840: DBMS_SQL.PARSE(l_org_id_cursor, l_org_id_sql, DBMS_SQL.NATIVE);
841:
842: DBMS_SQL.DEFINE_ARRAY(
843: c => l_org_id_cursor -- cursor --
844: , position => 1 -- select position --

Line 842: DBMS_SQL.DEFINE_ARRAY(

838: --Write_Debug('l_org_id_sql => '||l_org_id_sql);
839:
840: DBMS_SQL.PARSE(l_org_id_cursor, l_org_id_sql, DBMS_SQL.NATIVE);
841:
842: DBMS_SQL.DEFINE_ARRAY(
843: c => l_org_id_cursor -- cursor --
844: , position => 1 -- select position --
845: , c_tab => l_org_id_table -- table of chars --
846: , cnt => 10000 -- rows requested --

Line 850: DBMS_SQL.BIND_VARIABLE(l_org_id_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

846: , cnt => 10000 -- rows requested --
847: , lower_bound => 1 -- start at --
848: );
849:
850: DBMS_SQL.BIND_VARIABLE(l_org_id_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
851:
852: l_org_id_exec := DBMS_SQL.EXECUTE(l_org_id_cursor);
853: l_org_id_rows_cnt := DBMS_SQL.FETCH_ROWS(l_org_id_cursor);
854:

Line 852: l_org_id_exec := DBMS_SQL.EXECUTE(l_org_id_cursor);

848: );
849:
850: DBMS_SQL.BIND_VARIABLE(l_org_id_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
851:
852: l_org_id_exec := DBMS_SQL.EXECUTE(l_org_id_cursor);
853: l_org_id_rows_cnt := DBMS_SQL.FETCH_ROWS(l_org_id_cursor);
854:
855: DBMS_SQL.COLUMN_VALUE(l_org_id_cursor, 1, l_org_id_table);
856:

Line 853: l_org_id_rows_cnt := DBMS_SQL.FETCH_ROWS(l_org_id_cursor);

849:
850: DBMS_SQL.BIND_VARIABLE(l_org_id_cursor, ':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
851:
852: l_org_id_exec := DBMS_SQL.EXECUTE(l_org_id_cursor);
853: l_org_id_rows_cnt := DBMS_SQL.FETCH_ROWS(l_org_id_cursor);
854:
855: DBMS_SQL.COLUMN_VALUE(l_org_id_cursor, 1, l_org_id_table);
856:
857: Write_Debug('Number of rows where ITEM_NUMBER is NULL => '||To_char(l_org_id_rows_cnt));

Line 855: DBMS_SQL.COLUMN_VALUE(l_org_id_cursor, 1, l_org_id_table);

851:
852: l_org_id_exec := DBMS_SQL.EXECUTE(l_org_id_cursor);
853: l_org_id_rows_cnt := DBMS_SQL.FETCH_ROWS(l_org_id_cursor);
854:
855: DBMS_SQL.COLUMN_VALUE(l_org_id_cursor, 1, l_org_id_table);
856:
857: Write_Debug('Number of rows where ITEM_NUMBER is NULL => '||To_char(l_org_id_rows_cnt));
858:
859: IF (l_org_id_rows_cnt > 0) THEN

Line 871: DBMS_SQL.CLOSE_CURSOR(l_org_id_cursor);

867: ELSE
868: x_item_num_tbl := NULL;
869: END IF; --end: IF (l_org_id_rows_cnt > 0) THEN
870: -- Bug : 4099546
871: DBMS_SQL.CLOSE_CURSOR(l_org_id_cursor);
872: END Get_Seq_Gen_Item_Nums;
873:
874:
875: ---------------------------------------------------------------------------

Line 1178: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;

1174: i NUMBER;
1175: l_cursor_select INTEGER;
1176: l_cursor_execute INTEGER;
1177:
1178: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;
1179: l_org_id_table DBMS_SQL.NUMBER_TABLE;
1180: l_trans_type_table DBMS_SQL.VARCHAR2_TABLE;
1181: l_trans_id_table DBMS_SQL.NUMBER_TABLE;
1182:

Line 1179: l_org_id_table DBMS_SQL.NUMBER_TABLE;

1175: l_cursor_select INTEGER;
1176: l_cursor_execute INTEGER;
1177:
1178: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;
1179: l_org_id_table DBMS_SQL.NUMBER_TABLE;
1180: l_trans_type_table DBMS_SQL.VARCHAR2_TABLE;
1181: l_trans_id_table DBMS_SQL.NUMBER_TABLE;
1182:
1183: ---------------------------------------------------------------------

Line 1180: l_trans_type_table DBMS_SQL.VARCHAR2_TABLE;

1176: l_cursor_execute INTEGER;
1177:
1178: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;
1179: l_org_id_table DBMS_SQL.NUMBER_TABLE;
1180: l_trans_type_table DBMS_SQL.VARCHAR2_TABLE;
1181: l_trans_id_table DBMS_SQL.NUMBER_TABLE;
1182:
1183: ---------------------------------------------------------------------
1184: -- This is to store the Sequence Generated Item Numbers.

Line 1181: l_trans_id_table DBMS_SQL.NUMBER_TABLE;

1177:
1178: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;
1179: l_org_id_table DBMS_SQL.NUMBER_TABLE;
1180: l_trans_type_table DBMS_SQL.VARCHAR2_TABLE;
1181: l_trans_id_table DBMS_SQL.NUMBER_TABLE;
1182:
1183: ---------------------------------------------------------------------
1184: -- This is to store the Sequence Generated Item Numbers.
1185: ---------------------------------------------------------------------

Line 1486: l_cursor_select := DBMS_SQL.OPEN_CURSOR;

1482: -- l_dyn_sql := l_dyn_sql || ' AND '|| l_item_number_col ||' IS NOT NULL';
1483:
1484: Write_Debug(l_dyn_sql);
1485:
1486: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);

Line 1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);

1483:
1484: Write_Debug(l_dyn_sql);
1485:
1486: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);

Line 1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);

1484: Write_Debug(l_dyn_sql);
1485:
1486: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);
1492:

Line 1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);

1485:
1486: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);
1492:
1493: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line 1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);

1486: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);
1492:
1493: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
1494: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line 1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);

1487: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
1488: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);
1492:
1493: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
1494: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
1495: Write_Debug('About to start the Loop to fetch Rows');

Line 1493: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

1489: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);
1492:
1493: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
1494: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
1495: Write_Debug('About to start the Loop to fetch Rows');
1496:
1497: -------------------------------------------------------------

Line 1494: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

1490: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);
1491: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);
1492:
1493: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
1494: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
1495: Write_Debug('About to start the Loop to fetch Rows');
1496:
1497: -------------------------------------------------------------
1498: -- Separate Index to keep track of Generated Item Numbers.

Line 1508: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

1504: -- l_gen_itemnum_indx := l_gen_item_num_tbl.FIRST;
1505: -- END IF;
1506:
1507: LOOP
1508: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
1509: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
1510: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
1511: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);
1512: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);

Line 1509: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);

1505: -- END IF;
1506:
1507: LOOP
1508: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
1509: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
1510: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
1511: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);
1512: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);
1513:

Line 1510: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);

1506:
1507: LOOP
1508: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
1509: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
1510: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
1511: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);
1512: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);
1513:
1514: Write_Debug('Retrieved rows => '||To_char(l_count));

Line 1511: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);

1507: LOOP
1508: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
1509: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
1510: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
1511: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);
1512: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);
1513:
1514: Write_Debug('Retrieved rows => '||To_char(l_count));
1515:

Line 1512: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);

1508: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
1509: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
1510: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
1511: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);
1512: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);
1513:
1514: Write_Debug('Retrieved rows => '||To_char(l_count));
1515:
1516: -------------------------------------------------------------

Line 1660: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

1656: EXIT WHEN l_count <> 2500;
1657:
1658: END LOOP;
1659:
1660: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
1661:
1662: Write_Debug('Done with Item IDs population.');
1663:
1664: Write_Debug('Populating item number into ego_bulkload_intf for function generated items');

Line 3378: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;

3374: i NUMBER;
3375: l_cursor_select INTEGER;
3376: l_cursor_execute INTEGER;
3377:
3378: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;
3379: l_org_id_table DBMS_SQL.NUMBER_TABLE;
3380: l_temp NUMBER(10) := 1;
3381: l_count NUMBER := 0;
3382: l_exists VARCHAR2(2);

Line 3379: l_org_id_table DBMS_SQL.NUMBER_TABLE;

3375: l_cursor_select INTEGER;
3376: l_cursor_execute INTEGER;
3377:
3378: l_item_number_table DBMS_SQL.VARCHAR2_TABLE;
3379: l_org_id_table DBMS_SQL.NUMBER_TABLE;
3380: l_temp NUMBER(10) := 1;
3381: l_count NUMBER := 0;
3382: l_exists VARCHAR2(2);
3383:

Line 3384: l_trans_id_table DBMS_SQL.NUMBER_TABLE;

3380: l_temp NUMBER(10) := 1;
3381: l_count NUMBER := 0;
3382: l_exists VARCHAR2(2);
3383:
3384: l_trans_id_table DBMS_SQL.NUMBER_TABLE;
3385:
3386: --------------------------------------------
3387: -- Long Dynamic SQL String
3388: --------------------------------------------

Line 3546: l_cursor_select := DBMS_SQL.OPEN_CURSOR;

3542: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
3543: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
3544: Write_Debug(l_dyn_sql);
3545:
3546: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);

Line 3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);

3543: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
3544: Write_Debug(l_dyn_sql);
3545:
3546: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);
3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line 3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);

3544: Write_Debug(l_dyn_sql);
3545:
3546: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);
3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line 3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);

3545:
3546: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);
3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
3553: Write_Debug('About to start the Loop to fetch Rows');

Line 3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);

3546: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);
3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
3553: Write_Debug('About to start the Loop to fetch Rows');
3554:

Line 3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

3547: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);
3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
3553: Write_Debug('About to start the Loop to fetch Rows');
3554:
3555: LOOP

Line 3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

3548: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);
3549: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);
3550: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);
3551: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
3553: Write_Debug('About to start the Loop to fetch Rows');
3554:
3555: LOOP
3556: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line 3556: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

3552: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
3553: Write_Debug('About to start the Loop to fetch Rows');
3554:
3555: LOOP
3556: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
3557: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
3558: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
3559: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);
3560:

Line 3557: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);

3553: Write_Debug('About to start the Loop to fetch Rows');
3554:
3555: LOOP
3556: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
3557: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
3558: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
3559: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);
3560:
3561: Write_Debug('Retrieved rows => '||To_char(l_count));

Line 3558: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);

3554:
3555: LOOP
3556: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
3557: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
3558: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
3559: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);
3560:
3561: Write_Debug('Retrieved rows => '||To_char(l_count));
3562:

Line 3559: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);

3555: LOOP
3556: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
3557: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);
3558: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);
3559: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);
3560:
3561: Write_Debug('Retrieved rows => '||To_char(l_count));
3562:
3563: -------------------------------------------------------------

Line 3650: END LOOP; --l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

3646: -- For the final batch of records, either it will be 0 or < 2500
3647: -----------------------------------------------------------------
3648: EXIT WHEN l_count <> 2500;
3649:
3650: END LOOP; --l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
3651:
3652: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
3653:
3654: Write_Debug('Done with Item IDs population.');

Line 3652: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

3648: EXIT WHEN l_count <> 2500;
3649:
3650: END LOOP; --l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
3651:
3652: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
3653:
3654: Write_Debug('Done with Item IDs population.');
3655:
3656: -----------------------------------------------------------------

Line 4395: l_attr_id_table DBMS_SQL.VARCHAR2_TABLE; -- R12C UOM Change

4391: l_api_name VARCHAR2(32) := 'load_itm_or_rev_usrattr_intf()';
4392: l_prod_col_name_tbl VARCHAR_TBL_TYPE;
4393: l_intf_col_name_tbl VARCHAR_TBL_TYPE;
4394:
4395: l_attr_id_table DBMS_SQL.VARCHAR2_TABLE; -- R12C UOM Change
4396: --DBMS_SQL.NUMBER_TABLE;
4397: l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;
4398: l_data_level_id_table DBMS_SQL.NUMBER_TABLE;
4399:

Line 4396: --DBMS_SQL.NUMBER_TABLE;

4392: l_prod_col_name_tbl VARCHAR_TBL_TYPE;
4393: l_intf_col_name_tbl VARCHAR_TBL_TYPE;
4394:
4395: l_attr_id_table DBMS_SQL.VARCHAR2_TABLE; -- R12C UOM Change
4396: --DBMS_SQL.NUMBER_TABLE;
4397: l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;
4398: l_data_level_id_table DBMS_SQL.NUMBER_TABLE;
4399:
4400: l_usr_attr_data_tbl L_USER_ATTR_TBL_TYPE;

Line 4397: l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;

4393: l_intf_col_name_tbl VARCHAR_TBL_TYPE;
4394:
4395: l_attr_id_table DBMS_SQL.VARCHAR2_TABLE; -- R12C UOM Change
4396: --DBMS_SQL.NUMBER_TABLE;
4397: l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;
4398: l_data_level_id_table DBMS_SQL.NUMBER_TABLE;
4399:
4400: l_usr_attr_data_tbl L_USER_ATTR_TBL_TYPE;
4401:

Line 4398: l_data_level_id_table DBMS_SQL.NUMBER_TABLE;

4394:
4395: l_attr_id_table DBMS_SQL.VARCHAR2_TABLE; -- R12C UOM Change
4396: --DBMS_SQL.NUMBER_TABLE;
4397: l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;
4398: l_data_level_id_table DBMS_SQL.NUMBER_TABLE;
4399:
4400: l_usr_attr_data_tbl L_USER_ATTR_TBL_TYPE;
4401:
4402: l_item_id_char VARCHAR(15);

Line 4443: -- DBMS_SQL Open Cursor integers.

4439: l_number_data NUMBER;
4440: l_date_data DATE;
4441:
4442: ---------------------------------------------------------
4443: -- DBMS_SQL Open Cursor integers.
4444: ---------------------------------------------------------
4445: l_cursor_select INTEGER;
4446: l_cursor_execute INTEGER;
4447: l_cursor_attr_id_val INTEGER;

Line 4597: l_cursor_select := DBMS_SQL.OPEN_CURSOR;

4593: l_dyn_sql := l_dyn_sql || ' ORDER BY DISPLAY_SEQUENCE '; --- R12C UOM Change.so that UOM col comes next to corresponding number col
4594:
4595: Write_Debug(l_api_name || l_dyn_sql);
4596:
4597: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
4598: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);

Line 4598: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);

4594:
4595: Write_Debug(l_api_name || l_dyn_sql);
4596:
4597: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
4598: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:

Line 4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);

4595: Write_Debug(l_api_name || l_dyn_sql);
4596:
4597: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
4598: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:
4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line 4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);

4596:
4597: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
4598: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:
4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');

Line 4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);

4597: l_cursor_select := DBMS_SQL.OPEN_CURSOR;
4598: DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:
4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line 4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

4599: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:
4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line 4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');

4600: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:
4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);

Line 4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

4601: DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);
4602:
4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);

Line 4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

4603: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
4610: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);
4611:

Line 4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);

4604: DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
4610: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);
4611:
4612: Write_Debug(l_api_name || 'Retrieved rows => '||To_char(l_count));

Line 4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);

4605: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
4610: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);
4611:
4612: Write_Debug(l_api_name || 'Retrieved rows => '||To_char(l_count));
4613: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line 4610: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);

4606: Write_Debug(l_api_name || 'About to start the Loop to fetch Rows');
4607: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
4608: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
4610: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);
4611:
4612: Write_Debug(l_api_name || 'Retrieved rows => '||To_char(l_count));
4613: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
4614:

Line 4613: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

4609: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
4610: DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);
4611:
4612: Write_Debug(l_api_name || 'Retrieved rows => '||To_char(l_count));
4613: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
4614:
4615: --------------------------------------------------------------------
4616: -- New DBMS_SQL Cursor for Select Attr Values.
4617: --------------------------------------------------------------------

Line 4616: -- New DBMS_SQL Cursor for Select Attr Values.

4612: Write_Debug(l_api_name || 'Retrieved rows => '||To_char(l_count));
4613: DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
4614:
4615: --------------------------------------------------------------------
4616: -- New DBMS_SQL Cursor for Select Attr Values.
4617: --------------------------------------------------------------------
4618: l_cursor_attr_id_val := DBMS_SQL.OPEN_CURSOR;
4619: l_dyn_attr_id_val_sql := ' SELECT ';
4620: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' TRANSACTION_ID , ';

Line 4618: l_cursor_attr_id_val := DBMS_SQL.OPEN_CURSOR;

4614:
4615: --------------------------------------------------------------------
4616: -- New DBMS_SQL Cursor for Select Attr Values.
4617: --------------------------------------------------------------------
4618: l_cursor_attr_id_val := DBMS_SQL.OPEN_CURSOR;
4619: l_dyn_attr_id_val_sql := ' SELECT ';
4620: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' TRANSACTION_ID , ';
4621: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' UPPER(TRANSACTION_TYPE) , ';
4622: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK1_VALUE , ';

Line 4672: DBMS_SQL.PARSE(l_cursor_attr_id_val, l_dyn_attr_id_val_sql, DBMS_SQL.NATIVE);

4668: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
4669: l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' AND PROCESS_STATUS = :PROCESS_STATUS ';
4670: Write_Debug(l_api_name || l_dyn_attr_id_val_sql);
4671:
4672: DBMS_SQL.PARSE(l_cursor_attr_id_val, l_dyn_attr_id_val_sql, DBMS_SQL.NATIVE);
4673: --------------------------------------------------------------------
4674: --Setting Data Type for Trasaction ID
4675: --------------------------------------------------------------------
4676: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 1, l_number_example);

Line 4676: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 1, l_number_example);

4672: DBMS_SQL.PARSE(l_cursor_attr_id_val, l_dyn_attr_id_val_sql, DBMS_SQL.NATIVE);
4673: --------------------------------------------------------------------
4674: --Setting Data Type for Trasaction ID
4675: --------------------------------------------------------------------
4676: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 1, l_number_example);
4677:
4678: --------------------------------------------------------------------
4679: --Setting Data Type for Trasaction Type
4680: --------------------------------------------------------------------

Line 4681: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example,10);

4677:
4678: --------------------------------------------------------------------
4679: --Setting Data Type for Trasaction Type
4680: --------------------------------------------------------------------
4681: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example,10);
4682:
4683: --------------------------------------------------------------------
4684: --Setting Data Type for INSTANCE_PK1_VALUE (Item ID)
4685: --------------------------------------------------------------------

Line 4686: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);

4682:
4683: --------------------------------------------------------------------
4684: --Setting Data Type for INSTANCE_PK1_VALUE (Item ID)
4685: --------------------------------------------------------------------
4686: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);
4687:
4688: --------------------------------------------------------------------
4689: --Setting Data Type for INSTANCE_PK2_VALUE (Org ID)
4690: --------------------------------------------------------------------

Line 4691: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 4, l_varchar_example, 1000);

4687:
4688: --------------------------------------------------------------------
4689: --Setting Data Type for INSTANCE_PK2_VALUE (Org ID)
4690: --------------------------------------------------------------------
4691: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 4, l_varchar_example, 1000);
4692:
4693: --------------------------------------------------------------------
4694: --Setting Data Type for INSTANCE_PK3_VALUE (Revision ID)
4695: --------------------------------------------------------------------

Line 4696: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 5, l_varchar_example, 1000);

4692:
4693: --------------------------------------------------------------------
4694: --Setting Data Type for INSTANCE_PK3_VALUE (Revision ID)
4695: --------------------------------------------------------------------
4696: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 5, l_varchar_example, 1000);
4697:
4698: --------------------------------------------------------------------
4699: --Setting Data Type for Item Num
4700: --------------------------------------------------------------------

Line 4701: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 6, l_varchar_example, 1000);

4697:
4698: --------------------------------------------------------------------
4699: --Setting Data Type for Item Num
4700: --------------------------------------------------------------------
4701: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 6, l_varchar_example, 1000);
4702:
4703: --------------------------------------------------------------------
4704: --Setting Data Type for Org Code
4705: --------------------------------------------------------------------

Line 4706: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 7, l_varchar_example, 1000);

4702:
4703: --------------------------------------------------------------------
4704: --Setting Data Type for Org Code
4705: --------------------------------------------------------------------
4706: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 7, l_varchar_example, 1000);
4707:
4708: --------------------------------------------------------------------
4709: --Setting Data Type for Revision Code
4710: --------------------------------------------------------------------

Line 4711: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 8, l_varchar_example, 1000);

4707:
4708: --------------------------------------------------------------------
4709: --Setting Data Type for Revision Code
4710: --------------------------------------------------------------------
4711: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 8, l_varchar_example, 1000);
4712:
4713: --------------------------------------------------------------------
4714: --Setting Data Type for Source System Id
4715: --------------------------------------------------------------------

Line 4716: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 9, l_number_example);

4712:
4713: --------------------------------------------------------------------
4714: --Setting Data Type for Source System Id
4715: --------------------------------------------------------------------
4716: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 9, l_number_example);
4717:
4718: --------------------------------------------------------------------
4719: --Setting Data Type for Source System Reference
4720: --------------------------------------------------------------------

Line 4721: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 10, l_varchar_example, 1000);

4717:
4718: --------------------------------------------------------------------
4719: --Setting Data Type for Source System Reference
4720: --------------------------------------------------------------------
4721: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 10, l_varchar_example, 1000);
4722:
4723: ---------------------------------------------------------------
4724: --R12C setting data type for suppplier/supplier site columns
4725: ---------------------------------------------------------------

Line 4728: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);

4724: --R12C setting data type for suppplier/supplier site columns
4725: ---------------------------------------------------------------
4726: l_dummy := 0;
4727: IF(l_supplier_name_col IS NOT NULL) THEN
4728: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);
4729: l_dummy := l_dummy +1;
4730: END IF;
4731: IF(l_supplier_number_col IS NOT NULL) THEN
4732: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);

Line 4732: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);

4728: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);
4729: l_dummy := l_dummy +1;
4730: END IF;
4731: IF(l_supplier_number_col IS NOT NULL) THEN
4732: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);
4733: l_dummy := l_dummy +1;
4734: END IF;
4735: IF(l_supplier_site_name_col IS NOT NULL) THEN
4736: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);

Line 4736: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);

4732: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);
4733: l_dummy := l_dummy +1;
4734: END IF;
4735: IF(l_supplier_site_name_col IS NOT NULL) THEN
4736: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 11 + l_dummy, l_varchar_example, 1000);
4737: l_dummy := l_dummy +1;
4738: END IF;
4739:
4740: --------------------------------------------------------------------

Line 4761: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);

4757: -- Based on the Data Type of the attribute, define the column
4758: ------------------------------------------------------------------------
4759:
4760: IF (l_data_type_code = 'C') THEN
4761: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);
4762: ELSIF (l_data_type_code = 'U') THEN -- R12C UOM Changes
4763: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 150);
4764: ELSIF (l_data_type_code = 'N') THEN
4765: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);

Line 4763: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 150);

4759:
4760: IF (l_data_type_code = 'C') THEN
4761: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);
4762: ELSIF (l_data_type_code = 'U') THEN -- R12C UOM Changes
4763: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 150);
4764: ELSIF (l_data_type_code = 'N') THEN
4765: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);
4766: ELSE --IF (l_data_type_code = 'D') THEN
4767: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);

Line 4765: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);

4761: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);
4762: ELSIF (l_data_type_code = 'U') THEN -- R12C UOM Changes
4763: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 150);
4764: ELSIF (l_data_type_code = 'N') THEN
4765: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);
4766: ELSE --IF (l_data_type_code = 'D') THEN
4767: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);
4768: END IF; --IF (l_data_type_code = 'C') THEN
4769:

Line 4767: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);

4763: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 150);
4764: ELSIF (l_data_type_code = 'N') THEN
4765: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);
4766: ELSE --IF (l_data_type_code = 'D') THEN
4767: DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);
4768: END IF; --IF (l_data_type_code = 'C') THEN
4769:
4770: END LOOP LIORUI_data_types_loop_1; --FOR i IN 1..l_attr_id_table.COUNT LOOP
4771:

Line 4773: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);

4769:
4770: END LOOP LIORUI_data_types_loop_1; --FOR i IN 1..l_attr_id_table.COUNT LOOP
4771:
4772:
4773: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
4774:
4775: write_debug(l_api_name || 'Binding the PROCESS_STATUS = '||G_INTF_STATUS_TOBE_PROCESS);
4776: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_INTF_STATUS_TOBE_PROCESS);
4777: ------------------------------------------------------------------------

Line 4776: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_INTF_STATUS_TOBE_PROCESS);

4772:
4773: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
4774:
4775: write_debug(l_api_name || 'Binding the PROCESS_STATUS = '||G_INTF_STATUS_TOBE_PROCESS);
4776: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_INTF_STATUS_TOBE_PROCESS);
4777: ------------------------------------------------------------------------
4778: -- Execute to get the Item User-Defined Attr values.
4779: ------------------------------------------------------------------------
4780: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_attr_id_val);

Line 4780: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_attr_id_val);

4776: DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_INTF_STATUS_TOBE_PROCESS);
4777: ------------------------------------------------------------------------
4778: -- Execute to get the Item User-Defined Attr values.
4779: ------------------------------------------------------------------------
4780: l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_attr_id_val);
4781:
4782: l_rows_per_attr_grp_indx := 0;
4783: ------------------------------------------------------------------------
4784: -- Loop for each row found in EBI

Line 4792: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN

4788:
4789: Write_Debug(l_api_name || 'LIORUI_ebi_rows_loop - begin');
4790:
4791:
4792: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
4793:
4794: ------------------------------------------------------------------------
4795: --Increment Row Identifier per (Attribute Group + Row) Combination.
4796: ------------------------------------------------------------------------

Line 4804: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);

4800:
4801: ------------------------------------------------------------------------
4802: -- First column is Transaction ID.
4803: ------------------------------------------------------------------------
4804: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);
4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);

Line 4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);

4801: ------------------------------------------------------------------------
4802: -- First column is Transaction ID.
4803: ------------------------------------------------------------------------
4804: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);
4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);

Line 4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);

4802: -- First column is Transaction ID.
4803: ------------------------------------------------------------------------
4804: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);
4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);

Line 4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);

4803: ------------------------------------------------------------------------
4804: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);
4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);

Line 4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);

4804: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);
4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);
4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);

Line 4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);

4805: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_transaction_type);
4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);
4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);
4813: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 10, l_source_system_ref);

Line 4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);

4806: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_item_id_char);
4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);
4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);
4813: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 10, l_source_system_ref);
4814:

Line 4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);

4807: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_org_id_char);
4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);
4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);
4813: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 10, l_source_system_ref);
4814:
4815: l_dummy := 0;

Line 4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);

4808: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_item_rev_id_char);
4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);
4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);
4813: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 10, l_source_system_ref);
4814:
4815: l_dummy := 0;
4816: l_supplier_site_id := NULL;

Line 4813: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 10, l_source_system_ref);

4809: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_item_num_char);
4810: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code_char);
4811: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_item_rev_code_char);
4812: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_source_system_id);
4813: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 10, l_source_system_ref);
4814:
4815: l_dummy := 0;
4816: l_supplier_site_id := NULL;
4817: l_supplier_id := NULL;

Line 4823: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 11 + l_dummy, l_supplier_name);

4819: l_supplier_number := NULL;
4820: l_supplier_site_name := NULL;
4821:
4822: IF(l_supplier_name_col IS NOT NULL) THEN
4823: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 11 + l_dummy, l_supplier_name);
4824: l_dummy := l_dummy +1;
4825:
4826: IF(l_supplier_name IS NOT NULL) THEN
4827: BEGIN

Line 4840: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 11 + l_dummy, l_supplier_number);

4836: END IF;
4837: END IF;
4838:
4839: IF(l_supplier_number_col IS NOT NULL) THEN
4840: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 11 + l_dummy, l_supplier_number);
4841: l_dummy := l_dummy +1;
4842: IF(l_supplier_number IS NOT NULL) THEN
4843: BEGIN
4844: SELECT VENDOR_ID

Line 4882: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 11 + l_dummy -1, l_supplier_site_name);

4878: ------------------------------------------------------------------------
4879: IF c_attr_grp_details%FOUND THEN
4880:
4881: IF(l_supplier_site_name_col IS NOT NULL) THEN
4882: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 11 + l_dummy -1, l_supplier_site_name);
4883: IF(l_supplier_site_name IS NOT NULL AND l_supplier_id IS NOT NULL) THEN
4884: BEGIN
4885:
4886: l_site_org_id := FND_PROFILE.VALUE('ORG_ID');

Line 4969: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_data);

4965: l_date_data := NULL;
4966: l_dummy_char := SUBSTR (l_intf_col_name_table(i), 1, 1);
4967:
4968: IF l_dummy_char = 'C' THEN
4969: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_data);
4970: IF l_dummy_char = l_attr_data_type THEN
4971: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR := l_varchar_data;
4972: ELSE
4973: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DISP_VALUE := l_varchar_data;

Line 4977: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_data);

4973: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DISP_VALUE := l_varchar_data;
4974: END IF;
4975: Write_Debug(l_api_name || 'String Value =>'||l_varchar_data);
4976: ELSIF l_dummy_char = 'N' THEN
4977: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_data);
4978: IF l_dummy_char = l_attr_data_type THEN
4979: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM := l_number_data;
4980: ELSE
4981: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DISP_VALUE := l_number_data;

Line 4985: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_data);

4981: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DISP_VALUE := l_number_data;
4982: END IF;
4983: Write_Debug(l_api_name || 'Number Value =>'||l_number_data);
4984: ELSE --IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'D') THEN
4985: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_data);
4986: IF l_dummy_char = l_attr_data_type THEN
4987: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE := l_date_data;
4988: ELSE
4989: l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DISP_VALUE := l_date_data;

Line 5025: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_uom_meaning);

5021: ELSE -- R12C UOM Changes:if uom column comes, previous row was the number col. so not increasing index
5022:
5023: l_varchar_data := null;
5024: l_actual_userattr_indx := l_actual_userattr_indx +1 ;
5025: DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_uom_meaning);
5026:
5027: IF (l_uom_class IS NOT NULL AND l_uom_meaning IS NOT NULL) THEN --BugFix:6271824
5028: SELECT UOM_CODE
5029: INTO l_varchar_data

Line 5042: ELSE --end: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN

5038: END IF ; -- end IF ( INSTR(l_attr_id_table(i),'$$UOM') = 0 ) -- R12C UOM Changes:
5039:
5040: END LOOP LIORUI_data_types_loop_2; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
5041:
5042: ELSE --end: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
5043:
5044: Write_Debug(l_api_name || 'Nothing Found (or) Done.');
5045: EXIT;
5046:

Line 5047: END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN

5043:
5044: Write_Debug(l_api_name || 'Nothing Found (or) Done.');
5045: EXIT;
5046:
5047: END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
5048:
5049: END LOOP LIORUI_ebi_rows_loop; --END: LOOP FOR CURSOR_ATTR_ID_VAL
5050:
5051: l_attr_id_table.DELETE;

Line 5055: DBMS_SQL.CLOSE_CURSOR(l_cursor_attr_id_val);

5051: l_attr_id_table.DELETE;
5052: l_intf_col_name_table.DELETE;
5053: l_data_level_id_table.DELETE;
5054:
5055: DBMS_SQL.CLOSE_CURSOR(l_cursor_attr_id_val);
5056:
5057: -------------------------------------------------------------------
5058: -- Loop for all the rows to be inserted per Attribute Group.
5059: -------------------------------------------------------------------

Line 5364: l_attr_id_table DBMS_SQL.NUMBER_TABLE;

5360: l_org_id_char VARCHAR2(15);
5361: l_trans_type_char VARCHAR2(15);
5362: l_attr_name VARCHAR2(100);
5363:
5364: l_attr_id_table DBMS_SQL.NUMBER_TABLE;
5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;
5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;
5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;
5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;

Line 5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;

5361: l_trans_type_char VARCHAR2(15);
5362: l_attr_name VARCHAR2(100);
5363:
5364: l_attr_id_table DBMS_SQL.NUMBER_TABLE;
5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;
5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;
5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;
5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;
5369: l_msii_col_table DBMS_SQL.VARCHAR2_TABLE;

Line 5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;

5362: l_attr_name VARCHAR2(100);
5363:
5364: l_attr_id_table DBMS_SQL.NUMBER_TABLE;
5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;
5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;
5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;
5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;
5369: l_msii_col_table DBMS_SQL.VARCHAR2_TABLE;
5370:

Line 5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;

5363:
5364: l_attr_id_table DBMS_SQL.NUMBER_TABLE;
5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;
5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;
5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;
5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;
5369: l_msii_col_table DBMS_SQL.VARCHAR2_TABLE;
5370:
5371: -- Example Data Types to be used in Bind Variable. --

Line 5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;

5364: l_attr_id_table DBMS_SQL.NUMBER_TABLE;
5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;
5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;
5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;
5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;
5369: l_msii_col_table DBMS_SQL.VARCHAR2_TABLE;
5370:
5371: -- Example Data Types to be used in Bind Variable. --
5372: l_varchar_example VARCHAR2(1000);

Line 5369: l_msii_col_table DBMS_SQL.VARCHAR2_TABLE;

5365: l_attr_disp_name_table DBMS_SQL.VARCHAR2_TABLE;
5366: l_intf_col_table DBMS_SQL.VARCHAR2_TABLE;
5367: l_attr_disp_val_table DBMS_SQL.VARCHAR2_TABLE;
5368: l_attr_int_val_table DBMS_SQL.VARCHAR2_TABLE;
5369: l_msii_col_table DBMS_SQL.VARCHAR2_TABLE;
5370:
5371: -- Example Data Types to be used in Bind Variable. --
5372: l_varchar_example VARCHAR2(1000);
5373: l_number_example NUMBER;

Line 5380: -- DBMS_SQL Open Cursor integers. --

5376: -- Actual Data to store corresponding data type value. --
5377: l_varchar_data VARCHAR2(1000);
5378: l_number_data NUMBER;
5379:
5380: -- DBMS_SQL Open Cursor integers. --
5381: l_cursor_oper_attr INTEGER;
5382: l_execute INTEGER;
5383:
5384: --API return parameters

Line 5457: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;

5453: -- Fetch the Oper Attrs IDs, Intf Column names. --
5454: ----------------------------------------------------------------------------
5455: Write_Debug(l_item_oper_attr_sql);
5456:
5457: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;
5458: DBMS_SQL.PARSE(l_cursor_oper_attr, l_item_oper_attr_sql, DBMS_SQL.NATIVE);
5459: DBMS_SQL.DEFINE_ARRAY(
5460: c => l_cursor_oper_attr -- cursor --
5461: , position => 1 -- select position --

Line 5458: DBMS_SQL.PARSE(l_cursor_oper_attr, l_item_oper_attr_sql, DBMS_SQL.NATIVE);

5454: ----------------------------------------------------------------------------
5455: Write_Debug(l_item_oper_attr_sql);
5456:
5457: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;
5458: DBMS_SQL.PARSE(l_cursor_oper_attr, l_item_oper_attr_sql, DBMS_SQL.NATIVE);
5459: DBMS_SQL.DEFINE_ARRAY(
5460: c => l_cursor_oper_attr -- cursor --
5461: , position => 1 -- select position --
5462: , n_tab => l_attr_id_table -- table of numbers --

Line 5459: DBMS_SQL.DEFINE_ARRAY(

5455: Write_Debug(l_item_oper_attr_sql);
5456:
5457: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;
5458: DBMS_SQL.PARSE(l_cursor_oper_attr, l_item_oper_attr_sql, DBMS_SQL.NATIVE);
5459: DBMS_SQL.DEFINE_ARRAY(
5460: c => l_cursor_oper_attr -- cursor --
5461: , position => 1 -- select position --
5462: , n_tab => l_attr_id_table -- table of numbers --
5463: , cnt => 2500 -- rows requested --

Line 5466: DBMS_SQL.DEFINE_ARRAY(

5462: , n_tab => l_attr_id_table -- table of numbers --
5463: , cnt => 2500 -- rows requested --
5464: , lower_bound => 1 -- start at --
5465: );
5466: DBMS_SQL.DEFINE_ARRAY(
5467: c => l_cursor_oper_attr -- cursor --
5468: , position => 2 -- select position --
5469: , c_tab => l_intf_col_table -- table of varchar --
5470: , cnt => 2500 -- rows requested --

Line 5474: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

5470: , cnt => 2500 -- rows requested --
5471: , lower_bound => 1 -- start at --
5472: );
5473:
5474: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
5475: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':ATTR_GROUP_ID', attr_grp_id_rec.OPER_ATTR_GRP_ID);
5476: Write_Debug('Binding :RESULTFMT_USAGE_ID to => '||p_resultfmt_usage_id);
5477: Write_Debug('Binding :ATTR_GROUP_ID to => '||attr_grp_id_rec.OPER_ATTR_GRP_ID);
5478:

Line 5475: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':ATTR_GROUP_ID', attr_grp_id_rec.OPER_ATTR_GRP_ID);

5471: , lower_bound => 1 -- start at --
5472: );
5473:
5474: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
5475: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':ATTR_GROUP_ID', attr_grp_id_rec.OPER_ATTR_GRP_ID);
5476: Write_Debug('Binding :RESULTFMT_USAGE_ID to => '||p_resultfmt_usage_id);
5477: Write_Debug('Binding :ATTR_GROUP_ID to => '||attr_grp_id_rec.OPER_ATTR_GRP_ID);
5478:
5479: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);

Line 5479: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);

5475: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':ATTR_GROUP_ID', attr_grp_id_rec.OPER_ATTR_GRP_ID);
5476: Write_Debug('Binding :RESULTFMT_USAGE_ID to => '||p_resultfmt_usage_id);
5477: Write_Debug('Binding :ATTR_GROUP_ID to => '||attr_grp_id_rec.OPER_ATTR_GRP_ID);
5478:
5479: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5480: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr);
5481: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_attr_id_table);
5482: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_intf_col_table);
5483:

Line 5480: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr);

5476: Write_Debug('Binding :RESULTFMT_USAGE_ID to => '||p_resultfmt_usage_id);
5477: Write_Debug('Binding :ATTR_GROUP_ID to => '||attr_grp_id_rec.OPER_ATTR_GRP_ID);
5478:
5479: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5480: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr);
5481: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_attr_id_table);
5482: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_intf_col_table);
5483:
5484: Write_Debug('load_item_oper_attr_values: Retrieved rows => '||To_char(l_count));

Line 5481: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_attr_id_table);

5477: Write_Debug('Binding :ATTR_GROUP_ID to => '||attr_grp_id_rec.OPER_ATTR_GRP_ID);
5478:
5479: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5480: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr);
5481: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_attr_id_table);
5482: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_intf_col_table);
5483:
5484: Write_Debug('load_item_oper_attr_values: Retrieved rows => '||To_char(l_count));
5485: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);

Line 5482: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_intf_col_table);

5478:
5479: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5480: l_count := DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr);
5481: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_attr_id_table);
5482: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_intf_col_table);
5483:
5484: Write_Debug('load_item_oper_attr_values: Retrieved rows => '||To_char(l_count));
5485: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);
5486:

Line 5485: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);

5481: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_attr_id_table);
5482: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_intf_col_table);
5483:
5484: Write_Debug('load_item_oper_attr_values: Retrieved rows => '||To_char(l_count));
5485: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);
5486:
5487: ----------------------------------------------------------------------------
5488: -- Fetch the Oper Attrs values from EGO_BULKLOAD_INTF. --
5489: ----------------------------------------------------------------------------

Line 5491: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;

5487: ----------------------------------------------------------------------------
5488: -- Fetch the Oper Attrs values from EGO_BULKLOAD_INTF. --
5489: ----------------------------------------------------------------------------
5490:
5491: l_cursor_oper_attr := DBMS_SQL.OPEN_CURSOR;
5492: l_dyn_sql := ' SELECT ';
5493: l_dyn_sql := l_dyn_sql || ' TRANSACTION_ID , ';
5494: l_dyn_sql := l_dyn_sql || ' UPPER(TRANSACTION_TYPE) , ';
5495: l_dyn_sql := l_dyn_sql || ' INSTANCE_PK1_VALUE , ';

Line 5509: DBMS_SQL.PARSE(l_cursor_oper_attr, l_dyn_sql, DBMS_SQL.NATIVE);

5505: l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
5506: l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
5507:
5508: Write_Debug(l_dyn_sql);
5509: DBMS_SQL.PARSE(l_cursor_oper_attr, l_dyn_sql, DBMS_SQL.NATIVE);
5510:
5511: ----------------------------------------------------------------------------
5512: -- Setting the Data type for Oper Attrs values before Select. --
5513: ----------------------------------------------------------------------------

Line 5515: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 1, l_number_example);

5511: ----------------------------------------------------------------------------
5512: -- Setting the Data type for Oper Attrs values before Select. --
5513: ----------------------------------------------------------------------------
5514: -- Setting Data Type for Trasaction ID --
5515: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 1, l_number_example);
5516: -- Setting Data Type for Transaction Type --
5517: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 2, l_varchar_example, 1000);
5518: --Setting Data Type for INSTANCE_PK1_VALUE (Item ID)
5519: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 3, l_varchar_example, 1000);

Line 5517: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 2, l_varchar_example, 1000);

5513: ----------------------------------------------------------------------------
5514: -- Setting Data Type for Trasaction ID --
5515: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 1, l_number_example);
5516: -- Setting Data Type for Transaction Type --
5517: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 2, l_varchar_example, 1000);
5518: --Setting Data Type for INSTANCE_PK1_VALUE (Item ID)
5519: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 3, l_varchar_example, 1000);
5520: --Setting Data Type for INSTANCE_PK2_VALUE (Org ID)
5521: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 4, l_varchar_example, 1000);

Line 5519: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 3, l_varchar_example, 1000);

5515: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 1, l_number_example);
5516: -- Setting Data Type for Transaction Type --
5517: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 2, l_varchar_example, 1000);
5518: --Setting Data Type for INSTANCE_PK1_VALUE (Item ID)
5519: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 3, l_varchar_example, 1000);
5520: --Setting Data Type for INSTANCE_PK2_VALUE (Org ID)
5521: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 4, l_varchar_example, 1000);
5522: FOR i in 1..l_attr_id_table.COUNT LOOP
5523: ------------------------------------------------------------------------------------

Line 5521: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 4, l_varchar_example, 1000);

5517: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 2, l_varchar_example, 1000);
5518: --Setting Data Type for INSTANCE_PK1_VALUE (Item ID)
5519: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 3, l_varchar_example, 1000);
5520: --Setting Data Type for INSTANCE_PK2_VALUE (Org ID)
5521: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, 4, l_varchar_example, 1000);
5522: FOR i in 1..l_attr_id_table.COUNT LOOP
5523: ------------------------------------------------------------------------------------
5524: -- Since TRANSACTION_ID, TRANSACTION_TYPE, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE --
5525: -- are added to the SELECT before we need to adjust the index as follows. --

Line 5528: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, l_indx, l_varchar_example, 1000);

5524: -- Since TRANSACTION_ID, TRANSACTION_TYPE, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE --
5525: -- are added to the SELECT before we need to adjust the index as follows. --
5526: ------------------------------------------------------------------------------------
5527: l_indx := i + 4;
5528: DBMS_SQL.DEFINE_COLUMN(l_cursor_oper_attr, l_indx, l_varchar_example, 1000);
5529: END LOOP; --2nd: FOR (i in 1..l_attr_id_table.COUNT) LOOP
5530:
5531: ----------------------------------------------------------------------------
5532: -- Binding the ResultFmtUsageId and Executing the Query. --

Line 5534: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);

5530:
5531: ----------------------------------------------------------------------------
5532: -- Binding the ResultFmtUsageId and Executing the Query. --
5533: ----------------------------------------------------------------------------
5534: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
5535: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5536:
5537: LOOP --Loop for l_cursor_oper_attr
5538:

Line 5535: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);

5531: ----------------------------------------------------------------------------
5532: -- Binding the ResultFmtUsageId and Executing the Query. --
5533: ----------------------------------------------------------------------------
5534: DBMS_SQL.BIND_VARIABLE(l_cursor_oper_attr,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
5535: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5536:
5537: LOOP --Loop for l_cursor_oper_attr
5538:
5539: IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN

Line 5539: IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN

5535: l_execute := DBMS_SQL.EXECUTE(l_cursor_oper_attr);
5536:
5537: LOOP --Loop for l_cursor_oper_attr
5538:
5539: IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5540:
5541: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_transaction_id);
5542: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_trans_type_char);
5543: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 3, l_item_id_char);

Line 5541: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_transaction_id);

5537: LOOP --Loop for l_cursor_oper_attr
5538:
5539: IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5540:
5541: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_transaction_id);
5542: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_trans_type_char);
5543: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 3, l_item_id_char);
5544: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 4, l_org_id_char);
5545:

Line 5542: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_trans_type_char);

5538:
5539: IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5540:
5541: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_transaction_id);
5542: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_trans_type_char);
5543: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 3, l_item_id_char);
5544: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 4, l_org_id_char);
5545:
5546: Write_Debug('load_item_oper_attr_values: l_trans_type_char => '||l_trans_type_char);

Line 5543: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 3, l_item_id_char);

5539: IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5540:
5541: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_transaction_id);
5542: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_trans_type_char);
5543: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 3, l_item_id_char);
5544: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 4, l_org_id_char);
5545:
5546: Write_Debug('load_item_oper_attr_values: l_trans_type_char => '||l_trans_type_char);
5547: ------------------------------------------------------------------------------------

Line 5544: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 4, l_org_id_char);

5540:
5541: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 1, l_transaction_id);
5542: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 2, l_trans_type_char);
5543: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 3, l_item_id_char);
5544: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, 4, l_org_id_char);
5545:
5546: Write_Debug('load_item_oper_attr_values: l_trans_type_char => '||l_trans_type_char);
5547: ------------------------------------------------------------------------------------
5548: -- Prepare Primary Key Name Value pair object. --

Line 5575: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, l_indx, l_varchar_data);

5571: -- Since TRANSACTION_ID, TRANSACTION_TYPE, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE --
5572: -- are added to the SELECT before we need to adjust the index as follows. --
5573: ------------------------------------------------------------------------------------
5574: l_indx := i + 4;
5575: DBMS_SQL.COLUMN_VALUE(l_cursor_oper_attr, l_indx, l_varchar_data);
5576: Write_Debug('load_item_oper_attr_values: String Value =>'||l_varchar_data);
5577:
5578: -- store the Display values in a plsql table --
5579: l_attr_disp_val_table(i) := l_varchar_data;

Line 5750: ELSE --IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN

5746: Write_Debug(l_dyn_sql);
5747:
5748: EXECUTE IMMEDIATE l_dyn_sql;
5749:
5750: ELSE --IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5751: -------------------------------------------------------------------
5752: -- Exit loop as there are no more rows available for processing. --
5753: -------------------------------------------------------------------
5754: Write_Debug('load_item_oper_attr_values: No more rows found !');

Line 5756: END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN

5752: -- Exit loop as there are no more rows available for processing. --
5753: -------------------------------------------------------------------
5754: Write_Debug('load_item_oper_attr_values: No more rows found !');
5755: EXIT;
5756: END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5757:
5758: END LOOP; --END: Loop for l_cursor_oper_attr
5759: -- Bug : 4099546
5760: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);

Line 5760: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);

5756: END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_oper_attr)>0 THEN
5757:
5758: END LOOP; --END: Loop for l_cursor_oper_attr
5759: -- Bug : 4099546
5760: DBMS_SQL.CLOSE_CURSOR(l_cursor_oper_attr);
5761: END IF; --IF (attr_grp_id_rec.OPER_ATTR_GRP_ID IS NOT NULL) THEN
5762:
5763: -------------------------------------------------------------------
5764: -- Before proceeding to process the next Attribute Group rows, --

Line 6998: l_org_id_table DBMS_SQL.VARCHAR2_TABLE;

6994: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6995:
6996: l_cc_id_table num_tbl_type;
6997: l_trans_id_table num_tbl_type;
6998: l_org_id_table DBMS_SQL.VARCHAR2_TABLE;
6999: l_ss_id_table num_tbl_type;
7000: l_ss_ref_table EGO_VARCHAR_TBL_TYPE;
7001: l_item_num_table EGO_VARCHAR_TBL_TYPE;
7002: l_old_item_num_table EGO_VARCHAR_TBL_TYPE; --Added R12C