| Summary: | DSS QUERY DEPOT TO DEPOT STOCK TRANSFER | ||
|---|---|---|---|
| Product: | [01.IMIS] MANAGEMENT INFORMATION SYSTEM | Reporter: | SANKALP SHUKLA <sankalp.shukla> |
| Component: | MIS FORMS AND REPORTS | Assignee: | Mahender <mahender.singh> |
| Status: | RESOLVED USER TO VERIFY | ||
| Severity: | major | CC: | mahender.singh, sharad.sharma |
| Priority: | --- | ||
| Version: | unspecified | ||
| Hardware: | PC | ||
| OS: | Windows | ||
| Database: | Module ID: | ||
| Attachments: | DSS QUERY DEPOT TO DEPOT STOCK TRANSFER | ||
|
Comment 3
Mahender
2021-02-26 10:31:10 IST
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
|