BI_GST_HDR_VW(SQL View) |
Index Back |
|---|---|
Billing GST Header View |
| SELECT HDR.BUSINESS_UNIT , HDR.INVOICE , 'H' , GST.VERSION_CHAR , ' ' , '' , GST.TPR_TYPE , GST.GST_TXN_CATG , GST.GST_DOC_TYPE , ' ' , HDR.INVOICE_DT , HDR.BI_CURRENCY_CD , GST.REV_CHARGE_APPL , '' , '' , HDR.ORDER_NO , CASE WHEN HDR.INVOICE_TYPE = 'ACR' THEN HDR.ORIGINAL_INVOICE ELSE ' ' END , CASE WHEN HDR.INVOICE_TYPE = 'ACR' THEN H.INVOICE_DT ELSE NULL END , HDR.PYMNT_TERMS_CD , ' ' , HDR.CONTRACT_NUM , HDR.PROJECT_ID , HDR.PO_REF , '' , LOC1.DESCR , GRN1.GST_RGSTN_NBR , LOC1.ADDRESS1 , LOC1.ADDRESS2 , LOC1.CITY , LOC1.NUMERIC_CD , LOC1.POSTAL , LOC1.PHONE , ' ' , CUST1.NAME1 , CUST1.GST_RGSTN_NBR , GST.POS_NUMERIC_CD , CUST1.ADDRESS1 , CUST1.ADDRESS2 , CUST1.CITY , CASE WHEN GST.GST_TXN_CATG IN ('EXPWP' ,'EXPWOP') THEN '96' ELSE CUST1.NUMERIC_CD END , CUST1.POSTAL , CUST1.COUNTRY , CUST1.PHONE , ' ' , BANK.BANK_ACCOUNT_NUM , HDR.PAYMENT_METHOD , BANK.BRANCH_NAME , '' , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.DESCR ELSE LOC2.DESCR END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.ADDRESS1 ELSE LOC2.ADDRESS1 END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.ADDRESS2 ELSE LOC2.ADDRESS2 END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.CITY ELSE LOC2.CITY END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.NUMERIC_CD ELSE LOC2.NUMERIC_CD END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.POSTAL ELSE LOC2.POSTAL END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.NAME1 ELSE CUST2.NAME1 END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.GST_RGSTN_NBR ELSE CUST2.GST_RGSTN_NBR END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.ADDRESS1 ELSE CUST2.ADDRESS1 END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.ADDRESS2 ELSE CUST2.ADDRESS2 END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.CITY ELSE CUST2.CITY END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.POSTAL ELSE CUST2.POSTAL END , CASE WHEN GST.GST_TXN_CATG IN ('EXPWP' ,'EXPWOP') THEN (CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.NUMERIC_CD ELSE LOC2.NUMERIC_CD END) ELSE (CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.NUMERIC_CD ELSE CUST2.NUMERIC_CD END) END , %Abs(HDR.INVOICE_PRETAX_BSE) , %Abs(TAX.IGST_AMT_BSE) , %Abs(TAX.CGST_AMT_BSE) , %Abs(TAX.SGST_AMT_BSE) , 0 , 0 , 0 , %Abs(HDR.INVOICE_AMT_BSE) , %Abs(HDR.INVOICE_AMOUNT) , %Abs(HDR.PAID_AMT_BSE) , %Abs(HDR.FORWARD_BAL_BSE) , 0 , 0 , 0 , %Abs(HDR.TOT_STX_AMT_BSE) , 0 , 'GST' , %Abs(SUM(LINE.TOT_DISCOUNT_BSE)) , CASE WHEN GST.GST_TXN_CATG IN ('SEZWP' , 'SEZWOP' , 'DEXP') THEN 'Y' ELSE 'N' END FROM PS_BI_HDR HDR , PS_BI_HDR_GST GST , PS_BI_HDR H , PS_BI_LINE LINE , PS_BI_LOCATION_VW LOC1 , PS_BI_GST_RGSTN_VW GRN1 , PS_BI_GST_CUST_VW CUST1 , PS_BI_GST_BANK_STG BANK , PS_BI_LOCATION_VW LOC2 , PS_BI_GST_RGSTN_VW GRN2 , PS_BI_GST_CUST_VW CUST2 , PS_BI_HD_GST_TX_VW TAX 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 HDR.FINAL_CURCNV_FLG = 'Y' AND GST.TPR_TYPE NOT IN ('B2CS', 'B2CL') AND GST.EINV_PROC_FLAG IN ('N', 'R') AND GST.TPR_TYPE <> ' ' AND GST.GST_TXN_CATG <> ' ' AND HDR.BUSINESS_UNIT = H.BUSINESS_UNIT AND HDR.ORIGINAL_INVOICE = H.INVOICE AND HDR.BUSINESS_UNIT = LOC1.BUSINESS_UNIT AND LOC1.BUSINESS_UNIT = GRN1.BUSINESS_UNIT AND GRN1.STATE = LOC1.STATE AND HDR.BUSINESS_UNIT = LINE.BUSINESS_UNIT AND HDR.INVOICE = LINE.INVOICE AND CUST1.SETID = ( SELECT REC.SETID FROM PS_SET_CNTRL_REC REC WHERE REC.SETCNTRLVALUE = HDR.BUSINESS_UNIT AND REC.RECNAME = 'CUSTOMER') AND CUST1.CUST_ID = HDR.BILL_TO_CUST_ID AND CUST1.ADDRESS_SEQ_NUM = HDR.ADDRESS_SEQ_NUM AND HDR.BUSINESS_UNIT = BANK.BUSINESS_UNIT AND HDR.INVOICE = BANK.INVOICE AND HDR.BANK_CD = BANK.BANK_CD AND HDR.BANK_ACCT_KEY = BANK.BANK_ACCT_KEY AND HDR.SHIP_FROM_BU = LOC2.BUSINESS_UNIT(+) AND LOC2.BUSINESS_UNIT = GRN2.BUSINESS_UNIT (+) AND LOC2.STATE = GRN2.STATE (+) AND CUST1.SETID = CUST2.SETID (+) AND HDR.SHIP_TO_CUST_ID = CUST2.CUST_ID (+) AND HDR.SHIP_TO_ADDR_NUM = CUST2.ADDRESS_SEQ_NUM (+) AND HDR.BUSINESS_UNIT = TAX.BUSINESS_UNIT AND HDR.INVOICE = TAX.INVOICE GROUP BY HDR.BUSINESS_UNIT , HDR.INVOICE , GST.VERSION_CHAR , GST.TPR_TYPE , GST.GST_TXN_CATG , GST.GST_DOC_TYPE , HDR.INVOICE_DT , HDR.BI_CURRENCY_CD , GST.REV_CHARGE_APPL , HDR.ORDER_NO , CASE WHEN HDR.INVOICE_TYPE = 'ACR' THEN HDR.ORIGINAL_INVOICE ELSE ' ' END , CASE WHEN HDR.INVOICE_TYPE = 'ACR' THEN H.INVOICE_DT ELSE NULL END , HDR.PYMNT_TERMS_CD , HDR.CONTRACT_NUM , HDR.PROJECT_ID , HDR.PO_REF , LOC1.DESCR , GRN1.GST_RGSTN_NBR , LOC1.ADDRESS1 , LOC1.ADDRESS2 , LOC1.CITY , LOC1.NUMERIC_CD , LOC1.POSTAL , LOC1.PHONE , CUST1.NAME1 , CUST1.GST_RGSTN_NBR , GST.POS_NUMERIC_CD , CUST1.ADDRESS1 , CUST1.ADDRESS2 , CUST1.CITY , CASE WHEN GST.GST_TXN_CATG IN ('EXPWP', 'EXPWOP') THEN '96' ELSE CUST1.NUMERIC_CD END , CUST1.POSTAL , CUST1.COUNTRY , CUST1.PHONE , BANK.BANK_ACCOUNT_NUM , HDR.PAYMENT_METHOD , BANK.BRANCH_NAME , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.DESCR ELSE LOC2.DESCR END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.ADDRESS1 ELSE LOC2.ADDRESS1 END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.ADDRESS2 ELSE LOC2.ADDRESS2 END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.CITY ELSE LOC2.CITY END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.NUMERIC_CD ELSE LOC2.NUMERIC_CD END , CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.POSTAL ELSE LOC2.POSTAL END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.NAME1 ELSE CUST2.NAME1 END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.GST_RGSTN_NBR ELSE CUST2.GST_RGSTN_NBR END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.ADDRESS1 ELSE CUST2.ADDRESS1 END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.ADDRESS2 ELSE CUST2.ADDRESS2 END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.CITY ELSE CUST2.CITY END , CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.POSTAL ELSE CUST2.POSTAL END , CASE WHEN GST.GST_TXN_CATG IN ('EXPWP','EXPWOP') THEN (CASE WHEN HDR.SHIP_FROM_BU = ' ' THEN LOC1.NUMERIC_CD ELSE LOC2.NUMERIC_CD END) ELSE (CASE WHEN HDR.SHIP_TO_CUST_ID = ' ' THEN CUST1.NUMERIC_CD ELSE CUST2.NUMERIC_CD END) END , %Abs(HDR.INVOICE_PRETAX_BSE) , %Abs(TAX.IGST_AMT_BSE) , %Abs(TAX.CGST_AMT_BSE) , %Abs(TAX.SGST_AMT_BSE) , %Abs(HDR.INVOICE_AMT_BSE) , %Abs(HDR.INVOICE_AMOUNT) , %Abs(HDR.PAID_AMT_BSE) , %Abs(HDR.FORWARD_BAL_BSE) , %Abs(HDR.TOT_STX_AMT_BSE) , CASE WHEN GST.GST_TXN_CATG IN ('SEZWP' , 'SEZWOP' , 'DEXP') THEN 'Y' ELSE 'N' END |
| # | 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 | ROW_TYPE | Character(2) | VARCHAR2(2) NOT NULL | 01/23/01 SCC CN#WV802-1.0: New Field |
| 4 | VERSION_CHAR | Character(10) | VARCHAR2(10) NOT NULL | Version |
| 5 | IRN | Character(64) | VARCHAR2(64) NOT NULL | Invoice Reference Number |
| 6 | IRP_DATE | DateTime(26) | TIMESTAMP | IRP Date |
| 7 | TPR_TYPE | Character(30) | VARCHAR2(30) NOT NULL |
Trading Partner type
Prompt Table: GST_TPR_TYPE_VW |
| 8 | GST_TXN_CATG | Character(30) | VARCHAR2(30) NOT NULL |
GST Transaction Category
Prompt Table: TPR_TXN_CAT_VW |
| 9 | GST_DOC_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
GST Document Type
CRN=Credit Note DBN=Debit Note INV=Regular Invoice |
| 10 | QR_CODE | Long Character | CLOB | QR Code |
| 11 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 12 | BI_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Billing Currency Code |
| 13 | REV_CHARGE_APPL | Character(1) | VARCHAR2(1) NOT NULL | Reverse Charge Applicability |
| 14 | FROM_DT | Date(10) | DATE | From Date |
| 15 | TO_DT | Date(10) | DATE | To Date |
| 16 | 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 |
| 17 | ORIGINAL_INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Original Invoice |
| 18 | ORIG_INVOICE_DT | Date(10) | DATE | Original Invoice Date. Used in BI_EXTRCT for adjustment invoices to hold the original invoice date. |
| 19 | PYMNT_TERMS_CD | Character(5) | VARCHAR2(5) NOT NULL | Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers. |
| 20 | LOT | Character(20) | VARCHAR2(20) NOT NULL | Lot Number |
| 21 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 22 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 23 | PO_REF | Character(30) | VARCHAR2(30) NOT NULL | Specifies the purchase order number associated with a receivables item. |
| 24 | PO_DT | Date(10) | DATE | Date |
| 25 | BU_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Business unit description. |
| 26 | GST_RGSTN_NBR | Character(30) | VARCHAR2(30) NOT NULL | GST Registration Number |
| 27 | ADDRESS1_BU | Character(55) | VARCHAR2(55) NOT NULL | BU Address Line 1 |
| 28 | ADDRESS2_BU | Character(55) | VARCHAR2(55) NOT NULL | BU Address Line 2 |
| 29 | CITY_BU | Character(30) | VARCHAR2(30) NOT NULL | Business Unit City |
| 30 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 31 | POSTAL_BU | Character(12) | VARCHAR2(12) NOT NULL | Business Unit Postal Code |
| 32 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 33 | EMAILID | Character(70) | VARCHAR2(70) NOT NULL | A user's E-mail address |
| 34 | BILL_CUST_NAME | Character(40) | VARCHAR2(40) NOT NULL | Bill To Name |
| 35 | GST_RGSTN_NBR_1 | Character(30) | VARCHAR2(30) NOT NULL | GST Registration Number |
| 36 | STATE_BILLTO | Character(6) | VARCHAR2(6) NOT NULL | Bill To State |
| 37 | ADDRESS1_BILL | Character(55) | VARCHAR2(55) NOT NULL | Bill to Address Line 1 |
| 38 | ADDRESS2_BILL | Character(55) | VARCHAR2(55) NOT NULL | Bill to Address Line 2 |
| 39 | CITY_BILLTO | Character(30) | VARCHAR2(30) NOT NULL | Bill To City |
| 40 | STATE_BILLTO_NAME | Character(30) | VARCHAR2(30) NOT NULL | State Name |
| 41 | POSTAL_BILLTO | Character(12) | VARCHAR2(12) NOT NULL | Bill To Postal |
| 42 | COUNTRY_BILLTO | Character(3) | VARCHAR2(3) NOT NULL | Bill To Country |
| 43 | BILLTO_PHONE | Character(24) | VARCHAR2(24) NOT NULL | Buyer Phone |
| 44 | EMAILID2 | Character(70) | VARCHAR2(70) NOT NULL | Email ID |
| 45 | BANK_ACCOUNT_NUM | Character(35) | VARCHAR2(35) NOT NULL | The literal unique identifier associated with a given bank/counterparty account. |
| 46 | PAYMENT_METHOD | Character(3) | VARCHAR2(3) NOT NULL |
Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft.
10/28/2003 edw 655070000 - Removed LTC and CSH xlats.
ACH=ACH CC=Credit Card CHK=Check CSH=Cash DD=Direct Debit DR=Draft EC=Electronic Check EFT=Electronic Fund Transfer GE=Giro - EFT PL=PayPal WIR=Wire Report |
| 47 | BRANCH_NAME | Character(10) | VARCHAR2(10) NOT NULL | Branch Name |
| 48 | PAYMENT_DUE_DT | Date(10) | DATE | Payment Due Date |
| 49 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 50 | SHIP_FROM_ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Ship From Address 1 |
| 51 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 52 | SHIP_FROM_CITY | Character(30) | VARCHAR2(30) NOT NULL | Ship From City |
| 53 | SHIP_FROM_STATE | Character(6) | VARCHAR2(6) NOT NULL | Ship From State |
| 54 | SHIP_FROM_POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Ship From Postal |
| 55 | CUST_SHIPTO_SRCH | Character(50) | VARCHAR2(50) NOT NULL | Ship To Customer |
| 56 | GST_RGSTN_NBR_2 | Character(30) | VARCHAR2(30) NOT NULL | GST Registration Number |
| 57 | SHIPTO_ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Recepient Street |
| 58 | SHIPTO_ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Recepient Street Suppliment 1 |
| 59 | SHIPTO_CITY | Character(30) | VARCHAR2(30) NOT NULL | Recepient City |
| 60 | SHIPTO_POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Recepient POSTAL |
| 61 | SHIPTO_STATE | Character(6) | VARCHAR2(6) NOT NULL | Ship To State |
| 62 | INVOICE_PRETAX_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Pretax Invoice Amount |
| 63 | IGST_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | IGST Tax Amount |
| 64 | CGST_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | CGST Tax Amount |
| 65 | SGST_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | SGST Tax Amount |
| 66 | GST_CESS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | GST Cess amount |
| 67 | WTHD_CESS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Field to hold the amount value for Wihtholding Cess amount |
| 68 | ROUND_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 69 | INVOICE_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 70 | INVOICE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 71 | PAID_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Base Paid Amount |
| 72 | FORWARD_BAL_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Forwarding Balance |
| 73 | FREIGHT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Freight Amount |
| 74 | INSURANCE_CHARGE | Signed Number(20,2) | DECIMAL(18,2) NOT NULL | Insurance Charge |
| 75 | OTHER_AMT | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Other Amnt |
| 76 | TOT_STX_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Sales Tax Amount Base |
| 77 | TOTAL_CHARGES | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount of Charges |
| 78 | DEFAULTSCHEME | Character(10) | VARCHAR2(10) NOT NULL |
Protocol
H=http S=https |
| 79 | TOT_DISCOUNT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Line Discount Amount |
| 80 | IGST_APPLICABILITY | Character(1) | VARCHAR2(1) NOT NULL | IGST Applicability |