BI_GST_LINE_VW

(SQL View)
 Index Back

Billing GST Line View


SELECT LINE.BUSINESS_UNIT , LINE.INVOICE , LINE.LINE_SEQ_NUM , 'L' , LINE.DESCR , CASE WHEN PROD.EXD_TAX_ITEM_TYPE = 'S' THEN 'Y' ELSE 'N' END , CASE WHEN EXS.HSN_CODE = ' ' THEN EXS.SAC_CODE ELSE EXS.HSN_CODE END , %Abs(LINE.QTY) , LINE.UNIT_OF_MEASURE , %Abs(%ROUND(LINE.UNIT_AMT , 2)) , %Abs(LINE.GROSS_EXTENDED_BSE) , %Abs(LINE.TOT_DISCOUNT_BSE) , %Abs(LINE.NET_EXTENDED_BSE) , SUM(DTL.EXS_TAX_RATE_PCT) , %Abs(TAX.IGST_AMT_BSE) , %Abs(TAX.CGST_AMT_BSE) , %Abs(TAX.SGST_AMT_BSE) , 0 , 0 , 0 , LINE.ORDER_NO , %Abs(LINE.NET_EXTENDED_BSE + LINE.STX_TAX_AMT_BSE) , ' ' FROM PS_BI_HDR HDR , PS_BI_HDR_GST GST , PS_BI_LINE LINE , PS_BI_LINE_EXS_GST EXS , PS_BI_LINE_EXS_DTL DTL , PS_BI_LN_GST_TX_VW TAX , PS_PROD_MASTER_EXS PROD WHERE HDR.BUSINESS_UNIT = GST.BUSINESS_UNIT AND HDR.INVOICE = GST.INVOICE AND HDR.BILL_STATUS IN ('INV', 'FNL') AND HDR.BI_BU_TAX_IND = '2' AND GST.TPR_TYPE NOT IN ('B2CS', 'B2CL') AND GST.EINV_PROC_FLAG IN ('N', 'R') AND HDR.FINAL_CURCNV_FLG = 'Y' AND HDR.BUSINESS_UNIT = LINE.BUSINESS_UNIT AND HDR.INVOICE = LINE.INVOICE AND LINE.BUSINESS_UNIT = EXS.BUSINESS_UNIT AND LINE.INVOICE = EXS.INVOICE AND LINE.LINE_SEQ_NUM = EXS.LINE_SEQ_NUM AND LINE.BUSINESS_UNIT = DTL.BUSINESS_UNIT AND LINE.INVOICE = DTL.INVOICE AND LINE.LINE_SEQ_NUM = DTL.LINE_SEQ_NUM AND DTL.EXS_TAX_TYPE = 'G' AND DTL.BUSINESS_UNIT = TAX.BUSINESS_UNIT AND DTL.INVOICE = TAX.INVOICE AND DTL.LINE_SEQ_NUM = TAX.LINE_SEQ_NUM AND DTL.EXS_TAX_TYPE = TAX.EXS_TAX_TYPE AND PROD.SETID = ( SELECT REC.SETID FROM PS_SET_CNTRL_REC REC WHERE REC.SETCNTRLVALUE = HDR.BUSINESS_UNIT AND REC.RECNAME = 'PROD_MASTER_EXS') AND LINE.IDENTIFIER = PROD.PRODUCT_ID GROUP BY LINE.BUSINESS_UNIT , LINE.INVOICE , LINE.LINE_SEQ_NUM , LINE.DESCR , CASE WHEN PROD.EXD_TAX_ITEM_TYPE = 'S' THEN 'Y' ELSE 'N' END, CASE WHEN EXS.HSN_CODE = ' ' THEN EXS.SAC_CODE ELSE EXS.HSN_CODE END, LINE.QTY , LINE.UNIT_OF_MEASURE , LINE.UNIT_AMT , LINE.GROSS_EXTENDED_BSE , LINE.TOT_DISCOUNT_BSE , LINE.NET_EXTENDED_BSE , TAX.IGST_AMT_BSE , TAX.CGST_AMT_BSE , TAX.SGST_AMT_BSE , LINE.ORDER_NO, LINE.NET_EXTENDED_BSE + LINE.STX_TAX_AMT_BSE ORDER BY LINE.BUSINESS_UNIT , LINE.INVOICE , LINE.LINE_SEQ_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
3 LINE_SEQ_NUM Number(5,0) INTEGER NOT NULL Sequence
4 ROW_TYPE Character(2) VARCHAR2(2) NOT NULL 01/23/01 SCC CN#WV802-1.0: New Field
5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 SERVICE_FLG Character(1) VARCHAR2(1) NOT NULL Web Service?
7 HSN_CODE Character(10) VARCHAR2(10) NOT NULL HSN Code for GST
8 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity
9 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
10 UNIT_AMT Signed Number(17,4) DECIMAL(15,4) NOT NULL Unit Price
11 GROSS_EXTENDED_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Extended
12 TOT_DISCOUNT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Line Discount Amount
13 NET_EXTENDED_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Extended Amount
14 EXS_TAX_RATE_PCT Number(8,4) DECIMAL(7,4) NOT NULL Tax Rate Percentage
15 IGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL IGST Tax Amount
16 CGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL CGST Tax Amount
17 SGST_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL SGST Tax Amount
18 GST_CESS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL GST Cess amount
19 WTHD_CESS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Field to hold the amount value for Wihtholding Cess amount
20 OTHER_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Other Amnt
21 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
22 LINE_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Plus Tax
23 COUNTRY_ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin Country