Israr Oracle: Function

XXPCL_DOCUMENT_ATTACHED(APA.INVOICE_ID) Doc_Attached,

-------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION APPS.XXPCL_DOCUMENT_ATTACHED (
P_INVOICE_ID IN NUMBER)
RETURN VARCHAR
AS
V_TITLE VARCHAR2 (4000);
V_DESCRIPTION VARCHAR2 (4000);
V_ENTITY_NAME VARCHAR2 (4000);

--V_COUNT NUMBER:=0;

CURSOR C1
IS
SELECT TITLE --|| DECODE (DT.DESCRIPTION, NULL, NULL, ' - ' || DT.DESCRIPTION)
|| ' - ' || d.FILE_NAME DOC_REF
--AD.SEQ_NUM, DCT.USER_NAME, DT.TITLE, DT.DESCRIPTION, AD.ENTITY_NAME, DAT.USER_NAME, d.FILE_NAME, AD.ATTACHED_DOCUMENT_ID, DET.USER_ENTITY_NAME,
--DAT.NAME, D.DOCUMENT_ID, AD.PK1_VALUE, D.MEDIA_ID, D.URL, DBMS_LOB.SUBSTR(L.file_data,1,10) file_data
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET,
FND_DOCUMENTS_TL DT,
FND_DOCUMENTS D,
FND_DOCUMENT_CATEGORIES_TL DCT,
FND_ATTACHED_DOCUMENTS AD,
FND_LOBS L
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND L.FILE_ID = D.MEDIA_ID
AND DT.TITLE IS NOT NULL
--and TITLE='JV 5492a'
AND AD.ATTACHED_DOCUMENT_ID<>843450
AND PK1_VALUE =to_char( P_INVOICE_ID) --270952
AND DAT.name IN ('FILE');
--and Ad.ENTITY_NAME not in ('AP_CHECKS','AP_INVOICES','GL_JE_HEADERS');

A_COUNT NUMBER := 1;
--FIRST_REF VARCHAR2(100);
FINAL_REF VARCHAR2 (4000);
BEGIN
FOR I IN C1
LOOP
IF A_COUNT = 1
THEN
FINAL_REF := I.DOC_REF;
A_COUNT := A_COUNT + 1;
ELSE
FINAL_REF := FINAL_REF || ' / ' || I.DOC_REF;
END IF;
END LOOP;

RETURN (FINAL_REF);
EXCEPTION
WHEN OTHERS THEN RETURN (NULL);
END;
/

----------------------------------------------------------------------------------------------

XXPCL_CREATE_BY_NAME(APA.created_by ) CREATED_BY_NAME

--------------------------------------------------------------------------------------------

CREATE OR REPLACE function APPS.XXPCL_CREATE_BY_NAME(P_USER_ID IN NUMBER) RETURN VARCHAR as

V_user_name VARCHAR2(100);
V_EMPLOYEE_ID VARCHAR2(100);
V_FULL_NAME VARCHAR2(100);

BEGIN

select user_name, EMPLOYEE_ID
INTO V_user_name, V_EMPLOYEE_ID
from fnd_user
where user_id = P_USER_ID -- 3480
;

IF V_EMPLOYEE_ID IS NOT NULL THEN

select FULL_NAME
INTO V_FULL_NAME
from per_people_f A
where PERSON_ID = V_EMPLOYEE_ID ---1948
;

RETURN(V_FULL_NAME);

ELSE

RETURN (V_user_name);

END IF;

EXCEPTION WHEN OTHERS THEN RETURN(NULL);

END;
/

---------------------------------------------------------------------------------------------------------

XXPCL_CREATE_BY_EMP_NO(APA.created_by) CREATED_EMP_NO
---------------------------------------------------------------------------------------------------

CREATE OR REPLACE function APPS.XXPCL_CREATE_BY_EMP_NO(P_USER_ID IN NUMBER) RETURN VARCHAR as

V_user_name VARCHAR2(100);
V_EMPLOYEE_ID VARCHAR2(100);
V_FULL_NAME VARCHAR2(100);

BEGIN

