Attachment 2730 Details for Bug 6934 – SS_0936_IMIS_Against call id - 6934_new.sql

SS_0936_IMIS_Against call id - 6934_new.sql

SS_0936_IMIS_Against call id - 6934_new.sql (text/x-vhdl), 5.10 KB, created by Sharad on 2022-05-03 10:58:04 IST

(hide)

Description:

Filename:

MIME Type:

Creator: Sharad

Created: 2022-05-03 10:58:04 IST

Size: 5.10 KB

patch

obsolete

>-- Script for misc Ap Expense Code 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.PURCH_GSTIN_FLAG 																	PURCH_GSTIN_FLAG
>	, A.CHARGED_ON_BILLS																	CHARGED_ON_BILLS
>	, A.ALLOW_GST_CREDIT  																	ALLOW_GST_CREDIT
>	, A.GST_REVERSE_FLAG																	GST_REVERSE_FLAG
>	, A.NARRATION   																		NARRATION
>	, A.CREATED_BY																			CREATED_BY
>	, (SELECT USER_NAME FROM USER_CODES WHERE USER_CODE = A.CREATED_BY)						CREATED_BY_NAME
>	, A.CREATED_ON 																			CREATED_BY
>	, A.LAST_UPD_BY																			LAST_UPD_BY
>	, (SELECT USER_NAME FROM USER_CODES WHERE USER_CODE = A.LAST_UPD_BY)					LAST_UPD_BY_NAME
>	, A.LAST_UPD_ON 																		LAST_UPD_ON
>FROM VW_MISC_AP_VOUCHERS A, AP_VOUCHER_MISC_DETAILS B, WITH_HOLD_TAX_DETAILS C, ALL_SUPPLIERS 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.SUPPLIER_CODE
>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 misc Ap 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_AP.GET_SUPPLIER_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.PURCH_GSTIN_FLAG 																	PURCH_GSTIN_FLAG
>	, A.CHARGED_ON_BILLS																	CHARGED_ON_BILLS
>	, A.ALLOW_GST_CREDIT  																	ALLOW_GST_CREDIT
>	, A.GST_REVERSE_FLAG																	GST_REVERSE_FLAG
>	, A.NARRATION   																		NARRATION
>	, A.CREATED_BY																			CREATED_BY
>	, (SELECT USER_NAME FROM USER_CODES WHERE USER_CODE = A.CREATED_BY)						CREATED_BY_NAME
>	, A.CREATED_ON 																			CREATED_BY
>	, A.LAST_UPD_BY																			LAST_UPD_BY
>	, (SELECT USER_NAME FROM USER_CODES WHERE USER_CODE = A.LAST_UPD_BY)					LAST_UPD_BY_NAME
>	, A.LAST_UPD_ON 																		LAST_UPD_ON
>FROM VW_MISC_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_SUPPLIERS 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.SUPPLIER_CODE
>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 6934: 2123 | 2126 | 2730