THIS IS DONE CHECK AND CONFIRM
Query Modify in DSS as per requirement.
DSS QUERY SELECT o100191.TYPE_OF_PRODUCT as E109930,o161524.COMPANY as E161530,o161524.TRANSFER_TO_COMPANY as E161531,o161524.VOUCHER_TYPE as E161534,o161524.VOUCHER_DATE as E161538,o161524.PRODUCT_NAME as E161544,decode(o161524.VOUCHER_DATE,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(o161524.VOUCHER_DATE,'YYYY'),'YYYY') || '01','YYYYMM')) as E161581,decode(o161524.VOUCHER_DATE,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(o161524.VOUCHER_DATE,'MM'),'MM') || '1900','MMYYYY')) as E161590,o161524.GROUP_DESCRIPTION as E537683,o161524.ITEM_GROUP as E537684,SUM(o161524.NO_OF_PACKS) as E161547_SUM,( SUM(o161524.VALUE) ) as C_1,o161524.PRICE as E161550 FROM IMISMAIN.PRODUCTS o100191, IMISMAIN.PRODUCT_STOCK_DETAIL o100196, ( SELECT COMPANY ,TRANSFER_TO_COMPANY ,(SELECT DESCRIPTION FROM COMPANIES WHERE COMPANY = TRANSFER_TO_COMPANY) TRANSFER_TO_COMPANY_NAME ,CITY ,VOUCHER_TYPE ,VOUCHER_SERIES ,VOUCHER_NO ,ENTRY_NO ,VOUCHER_DATE ,ST_FORM_CODE ,ST_FORM_NO ,ITEM_CODE ,STOCK_TYPE ,STORE_ID ,PKG_FG.GET_GROUP_DESCRIPTION(ITEM_CODE) GROUP_DESCRIPTION ,PKG_FG.GET_ITEM_GROUP(ITEM_CODE) ITEM_GROUP ,PRODUCTS_PKG.PRODUCT_STOCK_NAME(COMPANY,ITEM_CODE,STOCK_TYPE) PRODUCT_NAME ,MFG_COMPANY ,MFG_BATCH_NO ,PRODUCTS_PKG.UNIT_WEIGHT(COMPANY,ITEM_CODE,STOCK_TYPE,STORE_ID) UNIT_WEIGHT ,nvl(NO_OF_PACKS,0) NO_OF_PACKS ,QTY ,ROUND(DECODE(PRODUCTS_PKG.PRODUCT_UM(COMPANY,ITEM_CODE),'GM',PRODUCTS_PKG.PRODUCT_TOTAL_NET_WEIGHT(COMPANY,ITEM_CODE,PRODUCTS_PKG.PACKS_TO_BASE_QTY(COMPANY,ITEM_CODE,STOCK_TYPE,NO_OF_PACKS))/1000, PRODUCTS_PKG.PRODUCT_TOTAL_NET_WEIGHT(COMPANY,ITEM_CODE,PRODUCTS_PKG.PACKS_TO_BASE_QTY(COMPANY,ITEM_CODE,STOCK_TYPE,NO_OF_PACKS))),3) QTY_KG ,PRICE ,nvl(VALUE,0) VALUE ,PRODUCTS_PKG.GET_PRINT_SEQ_NO(COMPANY,ITEM_CODE) PRINT_SEQ_NO ,ASSESSABLE_VALUE ,DUTY_AMOUNT FROM ( SELECT /*+ INDEX(WI NDX_WHI_001 */ W.COMPANY COMPANY ,W.TRANSFER_TO_COMPANY TRANSFER_TO_COMPANY ,W.BOOKING_TO CITY ,W.TRANSACTION_TYPE VOUCHER_TYPE ,W.TRANSACTION_SERIES VOUCHER_SERIES ,W.TRANSACTION_NO VOUCHER_NO ,W.ENTRY_NO ENTRY_NO ,W.TRANSACTION_DATE VOUCHER_DATE ,W.ST_FORM_CODE ST_FORM_CODE ,W.ST_FORM_NO ST_FORM_NO ,WI.TRANSACTION_ITEM_CODE ITEM_CODE ,WI.TRANSACTION_STOCK_TYPE STOCK_TYPE ,WI.TRANSACTION_STORE_ID STORE_ID ,WI.MFG_COMPANY MFG_COMPANY ,WI.MFG_BATCH_NO MFG_BATCH_NO -- ,SUM(ABS(WI.NO_OF_PACKAGES)) NO_OF_PACKS ,(CASE WHEN WI.NO_OF_PACKAGES IS NULL THEN --Removed above column and added Case on 20/08/2008 by jb. SUM(ABS(WI.TRANSACTION_ITEM_QTY)) ELSE SUM(ABS(WI.NO_OF_PACKAGES)) END) NO_OF_PACKS ,SUM(ABS(WI.TRANSACTION_ITEM_QTY)) QTY ,WI.TRANSACTION_ITEM_RATE PRICE --,SUM(NVL(ABS(WI.NO_OF_PACKAGES),0) * NVL(WI.TRANSACTION_ITEM_RATE,0)) VALUE ,SUM(nvl(WI.TRANSACTION_ITEM_VALUE,0)) VALUE --Removed above column and added this on 19/08/2008 by jb. ,SUM(WI.ASSESSABLE_VALUE) ASSESSABLE_VALUE ,SUM(PKG_SL.GET_TOTAL_DUTY(W.COMPANY, W.WH_RECID, WI.SEQ_NO, 'WI')) DUTY_AMOUNT FROM WAREHOUSE_TRANSACTIONS W,WAREHOUSE_TRANSACTION_ITEMS WI WHERE W.COMPANY = WI.COMPANY AND W.WH_RECID = WI.PARENT_RECID AND W.TRANSACTION_TYPE IN ('W7','DC') AND W.TRANSFER_TO_COMPANY IS NOT NULL AND W.STATUS IN ('A','Y') GROUP BY W.COMPANY ,W.TRANSFER_TO_COMPANY ,W.BOOKING_TO ,W.TRANSACTION_TYPE ,W.TRANSACTION_SERIES ,W.TRANSACTION_NO ,W.ENTRY_NO ,W.TRANSACTION_DATE ,W.ST_FORM_CODE ,W.ST_FORM_NO ,WI.TRANSACTION_ITEM_CODE ,WI.TRANSACTION_STOCK_TYPE ,WI.TRANSACTION_STORE_ID ,WI.MFG_COMPANY ,WI.MFG_BATCH_NO ,WI.NO_OF_PACKAGES ,WI.TRANSACTION_ITEM_RATE UNION ALL SELECT /*+ INDEX(WI NDX_WHI_001 */ W.COMPANY COMPANY ,W.XFR_COMPANY TRANSFER_TO_COMPANY ,W.BOOKING_TO CITY ,W.INVOICE_TYPE VOUCHER_TYPE ,W.INVOICE_SERIES VOUCHER_SERIES ,W.INVOICE_NO VOUCHER_NO ,W.ENTRY_NO ENTRY_NO ,W.INVOICE_DATE VOUCHER_DATE ,W.ST_FORM_CODE ST_FORM_CODE ,W.ST_FORM_NO ST_FORM_NO ,EI.ITEM_CODE ITEM_CODE ,EI.STOCK_TYPE STOCK_TYPE ,EI.STORE_ID STORE_ID ,WI.MFG_COMPANY MFG_COMPANY ,WI.MFG_BATCH_NO MFG_BATCH_NO ,(CASE WHEN WI.NO_OF_PACKAGES IS NULL THEN SUM(ABS(WI.TRANSACTION_ITEM_QTY)) ELSE SUM(ABS(WI.NO_OF_PACKAGES)) END) NO_OF_PACKS ,SUM(ABS(WI.TRANSACTION_ITEM_QTY)) QTY ,EI.NET_PRICE PRICE ,SUM(EI.NET_PRICE * NVL(WI.NO_OF_PACKAGES,WI.TRANSACTION_ITEM_QTY )) VALUE ,0 ASSESSABLE_VALUE ,SUM(PKG_SL.GET_TOTAL_DUTY(W.COMPANY, W.EI_RECID, WI.SEQ_NO, 'WI')) DUTY_AMOUNT FROM EXCISE_INVOICE W,EXCISE_INVOICE_ITEMS EI , WAREHOUSE_TXN_ITEM_BATCH_DTL WI WHERE W.COMPANY = EI.COMPANY AND W.EI_RECID = EI.PARENT_RECID AND EI.INVOICE_TYPE = WI.TRANSACTION_TYPE AND EI.ENTRY_NO = WI.ENTRY_NO AND EI.ITEM_CODE = WI.TRANSACTION_ITEM_CODE AND EI.STOCK_TYPE = WI.TRANSACTION_STOCK_TYPE AND EI.SEQ_NO = WI.SEQ_NO AND W.INVOICE_TYPE IN ('I1','LI') AND W.XFR_COMPANY IS NOT NULL AND W.STATUS IN ('A','Y') GROUP BY W.COMPANY ,W.XFR_COMPANY ,W.BOOKING_TO ,W.INVOICE_TYPE ,W.INVOICE_SERIES ,W.INVOICE_NO ,W.ENTRY_NO ,W.INVOICE_DATE ,W.ST_FORM_CODE ,W.ST_FORM_NO ,EI.ITEM_CODE ,EI.STOCK_TYPE ,EI.STORE_ID ,WI.MFG_COMPANY ,WI.MFG_BATCH_NO ,WI.NO_OF_PACKAGES ,EI.NET_PRICE ) ) o161524 WHERE ( (o100196.COMPANY = o100191.COMPANY AND o100196.PRODUCT_CODE = o100191.PRODUCT_CODE) and (o161524.COMPANY = o100196.COMPANY AND o161524.ITEM_CODE = o100196.PRODUCT_CODE AND o161524.STOCK_TYPE = o100196.PRODUCT_STOCK_TYPE AND o161524.STORE_ID = o100196.FG_STORE_ID)) AND (o161524.ITEM_GROUP NOT IN ('WP','RN')) AND (o161524.VOUCHER_DATE < TO_DATE('20210224000000','YYYYMMDDHH24MISS')) AND (o161524.COMPANY NOT IN ('00','04','70','82','85','80','56','75','79','18')) AND (o161524.TRANSFER_TO_COMPANY NOT IN ('82','73','04','71','00','72','02','84','85','67','70')) AND (o161524.TRANSFER_TO_COMPANY < '99') AND (decode(o161524.VOUCHER_DATE,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(o161524.VOUCHER_DATE,'YYYY'),'YYYY') || '01','YYYYMM')) IN (TO_DATE('20210101000000','YYYYMMDDHH24MISS'))) GROUP BY o100191.TYPE_OF_PRODUCT,o161524.COMPANY,o161524.TRANSFER_TO_COMPANY,o161524.VOUCHER_TYPE,o161524.VOUCHER_DATE,o161524.PRODUCT_NAME,o161524.PRICE,decode(o161524.VOUCHER_DATE,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(o161524.VOUCHER_DATE,'YYYY'),'YYYY') || '01','YYYYMM')),decode(o161524.VOUCHER_DATE,null,to_date(null, 'MMDDYYYY'),to_date(to_char(trunc(o161524.VOUCHER_DATE,'MM'),'MM') || '1900','MMYYYY')),o161524.GROUP_DESCRIPTION,o161524.ITEM_GROUP;
Created attachment 1518 [details] DSS QUERY DEPOT TO DEPOT STOCK TRANSFER Dear Sir, Kindly help for Create a query of depot to depot transfer on the basis of new items descriptions which mapped by Animesh Sir,. also attached old transfer query . regards sankalp