VIEW: APPS.OKL_CS_INVCM_TAX_SUM_UV

Source

View Text - Preformatted

SELECT HZ.ACCOUNT_NAME ||'(' || HZ.ACCOUNT_NUMBER ||')' BILL_TO_CUSTOMER, lntyp.meaning LINE_TYPE, OKCL.NAME, sty.meaning STREAM_TYPE, okl_lla_util_pvt.get_Asset_location(TIL.KLE_ID,TAI.KHR_ID) ASSET_LOCATION, ZXL.TRX_LINE_DATE TAX_DATE, ZXL.LINE_AMT TAXABLE_AMOUNT, SUM(ZXL.TAX_AMT) TOTAL_TAX, TBC.CONCAT_CLASSIF_NAME TRX_BUSINESS_CATEGORY, PC.CLASSIFICATION_NAME PRODUCT_CATEGORY, UFC.CLASSIFICATION_NAME USER_DEFINED_FISC_CLASS, IU.CLASSIFICATION_NAME LINE_INTENDED_USE, TIL.KLE_ID, TAI.KHR_ID, ZXL.TRX_ID, ZXL.TRX_LINE_ID, ZXL.ENTITY_CODE, ZXL.EVENT_CLASS_CODE, ZXL.APPLICATION_ID, ZXL.TRX_LEVEL_TYPE, TAI.TRY_ID, TAI.TRX_NUMBER REFERENCE_NUMBER, ZXL.TRX_CURRENCY_CODE, ZXL.legal_entity_id, ZXL.internal_organization_id, TLD.ID TLD_ID, ZX_DET.PRODUCT_CATEGORY, ZX_DET.USER_DEFINED_FISC_CLASS, ZX_DET.LINE_INTENDED_USE, ZX_DET.TRX_BUSINESS_CATEGORY, ZX_DET.DEFAULT_TAXATION_COUNTRY, ZXL.internal_organization_id FROM ZX_LINES ZXL, ZX_LINES_DET_FACTORS ZX_DET, OKC_K_LINES_V OKCL, OKC_LINE_STYLES_B OKLS, OKL_STRM_TYPE_B STRMT, HZ_CUST_ACCOUNTS HZ, OKL_TRX_AR_INVOICES_B TAI, OKL_TXL_AR_INV_LNS_B TIL, OKL_TXD_AR_LN_DTLS_B TLD, FND_LOOKUPS lntyp, FND_LOOKUPS sty, ZX_FC_CODES_DENORM_B TBC, ZX_FC_PRODUCT_CATEGORIES_V PC, ZX_FC_USER_DEFINED_V UFC, ZX_FC_INTENDED_USE_V IU WHERE TLD.TIL_ID_DETAILS = TIL.ID AND TIL.TAI_ID = TAI.ID AND TIL.KLE_ID = OKCL.ID (+) AND OKCL.LSE_ID = OKLS.ID (+) AND TIL.STY_ID = STRMT.ID (+) AND HZ.CUST_ACCOUNT_ID = TAI.IXX_ID AND lntyp.lookup_type(+) = 'OKC_LINE_TYPE' AND lntyp.lookup_code(+) = OKLS.LTY_CODE AND sty.lookup_type(+) = 'OKL_STREAM_TYPE_PURPOSE' AND sty.lookup_code(+) = STRMT.STREAM_TYPE_PURPOSE AND ZXL.APPLICATION_ID = 222 AND ZXL.TRX_LEVEL_TYPE = 'LINE' AND ZXL.ENTITY_CODE = 'TRANSACTIONS' AND ZXL.TRX_ID = ZX_DET. TRX_ID AND ZXL.TRX_LINE_ID = ZX_DET.TRX_LINE_ID AND ZXL.APPLICATION_ID = ZX_DET.APPLICATION_ID AND ZXL.TRX_LEVEL_TYPE = ZX_DET.TRX_LEVEL_TYPE AND ZXL.ENTITY_CODE = ZX_DET.ENTITY_CODE AND ZXL.EVENT_CLASS_CODE = ZX_DET.EVENT_CLASS_CODE AND ZX_DET.TRX_BUSINESS_CATEGORY = TBC.CONCAT_CLASSIF_CODE(+) AND TBC.CLASSIFICATION_TYPE_CODE(+) = 'TRX_BUSINESS_CATEGORY' AND TBC.LANGUAGE (+) = USERENV('LANG') AND ZX_DET.PRODUCT_CATEGORY = PC.CLASSIFICATION_CODE(+) AND ZX_DET.USER_DEFINED_FISC_CLASS = UFC.CLASSIFICATION_CODE(+) AND ZX_DET.LINE_INTENDED_USE = IU.CLASSIFICATION_CODE(+) And nvl(ZXL.cancel_flag,'N') <> 'Y' GROUP BY HZ.ACCOUNT_NAME ||'(' || HZ.ACCOUNT_NUMBER ||')' , lntyp.meaning , OKCL.NAME, sty.meaning , okl_lla_util_pvt.get_Asset_location(TIL.KLE_ID,TAI.KHR_ID) , ZXL.TRX_LINE_DATE , ZXL.LINE_AMT , TBC.CONCAT_CLASSIF_NAME , PC.CLASSIFICATION_NAME , UFC.CLASSIFICATION_NAME , IU.CLASSIFICATION_NAME , TIL.KLE_ID, TAI.KHR_ID, ZXL.TRX_ID, ZXL.TRX_LINE_ID, ZXL.ENTITY_CODE, ZXL.EVENT_CLASS_CODE, ZXL.APPLICATION_ID, ZXL.TRX_LEVEL_TYPE, TAI.TRY_ID, TAI.TRX_NUMBER, ZXL.TRX_CURRENCY_CODE, ZXL.legal_entity_id, ZXL.internal_organization_id, TLD.ID, ZX_DET.PRODUCT_CATEGORY, ZX_DET.USER_DEFINED_FISC_CLASS, ZX_DET.LINE_INTENDED_USE, ZX_DET.TRX_BUSINESS_CATEGORY, ZX_DET.DEFAULT_TAXATION_COUNTRY, ZXL.internal_organization_id

