[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.column_value line
BULK COLLECT INTO l_sortedArray
FROM TABLE(LineArray) a
WHERE a.column_value IS NOT NULL
ORDER BY line;
SELECT To_Number(ClinNum) INTO temp FROM dual;
SELECT LPad(To_Char(temp),4,'0') INTO retValue FROM dual;
SELECT ELIN_TO_DECIMAL(ElinNum) INTO temp FROM dual;
SELECT DECIMAL_TO_ELIN(temp) INTO retValue FROM dual;
Select LPad(to_char(LEVEL),4,'0') from dual
connect by level < 10000
3. Maintain ASSIGNED_NUM_ARRAY_PTR = 0 as pointer to first element of ASSIGNED_NUM_ARRAY
4. Maintain ALL_NUMBERS_PTR = 0 as pointer to the first element of ALL_NUMBERS.
5. While ASSIGNED_NUM_ARRAY_PTR < length(ASSIGNED_NUM_ARRAY_PTR) loop
7. If ASSIGNED_NUM_ARRAY[ASSIGNED_NUM_ARRAY_PTR] greater than
ALL_NUMBERS[ALL_NUMBERS_PTR] then return
ALL_NUMBERS[ALL_NUMBERS_PTR] and exit the function as we have found a
gap in the numbering.
6. If ASSIGNED_NUM_ARRAY[ASSIGNED_NUM_ARRAY_PTR] equals to
ALL_NUMBERS[ALL_NUMBERS_PTR] then increment
ASSIGNED_NUM_ARRAY_PTR and ALL_NUMBERS_PTR by 1
8. End While Loop
9. If ALL_NUMBERS_PTR < 9999
then return ALL_NUMBERS[ALL_NUMBERS_PTR]
10. Return exception NO_MORE_LINES_CAN_BE_ADDED
Note: In the while loop, ASSIGNED_NUM_ARRAY[ASSIGNED_NUM_ARRAY_PTR] can never be less than ALL_NUMBERS[ALL_NUMBERS_PTR],
since the ALL_NUMBERS array is gapless.
Assumption - The input Array is already in sorted order.
***************************************************************************************/
FUNCTION NEXT_CLIN_NUM (ASSIGNED_NUM_ARRAY PO_TBL_VARCHAR100)
return VARCHAR2
IS
ALL_NUMBERS PO_TBL_VARCHAR100;
SELECT LPad(To_Char(LEVEL),4,'0')
BULK COLLECT INTO ALL_NUMBERS
FROM dual
CONNECT BY LEVEL <10000;
l_line_id_qry := 'select ' || lineid_col ||' from ' || lines_tbl_name
|| ' where ' || doc_id_col || ' = ' || P_Document_ID
|| ' and LINE_NUM_DISPLAY = '''|| p_ClinNumber||'''';
line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
|| ' where group_line_id is null and '|| exhibit_col ||' is null and '
|| doc_id_col ||' = ' || p_Document_ID
|| ' order by line_num_display';
line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
|| ' where group_line_id is null and ' || doc_id_col || ' = ' || p_Document_ID
|| ' order by line_num_display';
SELECT Nvl(Max(ROWNUM),0)+1
INTO lineNumber
FROM
(SELECT pon_clo_renumber_pkg.ELIN_TO_DECIMAL(SubStr(line_num_display,exhibit_len+1,4-exhibit_len)) elin_decimal
FROM
pon_auction_item_prices_all where auction_header_id = p_Document_ID
and exhibit_number = p_exhibit_number AND line_num_display IS NOT null order by elin_decimal)
WHERE ELIN_DECIMAL=rownum;
line_num_tbl_qry := 'SELECT Nvl(Max(ROWNUM),0)+1 FROM (SELECT pon_clo_renumber_pkg.ELIN_TO_DECIMAL(SubStr(line_num_display,'
|| exhibit_len||'+1,4-'||exhibit_len||')) elin_decimal FROM '
|| lines_tbl_name
|| ' where ' || doc_id_col || ' = ' || p_Document_ID
|| ' and '||exhibit_col||' ='''||p_exhibit_number||''''
|| ' order by elin_decimal) '
|| ' WHERE ELIN_DECIMAL=rownum';
line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
|| ' where group_line_id is null and '|| exhibit_col ||' is null and '
|| doc_id_col || ' = ' || p_Document_ID
|| ' order by line_num_display';
line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
|| ' where group_line_id is null and ' || doc_id_col || ' = ' || p_Document_ID
|| ' order by line_num_display';
sel_oldnum_qry := 'SELECT line_num_display
FROM ' || lines_tbl_name || ' where ' || lineid_col || ' = ' || p_line_id || ' and '
|| doc_id_col || ' = ' || p_doc_id;
dup_check_qry := 'SELECT ''D''
FROM ' || lines_tbl_name || ' where group_line_id is null
and line_num_display = :new_num and ' || doc_id_col || ' = :doc_id';
l_clin_query := 'Select line_num_display, ' || lineid_col || ' from ' || lines_tbl_name ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and '|| exhibit_col ||' is null and '||'group_line_id is null order by line_number ';
l_clin_query := 'Select line_num_display, ' || lineid_col || ' from ' || lines_tbl_name ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and '|| exhibit_col ||' is null '|| 'and group_line_id is null order by line_number ';
l_clin_query := 'Select line_num_display, ' || lineid_col || ' from ' || lines_tbl_name ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and group_line_id is null'
|| ' and nvl(LINE_DISP_EDITED_FLAG,''N'') = ''N''';
l_option_query := 'update ' || lines_tbl_name ||
' set clm_base_line_num = ' || lastClinNum ||
' where ' || doc_id_col || ' = ' || Document_ID ||
' and clm_base_line_num = ' || tmpLineId;
l_updt_query := 'update ' || lines_tbl_name ||
' set line_num_display = ''' || lastClinNum || '''' ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and ' || lineid_col || ' = ' || tmpLineId;
l_slin_query := 'select ' || lineid_col || ', clm_info_flag FROM ' || lines_tbl_name
|| ' where group_line_id = ' || tmpLineId
|| ' and ' || doc_id_col || ' = ' || p_Document_ID
|| ' order by line_number ';
l_slin_query := 'select ' || lineid_col || ', clm_info_flag FROM ' || lines_tbl_name
|| ' where group_line_id = ' || tmpLineId
|| ' and ' || doc_id_col || ' = ' || p_Document_ID
|| ' and nvl(LINE_DISP_EDITED_FLAG,''N'') = ''N''';
l_updt_query := 'update ' || lines_tbl_name ||
' set line_num_display = ''' || lastPricedSlin || '''' ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and ' || lineid_col || ' = ' || slnLineId;
l_updt_query := 'update ' || lines_tbl_name ||
' set line_num_display = ''' || lastInfoSlin || '''' ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and ' || lineid_col || ' = ' || slnLineId;
l_info_slin_query := 'select line_num_display FROM ' || lines_tbl_name
|| ' where group_line_id = ' || l_Line_id
|| ' and ' || doc_id_col || ' = ' || P_Document_ID
|| ' and CLM_INFO_FLAG=''Y''
order by line_num_display';
l_price_slin_query := 'select LINE_NUM_DISPLAY FROM ' || lines_tbl_name
|| ' where group_line_id = ' || l_Line_id
|| ' and ' || doc_id_col || ' = ' || P_Document_ID
|| ' and CLM_INFO_FLAG <> ''Y''
order by line_num_display';
l_slin_query := 'select ' || lineid_col || ', clm_info_flag FROM ' || lines_tbl_name
|| ' where group_line_id = ' || l_group_line_id
|| ' and ' || doc_id_col || ' = ' || p_Document_ID
|| ' order by ' || lineid_col;
l_updt_query := 'update ' || lines_tbl_name ||
' set line_num_display = ''' || lastPricedSlin || '''' ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and ' || lineid_col || ' = ' || slnLineId;
l_updt_query := 'update ' || lines_tbl_name ||
' set line_num_display = ''' || lastInfoSlin || '''' ||
' where ' || doc_id_col || ' = ' || p_Document_ID ||
' and ' || lineid_col || ' = ' || slnLineId;
select nvl(clm_info_flag,'N'),group_line_id into l_infoFlag,l_clinNum
from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id
and line_number = p_line_number;
select line_num_display into l_clinNumDsp
from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id
and line_number = l_clinNum;
update pon_auction_item_prices_all
set line_num_display = l_lineNum
where auction_header_id = p_auction_header_id
and line_number = p_line_number;
SELECT INTERFACE_LINE_ID
INTO l_group_line_id
FROM PO_LINES_INTERFACE
WHERE INTERFACE_HEADER_ID = p_interface_header_id AND
LINE_NUM_DISPLAY = p_clinnumber;
l_slin_query := 'SELECT INTERFACE_LINE_ID,
CLM_INFO_FLAG
FROM PO_LINES_INTERFACE
WHERE GROUP_LINE_ID = ' || l_group_line_id || ' AND
INTERFACE_HEADER_ID = ' || p_interface_header_id ||
' ORDER BY INTERFACE_LINE_ID';
l_updt_query := ' UPDATE PO_LINES_INTERFACE
SET LINE_NUM_DISPLAY = ''' || lastPricedSlin || '''' ||
' WHERE INTERFACE_HEADER_ID = ' || p_interface_header_id ||
' AND INTERFACE_LINE_ID = ' || slnLineId;
l_updt_query := ' UPDATE PO_LINES_INTERFACE
SET LINE_NUM_DISPLAY = ''' || lastInfoSlin || '''' ||
' WHERE INTERFACE_HEADER_ID = ' || p_interface_header_id ||
' AND INTERFACE_LINE_ID = ' || slnLineId;
INSERT INTO vhk_dummy VALUES (err_msg);
line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
|| ' where group_line_id is null and ' || doc_id_col || ' = ' || p_bid_number
|| ' and auction_line_number = -1 ' || ' union ' ||
' SELECT LPad(To_Char(LEVEL),4,0) FROM dual '||
' CONNECT BY LEVEL <= (SELECT to_NUMBER(Max(line_num_display)) '||
' FROM pon_auction_item_prices_all WHERE AUCTION_HEADER_ID = '|| p_auction_id ||
' AND GROUP_LINE_ID is null and exhibit_number is null)' ;