select user_name, EMPLOYEE_ID
INTO V_user_name, V_EMPLOYEE_ID
from fnd_user
where user_id = P_USER_ID -- 3480
;

IF V_EMPLOYEE_ID IS NOT NULL THEN

select EMPLOYEE_NUMBER
INTO V_FULL_NAME
from per_people_f A
where PERSON_ID = V_EMPLOYEE_ID ---1948
;

RETURN(V_FULL_NAME);

ELSE

RETURN (V_user_name);

END IF;

EXCEPTION WHEN OTHERS THEN RETURN(NULL);

END;
/

------------------------------------------------------------------------------------------------------

XXPCL_GET_ACCOUNT_DESC( aid.dist_code_combination_id) ACCOUNT_DESC
--------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION APPS.XXPCL_get_account_desc (

p_ccc_id IN NUMBER)

RETURN CHAR

CURSOR cur_segments (

p_ccc_id IN NUMBER)

SELECT *

FROM gl_code_combinations

WHERE code_combination_id = p_ccc_id;

lv_all_desc VARCHAR2 (1000);

lv_desc1 VARCHAR2 (100);

lv_desc2 VARCHAR2 (100);

lv_desc3 VARCHAR2 (200);

lv_desc4 VARCHAR2 (200);

lv_desc5 VARCHAR2 (100);

lv_desc6 VARCHAR2 (100);

lv_desc7 VARCHAR2 (100);

lv_desc8 VARCHAR2 (100);

lv_desc9 VARCHAR2 (100);

lv_desc10 VARCHAR2 (100);

lv_desc11 VARCHAR2 (100);

v_error VARCHAR2 (2000);

BEGIN

FOR rec_segments IN cur_segments (p_ccc_id)

LOOP

BEGIN

SELECT ffvt.description

INTO lv_desc1

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Company'

AND ffv.flex_value = rec_segments.segment1;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc2

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL MIS'

AND ffv.flex_value = rec_segments.segment2;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc3

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Account'

AND ffv.flex_value = rec_segments.segment3;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc4

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Company'

AND ffv.flex_value = rec_segments.segment4;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc5

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Future'

AND ffv.flex_value = rec_segments.segment5;

END;

lv_all_desc := lv_desc1 || '-' || lv_desc2 || '-' || lv_desc3 || '-'

|| lv_desc4 || '-' || lv_desc5;

END LOOP;

DBMS_OUTPUT.put_line ('Function Returns - ' || lv_all_desc);

-- insert into ss_test51(id,msg)values(100,'Function end' ||lv_all_desc);

RETURN lv_all_desc;

EXCEPTION

WHEN OTHERS THEN

v_error := substr(SQLERRM, 0, 200);

DBMS_OUTPUT.put_line ('Function end with Error - ' || v_error);

--insert into ss_test51(id,msg)values(100,'Function end with Error - ' || v_error);

return v_error;

END;

----------------------------------------------------------------------------------------------------------

XXPCL_GET_ACCT_DESC( 3,GCC.code_combination_id) ACCT_DESC

----------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION APPS.XXPCL_GET_ACCT_DESC (

P_SEGMENT_LOC IN NUMBER,

p_ccc_id IN NUMBER)

RETURN CHAR

CURSOR cur_segments (

p_ccc_id IN NUMBER)

SELECT *

FROM gl_code_combinations

WHERE code_combination_id = p_ccc_id;

lv_all_desc VARCHAR2 (1000);

lv_desc1 VARCHAR2 (100);

lv_desc2 VARCHAR2 (100);

lv_desc3 VARCHAR2 (200);

lv_desc4 VARCHAR2 (200);

lv_desc5 VARCHAR2 (100);

lv_desc6 VARCHAR2 (100);

lv_desc7 VARCHAR2 (100);

lv_desc8 VARCHAR2 (100);

lv_desc9 VARCHAR2 (100);

lv_desc10 VARCHAR2 (100);

lv_desc11 VARCHAR2 (100);

v_error VARCHAR2 (2000);

BEGIN

FOR rec_segments IN cur_segments (p_ccc_id)

LOOP

BEGIN

SELECT ffvt.description

INTO lv_desc1

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Company'

AND ffv.flex_value = rec_segments.segment1;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc2

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL MIS'

