Home » SQL & PL/SQL » SQL & PL/SQL » worklist sql
worklist sql [message #656885] Fri, 21 October 2016 06:35 Go to next message
suji6281
Messages: 134
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous message
suji6281
Messages: 134
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
Previous Topic: error ORA 210001
Next Topic: Referencing Collection elements
Goto Forum:
  


Current Time: Fri Mar 29 01:30:11 CDT 2024