LKM , IKM and CKM (Knowledge Modules)

URL:
https://www.youtube.com/watch?v=VzHgbFyjwHA

                                                              LKM ,IKM and CKM

                                                                             CKM

RefCursor

DECLARE
  refvar    VARCHAR2(100)   :='and EMP_MI=''J''';
  query_str VARCHAR2 (3000) := NULL;
TYPE cur_typ
IS
  REF
  CURSOR;
    c cur_typ;
    V_SPRIDEN_ID VARCHAR2(1000);
  BEGIN
    query_str:='SELECT SPRIDEN_ID FROM EMP WHERE EMP_ID in(''G19320418'',''G18649335'')'||refvar||'';
    OPEN c FOR query_str;
    LOOP
      FETCH c INTO V_SPRIDEN_ID;
    EXIT
  WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_SPRIDEN_ID);
  END LOOP;
  CLOSE c;
END;

Comma Separated Values Code

/* Comma Separated Values By Dynamic Sql */

DECLARE
  L_COUNT BINARY_INTEGER;
  L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
  VAR VARCHAR2(100);
  temp_count9999 NUMBER;
BEGIN
  DBMS_UTILITY.COMMA_TO_TABLE (
  LIST => REGEXP_REPLACE ('10,11,121,123', '(^|,)', '\1X'), TABLEN => L_COUNT, TAB => L_ARRAY ) ;
  DBMS_OUTPUT.PUT_LINE ('L_COUNT :   ' || L_COUNT);
  FOR I IN 1 .. L_COUNT
  LOOP
    --DBMS_OUTPUT.PUT_LINE (SUBSTR (L_ARRAY (I), 2));
    VAR:=SUBSTR (L_ARRAY (I), 2);
    --DBMS_OUTPUT.PUT_LINE (VAR);
    select
    to_char(nvl(count(*), 0))  INTO temp_count9999
    from  (SELECT COLUMN_VALUE col_value FROM TABLE (gzkplib.f_string_2_tab ('10,43,51,85'))) a
    where a.col_value = VAR;
    DBMS_OUTPUT.PUT_LINE('temp_count9999 :'||temp_count9999);
  END LOOP;
END;


Dynamic Sql


If par_value is not null then

if ((PARA_VAL_TABLE IS NOT NULL) AND (PARA_VAL_COLUMN IS NOT NULL) AND (PARA_VAL_CONDITION IS NOT NULL)) THEN

--Separating With Commas **

  DBMS_UTILITY.COMMA_TO_TABLE (
  LIST => REGEXP_REPLACE (par_value, '(^|,)', '\1X'), TABLEN => L_COUNT, TAB => L_ARRAY ) ;
  DBMS_OUTPUT.PUT_LINE ('L_COUNT :   ' || L_COUNT);
  --var_value:=SUBSTR (L_ARRAY (I), 2);

FOR I IN 1 .. L_COUNT
  LOOP
  var_value:=SUBSTR (L_ARRAY (I), 2);
  temp_str:=PARA_VAL_TABLE||' where '||PARA_VAL_COLUMN || ' ' ||PARA_VAL_CONDITION  || ' ''' || var_value || '''';
    DBMS_OUTPUT.PUT_LINE (SUBSTR (L_ARRAY (I), 2));
    DBMS_OUTPUT.PUT_LINE (var_value);
    
    ValidatePara_Qry := 'select    to_char(nvl(count(*), 0)) temp_count9999  from  '||temp_str||'';
    DBMS_OUTPUT.PUT_LINE(ValidatePara_Qry);  
    EXECUTE IMMEDIATE ValidatePara_Qry into v_temp_count9999;
    DBMS_OUTPUT.PUT_LINE('temp_count9999 :'||v_temp_count9999);
   
   
     /* if #_debug
      dbms_output.put_line('Validation count = '||v_temp_count9999||' for# '||para_number||'.'||para_index||' select= '||temp_str); -- show 'Validation count = ' &temp_count9999 ' for# ' #para_number'.' #para_index  ' select= ' $temp_str
      end-if */
      if (v_temp_count9999= '0') then
      temp_str:= 'Invalid Value. Value not in ';
      temp_str:= temp_str || PARA_VAL_TABLE;
     if PARA_VAL_ERROR is null then
      SetParaError(para_number, para_index, temp_str,v_job_name ,v_job_number ) ;
      else
      SetParaError(para_number, para_index, PARA_VAL_ERROR ,v_job_name ,v_job_number ) ;
      end if;
      end if;

SUB TEMPLATES

For Desktop  Testing:

<?import:file:C:///Users/Vishnun/Desktop/RTF/BIP7Reports/Subtemplate/Etext_Subtemplate.rtf?>

InBIP Importing:

<?import:xdoxsl:///app/banner/lib/devl/links/FINAL_REPORTS/BIP_Reports/BIP_TESTED/BI_SUBTEMPLATE.xsb?>

Calling:

<?call-template: xxxx_BIP_HEADER?>

Presentation variables(obiee):: in Title

Presentation variables(obiee)::

SELECT * FROM OEFG_ORDER_HEADERS WHERE HEADER_ID = '@{PP_HEADER_ID}'
Multiple::

IN (@{PP_HEADER_ID}['@']{'1023'})


Title Dianamically::
INVENTORY (@{PV_SUBINVENTORY_NAME}['@']{'013'})

Wrod Wrapin in PL Sql

----Wrod Wrap-- package--
CREATE OR REPLACE PACKAGE WROD_WRAP
AS
FUNCTION bi_word_wrap (p_str varchar2, p_linesize pls_integer, p_sep varchar2 := ' ') RETURN VARCHAR2;
END WROD_WRAP;

---BOdy---
CREATE OR REPLACE PACKAGE BODY WROD_WRAP
AS
FUNCTION bi_word_wrap (p_str varchar2, p_linesize pls_integer, p_sep varchar2 := ' ') RETURN VARCHAR2
as
l_str long := p_str || p_sep;
l_line long;
l_pos pls_integer;
R_VAL VARCHAR2(3000):=NULL;
begin
while l_str is not null loop
l_line := substr(l_str, 1, p_linesize);
l_pos := instr(l_line, p_sep, -1);
l_line := substr(l_line, 1, l_pos);
dbms_output.put_line(l_line);
R_VAL:=R_VAL||CHR(10)||l_line;
l_str := substr(l_str, l_pos + 1);
end loop;
RETURN R_VAL;
end bi_word_wrap;
END WROD_WRAP;

 ----execution 
  DECLARE
  RET VARCHAR2(1000);
  begin
  RET:=WROD_WRAP.bi_word_wrap(:S,20);
  END;

select WROD_WRAP.bi_word_wrap(:S,20) a from dual;

Public Synonym

CREATE PUBLIC SYNONYM PACKAGE_NAME FOR BANINST1.PACKAGE_NAME