View Text - HTML Formatted

SELECT HZ.ACCOUNT_NAME ||'(' || HZ.ACCOUNT_NUMBER ||')' BILL_TO_CUSTOMER
, LNTYP.MEANING LINE_TYPE
, OKCL.NAME
, STY.MEANING STREAM_TYPE
, OKL_LLA_UTIL_PVT.GET_ASSET_LOCATION(TIL.KLE_ID
, TAI.KHR_ID) ASSET_LOCATION
, ZXL.TRX_LINE_DATE TAX_DATE
, ZXL.LINE_AMT TAXABLE_AMOUNT
, SUM(ZXL.TAX_AMT) TOTAL_TAX
, TBC.CONCAT_CLASSIF_NAME TRX_BUSINESS_CATEGORY
, PC.CLASSIFICATION_NAME PRODUCT_CATEGORY
, UFC.CLASSIFICATION_NAME USER_DEFINED_FISC_CLASS
, IU.CLASSIFICATION_NAME LINE_INTENDED_USE
, TIL.KLE_ID
, TAI.KHR_ID
, ZXL.TRX_ID
, ZXL.TRX_LINE_ID
, ZXL.ENTITY_CODE
, ZXL.EVENT_CLASS_CODE
, ZXL.APPLICATION_ID
, ZXL.TRX_LEVEL_TYPE
, TAI.TRY_ID
, TAI.TRX_NUMBER REFERENCE_NUMBER
, ZXL.TRX_CURRENCY_CODE
, ZXL.LEGAL_ENTITY_ID
, ZXL.INTERNAL_ORGANIZATION_ID
, TLD.ID TLD_ID
, ZX_DET.PRODUCT_CATEGORY
, ZX_DET.USER_DEFINED_FISC_CLASS
, ZX_DET.LINE_INTENDED_USE
, ZX_DET.TRX_BUSINESS_CATEGORY
, ZX_DET.DEFAULT_TAXATION_COUNTRY
, ZXL.INTERNAL_ORGANIZATION_ID
FROM ZX_LINES ZXL
, ZX_LINES_DET_FACTORS ZX_DET
, OKC_K_LINES_V OKCL
, OKC_LINE_STYLES_B OKLS
, OKL_STRM_TYPE_B STRMT
, HZ_CUST_ACCOUNTS HZ
, OKL_TRX_AR_INVOICES_B TAI
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TXD_AR_LN_DTLS_B TLD
, FND_LOOKUPS LNTYP
, FND_LOOKUPS STY
, ZX_FC_CODES_DENORM_B TBC
, ZX_FC_PRODUCT_CATEGORIES_V PC
, ZX_FC_USER_DEFINED_V UFC
, ZX_FC_INTENDED_USE_V IU
WHERE TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND TIL.KLE_ID = OKCL.ID (+)
AND OKCL.LSE_ID = OKLS.ID (+)
AND TIL.STY_ID = STRMT.ID (+)
AND HZ.CUST_ACCOUNT_ID = TAI.IXX_ID
AND LNTYP.LOOKUP_TYPE(+) = 'OKC_LINE_TYPE'
AND LNTYP.LOOKUP_CODE(+) = OKLS.LTY_CODE
AND STY.LOOKUP_TYPE(+) = 'OKL_STREAM_TYPE_PURPOSE'
AND STY.LOOKUP_CODE(+) = STRMT.STREAM_TYPE_PURPOSE
AND ZXL.APPLICATION_ID = 222
AND ZXL.TRX_LEVEL_TYPE = 'LINE'
AND ZXL.ENTITY_CODE = 'TRANSACTIONS'
AND ZXL.TRX_ID = ZX_DET. TRX_ID
AND ZXL.TRX_LINE_ID = ZX_DET.TRX_LINE_ID
AND ZXL.APPLICATION_ID = ZX_DET.APPLICATION_ID
AND ZXL.TRX_LEVEL_TYPE = ZX_DET.TRX_LEVEL_TYPE
AND ZXL.ENTITY_CODE = ZX_DET.ENTITY_CODE
AND ZXL.EVENT_CLASS_CODE = ZX_DET.EVENT_CLASS_CODE
AND ZX_DET.TRX_BUSINESS_CATEGORY = TBC.CONCAT_CLASSIF_CODE(+)
AND TBC.CLASSIFICATION_TYPE_CODE(+) = 'TRX_BUSINESS_CATEGORY'
AND TBC.LANGUAGE (+) = USERENV('LANG')
AND ZX_DET.PRODUCT_CATEGORY = PC.CLASSIFICATION_CODE(+)
AND ZX_DET.USER_DEFINED_FISC_CLASS = UFC.CLASSIFICATION_CODE(+)
AND ZX_DET.LINE_INTENDED_USE = IU.CLASSIFICATION_CODE(+)
AND NVL(ZXL.CANCEL_FLAG
, 'N') <> 'Y' GROUP BY HZ.ACCOUNT_NAME ||'(' || HZ.ACCOUNT_NUMBER ||')'
, LNTYP.MEANING
, OKCL.NAME
, STY.MEANING
, OKL_LLA_UTIL_PVT.GET_ASSET_LOCATION(TIL.KLE_ID
, TAI.KHR_ID)
, ZXL.TRX_LINE_DATE
, ZXL.LINE_AMT
, TBC.CONCAT_CLASSIF_NAME
, PC.CLASSIFICATION_NAME
, UFC.CLASSIFICATION_NAME
, IU.CLASSIFICATION_NAME
, TIL.KLE_ID
, TAI.KHR_ID
, ZXL.TRX_ID
, ZXL.TRX_LINE_ID
, ZXL.ENTITY_CODE
, ZXL.EVENT_CLASS_CODE
, ZXL.APPLICATION_ID
, ZXL.TRX_LEVEL_TYPE
, TAI.TRY_ID
, TAI.TRX_NUMBER
, ZXL.TRX_CURRENCY_CODE
, ZXL.LEGAL_ENTITY_ID
, ZXL.INTERNAL_ORGANIZATION_ID
, TLD.ID
, ZX_DET.PRODUCT_CATEGORY
, ZX_DET.USER_DEFINED_FISC_CLASS
, ZX_DET.LINE_INTENDED_USE
, ZX_DET.TRX_BUSINESS_CATEGORY
, ZX_DET.DEFAULT_TAXATION_COUNTRY
, ZXL.INTERNAL_ORGANIZATION_ID