Home » SQL & PL/SQL » SQL & PL/SQL » inconsistent datatypes: expected - got -
inconsistent datatypes: expected - got - [message #268896] |
Thu, 20 September 2007 01:12  |
kogilaah_k
Messages: 20 Registered: May 2007 Location: Malaysia
|
Junior Member |
|
|
hi there,
I need some help with my codes.
Below is my sql code, and when i execute it says "inconsistent datatypes: expected - got -'!!. I am using dynamic sql to simplify the codings...what could be the problem..please assist me.
.....
/*=======================================
MAIN
=======================================*/
BEGIN
FND_FILE.PUT_NAMES'/oca/ocafsh/tmp/TMPO0430.log','/oca/ocafsh/tmp/TMPO0430.out','/oca/ocafsh/tmp');
CASE
WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NOT NULL THEN
c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';
WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NULL THEN
c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
c_smt2 := null;
WHEN PRA_BUYER_ID IS NULL AND PRA_VENDOR_ID IS NOT NULL THEN
c_smt1 := null;
c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';
else
c_smt1 := null;
c_smt2 := null;
END CASE;
/*SELECT HE.FULL_NAME, SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,'@'))
INTO T_BUYER_NAME, T_MAIL
FROM HR_EMPLOYEES HE
WHERE HE.EMPLOYEE_ID = PRA_BUYER_ID;*/
/*SELECT PV.SEGMENT1
INTO T_VENDOR_CODE
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID = PRA_VENDOR_ID;
*/
-- Construct a dynamic SQL to retrieve the vendors details of the buyer
c_sql_smt := ' SELECT distinct PHA.Vendor_Id,'||
' HE.FULL_NAME,'||
' SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,''@'')),'||
' PV.SEGMENT1'||
' FROM PO_HEADERS_ALL PHA,'||
'PO_LINES_ALL PLA,'||
'PO_LINE_LOCATIONS_ALL PLLA,'||
'HR_EMPLOYEES HE,'||
'PO_VENDORS PV'||
' WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID'||
' AND PHA.AUTHORIZATION_STATUS= ''APPROVED'''||
' AND PHA.TYPE_LOOKUP_CODE IN (''BLANKET'',''STANDARD'')'||
' AND nvl(PHA.CANCEL_FLAG,''N'')= ''N'''||
' AND nvl(PHA.CLOSED_CODE,''OPEN'') = ''OPEN'''||
' AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID'||
' AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID'||
' AND PLA.QUANTITY > PLLA.QUANTITY_RECEIVED'||
' '||c_smt1||
' '||c_smt2;
FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL_STATEMENT = '||C_SQL_SMT);
--V_MSG := 'OPEN CSR_TMR';
OPEN CSR_TMR FOR c_sql_smt; --(PRA_BUYER_ID)
FND_FILE.PUT_LINE(FND_FILE.LOG,'OPEN C_SQL_SMT');
LOOP
--V_MSG := 'FETCH CSR_TMR';
----- fetch buyer ID into v_buyer_ID -----
FETCH CSR_TMR INTO REC_TMR(k);
FND_FILE.PUT_LINE(FND_FILE.LOG,'FETCH CSR_TMR');
EXIT WHEN CSR_TMR%NOTFOUND;
----- auto submit request TMPO0080 -----
FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST1');
REQ_ID := FND_REQUEST.SUBMIT_REQUEST (
'TIM',
'TMPO0080',
NULL,
NULL,
FALSE,
nvl(PRA_BUYER_ID,''),
nvl(PRA_VENDOR_ID,''),
chr(0),
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','');
FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST2');
COMMIT;
/*SELECT PV.SEGMENT1
INTO T_VENDOR_CODE
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID = V_VENDOR_ID; */
IF REQ_ID = 0 THEN
V_MSG := 'TMPO0080 encounter error, report not submitted';
RAISE USR_ERROR;
END IF;
--- wait for TMPO0080 request finish ---
T_SUCCESS := FND_CONCURRENT.WAIT_FOR_REQUEST (
REQ_ID,
2,
0,
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message );
IF lv_phase = 'Completed' AND lv_status = 'Normal' THEN
T_SUBJECT := 'Open PO - ' || T_VENDOR_CODE || ' - ' || T_BUYER_NAME;
REC_REQ_ID(l) := REQ_ID;
REC_EMAIL(l) := T_MAIL;
REC_SUBJECT(l) := T_SUBJECT;
l := l + 1;
--DBMS_LOCK.SLEEP(20);
--tim_alrutils.ins_alert_email_subject (REQ_ID, T_MAIL, T_SUBJECT,'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
--COMMIT;
END IF;
k := k + 1;
END LOOP;
--V_MSG := 'CLOSE CSR_TMR';
FND_FILE.PUT_LINE(FND_FILE.LOG,'CLOSE CSR_TMR');
CLOSE CSR_TMR;
/*===== send email =====*/
FOR IDX IN 0 .. l-1 LOOP
DBMS_OUTPUT.PUT_LINE(REC_REQ_ID(IDX));
DBMS_LOCK.SLEEP(30);
-- tim_alrutils.ins_alert_email_subject (REC_REQ_ID(IDX), REC_EMAIL(IDX), REC_SUBJECT(IDX),'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
COMMIT;
END LOOP;
EXCEPTION
WHEN USR_ERROR THEN
IF C0%ISOPEN THEN
CLOSE C0;
END IF;
WHEN OTHERS THEN
IF C0%ISOPEN THEN
CLOSE C0;
END IF;
DBMS_OUTPUT.PUT_LINE ('USR_ERROR :' ||V_MSG);
--- Log output of contents of an error ---
DBMS_OUTPUT.PUT_LINE ('PROGRAM ID [TMSYS0080]');
DBMS_OUTPUT.PUT_LINE ('ERROR :' ||TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE ('ERROR MSG:' || SQLERRM );
CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
END ;
/
|
|
|
|
Re: inconsistent datatypes: expected - got - [message #268904 is a reply to message #268896] |
Thu, 20 September 2007 01:29   |
kogilaah_k
Messages: 20 Registered: May 2007 Location: Malaysia
|
Junior Member |
|
|
yes, actually i only posted the past where i had to ammend and where i thought would be causing the probelm...
below is the complete code...please advise!!
/*
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
CURSOR C0 (P_BUYER_ID PO.PO_AGENTS.AGENT_ID%TYPE)
IS
SELECT distinct
PHA.Vendor_Id
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA
WHERE
PHA.AGENT_ID = P_BUYER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.AUTHORIZATION_STATUS= 'APPROVED'
AND PHA.TYPE_LOOKUP_CODE In ('BLANKET','STANDARD')
AND nvl(PHA.CANCEL_FLAG,'N')= 'N'
AND nvl(PHA.CLOSED_CODE,'OPEN') = 'OPEN'
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.QUANTITY > PLLA.QUANTITY_RECEIVED
;
TYPE REC_REQ_ID_TYPE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
REC_REQ_ID REC_REQ_ID_TYPE;
TYPE REC_EMAIL_TYPE IS TABLE OF HR_EMPLOYEES.EMAIL_ADDRESS%TYPE
INDEX BY BINARY_INTEGER;
REC_EMAIL REC_EMAIL_TYPE;
TYPE REC_SUBJECT_TYPE IS TABLE OF VARCHAR(500)
INDEX BY BINARY_INTEGER;
REC_SUBJECT REC_SUBJECT_TYPE;
TYPE TMRCurTyp IS REF CURSOR;
CSR_TMR TMRCurTyp;
TYPE TMRRecTyp IS RECORD (
VENDOR_ID PO_HEADERS_ALL.VENDOR_ID%TYPE
);
TYPE TMRRec IS TABLE OF TMRRecTyp
INDEX BY PLS_INTEGER;
rec_TMR TMRRec;
V_VENDOR_ID PO.PO_HEADERS_ALL.AGENT_ID%TYPE;
PRA_BUYER_ID NUMBER;
PRA_VENDOR_ID NUMBER;
PRA_COMMENT1 VARCHAR2(80);
T_VENDOR_CODE PO_VENDORS.SEGMENT1%TYPE;
T_BUYER_NAME HR_EMPLOYEES.FULL_NAME%TYPE;
T_SUCCESS BOOLEAN;
T_MAIL VARCHAR2(100);
T_SUBJECT VARCHAR2(500);
lv_phase VARCHAR2(100);
lv_status VARCHAR2(100);
lv_dev_phase VARCHAR2(100);
lv_dev_status VARCHAR2(100);
lv_message VARCHAR2(500);
k PLS_INTEGER DEFAULT 0;
l BINARY_INTEGER DEFAULT 0;
IDX NUMBER;
c_smt1 VARCHAR2(100);
c_smt2 VARCHAR2(100);
c_sql_smt VARCHAR2(3000);
V_MSG VARCHAR2(241);
USR_ERROR EXCEPTION;
CNC_RET BOOLEAN;
CNT NUMBER;
REQ_ID NUMBER;
ERR_MSG VARCHAR2(500);
ERR_CODE NUMBER;
/*=======================================
MAIN
=======================================*/
BEGIN
FND_FILE.PUT_NAMES('/oca/ocafsh/tmp/TMPO0430.log','/oca/ocafsh/tmp/TMPO0430.out','/oca/ocafsh/tmp');
CASE
WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NOT NULL THEN
c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';
WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NULL THEN
c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
c_smt2 := null;
WHEN PRA_BUYER_ID IS NULL AND PRA_VENDOR_ID IS NOT NULL THEN
c_smt1 := null;
c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';
else
c_smt1 := null;
c_smt2 := null;
END CASE;
/*SELECT HE.FULL_NAME, SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,'@'))
INTO T_BUYER_NAME, T_MAIL
FROM HR_EMPLOYEES HE
WHERE HE.EMPLOYEE_ID = PRA_BUYER_ID;*/
/*SELECT PV.SEGMENT1
INTO T_VENDOR_CODE
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID = PRA_VENDOR_ID;
*/
-- Construct a dynamic SQL to retrieve the vendors details of the buyer
c_sql_smt := ' SELECT distinct PHA.Vendor_Id,'||
' HE.FULL_NAME,'||
' SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,''@'')),'||
' PV.SEGMENT1'||
' FROM PO_HEADERS_ALL PHA,'||
'PO_LINES_ALL PLA,'||
'PO_LINE_LOCATIONS_ALL PLLA,'||
'HR_EMPLOYEES HE,'||
'PO_VENDORS PV'||
' WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID'||
' AND PHA.AUTHORIZATION_STATUS= ''APPROVED'''||
' AND PHA.TYPE_LOOKUP_CODE IN (''BLANKET'',''STANDARD'')'||
' AND nvl(PHA.CANCEL_FLAG,''N'')= ''N'''||
' AND nvl(PHA.CLOSED_CODE,''OPEN'') = ''OPEN'''||
' AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID'||
' AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID'||
' AND PLA.QUANTITY > PLLA.QUANTITY_RECEIVED'||
' '||c_smt1||
' '||c_smt2;
FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL_STATEMENT = '||C_SQL_SMT);
--V_MSG := 'OPEN CSR_TMR';
OPEN CSR_TMR FOR c_sql_smt; --(PRA_BUYER_ID)
FND_FILE.PUT_LINE(FND_FILE.LOG,'OPEN C_SQL_SMT');
LOOP
--V_MSG := 'FETCH CSR_TMR';
----- fetch buyer ID into v_buyer_ID -----
FETCH CSR_TMR INTO REC_TMR(k);
FND_FILE.PUT_LINE(FND_FILE.LOG,'FETCH CSR_TMR');
EXIT WHEN CSR_TMR%NOTFOUND;
----- auto submit request TMPO0080 -----
FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST1');
REQ_ID := FND_REQUEST.SUBMIT_REQUEST (
'TIM',
'TMPO0080',
NULL,
NULL,
FALSE,
nvl(PRA_BUYER_ID,''),
nvl(PRA_VENDOR_ID,''),
chr(0),
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','');
FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST2');
COMMIT;
/*SELECT PV.SEGMENT1
INTO T_VENDOR_CODE
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID = V_VENDOR_ID; */
IF REQ_ID = 0 THEN
V_MSG := 'TMPO0080 encounter error, report not submitted';
RAISE USR_ERROR;
END IF;
--- wait for TMPO0080 request finish ---
T_SUCCESS := FND_CONCURRENT.WAIT_FOR_REQUEST (
REQ_ID,
2,
0,
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message );
IF lv_phase = 'Completed' AND lv_status = 'Normal' THEN
T_SUBJECT := 'Open PO - ' || T_VENDOR_CODE || ' - ' || T_BUYER_NAME;
REC_REQ_ID(l) := REQ_ID;
REC_EMAIL(l) := T_MAIL;
REC_SUBJECT(l) := T_SUBJECT;
l := l + 1;
--DBMS_LOCK.SLEEP(20);
--tim_alrutils.ins_alert_email_subject (REQ_ID, T_MAIL, T_SUBJECT,'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
--COMMIT;
END IF;
k := k + 1;
END LOOP;
--V_MSG := 'CLOSE CSR_TMR';
FND_FILE.PUT_LINE(FND_FILE.LOG,'CLOSE CSR_TMR');
CLOSE CSR_TMR;
/*===== send email =====*/
FOR IDX IN 0 .. l-1 LOOP
DBMS_OUTPUT.PUT_LINE(REC_REQ_ID(IDX));
DBMS_LOCK.SLEEP(30);
-- tim_alrutils.ins_alert_email_subject (REC_REQ_ID(IDX), REC_EMAIL(IDX), REC_SUBJECT(IDX),'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
COMMIT;
END LOOP;
EXCEPTION
WHEN USR_ERROR THEN
IF C0%ISOPEN THEN
CLOSE C0;
END IF;
WHEN OTHERS THEN
IF C0%ISOPEN THEN
CLOSE C0;
END IF;
DBMS_OUTPUT.PUT_LINE ('USR_ERROR :' ||V_MSG);
--- Log output of contents of an error ---
DBMS_OUTPUT.PUT_LINE ('PROGRAM ID [TMSYS0080]');
DBMS_OUTPUT.PUT_LINE ('ERROR :' ||TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE ('ERROR MSG:' || SQLERRM );
CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
END ;
/
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 18:54:20 CST 2025
|