View: JAI_CMN_CESS_TRXS_V

Product:	JA - Asia/Pacific Localizations
Description:
Implementation/DBA Data:	APPS.JAI_CMN_CESS_TRXS_V

View Text

SELECT DISTINCT SOURCE
, PARTY_NAME
, PARTY_SITE
, SLNO
, REGISTER_ID
, TRANSACTION_DATE
, CODE_COMBINATION_ID
, ITEM_ID
, EX_INV_NO
, TAX_NAME
, TAX_TYPE
, CESS_TYPE
, RATE
, CESS_AMT
, TAXABLE_BASIS
FROM ( SELECT 'MANUAL AR INVOICES' SOURCE
, JP.PARTY_NAME PARTY_NAME
, JPS.PARTY_SITE_CODE PARTY_SITE
, /* RAX.TRX_NUMBER DOC_NUMBER
, */ TO_CHAR(RAX.TRX_NUMBER) SLNO
, NULL REGISTER_ID
, TRUNC(JRL.CREATION_DATE) TRANSACTION_DATE
, JTC.TAX_ACCOUNT_ID CODE_COMBINATION_ID
, JRL.INVENTORY_ITEM_ID ITEM_ID
, SUBSTR(JRL.EXCISE_INVOICE_NO
, 1
, 30) EX_INV_NO
, JTC.TAX_NAME TAX_NAME
, JTC.TAX_TYPE TAX_TYPE
, JTC.STFORM_TYPE CESS_TYPE
, JTC.TAX_RATE RATE
, JRTL.TAX_AMOUNT CESS_AMT
, JRTL.TAX_AMOUNT / (JTC.TAX_RATE /100) TAXABLE_BASIS
FROM JAI_AR_TRX_LINES JRL
, JAI_AR_TRX_TAX_LINES JRTL
, JAI_CMN_TAXES_ALL JTC
, RA_CUSTOMER_TRX_ALL RAX
, JAI_CMN_CUSTOMER_SITES_V JPS
, JAI_CMN_CUSTOMERS_V JP
WHERE JRL.CUSTOMER_TRX_LINE_ID = JRTL.LINK_TO_CUST_TRX_LINE_ID
AND JRTL.TAX_ID = JTC.TAX_ID
AND RAX.CUSTOMER_TRX_ID = JRL.CUSTOMER_TRX_ID
AND RAX.BILL_TO_CUSTOMER_ID = JP.PARTY_ID
AND JP.PARTY_ID = JPS.PARTY_ID
AND RAX.CREATED_FROM = 'ARXTWMAI'
AND JTC.STFORM_TYPE LIKE '%CESS%' UNION ALL SELECT 'SHIPMENT DETAILS' SOURCE
, JP.PARTY_NAME PARTY_NAME
, JPS.PARTY_SITE_CODE PARTY_SITE
, /* TO_CHAR(OEH.ORDER_NUMBE) DOC_NUMBER
, */ TO_CHAR(OEH.ORDER_NUMBER) SLNO
, NULL REGISTER_ID
, TRUNC(JSPL.CREATION_DATE) TRANSACTION_DATE
, JTC.TAX_ACCOUNT_ID CODE_COMBINATION_ID
, JSPL.INVENTORY_ITEM_ID ITEM_ID
, SUBSTR(JSPL.EXCISE_INVOICE_NO
, 1
, 30) EX_INV_NO
, JTC.TAX_NAME TAX_NAME
, JTC.TAX_TYPE TAX_TYPE
, JTC.STFORM_TYPE CESS_TYPE
, JTC.TAX_RATE RATE
, JSPTL.TAX_AMOUNT CESS_AMT
, JSPTL.TAX_AMOUNT / (JTC.TAX_RATE/100) TAXABLE_BASIS
FROM JAI_OM_WSH_LINES_ALL JSPL
, JAI_OM_WSH_LINE_TAXES JSPTL
, JAI_CMN_TAXES_ALL JTC
, OE_ORDER_HEADERS_ALL OEH
, JAI_CMN_CUSTOMERS_V JP
, JAI_CMN_CUSTOMER_SITES_V JPS
WHERE JSPL.DELIVERY_DETAIL_ID = JSPTL.DELIVERY_DETAIL_ID
AND JSPTL.TAX_ID = JTC.TAX_ID
AND OEH.HEADER_ID = JSPL.ORDER_HEADER_ID
AND OEH.SOLD_TO_ORG_ID = JP.PARTY_ID
AND JP.PARTY_ID = JPS.PARTY_SITE_ID
AND JTC.STFORM_TYPE LIKE '%CESS%' UNION ALL SELECT RCVT.TRANSACTION_TYPE SOURCE
, POV.VENDOR_NAME PARTY_NAME
, POVS.VENDOR_SITE_CODE PARTY_SITE
, /* JRL.RECEIPT_NUM DOC_NUMBER
, */ TO_CHAR(JRL.RECEIPT_NUM) SLNO
, NULL REGISTER_ID
, TRUNC(RCVT.TRANSACTION_DATE) TRANSACTION_DATE
, JTC.TAX_ACCOUNT_ID CODE_COMBINATION_ID
, JRL.INVENTORY_ITEM_ID ITEM_ID
, SUBSTR(JRL.EXCISE_INVOICE_NO
, 1
, 30) EX_INV_NO
, JTC.TAX_NAME TAX_NAME
, JTC.TAX_TYPE TAX_TYPE
, JTC.STFORM_TYPE CESS_TYPE
, JTC.TAX_RATE RATE
, (RCVT.QUANTITY / JRL.QTY_RECEIVED)* JRTL.TAX_AMOUNT CESS_AMOUNT
, ((RCVT.QUANTITY / JRL.QTY_RECEIVED)* JRTL.TAX_AMOUNT) /(JTC.TAX_RATE /100) TAXABLE_BASIS
FROM PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, RCV_TRANSACTIONS RCVT
, JAI_RCV_LINES JRL
, JAI_RCV_LINE_TAXES JRTL
, JAI_CMN_TAXES_ALL JTC
WHERE POV.VENDOR_ID = RCVT.VENDOR_ID
AND POVS.VENDOR_ID = POV.VENDOR_ID
AND RCVT.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND JRL.SHIPMENT_LINE_ID = JRTL.SHIPMENT_LINE_ID
AND JTC.TAX_ID = JRTL.TAX_ID
AND JRL.SHIPMENT_LINE_ID = RCVT.SHIPMENT_LINE_ID
AND JTC.STFORM_TYPE LIKE '%CESS%' UNION ALL SELECT 'MANUAL JVS' SOURCE
, NULL PARTY_NAME
, NULL PARTY_SITE
, /* TO_CHAR(GLH.DOC_SEQUENCE_VALUE) DOC_NUMBER
, */ TO_CHAR(GLH.DOC_SEQUENCE_VALUE) SLNO
, NULL REGISTER_ID
, GLL.CREATION_DATE TRANSACTION_DATE
, GLL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 0 ITEM_ID
, NULL EX_INV_NO
, NULL TAX_NAME
, NULL TAX_TYPE
, NULL CESS_TYPE
, 2 RATE
, NVL(GLL.ENTERED_DR
, GLL.ENTERED_CR) CESS_AMT
, NVL(GLL.ENTERED_DR
, GLL.ENTERED_CR) TAXABLE_BASIS
FROM GL_JE_HEADERS GLH
, GL_JE_LINES GLL
WHERE GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GLH.JE_SOURCE = ('MANUAL') UNION ALL SELECT 'MANUAL PLA' SOURCE
, JP.PARTY_NAME PARTY_NAME
, JPS.PARTY_SITE_CODE PARTY_SITE
, /* PLA.SLNO || ' - ' || PLA.REGISTER_ID DOC_NUMBER
, */ TO_CHAR(PLA.SLNO) SLNO
, PLA.REGISTER_ID REGISTER_ID
, PLA.TRANSACTION_DATE TRANSACTION_DATE
, CHARGE_ACCOUNT_ID CODE_COMBINATION_ID
, INVENTORY_ITEM_ID ITEM_ID
, PLA.DR_INVOICE_NO EX_INV_NO
, 'CENVAT-CESS' TAX_NAME
, 'CENVAT-CESS' TAX_TYPE
, 'CENVAT-CESS' CESS_TYPE
, 2 RATE
, NVL(DR_BASIC_ED
, 0) + NVL(DR_ADDITIONAL_ED
, 0) + NVL(DR_OTHER_ED
, 0) CESS_AMT
, NVL(DR_BASIC_ED
, 0) + NVL(DR_ADDITIONAL_ED
, 0) + NVL(DR_OTHER_ED
, 0) TAXABLE_BASIS
FROM JAI_CMN_RG_PLA_TRXS PLA
, JAI_CMN_CUSTOMERS_V JP
, JAI_CMN_CUSTOMER_SITES_V JPS
WHERE PLA.TRANSACTION_SOURCE_NUM = 99
AND PLA.VENDOR_ID = JP.PARTY_ID
AND PLA.VENDOR_SITE_ID = JPS.PARTY_SITE_ID
AND JP.PARTY_ID = JPS.PARTY_ID
AND PLA.VENDOR_CUST_FLAG = 'C' )

Columns