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
IS
CURSOR cur_segments (
p_ccc_id IN NUMBER)
IS
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
IS
CURSOR cur_segments (
p_ccc_id IN NUMBER)
IS
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;
/
-----------------------------------------------------------