AND ffv.flex_value = rec_segments.segment2;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc3

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Account'

AND ffv.flex_value = rec_segments.segment3;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc4

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Company'

AND ffv.flex_value = rec_segments.segment4;

END;

BEGIN

SELECT ffvt.description

INTO lv_desc5

FROM fnd_flex_value_sets ffvs

,fnd_flex_values ffv

,fnd_flex_values_tl ffvt

WHERE 1 = 1

AND ffvs.flex_value_set_id = ffv.flex_value_set_id

AND ffv.flex_value_id = ffvt.flex_value_id

AND ffvs.flex_value_set_name LIKE 'PFCCL Future'

AND ffv.flex_value = rec_segments.segment5;

END;

lv_all_desc := lv_desc1 || '-' || lv_desc2 || '-' || lv_desc3 || '-'

|| lv_desc4 || '-' || lv_desc5;

IF P_SEGMENT_LOC = 1 THEN

RETURN(lv_desc1);

ELSIF P_SEGMENT_LOC = 2 THEN

RETURN(lv_desc2);

ELSIF P_SEGMENT_LOC = 3 THEN

RETURN(lv_desc3);

ELSIF P_SEGMENT_LOC = 4 THEN

RETURN(lv_desc4);

ELSIF P_SEGMENT_LOC = 5 THEN

RETURN(lv_desc5);

ELSE RETURN(lv_all_desc);

END IF;

END LOOP;

DBMS_OUTPUT.put_line ('Function Returns - ' || lv_all_desc);

-- insert into ss_test51(id,msg)values(100,'Function end' ||lv_all_desc);

RETURN lv_all_desc;

EXCEPTION

WHEN OTHERS THEN

v_error := substr(SQLERRM, 0, 200);

DBMS_OUTPUT.put_line ('Function end with Error - ' || v_error);

--insert into ss_test51(id,msg)values(100,'Function end with Error - ' || v_error);

return v_error;

END;

----------------------------------Convert value in word-----------------------

fnd_file.put_line (fnd_file.output,'<DIGIT_TO_WORD>' ||digit_to_word1(V_TOTAL_DR ) || '</DIGIT_TO_WORD>' );

-----------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE function APPS.digit_to_word1(net_amount number) return char is

p_word varchar2(500);

p_int varchar2(480);

p_dec Varchar2(100);

p_pos number ;

begin

select

decode(substr(lpad(trunc(net_amount),9,'0'),1,2),

0,null,to_char(to_date(substr(lpad(trunc(net_amount),9,'0'),1,

2),'J'),'Jsp')|| ' Crores ' )

decode(substr(lpad(trunc(net_amount),9,'0'),3,2),

0,null,to_char(to_date(substr(lpad(trunc(net_amount),9,'0'),3,

2),'J'),'Jsp')|| ' Lakhs ' )

|| decode(substr(lpad(trunc(net_amount),9,'0'),5,2),

0,null,to_char(to_date(substr(lpad(trunc(net_amount),9,'0'),5,

2),'J'),'Jsp')|| ' Thousand ' )

|| decode(substr(lpad(trunc(net_amount),9,'0'),7,1),

0,null,to_char(to_date(substr(lpad(trunc(net_amount),9,'0'),7,1),'J'),'Jsp')|| ' Hundred ' )

|| decode(substr(lpad(trunc(net_amount),9,'0'),8),0,null,to_char(to_date(substr(lpad(trunc(net_amount),9,'0'),8),'J'),'Jsp')

)

into p_int

from dual;

p_dec :=dec_word(net_amount);

IF trunc(net_amount) =0 then

IF P_dec is not null then

p_dec := p_dec||' Paise ';

return(p_dec||' Only ');

else

return(null);

END IF;

elsif trunc(net_amount) >0 then

IF P_dec is not null then

p_dec := p_dec||' Paise ';

return('Rupees '||p_int||' '||' and '||p_dec||' Only ');

else

return('Rupees '||p_int||' Only ');

END IF;

END if;

end;

-----------------------------------------------------------

Israr Oracle

Monday, October 8, 2018

Function

No comments:

Post a Comment