Home » SQL & PL/SQL » SQL & PL/SQL » worklist sql
worklist sql [message #656885] |
Fri, 21 October 2016 06:35 |
|
suji6281
Messages: 135 Registered: September 2014
|
Senior Member |
|
|
Hi Team,
Can you please help me with SQL.
I have prepared a SQL Query as per my requirement but some how I couldn't get the correct results. My query Shouldn't get the row but it was displaying one row. Can you please tweak the below sql Query. My SQL Query has 4 tables (pv_req_aw a, eoaw_wl b, req_line L, psworklist c). My Query shouldn't return rows whose status EOAWSTEP_STATUS = 'P'. But I am not able to join the EOAW_STEPINST table in my SQL.
My SQL:
SELECT DISTINCT( b.transactionid ),
b.eoawthread_id,
c.oprid,
a.req_id,
a.business_unit
FROM pv_req_aw a,
eoaw_wl b,
req_line L,
psworklist c
WHERE a.eoawprcs_id = b.eoawprcs_id
AND a.eoawdefn_id = b.eoawdefn_id
AND b.eoawthread_id = a.eoawparent_thread
AND a.eoawprcs_id = b.eoawprcs_id
AND a.eoawdefn_id = b.eoawdefn_id
AND a.eoawthread_status IN ( 'A', 'C', 'D', 'N', 'T', 'S' )
AND a.req_id = l.req_id
AND a.business_unit = L.business_unit
AND l.curr_status <> 'P'
AND b.busprocname = c.busprocname
AND b.activityname = c.activityname
AND b.eventname = c.eventname
AND b.worklistname = c.worklistname
AND b.instanceid = c.instanceid
AND b.transactionid = c.transactionid
AND c.inststatus = 1
AND c.busprocname = 'EOAW_APPROVALS'
AND c.activityname = 'EOAW_ROUTE'
and a.req_id = '0003106046' and a.business_unit = 'PS001'
ORDER BY c.oprid,
a.req_id,
a.business_unit;
Here Included the Create Table and Insert statements for your reference.
CREATE TABLE PV_REQ_AW (EOAWTHREAD_ID DECIMAL(15) NOT NULL,
EOAWPRCS_ID VARCHAR2(30) NOT NULL,
EOAWDEFN_ID VARCHAR2(30) NOT NULL,
ORIGINATORID VARCHAR2(30) NOT NULL,
EOAWREQUESTOR_ID VARCHAR2(30) NOT NULL,
RECNAME VARCHAR2(15) NOT NULL,
EOAWTHREAD_STATUS VARCHAR2(1) NOT NULL,
EOAWPARENT_THREAD DECIMAL(15) NOT NULL,
EOAWDTTM_MODIFIED tIMESTAMP,
BUSINESS_UNIT VARCHAR2(5) NOT NULL,
REQ_ID VARCHAR2(10) NOT NULL,
LINE_NBR INTEGER NOT NULL,
COMMENTS CLOB);
CREATE TABLE EOAW_STEPINST (EOAWTHREAD_ID DECIMAL(15) NOT NULL,
EOAWSTEP_INSTANCE DECIMAL(15) NOT NULL,
EOAWPRCS_ID VARCHAR2(30) NOT NULL,
EOAWDEFN_ID VARCHAR2(30) NOT NULL,
EFFDT DATE NOT NULL,
EOAWPATH_ID VARCHAR2(30) NOT NULL,
EOAWSTAGE_NBR SMALLINT NOT NULL,
EOAWSTEP_NBR DECIMAL(5, 2) NOT NULL,
EOAWSTEP_TYPE VARCHAR2(1) NOT NULL,
EOAWSTEP_STATUS VARCHAR2(1) NOT NULL,
EOAWADHOC_BY VARCHAR2(30) NOT NULL,
EOAW_EXTERN_FLAG VARCHAR2(1) NOT NULL);
CREATE TABLE EOAW_WL (BUSPROCNAME VARCHAR2(30) NOT NULL,
ACTIVITYNAME VARCHAR2(30) NOT NULL,
EVENTNAME VARCHAR2(30) NOT NULL,
WORKLISTNAME VARCHAR2(30) NOT NULL,
INSTANCEID INTEGER NOT NULL,
TRANSACTIONID INTEGER NOT NULL,
EOAWPRCS_ID VARCHAR2(30) NOT NULL,
EOAWTHREAD_ID DECIMAL(15) NOT NULL,
EOAWDEFN_ID VARCHAR2(30) NOT NULL,
EFFDT DATE,
EOAW_EXTERN_FLAG VARCHAR2(1) NOT NULL,
EOAWLEVEL SMALLINT NOT NULL,
EOAWDESCR VARCHAR2(254) NOT NULL,
WORKLIST_DESCR VARCHAR2(50) NOT NULL);
CREATE TABLE PSWORKLIST (BUSPROCNAME VARCHAR2(30) DEFAULT ' ' NOT
NULL,
ACTIVITYNAME VARCHAR2(30) DEFAULT ' ' NOT NULL,
EVENTNAME VARCHAR2(30) DEFAULT ' ' NOT NULL,
WORKLISTNAME VARCHAR2(30) DEFAULT ' ' NOT NULL,
INSTANCEID INTEGER DEFAULT 999999999 NOT NULL,
TRANSACTIONID INTEGER DEFAULT 0 NOT NULL,
ACTIONDTTM tIMESTAMP,
OPRID VARCHAR2(30) DEFAULT ' ' NOT NULL,
ORIGINATORTYPE SMALLINT DEFAULT 0 NOT NULL,
ORIGINATORID VARCHAR2(30) DEFAULT ' ' NOT NULL,
INSTSTATUS SMALLINT DEFAULT 0 NOT NULL,
INSTAVAILABLEDTTM tIMESTAMP NOT NULL,
INSTSELECTEDDTTM tIMESTAMP,
INSTWORKEDDTTM tIMESTAMP,
INSTTIMEOUTDTTM tIMESTAMP,
TIMEDOUT SMALLINT DEFAULT 0 NOT NULL,
PREVOPRID VARCHAR2(30) DEFAULT ' ' NOT NULL,
COMMENTSHORT VARCHAR2(30) DEFAULT ' ' NOT NULL,
WLDAYSTOSELECT DECIMAL(15, 7) DEFAULT 0 NOT NULL,
WLDAYSTOWORK DECIMAL(15, 7) DEFAULT 0 NOT NULL,
URL VARCHAR2(254) DEFAULT ' ' NOT NULL,
DO_REPLICATE_FLAG VARCHAR2(1) DEFAULT ' ' NOT NULL,
SYNCID INTEGER,
LASTUPDDTTM tIMESTAMP,
WL_PRIORITY VARCHAR2(1) DEFAULT ' ' NOT NULL,
DESCR254_MIXED VARCHAR2(254) DEFAULT ' ' NOT NULL);
CREATE TABLE REQ_LINE (BUSINESS_UNIT VARCHAR2(5) NOT NULL,
REQ_ID VARCHAR2(10) NOT NULL,
LINE_NBR INTEGER NOT NULL,
IN_PROCESS_FLG VARCHAR2(1) NOT NULL,
PROCESS_INSTANCE DECIMAL(10) NOT NULL,
QTY_REQ DECIMAL(15, 4) NOT NULL,
ITM_SETID VARCHAR2(5) NOT NULL,
INV_ITEM_ID VARCHAR2(18) NOT NULL,
VENDOR_SETID VARCHAR2(5) NOT NULL,
VENDOR_ID VARCHAR2(10) NOT NULL,
ITM_ID_VNDR VARCHAR2(50) NOT NULL,
VNDR_CATALOG_ID VARCHAR2(20) NOT NULL,
BUYER_ID VARCHAR2(30) NOT NULL,
CATEGORY_ID VARCHAR2(5) NOT NULL,
UNIT_OF_MEASURE VARCHAR2(3) NOT NULL,
MFG_ID VARCHAR2(50) NOT NULL,
PRICE_REQ DECIMAL(15, 5) NOT NULL,
MERCHANDISE_AMT DECIMAL(26, 3) NOT NULL,
INSPECT_CD VARCHAR2(1) NOT NULL,
SYSTEM_SOURCE VARCHAR2(3) NOT NULL,
SOURCE_STATUS VARCHAR2(1) NOT NULL,
SOURCE_DATE DATE NOT NULL,
CURRENCY_CD VARCHAR2(3) NOT NULL,
PCT_UNIT_PRC_TOL DECIMAL(5, 2) NOT NULL,
PCT_UNIT_PRC_TOL_L DECIMAL(5, 2) NOT NULL,
UNIT_PRC_TOL DECIMAL(13, 5) NOT NULL,
UNIT_PRC_TOL_L DECIMAL(13, 5) NOT NULL,
INVENTORY_SRC_FLG VARCHAR2(1) NOT NULL,
OVERRIDE_SUGG_VNDR VARCHAR2(1) NOT NULL,
CONSOLIDATE_FLG VARCHAR2(1) NOT NULL,
ROUTING_ID VARCHAR2(10) NOT NULL,
RFQ_IND VARCHAR2(1) NOT NULL,
CURRENCY_CD_BASE VARCHAR2(3) NOT NULL,
UNIT_MEASURE_STD VARCHAR2(3) NOT NULL,
MERCH_AMT_BSE DECIMAL(26, 3) NOT NULL,
QTY_REQ_STD DECIMAL(15, 4) NOT NULL,
PRICE_REQ_BSE DECIMAL(15, 5) NOT NULL,
UNIT_PRC_TOL_BSE DECIMAL(13, 5) NOT NULL,
UNIT_PRC_TOL_BSE_L DECIMAL(13, 5) NOT NULL,
DESCR254_MIXED VARCHAR2(254) NOT NULL,
BUSINESS_UNIT_CP VARCHAR2(5) NOT NULL,
CONFIG_CODE VARCHAR2(50) NOT NULL,
CP_TEMPLATE_ID VARCHAR2(10) NOT NULL,
BUSINESS_UNIT_IN VARCHAR2(5) NOT NULL,
ZERO_PRICE_IND VARCHAR2(1) NOT NULL,
VNDR_LOC VARCHAR2(10) NOT NULL,
PRICE_SETID VARCHAR2(5) NOT NULL,
PRICE_VENDOR VARCHAR2(10) NOT NULL,
PRICE_LOC VARCHAR2(10) NOT NULL,
MFG_ITM_ID VARCHAR2(50) NOT NULL,
CALC_PRICE_FLG VARCHAR2(1) NOT NULL,
REPLEN_OPT VARCHAR2(2) NOT NULL,
PHYSICAL_NATURE VARCHAR2(1) NOT NULL,
VAT_SVC_PERFRM_FLG VARCHAR2(1) NOT NULL,
USER_LINE_CHAR1 VARCHAR2(1) NOT NULL,
CUSTOM_C100_B1 VARCHAR2(100) NOT NULL,
CUSTOM_C100_B2 VARCHAR2(100) NOT NULL,
CUSTOM_C100_B3 VARCHAR2(100) NOT NULL,
CUSTOM_C100_B4 VARCHAR2(100) NOT NULL,
CUSTOM_DATE_B DATE,
CUSTOM_C1_B VARCHAR2(1) NOT NULL,
GPO_ID VARCHAR2(18) NOT NULL,
GPO_CNTRCT_NBR VARCHAR2(25) NOT NULL,
CHANGE_STATUS VARCHAR2(1) NOT NULL,
CHNG_ORD_SEQ SMALLINT NOT NULL,
CURR_STATUS VARCHAR2(1) NOT NULL,
CC_USE_FLAG VARCHAR2(1) NOT NULL,
AMT_ONLY_FLG VARCHAR2(1) NOT NULL,
USE_CNTRCT VARCHAR2(1) NOT NULL,
APPR_REQD VARCHAR2(1) NOT NULL,
ORIG_INV_ITEM_ID VARCHAR2(18) NOT NULL,
DESCR254_MIXED2 VARCHAR2(254) NOT NULL,
AUTO_SUB_FLG VARCHAR2(1) NOT NULL,
PO_GROUP_ID VARCHAR2(18) NOT NULL,
PRIMARY_UNIT VARCHAR2(1) NOT NULL,
UNIT_ALLOC_QTY DECIMAL(15, 4) NOT NULL,
UNIT_ALLOC_AMT DECIMAL(26, 3) NOT NULL,
LN_TYPE VARCHAR2(4) NOT NULL);
Insert into PV_REQ_AW (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294603,'Requisition','WF_Requisition_Domestic','TEVIKMD','TEVIKMD','PV_REQHDR_AW_VW','S',26294603,to_timestamp('17-OCT-16 06.58.42.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'PS001','0003106046',0, EMPTY_CLOB());
Insert into PV_REQ_AW (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294604,'Requisition','WF_Requisition_Domestic','TEVIKMD','TEVIKMD','PV_REQLIN_AW_VW','P',26294603,null,'PS001','0003106046',1, EMPTY_CLOB());
Insert into PV_REQ_AW (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294605,'Requisition','WF_Requisition_Domestic','TEVIKMD','TEVIKMD','PV_REQLIN_AW_VW','A',26294603,null,'PS001','0003106046',2, EMPTY_CLOB());
Insert into PV_REQ_AW (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294606,'Requisition','WF_Requisition_Domestic','TEVIKMD','TEVIKMD','PV_REQLIN_AW_VW','A',26294603,null,'PS001','0003106046',3, EMPTY_CLOB());
Insert into EOAW_STEPINST (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294604,26822779,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,2,'N','A',' ','N');
Insert into EOAW_STEPINST (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294604,26822780,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,3,'N','P',' ','N');
Insert into EOAW_STEPINST (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294605,26822781,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,2,'N','A',' ','N');
Insert into EOAW_STEPINST (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294606,26822782,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,2,'N','A',' ','N');
Insert into EOAW_WL (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,EOAWPRCS_ID,EOAWTHREAD_ID,EOAWDEFN_ID,EFFDT,EOAW_EXTERN_FLA G,EOAWLEVEL,EOAWDESCR,WORKLIST_DESCR) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098631,3098631,'Requisition',26294603,'WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'N',0,' BUSINESS_UNIT:PS001 REQ_ID:0003106046',' ');
Insert into EOAW_WL (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,EOAWPRCS_ID,EOAWTHREAD_ID,EOAWDEFN_ID,EFFDT,EOAW_EXTERN_FLA G,EOAWLEVEL,EOAWDESCR,WORKLIST_DESCR) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098632,3098632,'Requisition',26294603,'WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'N',0,' BUSINESS_UNIT:PS001 REQ_ID:0003106046',' ');
Insert into EOAW_WL (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,EOAWPRCS_ID,EOAWTHREAD_ID,EOAWDEFN_ID,EFFDT,EOAW_EXTERN_FLA G,EOAWLEVEL,EOAWDESCR,WORKLIST_DESCR) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098633,3098633,'Requisition',26294603,'WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'N',0,' BUSINESS_UNIT:PS001 REQ_ID:0003106046',' ');
Insert into PSWORKLIST (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098631,3098631,null,'A173236',0,'TEVIKMD',2,to_timestamp('17-OCT-16 06.17.39.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('17-OCT-16 06.58.38.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('17-OCT-16 06.58.42.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,0,' ',' ',0.0284606,0,' ','Y',159252905,to_timestamp('17-OCT-16 06.58.42.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'2',' ');
Insert into PSWORKLIST (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098632,3098632,null,' ',0,'TEVIKMD',0,to_timestamp('17-OCT-16 06.17.39.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,null,null,0,' ',' ',0,0,' ',' ',159252906,to_timestamp('17-OCT-16 06.17.39.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'2',' ');
Insert into PSWORKLIST (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098633,3098633,null,'MARJORIE.J.MUSKETT',0,'A173236',1,to_timestamp('17-OCT-16 06.58.42.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('17-OCT-16 07.01.15.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,null,0,' ',' ',0.0017708,0,' ','Y',159252907,to_timestamp('17-OCT-16 07.01.15.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'2',' ');
Insert into PSWORKLIST (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('NW_HEADER_PROCESS','NW_WDS_REVIEW','RouteToWDS','Manager Approval',3098633,3098632,null,'A460235',0,'PWILLIA1',2,to_timestamp('05-JUN-13 03.58.01.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('06-JUN-13 10.46.42.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('06-JUN-13 10.56.59.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('05-JUN-13 03.58.01.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),0,' ',' ',0.7838079,0,' ','Y',146037241,to_timestamp('06-JUN-13 10.56.59.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),' ',' ');
Insert into REQ_LINE (BUSINESS_UNIT,REQ_ID,LINE_NBR,IN_PROCESS_FLG,PROCESS_INSTANCE,QTY_REQ,ITM_SETID,INV_ITEM_ID,VENDOR_SETID,VENDOR_ID,ITM_ID_VNDR,VNDR_CATALOG_ID,BUYER_ID,CATEGORY_ID,UNIT_OF_MEASURE,MFG_ID,PRICE_REQ,MERCHANDISE_AMT,INSPECT_CD,SYSTEM_SOURCE,SOURCE_STATUS,SOURCE_DATE,CURRENCY_CD,PCT_UNIT_PRC_TOL,PCT_UNIT_PRC_TOL_L,UNIT_PRC_TOL,UNIT_PRC_TOL_L,INVENTORY_SRC_FLG,OVERRIDE_SUGG_VNDR,CONSOLIDATE_FLG,ROUTING_ID,RFQ_IND,CURRENCY_CD_BASE,UNIT_MEASURE_STD,MERCH_AMT_BSE,QTY_REQ_STD,PRICE_REQ_BSE,UNIT_PRC_TOL_BSE,UNIT_PRC_TOL_BSE_L,DESCR254_MIXED,BUSINESS_UNIT_CP,CONFIG_CODE,CP_TEMPLATE_ID,BUSINESS_UNIT_IN,ZERO_PRICE_IND,VNDR_LOC,PRICE_SETID,PRICE_VENDOR,PRICE_LOC,MFG_ITM_ID,CALC_PRICE_FLG,REPLEN_OPT,PHYSICAL_NATURE,VAT_SVC_PERFRM_FLG,USER_LINE_CHAR1,GPO_ID,GPO_CNTRCT_NBR,CHANGE_STATUS,CHNG_ORD_SEQ,CURR_STATUS,CC_USE_FLAG,AMT_ONLY_FLG,CUSTOM_C100_B1,CUSTOM_C100_B2,CUSTOM_C100_B3,CUSTOM_C100_B4,CUSTOM_DATE_B,CUSTOM_C1_B,USE_CNTRCT,APPR_REQD,ORIG_INV_ITEM_ID,DESCR254_MIXED2,AUTO_SUB_FLG,PO_GROUP_ID,PRIMARY_UNIT,UNIT_ALLOC_QTY,UNIT_ALLOC_AMT,LN_TYPE) values ('PS001','0003106046',1,'N',0,25,'000','000044102502000024','MNSA','0000058170','631-9601-00',' ','TEVIKMD','00185','EA','CUMMINS-ALLISON CORP.',1772.3,44307.5,'N','EPO','A',to_date('17-OCT-16','DD-MON-RR'),'USD',0,0,99999999.99999,99999999.99999,'N','Y','N',' ','N','USD',' ',44307.5,25,1772.3,99999999.99999,99999999.99999,'JETSORT - 1601 - 1,500 MIXED COINS PER MINUTE JETSORT - STANDARD COIN BOX CONFIGURATION WITH BUILT-IN LIFT TRAY',' ',' ',' ',' ','N','000019','MNSA','0000058170','000019','631-9601-00','Y','01',' ',' ',' ',' ',' ','U',0,'P','N','N',' ',' ',' ',' ',null,' ','Y','Y',' ',' ','Y',' ','N',0,0,'CAT');
Insert into REQ_LINE (BUSINESS_UNIT,REQ_ID,LINE_NBR,IN_PROCESS_FLG,PROCESS_INSTANCE,QTY_REQ,ITM_SETID,INV_ITEM_ID,VENDOR_SETID,VENDOR_ID,ITM_ID_VNDR,VNDR_CATALOG_ID,BUYER_ID,CATEGORY_ID,UNIT_OF_MEASURE,MFG_ID,PRICE_REQ,MERCHANDISE_AMT,INSPECT_CD,SYSTEM_SOURCE,SOURCE_STATUS,SOURCE_DATE,CURRENCY_CD,PCT_UNIT_PRC_TOL,PCT_UNIT_PRC_TOL_L,UNIT_PRC_TOL,UNIT_PRC_TOL_L,INVENTORY_SRC_FLG,OVERRIDE_SUGG_VNDR,CONSOLIDATE_FLG,ROUTING_ID,RFQ_IND,CURRENCY_CD_BASE,UNIT_MEASURE_STD,MERCH_AMT_BSE,QTY_REQ_STD,PRICE_REQ_BSE,UNIT_PRC_TOL_BSE,UNIT_PRC_TOL_BSE_L,DESCR254_MIXED,BUSINESS_UNIT_CP,CONFIG_CODE,CP_TEMPLATE_ID,BUSINESS_UNIT_IN,ZERO_PRICE_IND,VNDR_LOC,PRICE_SETID,PRICE_VENDOR,PRICE_LOC,MFG_ITM_ID,CALC_PRICE_FLG,REPLEN_OPT,PHYSICAL_NATURE,VAT_SVC_PERFRM_FLG,USER_LINE_CHAR1,GPO_ID,GPO_CNTRCT_NBR,CHANGE_STATUS,CHNG_ORD_SEQ,CURR_STATUS,CC_USE_FLAG,AMT_ONLY_FLG,CUSTOM_C100_B1,CUSTOM_C100_B2,CUSTOM_C100_B3,CUSTOM_C100_B4,CUSTOM_DATE_B,CUSTOM_C1_B,USE_CNTRCT,APPR_REQD,ORIG_INV_ITEM_ID,DESCR254_MIXED2,AUTO_SUB_FLG,PO_GROUP_ID,PRIMARY_UNIT,UNIT_ALLOC_QTY,UNIT_ALLOC_AMT,LN_TYPE) values ('PS001','0003106046',2,'N',0,150,'000','000044102502000025','MNSA','0000058170','610-0017-20',' ','TEVIKMD','00185','EA','CUMMINS-ALLISON CORP.',18,2700,'N','EPO','A',to_date('17-OCT-16','DD-MON-RR'),'USD',0,0,99999999.99999,99999999.99999,'N','Y','N',' ','N','USD',' ',2700,150,18,99999999.99999,99999999.99999,'JETSORT OPTION - COIN BOX FOR 1601',' ',' ',' ',' ','N','000019','MNSA','0000058170','000019','610-0017-20','Y','01',' ',' ',' ',' ',' ','U',0,'A','N','N',' ',' ',' ',' ',null,' ','Y','N',' ',' ','Y',' ','N',0,0,'CAT');
Insert into REQ_LINE (BUSINESS_UNIT,REQ_ID,LINE_NBR,IN_PROCESS_FLG,PROCESS_INSTANCE,QTY_REQ,ITM_SETID,INV_ITEM_ID,VENDOR_SETID,VENDOR_ID,ITM_ID_VNDR,VNDR_CATALOG_ID,BUYER_ID,CATEGORY_ID,UNIT_OF_MEASURE,MFG_ID,PRICE_REQ,MERCHANDISE_AMT,INSPECT_CD,SYSTEM_SOURCE,SOURCE_STATUS,SOURCE_DATE,CURRENCY_CD,PCT_UNIT_PRC_TOL,PCT_UNIT_PRC_TOL_L,UNIT_PRC_TOL,UNIT_PRC_TOL_L,INVENTORY_SRC_FLG,OVERRIDE_SUGG_VNDR,CONSOLIDATE_FLG,ROUTING_ID,RFQ_IND,CURRENCY_CD_BASE,UNIT_MEASURE_STD,MERCH_AMT_BSE,QTY_REQ_STD,PRICE_REQ_BSE,UNIT_PRC_TOL_BSE,UNIT_PRC_TOL_BSE_L,DESCR254_MIXED,BUSINESS_UNIT_CP,CONFIG_CODE,CP_TEMPLATE_ID,BUSINESS_UNIT_IN,ZERO_PRICE_IND,VNDR_LOC,PRICE_SETID,PRICE_VENDOR,PRICE_LOC,MFG_ITM_ID,CALC_PRICE_FLG,REPLEN_OPT,PHYSICAL_NATURE,VAT_SVC_PERFRM_FLG,USER_LINE_CHAR1,GPO_ID,GPO_CNTRCT_NBR,CHANGE_STATUS,CHNG_ORD_SEQ,CURR_STATUS,CC_USE_FLAG,AMT_ONLY_FLG,CUSTOM_C100_B1,CUSTOM_C100_B2,CUSTOM_C100_B3,CUSTOM_C100_B4,CUSTOM_DATE_B,CUSTOM_C1_B,USE_CNTRCT,APPR_REQD,ORIG_INV_ITEM_ID,DESCR254_MIXED2,AUTO_SUB_FLG,PO_GROUP_ID,PRIMARY_UNIT,UNIT_ALLOC_QTY,UNIT_ALLOC_AMT,LN_TYPE) values ('PS001','0003106046',3,'N',0,25,'000','000044102502000026','MNSA','0000058170','610-0262-20',' ','TEVIKMD','00185','EA','CUMMINS-ALLISON CORP.',270,6750,'N','EPO','A',to_date('17-OCT-16','DD-MON-RR'),'USD',0,0,99999999.99999,99999999.99999,'N','Y','N',' ','N','USD',' ',6750,25,270,99999999.99999,99999999.99999,'JETSORT OPTION - EXACT BAG STOP FUNCTION FOR 1601',' ',' ',' ',' ','N','000019','MNSA','0000058170','000019','610-0262-20','Y','01',' ',' ',' ',' ',' ','U',0,'A','N','N',' ',' ',' ',' ',null,' ','Y','N',' ',' ','Y',' ','N',0,0,'CAT');
Thanks
sumad
|
|
|
Re: worklist sql [message #656886 is a reply to message #656885] |
Fri, 21 October 2016 06:57 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote:My Query shouldn't return rows whose status EOAWSTEP_STATUS = 'P'. But I am not able to join the EOAW_STEPINST table in my SQL.
And how can we help with that?
How should we know how to join your tables together?
[Updated on: Fri, 21 October 2016 06:57] Report message to a moderator
|
|
|
Re: worklist sql [message #656902 is a reply to message #656885] |
Fri, 21 October 2016 19:58 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Adding the table and filter condition is easy.
As to adding the join conditions, we can only guess that the columns with the same names should be joined.
There may also be other columns with the same values that should be joined.
Only you can determine positively what columns should be joined based on what logic.
The following adds the table and the filter condition and joins the columns with the same names.
SELECT DISTINCT( b.transactionid ),
b.eoawthread_id,
c.oprid,
a.req_id,
a.business_unit
FROM pv_req_aw a,
eoaw_wl b,
req_line L,
psworklist c,
-- added table:
eoaw_stepinst d
WHERE a.eoawprcs_id = b.eoawprcs_id
AND a.eoawdefn_id = b.eoawdefn_id
AND b.eoawthread_id = a.eoawparent_thread
AND a.eoawprcs_id = b.eoawprcs_id
AND a.eoawdefn_id = b.eoawdefn_id
AND a.eoawthread_status IN ( 'A', 'C', 'D', 'N', 'T', 'S' )
AND a.req_id = l.req_id
AND a.business_unit = L.business_unit
AND l.curr_status <> 'P'
AND b.busprocname = c.busprocname
AND b.activityname = c.activityname
AND b.eventname = c.eventname
AND b.worklistname = c.worklistname
AND b.instanceid = c.instanceid
AND b.transactionid = c.transactionid
AND c.inststatus = 1
AND c.busprocname = 'EOAW_APPROVALS'
AND c.activityname = 'EOAW_ROUTE'
and a.req_id = '0003106046' and a.business_unit = 'PS001'
-- added join conditions for columns with same names:
AND a.eoawdefn_id = d.eoawdefn_id
AND a.eoawprcs_id = d.eoawprcs_id
AND a.eoawthread_id = d.eoawthread_id
AND b.effdt = d.effdt
AND b.eoaw_extern_flag = d.eoaw_extern_flag
AND b.eoawdefn_id = d.eoawdefn_id
AND b.eoawprcs_id = d.eoawprcs_id
AND b.eoawthread_id = d.eoawthread_id
-- added filter condition:
AND d.eoawstep_status != 'P'
ORDER BY c.oprid,
a.req_id,
a.business_unit;
[Updated on: Fri, 21 October 2016 19:58] Report message to a moderator
|
|
|
Re: worklist sql [message #656994 is a reply to message #656902] |
Tue, 25 October 2016 05:07 |
|
suji6281
Messages: 135 Registered: September 2014
|
Senior Member |
|
|
Hi Barbara,
Thanks for your reply.
the above sql is not returning any rows even if I remove the req_id and business_unit from the where clause.
If we can able to join the eoawthread_id of eoaw_stepinst with pv_req_aw table. then we are good. Please help me.
I have posted again new query on the same issue with title as 'transaction id Sql'.
Please help me with sql urgently.
Thanks
sujikar
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:54:27 CDT 2024
|