| Summary: | Additional Details Require in Query. | ||
|---|---|---|---|
| Product: | [AUTOMAIL] DAILY_MIS | Reporter: | Saurabh poddar <saurabh.poddar> |
| Component: | DAILY_MIS | Assignee: | Sharad <sharad.sharma> |
| Status: | RESOLVED USER TO VERIFY | ||
| Severity: | enhancement | CC: | mahender.singh, sharad.sharma |
| Priority: | --- | ||
| Version: | unspecified | ||
| Hardware: | PC | ||
| OS: | Windows | ||
| Database: | Module ID: | ||
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
|
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