Done Please check & confirm. user this query SELECT BILTY_NUMBER , BILTY_DATE , TPT_CODE TPT_CODE , PKG_AR.GET_TRANSPORTER_NAME(SUBSTR(DOCUMENT_STRING,0,2), TPT_CODE) TPT_NAME , NVL(NVL(LINK_CUSTOMER, CUSTOMER_CODE), XFR_COMPANY) CUSTOMER_CODE , NVL(PKG_AR.GET_CUSTOMER_NAME(COMPANY_CODE, NVL(LINK_CUSTOMER, CUSTOMER_CODE)), PKG_CONTROLS.GET_COMPANY_INFO(COMPANY_CODE, 'SHORT_NAME')) CUSTOMER_NAME , DOCUMENT_STRING , TO_CHAR(DOCUMENT_DATE,'DD-MM-YYYY') DOCUMENT_DATE , ITEM_CODE ITEM_CODE , STOCK_TYPE STOCK_TYPE , STOCK_NAME ITEM_NAME , ITEM_PRICE ITEM_RATE , NVL(IUOM_CODE, ( SELECT IUOM_CODE FROM ALL_STOCK_TYPES WHERE ITEM_CLASS = B.ITEM_CLASS AND ITEM_CODE = B.ITEM_CODE AND STOCK_TYPE = B.STOCK_TYPE )) UM , NVL(NO_OF_PACKS, ITEM_QTY) ITEM_QTY , ( SELECT QTY_IN_CASE FROM ALL_STOCK_TYPES WHERE ITEM_CLASS = B.ITEM_CLASS AND ITEM_CODE = B.ITEM_CODE AND STOCK_TYPE = B.STOCK_TYPE ) CASES_SIZE , TAXABLE_AMOUNT TAXABLE_VAL , BOOKING_FROM , DELIVERY_TO , DELIVERY_DATE , CHARGE_WEIGHT , FREIGHT_AMOUNT , GROSS_WEIGHT , NO_OF_CASES FROM ( SELECT DOCUMENT_RECID DOCUMENT_RECID , TRANSPORTER_CODE TPT_CODE , BILTY_NUMBER BILTY_NUMBER , TO_CHAR(BILTY_DATE, 'DD/MM/YYYY') BILTY_DATE , BOOKING_FROM BOOKING_FROM , DELIVERY_TO DELIVERY_TO , TO_CHAR(DOCUMENT_DATE, 'DD/MM/YYYY')DELIVERY_DATE , SUM(NVL(CHARGE_WEIGHT,0)) CHARGE_WEIGHT , SUM(NVL(FREIGHT_AMOUNT,0)) FREIGHT_AMOUNT , SUM(NVL(GROSS_WEIGHT,0)) GROSS_WEIGHT , SUM(NVL(TOTAL_CASES,0)) NO_OF_CASES FROM VW_TPT_BILL_DETAIL WHERE ( COMPANY_CODE = 'P1' OR ( 'Y' IN ('Y', 'C', 'W', 'X') AND COMPANY_CODE IN (SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = 'P1') ) ) AND DOCUMENT_DATE >= '01/07/2017' AND STATUS != 'Z' AND DOCUMENT_DATE BETWEEN '01/04/2020' AND '30/11/2020' GROUP BY DOCUMENT_RECID, TRANSPORTER_CODE, BILTY_NUMBER, BILTY_DATE, BOOKING_FROM, DELIVERY_TO, DOCUMENT_DATE ORDER BY 1, 2 ) A, VW_GST_INVOICE_ITEMS B WHERE A.DOCUMENT_RECID = B.PARENT_RECID ORDER BY 1,2,3,4,5,6,7,8
Please do the needful
Dear Sir, Please find below query which was given by you. we need General Items sales details with Finished Goods details. Please provide asap. SELECT BILTY_NUMBER , BILTY_DATE , TPT_CODE TPT_CODE , PKG_AR.GET_TRANSPORTER_NAME(SUBSTR(DOCUMENT_STRING,0,2), TPT_CODE) TPT_NAME , NVL(NVL(LINK_CUSTOMER, CUSTOMER_CODE), XFR_COMPANY) CUSTOMER_CODE , NVL(PKG_AR.GET_CUSTOMER_NAME(COMPANY_CODE, NVL(LINK_CUSTOMER, CUSTOMER_CODE)), PKG_CONTROLS.GET_COMPANY_INFO(COMPANY_CODE, 'SHORT_NAME')) CUSTOMER_NAME , DOCUMENT_STRING , TO_CHAR(DOCUMENT_DATE,'DD/MM/YYYY') DOCUMENT_DATE , ITEM_CODE ITEM_CODE , STOCK_TYPE STOCK_TYPE , STOCK_NAME ITEM_NAME , ITEM_PRICE ITEM_RATE , IUOM_CODE UM , NO_OF_PACKS ITEM_QTY , ( SELECT QTY_IN_CASE FROM ALL_STOCK_TYPES WHERE ITEM_CLASS = B.ITEM_CLASS AND ITEM_CODE = B.ITEM_CODE AND STOCK_TYPE = B.STOCK_TYPE ) CASES_SIZE , TAXABLE_AMOUNT TAXABLE_VAL , BOOKING_FROM , DELIVERY_TO , DELIVERY_DATE , CHARGE_WEIGHT , FREIGHT_AMOUNT , GROSS_WEIGHT , NO_OF_CASES FROM ( SELECT DOCUMENT_RECID DOCUMENT_RECID , TRANSPORTER_CODE TPT_CODE , BILTY_NUMBER BILTY_NUMBER , TO_CHAR(BILTY_DATE, 'DD/MM/YYYY') BILTY_DATE , BOOKING_FROM BOOKING_FROM , DELIVERY_TO DELIVERY_TO , TO_CHAR(DOCUMENT_DATE, 'DD/MM/YYYY')DELIVERY_DATE , SUM(NVL(CHARGE_WEIGHT,0)) CHARGE_WEIGHT , SUM(NVL(FREIGHT_AMOUNT,0)) FREIGHT_AMOUNT , SUM(NVL(GROSS_WEIGHT,0)) GROSS_WEIGHT , SUM(NVL(TOTAL_CASES,0)) NO_OF_CASES FROM VW_TPT_BILL_DETAIL WHERE ( COMPANY_CODE = '73' OR ( 'N' IN ('Y', 'C', 'W', 'X') AND COMPANY_CODE IN (SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = '73') ) ) --AND TRANSPORTER_CODE = :TPT_BILL.TRANSPORTER_CODE AND DOCUMENT_DATE >= '01/07/2017' --AND (TRANSPORTER_CODE, BILTY_NUMBER ) NOT IN (SELECT TRANSPORTER_CODE, BILTY_NUMBER FROM TPT_BILL_DETAIL) AND STATUS != 'Z' AND DOCUMENT_DATE BETWEEN '01/04/2020' AND TRUNC(SYSDATE)-1 GROUP BY DOCUMENT_RECID, TRANSPORTER_CODE, BILTY_NUMBER, BILTY_DATE, BOOKING_FROM, DELIVERY_TO, DOCUMENT_DATE ORDER BY 1, 2 ) A, VW_GST_INVOICE_ITEMS B WHERE A.DOCUMENT_RECID = B.PARENT_RECID ORDER BY 1,2,3,4 Regards, Saurabh Poddar Employee Code- E21060