Attachment 2151 Details for Bug 6995 – SS_0942_IMIS_Against call id - 5995

SS_0942_IMIS_Against call id - 5995

SS_0942_IMIS_Against call id - 5995.sql (text/x-vhdl), 4.32 KB, created by Sharad on 2021-08-24 12:46:32 IST

(hide)

Description:

Filename:

MIME Type:

Creator: Sharad

Created: 2021-08-24 12:46:32 IST

Size: 4.32 KB

patch

obsolete

>-- Script for Customer Expense Voucher (CE) wise detail --
>
>SELECT
>	  A.COMPANY																				COMPANY_CODE
>	, A.VOUCHER_TYPE||'-'||A.VOUCHER_SERIES||'-'||LTRIM(TO_CHAR(A.VOUCHER_NUMBER,'00000')) 	VOUCHER_INFO
>	, A.VOUCHER_DATE																		VOUCHER_DATE
>	, A.SUPPLIER_CODE																		SPLR_CODE
>	, NVL(D.GST_TMP_NO, D.GST_NUMBER )														GST_NUMBER
>	, A.SUPPLIER_REFERENCE  																SUPPLIER_REFERENCE_NUMBER
>	, A.SUPPLIER_REFERENCE_DATE																SUPPLIER_REFERENCE_DATE
>	, PKG_AP.GET_SUPPLIER_NAME(A.COMPANY, A.SUPPLIER_CODE)									SPLR_NAME
>	, B.EXPENSE_CODE																		EXPENSE_CODE
>	, B.DESCRIPTION																			EXPENSE_DESC
>	, B.MIS_CODE																			MIS_CODE
>	, A.SUPPLIER_AMOUNT_FC																	SUPPLIER_BILL_AMT
>	, C.TAX_BASE_AMOUNT																		BASE_VALUE
>	, A.CATEGORY_CODE																		CATEGORY_CODE
>	, C.WHT_RATE																			TDS_RATE
>	, C.WITH_HOLD_TAX_AMOUNT																TDS_AMOUNT
>	, NVL(B.COST_OF_GOODS,B.COST_OF_SERVICES)												TAXABLE_VAL
>	, NVL(B.IGST_AMOUNT,0) + NVL(B.CGST_AMOUNT,0 )+ NVL(B.SGST_AMOUNT,0)					GST_AMOUNT
>	, A.ALLOW_GST_CREDIT  																	ALLOW_GST_CREDIT
>	, A.GST_REVERSE_FLAG																	GST_REVERSE_FLAG
>	, A.NARRATION   																		NARRATION
>FROM VW_CUSTOMER_AP_VOUCHERS A, AP_VOUCHER_MISC_DETAILS B, WITH_HOLD_TAX_DETAILS C, ALL_CUSTOMERS D
>WHERE A.COMPANY 	=   B.COMPANY
>AND A.APV_RECID 	=   B.PARENT_RECID
>AND A.COMPANY		=	C.COMPANY 		(+)
>AND A.APV_RECID		= 	C.PARENT_RECID  (+)
>AND A.SUPPLIER_CODE	=	D.CUSTOMER_CODE
>AND A.VOUCHER_TYPE	=	'CE'
>AND ( A.COMPANY = 'P1' OR ( 'Y' IN ('Y', 'C', 'W', 'X') AND A.COMPANY IN (SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = 'P1') ))
>AND A.VOUCHER_DATE BETWEEN '01/04/2020' AND '31/03/2021'	-- Date to be change --
>AND A.STATUS     	IN 	('A', 'Y' )
>ORDER BY A.COMPANY, VOUCHER_INFO,VOUCHER_DATE, SPLR_CODE;
>
>---------------------------------------------------------------------------------------------------------------------------------------------------------
>
>-- Script for Customer Expense Voucher (CE) GST Rate wise summary --   
>
>SELECT
>	  A.COMPANY																				COMPANY_CODE
>	, A.VOUCHER_TYPE||'-'||A.VOUCHER_SERIES||'-'||LTRIM(TO_CHAR(A.VOUCHER_NUMBER,'00000')) 	VOUCHER_INFO
>	, A.VOUCHER_DATE																		VOUCHER_DATE
>	, A.SUPPLIER_CODE																		SPLR_CODE
>	, PKG_AR.GET_CUSTOMER_NAME(A.COMPANY, A.SUPPLIER_CODE)									SPLR_NAME
>	, NVL(E.GST_TMP_NO, E.GST_NUMBER )														GST_NUMBER
>	, A.SUPPLIER_REFERENCE  																SUPPLIER_REFERENCE_NUMBER
>	, A.SUPPLIER_REFERENCE_DATE																SUPPLIER_REFERENCE_DATE
>	, A.SUPPLIER_AMOUNT_FC																	SUPPLIER_BILL_AMT
>	, C.TAX_BASE_AMOUNT																		BASE_VALUE
>	, A.CATEGORY_CODE																		CATEGORY_CODE
>	, C.WHT_RATE																			TDS_RATE
>	, C.WITH_HOLD_TAX_AMOUNT																TDS_AMOUNT
>	, D.COG
>	, D.GST_RATE
>	, D.GST_AMT
>	, A.ALLOW_GST_CREDIT  																	ALLOW_GST_CREDIT
>	, A.GST_REVERSE_FLAG																	GST_REVERSE_FLAG
>	, A.NARRATION   																		NARRATION
>FROM VW_CUSTOMER_AP_VOUCHERS A, WITH_HOLD_TAX_DETAILS C, (
>															SELECT
>																  COMPANY
>																, PARENT_RECID
>																, SUM(NVL(B.COST_OF_GOODS,B.COST_OF_SERVICES)) 								COG
>																, NVL(B.IGST_RATE,0) + NVL(B.CGST_RATE,0 )+ NVL(B.SGST_RATE,0) 				GST_RATE
>																, SUM(NVL(B.IGST_AMOUNT,0) + NVL(B.CGST_AMOUNT,0 )+ NVL(B.SGST_AMOUNT,0)) 	GST_AMT
>															FROM AP_VOUCHER_MISC_DETAILS B
>															GROUP BY COMPANY, PARENT_RECID, NVL(B.IGST_RATE,0) + NVL(B.CGST_RATE,0 )+ NVL(B.SGST_RATE,0)
>														 ) D, ALL_CUSTOMERS E
>WHERE A.COMPANY		=	C.COMPANY  		(+)
>AND A.APV_RECID		= 	C.PARENT_RECID  (+)
>AND A.COMPANY		=	D.COMPANY
>AND A.APV_RECID		=	D.PARENT_RECID
>AND A.SUPPLIER_CODE	=	E.CUSTOMER_CODE
>AND A.VOUCHER_TYPE	=	'CE'
>AND ( A.COMPANY = 'P1' OR ( 'Y' IN ('Y', 'C', 'W', 'X') AND A.COMPANY IN (SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = 'P1') ))
>AND A.VOUCHER_DATE BETWEEN '01/04/2020' AND '31/03/2021'  -- Date to be change --
>AND A.STATUS     	IN 	('A', 'Y' )
>ORDER BY A.COMPANY, VOUCHER_INFO,VOUCHER_DATE, SPLR_CODE;
>

Actions: View

Attachments on bug 6995: 2150 | 2151