ZX Tax Register

Description
Categories: RDF
Imported from BI Publisher
Description: Tax Register
Application: E-Business Tax
Source: Tax Register (XML)
Short Name: ZXXSTVRR_XML
DB package: ZXXSTVRR

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

SELECT  
DET.LEDGER_ID                               C_LEDGER_ID,
DET.LEDGER_NAME                             C_LEDGER_NAME,
DET.FUNCTIONAL_CURRENCY_CODE                C_FUNC_CURRENCY_CODE,
CON.REP_CONTEXT_ENTITY_NAME                 C_ORGANIZATION_UNIT_NAME, CON.TAXPAYER_ID				    C_Taxpayer_ID,
CON.REP_CONTEXT_ENTITY_ADDRESS1		    C_REP_CONTEXT_ADDRESS1,
CON.REP_CONTEXT_ENTITY_ADDRESS2		    C_REP_CONTEXT_ADDRESS2,
CON.REP_CONTEXT_ENTITY_ADDRESS3		    C_REP_CONTEXT_ADDRESS3,
CON.REP_CONTEXT_ENTITY_CITY		    C_REP_CONTEXT_CITY,
DET.TERRITORY_SHORT_NAME		    C_TERRITORY_SHORT_NAME,
CON.REP_CONTEXT_ENTITY_POSTAL_CODE          C_REP_CONTEXT_POSTAL_CODE,					  
CON.REP_CONTEXT_ENTITY_PROVINCE             C_REP_CONTEXT_PROVINCE,
CON.LEGAL_CONTACT_PARTY_NAME                C_TAXABLE_PERSON, decode(CON.ACTIVITY_CODE,NULL,con.activity_code, 
             (SELECT description
               FROM ar_lookups 
             WHERE lookup_type = 'LEGAL_ACTIVITY_CODE_CL'
                 AND enabled_flag  = 'Y'
                 AND lookup_code = con.activity_code))  C_SERVICE_TYPE,
CON.LEGAL_CONTACT_PARTY_NAME                C_LEGAL_REPRESENTATIVE,
CON.LEGAL_CONTACT_PARTY_NUM                 C_REPRESENTATIVE_TAX_ID,
NVL(BILLING_TP_NUMBER, SHIPPING_TP_NUMBER)            C_CUSTOMER_TAXPAYER_ID,
NVL(BILLING_TP_NAME, SHIPPING_TP_NAME)                  C_CUST_NAME,
NVL(BILLING_TP_TAXPAYER_ID, SHIPPING_TP_TAXPAYER_ID)	            C_BILLING_TP_TAXPAYER_ID,
NVL(BILLING_TP_TAX_REG_NUM, SHIPPING_TP_TAX_REG_NUM)         C_BILLING_TP_TAX_REG_NUM,
EXT.GDF_RA_CUST_BILL_ATT12                  C_GDF_RA_CUST_BILL_ATT12,
EXT.GDF_RA_CUST_BILL_ATT10                  C_CUST_TAX_ID_TYPE,
EXT.ATTRIBUTE8                              C_CUST_VAT_REC_CODE,
EXT.ATTRIBUTE7                              C_DGI_CUST_CONDITION_CODE,
DET.TRX_ID				    C_TRANSACTION_ID,
DET.EXTRACT_SOURCE_LEDGER		    C_EXTRACT_SOURCE_LEDGER,
DET.TRX_NUMBER				    C_TRX_NUMBER,
DET.TRX_CURRENCY_CODE			    C_TRX_CURRENCY_CODE,
nvl(DET.CURRENCY_CONVERSION_RATE,1)	    C_EXCHANGE_RATE,
DECODE(SUBSTR(det.document_sub_type,10,2),NULL,
         (SELECT global_attribute1
             FROM ra_cust_trx_types_all rctt
           WHERE det.trx_type_id = rctt.cust_trx_type_id
             and det.internal_organization_id=rctt.org_id
         ),SUBSTR(det.document_sub_type,10,2)  )       C_DOCUMENT_SUB_TYPE,
DET.APPLIED_TO_TRX_NUMBER		    C_APPLIED_TO_TRX_NUMBER,
DET.TRX_BATCH_SOURCE_NAME		    C_TRX_BATCH_SOURCE_NAME,
decode(:P_TRX_SOURCE_NAME,NULL,NULL,DET.TRX_BATCH_SOURCE_NAME) C_PAR_TRX_BATCH_SRC_NAME,
DET.DOC_EVENT_STATUS			    C_DOC_EVENT_STATUS,
EXT.GDF_RA_CUST_TRX_ATT9                    C_ORIGINAL_TRX_NUMBER,
DET.TRX_CLASS_MNG                            C_TRX_TYPE_MEANING,
to_char(DET.TRX_DATE,'DD-MON-YYYY')				    C_TRX_DATE,
to_char(DET.TRX_DUE_DATE,'DD-MON_YYYY')			    C_TRX_SHIPPING_DATE,
DET.EVENT_CLASS_CODE			    C_TRX_CLASS_CODE,
DET.trx_line_id                             C_TRANSACTION_LINE,
DET.DOC_SEQ_NAME                            C_DOC_SEQ_NAME,
DET.DOC_SEQ_VALUE                           C_DOC_SEQ_VALUE,
EXT.GDF_RA_CUST_TRX_ATT19		    C_GDF_RA_CUST_TRX_ATT19,
EXT.GDF_RA_CUST_TRX_TYPES_ATT5		    C_GDF_RA_CUST_TRX_TYPES_ATT5,
0                                           C_VOID_AMOUNT,
0                                           C_VOID_RATE,
EXT.ATTRIBUTE4                              C_EXPORT_METHOD, EXT.ATTRIBUTE5                              C_EXPORT_NAME, EXT.ATTRIBUTE6                              C_EXPORT_TYPE, EXT.GDF_RA_CUST_TRX_ATT8                    C_EXPORT_DATE, EXT.GDF_RA_CUST_TRX_ATT4                    C_EXPORT_NUMBER, DET.TRX_BUSINESS_CATEGORY		    C_TRX_BUSINESS_CATEGORY,
decode(substrb(DET.TRX_BUSINESS_CATEGORY,1,14), 
     'EXPENSE_REPORT',  substrb(DET.TRX_BUSINESS_CATEGORY, 32),
     'PURCHASE_PREPA',  substrb(DET.TRX_BUSINESS_CATEGORY, 48),
     'PURCHASE_TRANS',  substrb(DET.TRX_BUSINESS_CATEGORY, 38),
     'SALES_TRANSACT',  substrb(DET.TRX_BUSINESS_CATEGORY, 19)
      )                                     C_DEDUCTIBLE_CODE,  EXT.TRX_BUSINESS_CATEGORY_MNG               C_DEDUCTIBLE_CODE_MEAN,   DET.PRODUCT_FISC_CLASSIFICATION             C_PRODUCT_FC,    DET.GDF_FND_CURRENCIES_ATT1	            C_GDF_FND_CURRENCIES_ATT1, EXT.ATTRIBUTE10			            C_DGI_TAX_TYPE_CODE,
EXT.GDF_RA_CUST_BILL_ATT10	            C_GDF_RA_CUST_BILL_ATT10, EXT.ATTRIBUTE4			            C_DGI_TRX_TYPE_CODE, EXT.ATTRIBUTE11                             C_DGI_CODE,
DET.TAX_REGIME_CODE			    C_DGI_TAX_REGIME_CODE,
EXT.GDF_PARTY_SITES_BILL_ATT8		    C_DGI_RESP_TYPE_CODE, 
DET.TRX_BATCH_SOURCE_ID                     C_BATCH_SOURCE_ID,
EXT.GDF_RA_BATCH_SOURCES_ATT7               C_FISCAL_PRINTER,
DET.SUPPLIER_TAX_INVOICE_NUMBER             C_CAI,
to_char(DET.SUPPLIER_TAX_INVOICE_DATE,'YYYY-MM-DD')               C_CAI_DUE_DATE,
DET.TRX_LINE_QUANTITY                       C_TAXABLE_QUANTITY,
DET.UOM_CODE                                C_TAXAB_QUANTITY_UOM_CODE,
DET.TRX_QUANTITY_UOM_MNG                    C_TAXAB_QUAN_UOM_MEANING,
EXT.GDF_RA_BATCH_SOURCES_ATT1               C_BATCH_SOURCES_ATT1,
EXT.GDF_RA_BATCH_SOURCES_ATT2               C_INITIAL_TRX_NUM,
EXT.GDF_RA_BATCH_SOURCES_ATT3               C_INVOICE_WORD,
EXT.GDF_RA_BATCH_SOURCES_ATT4               C_FINAL_TRX_NUM,
DET.TAX_TYPE_CODE			    C_TAX_TYPE,
DET.TAX_RATE_ID                             C_TAX_CODE_ID,
DET.TAX_RATE_CODE                           C_TAX_CODE,
DET.TAX_RATE_CODE_DESCRIPTION               C_TAX_CODE_DESC,
DET.TAX_RATE                                C_TAX_CODE_RATE,
EXT.GDF_PARTY_SITES_BILL_ATT9               C_USE_SITE_PROF,
EXT.ATTRIBUTE8                              C_VAT_REG_STATUS_CODE,
DET.TAX_STATUS_CODE			    C_TAX_STATUS_CODE,
EXT.TAX_STATUS_MNG                          C_GOV_TAX_TYPE_MEAN,   NULL                                        C_TAX_CATEGORY_ID,
EXT.ATTRIBUTE5                              C_GL_ACCT_TO_DEB,
EXT.ATTRIBUTE1				    C_JL_TURNOVER_JURIS_CODE,
EXT.ATTRIBUTE3				    C_JL_MUNICIPAL_JURIS_CODE,
NVL(DET.BILLING_TP_TAXPAYER_ID, DET.SHIPPING_TP_TAXPAYER_ID) ||'-'||EXT.ATTRIBUTE12 C_JL_CUSTOMER_TAXPAYER_ID,
EXT.ATTRIBUTE1				    C_TW_WINE_CIGARETTE,
   NVL(ACT.GL_TRANSFER_FLAG,'N')                  C_POSTED_FLAG,
DET.TAXABLE_AMT                             C_TAXABLE_AMT,
DET.TAX_AMT		                    C_TAX_AMT,
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt)                  C_TAXABLE_ACCOUNTED_AMOUNT, DET.TAXABLE_AMT                             C_TAXABLE_ENTERED_AMOUNT, nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt)                     C_TAX_ACCOUNTED_AMOUNT, DET.TAX_AMT		                    C_TAX_ENTERED_AMOUNT, EXT.NUMERIC12		                    C_EXTENDED_AMOUNT,
EXT.NUMERIC14                               C_EXEMPT_ACCTD_AMOUNT,
EXT.NUMERIC1		C_JL_NOT_REGIST_TAX_AMOUNT,
EXT.NUMERIC2		C_JL_VAT_EXEMPT_AMOUNT,
EXT.NUMERIC3		C_JL_VAT_PERCEP_AMOUNT,
EXT.NUMERIC4		C_JL_PROV_PRECEP_AMOUNT,
EXT.NUMERIC5		C_JL_MUNIC_PERCEP_AMOUNT,
EXT.NUMERIC6		C_JL_EXCISE_AMOUNT,
EXT.NUMERIC7		C_JL_OTHER_TAX_AMOUNT,
EXT.NUMERIC8		C_JL_NON_TAXABLE_AMOUNT,
EXT.NUMERIC9		C_JL_VAT_AMOUNT,
EXT.NUMERIC10		C_JL_TAXABLE_AMOUNT,
EXT.NUMERIC11		C_JL_VAT_ADDITIONAL_AMOUNT,
EXT.NUMERIC11		C_AR_REC_COUNT,
EXT.NUMERIC13		C_AR_VAT_OR_NONVAT_COUNT,
EXT.NUMERIC1		C_TW_CNT_TAXABLE_AMOUNT,
EXT.NUMERIC12		C_JL_TOTAL_DOCUMENT_AMOUNT,EXT.NUMERIC15		C_JL_TOTAL_AMT,nvl(EXT.NUMERIC2,0) +  nvl(EXT.NUMERIC10,0) + nvl(EXT.NUMERIC9,0) + nvl(EXT.NUMERIC7,0) 
C_JL_TOTAL_AMOUNT_CLRSL,
EXT.NUMERIC15                C_CLRSLL_ORDER,
DET.TAX_LINE_USER_CATEGORY C_TAX_CATEGORY,
EXT.ATTRIBUTE1 C_TAX_CATEGORY_DESC ,
ACT. ACCOUNT_FLEXFIELD C_TAX_ACCOUNT ,
DET.BILLING_TP_SITE_NAME C_BILLING_TP_SITE_NAME,
DET.TRX_LINE_NUMBER C_TRX_LINE_NUMBER ,
DET.TAX_LINE_NUMBER C_TAX_LINE_NUMBER ,
DET.TAX_AMT + DET.TAXABLE_AMT C_TOT_AMOUNT ,
DET.TRX_LINE_AMT - DET.TAXABLE_AMT  C_EXEMPT_ENTERED_AMOUNT ,
DET.TRX_LINE_AMT C_LINE_AMOUNT ,
 DECODE(DET.application_doc_status,'VD','Yes','No') C_VOID,
EXT.ATTRIBUTE15    C_TRX_TW_DATE,
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt) + nvl(nvl(DET.TAX_AMT_FUNCL_CURR,det.tax_amt),0) C_TOT_ACCOUNTED_AMOUNT,
NULL C_TAX_CLASS,
EXT.GDF_RA_CUST_TRX_ATT5 C_TW_EXPORT_NAME,
EXT.GDF_RA_CUST_TRX_ATT6 C_TW_EXPORT_METHOD,
EXT.GDF_RA_CUST_TRX_ATT7 C_TW_EXPORT_TYPE,
DET.RELATED_DOC_NUMBER C_RELATED_DOC_NUMBER,
DET.EXEMPT_REASON_CODE C_EXEMPT_REASON,
DET.EXEMPT_CERTIFICATE_NUMBER C_EXEMPT_CERTIFICATE_NUMBER,
DET.TRX_LINE_DESCRIPTION C_TRX_LINE_DESC,
DET.TRX_LINE_CLASS C_TRX_TYPE,
DET.BILLING_TP_COUNTY C_BILLING_TP_COUNTY,
DET.BILLING_TP_CITY C_BILLING_TP_CITY,
DET.BILLING_TP_STATE C_BILLING_TP_STATE,
EXT.NUMERIC2 C_AMOUNT_RECEIVED,
EXT.NUMERIC1 C_TAX_AMOUNT_RECEIVED,
DET.ADJUSTED_DOC_NUMBER C_ADJUSTED_DOC_NUMBER,
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt) + nvl(nvl(DET.TAX_AMT_FUNCL_CURR,det.tax_amt),0) C_TOT_FUNC_AMT,
NVL(ACT.TRX_TAX_BALANCING_SEGMENT,ACT.TRX_TAXABLE_BALANCING_SEGMENT)  C_COMPANY,
ACT.TRX_TAXABLE_BALSEG_DESC C_COMPANY_DESC,
ACT.ACTG_LINE_CCID C_CCID,
ACT.ACCOUNT_FLEXFIELD C_FLEXFIELD,
ACT.ACCOUNT_DESCRIPTION C_FLEXDATA_DESC,
to_char(DET.GL_DATE,'DD-MON-YYYY') C_GL_DATE,
DET.SHIPPING_TP_ADDRESS1||DET.SHIPPING_TP_ADDRESS2||DET.SHIPPING_TP_ADDRESS3||SHIPPING_TP_PROVINCE||SHIPPING_TP_COUNTRY C_SHIP_ADDRESS,
Decode(:P_SHIP_TO,'CITY', NVL(DET.SHIPPING_TP_CITY,DET.BILLING_TP_CITY),'COUNTRY', NVL(DET.SHIPPING_TP_COUNTRY,DET.BILLING_TP_COUNTRY), 'COUNTY',NVL(DET.SHIPPING_TP_COUNTY,DET.BILLING_TP_COUNTY), 'PROVINCE', NVL(DET.SHIPPING_TP_PROVINCE,DET.BILLING_TP_PROVINCE), 'STATE',NVL(DET.SHIPPING_TP_STATE,DET.BILLING_TP_STATE), 'NONE', NULL) C_SHIP_ADDR,
EXT.NUMERIC1 C_GL_ACTIVITY_DISP,
NULL C_SUM_COMP_GL_ACT_DISP,
EXT.ATTRIBUTE1 C_BAL_SEGMENT_PROMPT,
DET.HQ_ESTB_REG_NUMBER C_FIRST_PARTY_TAX_REG_NUM,
EXT.DOCUMENT_SUB_TYPE_MNG C_DOCUMENT_SUB_TYPE_MNG,
CON.ORG_INFORMATION2 	  C_ORG_INFORMATION2,
DECODE(SUBSTRB(DET.DOCUMENT_SUB_TYPE, 10,2), 
                               '31', DET.TRX_NUMBER, 
                               '32', DET.TRX_NUMBER,                                                                                                                                                 
                               '33', DET.APPLIED_TO_TRX_NUMBER,
                               '34', DET.APPLIED_TO_TRX_NUMBER,
                               '35', DET.TRX_NUMBER,
                               '36', DET.TRX_NUMBER,
                               '37', DET.TRX_NUMBER, DET.TRX_NUMBER,
                               NULL, '**********')   C_TW_GUI_NUMBER,
TO_CHAR(TO_NUMBER(SUBSTRB(fnd_date.date_to_canonical(det.trx_date),1,4)) - 1911) || '/' || SUBSTRB(fnd_date.date_to_canonical(det.trx_date),6,5) C_TW_TRX_DATE,
DET.TAX_TYPE_MNG				C_TAX_RATE_CODE_TYPE_MEANING,
DET.TAX_LINE_ID                             C_TAX_LINE_ID,
(
SELECT ROUND(    (DET.TRX_LINE_AMT * nvl(DET.CURRENCY_CONVERSION_RATE,1)) /
           NVL(cur.minimum_accountable_unit,power(10,(-1* precision))))
       * NVL(cur.minimum_accountable_unit, power(10, (-1 * precision)))       
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = det.ledger_id
AND cur.currency_code = sob.currency_code
) -
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt)   C_EXEMPT_ACCT_AMOUNT,
(
SELECT ROUND(    (DET.TRX_LINE_AMT * nvl(DET.CURRENCY_CONVERSION_RATE,1)) /
               NVL(cur.minimum_accountable_unit,power(10,(-1* precision))))
        * NVL(cur.minimum_accountable_unit, power(10, (-1 * precision)))       
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = det.ledger_id
AND cur.currency_code = sob.currency_code
)                                                 C_LINE_FUN_AMT,
DECODE(ACT.GL_TRANSFER_FLAG, 'Y', nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt) , 0) POSTED_AMT,
DECODE(ACT.GL_TRANSFER_FLAG, 'N', nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt) , NULL,nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt),0) UNPOSTED_AMT,
tax_exemption_id c_exemption_id,
NVL(DET.HISTORICAL_FLAG,'N') C_HISTORICAL_FLAG,
DET.TAX_ONLY_FLAG         C_TAX_ONLY_FLAG, 
	ZXXSTVRR.cf_not_used_guiformula(C_TRANSACTION_ID, C_TRX_NUMBER, C_TRX_BATCH_SOURCE_NAME) CF_NOT_USED_GUI, 
	ZXXSTVRR.cf_tax_category_descformula(C_TAX_CATEGORY_DESC) CF_TAX_CATEGORY_DESC, 
	ZXXSTVRR.cf_tw_export_dateformula(C_EXPORT_DATE) CF_TW_EXPORT_DATE, 
	ZXXSTVRR.cf_taxable_acc_amtformula(C_HISTORICAL_FLAG, C_TRANSACTION_ID, C_TRANSACTION_LINE, C_CCID, C_TAXABLE_ACCOUNTED_AMOUNT, C_LINE_AMOUNT, C_TAX_ENTERED_AMOUNT, C_TAX_ACCOUNTED_AMOUNT, C_TAXABLE_ENTERED_AMOUNT, C_TRX_CLASS_CODE, C_TRX_TYPE, C_TAX_CODE_ID, C_LINE_FUN_AMT, C_EXEMPTION_ID, C_EXEMPT_CERTIFICATE_NUMBER, C_EXEMPT_REASON, C_TAX_LINE_ID, C_TAX_ONLY_FLAG) CF_TAXABLE_ACC_AMT, 
	ZXXSTVRR.cf_taxable_amtformula(C_TRANSACTION_ID, C_TRANSACTION_LINE, C_TAX_CODE_ID, C_EXEMPTION_ID, C_EXEMPT_CERTIFICATE_NUMBER, C_EXEMPT_REASON, C_TAXABLE_ENTERED_AMOUNT, C_TRX_CURRENCY_CODE) CF_TAXABLE_AMT, 
	ZXXSTVRR.cf_tax_recon_amtformula() CF_Tax_Recon_Amt,
	ZXXSTVRR.CP_currency_code_p CP_currency_code,
	ZXXSTVRR.CP_exempt_amt_p CP_exempt_amt,
	ZXXSTVRR.CP_exempt_acct_amt_p CP_exempt_acct_amt,
	ZXXSTVRR.CP_Tax_Recon_Func_Amt_p CP_Tax_Recon_Func_Amt,
	ZXXSTVRR.CP_LINE_AMOUNT_p CP_LINE_AMOUNT,
	ZXXSTVRR.CP_TAXABLE_ACCOUNTED_AMT_p CP_TAXABLE_ACCOUNTED_AMT,
	ZXXSTVRR.CP_GUI_TRX_ID_UNUSED_p CP_GUI_TRX_ID_UNUSED,
	ZXXSTVRR.CP_LINE_FUN_AMT_p CP_LINE_FUN_AMT,
	ZXXSTVRR.CP_Tax_ccid_p CP_Tax_ccid,
	ZXXSTVRR.CF_TOT_FUNC_AMT_p CF_TOT_FUNC_AMT,
	ZXXSTVRR.CF_TOT_AMOUNT_p CF_TOT_AMOUNT,
	ZXXSTVRR.CP_TAX_LINE_ID_p CP_TAX_LINE_ID,
	ZXXSTVRR.CP_TRX_LINE_ID_p CP_TRX_LINE_ID,
	ZXXSTVRR.CP_TRX_ID_p CP_TRX_ID,
	ZXXSTVRR.CP_TRX_ID_ACC_p CP_TRX_ID_ACC,
	ZXXSTVRR.CP_TXBL_AMT_FLAG_p CP_TXBL_AMT_FLAG,
	ZXXSTVRR.CP_TAX_RATE_CODE_ID_ACC_p CP_TAX_RATE_CODE_ID_ACC,
	ZXXSTVRR.CP_TAX_RATE_CODE_ID_p CP_TAX_RATE_CODE_ID,
	ZXXSTVRR.CP_TRX_LINE_ID_ACC_p CP_TRX_LINE_ID_ACC,
	ZXXSTVRR.CP_Tax_Recon_amt_p CP_Tax_Recon_amt
FROM ar_lookups 
             WHERE lookup_type = 'LEGAL_ACTIVITY_CODE_CL'
                 AND enabled_flag  = 'Y'
                 AND lookup_code = con.activity_code))  C_SERVICE_TYPE,
CON.LEGAL_CONTACT_PARTY_NAME                C_LEGAL_REPRESENTATIVE,
CON.LEGAL_CONTACT_PARTY_NUM                 C_REPRESENTATIVE_TAX_ID,
NVL(BILLING_TP_NUMBER, SHIPPING_TP_NUMBER)            C_CUSTOMER_TAXPAYER_ID,
NVL(BILLING_TP_NAME, SHIPPING_TP_NAME)                  C_CUST_NAME,
NVL(BILLING_TP_TAXPAYER_ID, SHIPPING_TP_TAXPAYER_ID)	            C_BILLING_TP_TAXPAYER_ID,
NVL(BILLING_TP_TAX_REG_NUM, SHIPPING_TP_TAX_REG_NUM)         C_BILLING_TP_TAX_REG_NUM,
EXT.GDF_RA_CUST_BILL_ATT12                  C_GDF_RA_CUST_BILL_ATT12,
EXT.GDF_RA_CUST_BILL_ATT10                  C_CUST_TAX_ID_TYPE,
EXT.ATTRIBUTE8                              C_CUST_VAT_REC_CODE,
EXT.ATTRIBUTE7                              C_DGI_CUST_CONDITION_CODE,
DET.TRX_ID				    C_TRANSACTION_ID,
DET.EXTRACT_SOURCE_LEDGER		    C_EXTRACT_SOURCE_LEDGER,
DET.TRX_NUMBER				    C_TRX_NUMBER,
DET.TRX_CURRENCY_CODE			    C_TRX_CURRENCY_CODE,
nvl(DET.CURRENCY_CONVERSION_RATE,1)	    C_EXCHANGE_RATE,
DECODE(SUBSTR(det.document_sub_type,10,2),NULL,
         (SELECT global_attribute1
             FROM ra_cust_trx_types_all rctt
           WHERE det.trx_type_id = rctt.cust_trx_type_id
             and det.internal_organization_id=rctt.org_id
         ),SUBSTR(det.document_sub_type,10,2)  )       C_DOCUMENT_SUB_TYPE,
DET.APPLIED_TO_TRX_NUMBER		    C_APPLIED_TO_TRX_NUMBER,
DET.TRX_BATCH_SOURCE_NAME		    C_TRX_BATCH_SOURCE_NAME,
decode(:P_TRX_SOURCE_NAME,NULL,NULL,DET.TRX_BATCH_SOURCE_NAME) C_PAR_TRX_BATCH_SRC_NAME,
DET.DOC_EVENT_STATUS			    C_DOC_EVENT_STATUS,
EXT.GDF_RA_CUST_TRX_ATT9                    C_ORIGINAL_TRX_NUMBER,
DET.TRX_CLASS_MNG                            C_TRX_TYPE_MEANING,
to_char(DET.TRX_DATE,'DD-MON-YYYY')				    C_TRX_DATE,
to_char(DET.TRX_DUE_DATE,'DD-MON_YYYY')			    C_TRX_SHIPPING_DATE,
DET.EVENT_CLASS_CODE			    C_TRX_CLASS_CODE,
DET.trx_line_id                             C_TRANSACTION_LINE,
DET.DOC_SEQ_NAME                            C_DOC_SEQ_NAME,
DET.DOC_SEQ_VALUE                           C_DOC_SEQ_VALUE,
EXT.GDF_RA_CUST_TRX_ATT19		    C_GDF_RA_CUST_TRX_ATT19,
EXT.GDF_RA_CUST_TRX_TYPES_ATT5		    C_GDF_RA_CUST_TRX_TYPES_ATT5,
0                                           C_VOID_AMOUNT,
0                                           C_VOID_RATE,
EXT.ATTRIBUTE4                              C_EXPORT_METHOD, EXT.ATTRIBUTE5                              C_EXPORT_NAME, EXT.ATTRIBUTE6                              C_EXPORT_TYPE, EXT.GDF_RA_CUST_TRX_ATT8                    C_EXPORT_DATE, EXT.GDF_RA_CUST_TRX_ATT4                    C_EXPORT_NUMBER, DET.TRX_BUSINESS_CATEGORY		    C_TRX_BUSINESS_CATEGORY,
decode(substrb(DET.TRX_BUSINESS_CATEGORY,1,14), 
     'EXPENSE_REPORT',  substrb(DET.TRX_BUSINESS_CATEGORY, 32),
     'PURCHASE_PREPA',  substrb(DET.TRX_BUSINESS_CATEGORY, 48),
     'PURCHASE_TRANS',  substrb(DET.TRX_BUSINESS_CATEGORY, 38),
     'SALES_TRANSACT',  substrb(DET.TRX_BUSINESS_CATEGORY, 19)
      )                                     C_DEDUCTIBLE_CODE,  EXT.TRX_BUSINESS_CATEGORY_MNG               C_DEDUCTIBLE_CODE_MEAN,   DET.PRODUCT_FISC_CLASSIFICATION             C_PRODUCT_FC,    DET.GDF_FND_CURRENCIES_ATT1	            C_GDF_FND_CURRENCIES_ATT1, EXT.ATTRIBUTE10			            C_DGI_TAX_TYPE_CODE,
EXT.GDF_RA_CUST_BILL_ATT10	            C_GDF_RA_CUST_BILL_ATT10, EXT.ATTRIBUTE4			            C_DGI_TRX_TYPE_CODE, EXT.ATTRIBUTE11                             C_DGI_CODE,
DET.TAX_REGIME_CODE			    C_DGI_TAX_REGIME_CODE,
EXT.GDF_PARTY_SITES_BILL_ATT8		    C_DGI_RESP_TYPE_CODE, 
DET.TRX_BATCH_SOURCE_ID                     C_BATCH_SOURCE_ID,
EXT.GDF_RA_BATCH_SOURCES_ATT7               C_FISCAL_PRINTER,
DET.SUPPLIER_TAX_INVOICE_NUMBER             C_CAI,
to_char(DET.SUPPLIER_TAX_INVOICE_DATE,'YYYY-MM-DD')               C_CAI_DUE_DATE,
DET.TRX_LINE_QUANTITY                       C_TAXABLE_QUANTITY,
DET.UOM_CODE                                C_TAXAB_QUANTITY_UOM_CODE,
DET.TRX_QUANTITY_UOM_MNG                    C_TAXAB_QUAN_UOM_MEANING,
EXT.GDF_RA_BATCH_SOURCES_ATT1               C_BATCH_SOURCES_ATT1,
EXT.GDF_RA_BATCH_SOURCES_ATT2               C_INITIAL_TRX_NUM,
EXT.GDF_RA_BATCH_SOURCES_ATT3               C_INVOICE_WORD,
EXT.GDF_RA_BATCH_SOURCES_ATT4               C_FINAL_TRX_NUM,
DET.TAX_TYPE_CODE			    C_TAX_TYPE,
DET.TAX_RATE_ID                             C_TAX_CODE_ID,
DET.TAX_RATE_CODE                           C_TAX_CODE,
DET.TAX_RATE_CODE_DESCRIPTION               C_TAX_CODE_DESC,
DET.TAX_RATE                                C_TAX_CODE_RATE,
EXT.GDF_PARTY_SITES_BILL_ATT9               C_USE_SITE_PROF,
EXT.ATTRIBUTE8                              C_VAT_REG_STATUS_CODE,
DET.TAX_STATUS_CODE			    C_TAX_STATUS_CODE,
EXT.TAX_STATUS_MNG                          C_GOV_TAX_TYPE_MEAN,   NULL                                        C_TAX_CATEGORY_ID,
EXT.ATTRIBUTE5                              C_GL_ACCT_TO_DEB,
EXT.ATTRIBUTE1				    C_JL_TURNOVER_JURIS_CODE,
EXT.ATTRIBUTE3				    C_JL_MUNICIPAL_JURIS_CODE,
NVL(DET.BILLING_TP_TAXPAYER_ID, DET.SHIPPING_TP_TAXPAYER_ID) ||'-'||EXT.ATTRIBUTE12 C_JL_CUSTOMER_TAXPAYER_ID,
EXT.ATTRIBUTE1				    C_TW_WINE_CIGARETTE,
   NVL(ACT.GL_TRANSFER_FLAG,'N')                  C_POSTED_FLAG,
DET.TAXABLE_AMT                             C_TAXABLE_AMT,
DET.TAX_AMT		                    C_TAX_AMT,
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt)                  C_TAXABLE_ACCOUNTED_AMOUNT, DET.TAXABLE_AMT                             C_TAXABLE_ENTERED_AMOUNT, nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt)                     C_TAX_ACCOUNTED_AMOUNT, DET.TAX_AMT		                    C_TAX_ENTERED_AMOUNT, EXT.NUMERIC12		                    C_EXTENDED_AMOUNT,
EXT.NUMERIC14                               C_EXEMPT_ACCTD_AMOUNT,
EXT.NUMERIC1		C_JL_NOT_REGIST_TAX_AMOUNT,
EXT.NUMERIC2		C_JL_VAT_EXEMPT_AMOUNT,
EXT.NUMERIC3		C_JL_VAT_PERCEP_AMOUNT,
EXT.NUMERIC4		C_JL_PROV_PRECEP_AMOUNT,
EXT.NUMERIC5		C_JL_MUNIC_PERCEP_AMOUNT,
EXT.NUMERIC6		C_JL_EXCISE_AMOUNT,
EXT.NUMERIC7		C_JL_OTHER_TAX_AMOUNT,
EXT.NUMERIC8		C_JL_NON_TAXABLE_AMOUNT,
EXT.NUMERIC9		C_JL_VAT_AMOUNT,
EXT.NUMERIC10		C_JL_TAXABLE_AMOUNT,
EXT.NUMERIC11		C_JL_VAT_ADDITIONAL_AMOUNT,
EXT.NUMERIC11		C_AR_REC_COUNT,
EXT.NUMERIC13		C_AR_VAT_OR_NONVAT_COUNT,
EXT.NUMERIC1		C_TW_CNT_TAXABLE_AMOUNT,
EXT.NUMERIC12		C_JL_TOTAL_DOCUMENT_AMOUNT,EXT.NUMERIC15		C_JL_TOTAL_AMT,nvl(EXT.NUMERIC2,0) +  nvl(EXT.NUMERIC10,0) + nvl(EXT.NUMERIC9,0) + nvl(EXT.NUMERIC7,0) 
C_JL_TOTAL_AMOUNT_CLRSL,
EXT.NUMERIC15                C_CLRSLL_ORDER,
DET.TAX_LINE_USER_CATEGORY C_TAX_CATEGORY,
EXT.ATTRIBUTE1 C_TAX_CATEGORY_DESC ,
ACT. ACCOUNT_FLEXFIELD C_TAX_ACCOUNT ,
DET.BILLING_TP_SITE_NAME C_BILLING_TP_SITE_NAME,
DET.TRX_LINE_NUMBER C_TRX_LINE_NUMBER ,
DET.TAX_LINE_NUMBER C_TAX_LINE_NUMBER ,
DET.TAX_AMT + DET.TAXABLE_AMT C_TOT_AMOUNT ,
DET.TRX_LINE_AMT - DET.TAXABLE_AMT  C_EXEMPT_ENTERED_AMOUNT ,
DET.TRX_LINE_AMT C_LINE_AMOUNT ,
 DECODE(DET.application_doc_status,'VD','Yes','No') C_VOID,
EXT.ATTRIBUTE15    C_TRX_TW_DATE,
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt) + nvl(nvl(DET.TAX_AMT_FUNCL_CURR,det.tax_amt),0) C_TOT_ACCOUNTED_AMOUNT,
NULL C_TAX_CLASS,
EXT.GDF_RA_CUST_TRX_ATT5 C_TW_EXPORT_NAME,
EXT.GDF_RA_CUST_TRX_ATT6 C_TW_EXPORT_METHOD,
EXT.GDF_RA_CUST_TRX_ATT7 C_TW_EXPORT_TYPE,
DET.RELATED_DOC_NUMBER C_RELATED_DOC_NUMBER,
DET.EXEMPT_REASON_CODE C_EXEMPT_REASON,
DET.EXEMPT_CERTIFICATE_NUMBER C_EXEMPT_CERTIFICATE_NUMBER,
DET.TRX_LINE_DESCRIPTION C_TRX_LINE_DESC,
DET.TRX_LINE_CLASS C_TRX_TYPE,
DET.BILLING_TP_COUNTY C_BILLING_TP_COUNTY,
DET.BILLING_TP_CITY C_BILLING_TP_CITY,
DET.BILLING_TP_STATE C_BILLING_TP_STATE,
EXT.NUMERIC2 C_AMOUNT_RECEIVED,
EXT.NUMERIC1 C_TAX_AMOUNT_RECEIVED,
DET.ADJUSTED_DOC_NUMBER C_ADJUSTED_DOC_NUMBER,
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt) + nvl(nvl(DET.TAX_AMT_FUNCL_CURR,det.tax_amt),0) C_TOT_FUNC_AMT,
NVL(ACT.TRX_TAX_BALANCING_SEGMENT,ACT.TRX_TAXABLE_BALANCING_SEGMENT)  C_COMPANY,
ACT.TRX_TAXABLE_BALSEG_DESC C_COMPANY_DESC,
ACT.ACTG_LINE_CCID C_CCID,
ACT.ACCOUNT_FLEXFIELD C_FLEXFIELD,
ACT.ACCOUNT_DESCRIPTION C_FLEXDATA_DESC,
to_char(DET.GL_DATE,'DD-MON-YYYY') C_GL_DATE,
DET.SHIPPING_TP_ADDRESS1||DET.SHIPPING_TP_ADDRESS2||DET.SHIPPING_TP_ADDRESS3||SHIPPING_TP_PROVINCE||SHIPPING_TP_COUNTRY C_SHIP_ADDRESS,
Decode(:P_SHIP_TO,'CITY', NVL(DET.SHIPPING_TP_CITY,DET.BILLING_TP_CITY),'COUNTRY', NVL(DET.SHIPPING_TP_COUNTRY,DET.BILLING_TP_COUNTRY), 'COUNTY',NVL(DET.SHIPPING_TP_COUNTY,DET.BILLING_TP_COUNTY), 'PROVINCE', NVL(DET.SHIPPING_TP_PROVINCE,DET.BILLING_TP_PROVINCE), 'STATE',NVL(DET.SHIPPING_TP_STATE,DET.BILLING_TP_STATE), 'NONE', NULL) C_SHIP_ADDR,
EXT.NUMERIC1 C_GL_ACTIVITY_DISP,
NULL C_SUM_COMP_GL_ACT_DISP,
EXT.ATTRIBUTE1 C_BAL_SEGMENT_PROMPT,
DET.HQ_ESTB_REG_NUMBER C_FIRST_PARTY_TAX_REG_NUM,
EXT.DOCUMENT_SUB_TYPE_MNG C_DOCUMENT_SUB_TYPE_MNG,
CON.ORG_INFORMATION2 	  C_ORG_INFORMATION2,
DECODE(SUBSTRB(DET.DOCUMENT_SUB_TYPE, 10,2), 
                               '31', DET.TRX_NUMBER, 
                               '32', DET.TRX_NUMBER,                                                                                                                                                 
                               '33', DET.APPLIED_TO_TRX_NUMBER,
                               '34', DET.APPLIED_TO_TRX_NUMBER,
                               '35', DET.TRX_NUMBER,
                               '36', DET.TRX_NUMBER,
                               '37', DET.TRX_NUMBER, DET.TRX_NUMBER,
                               NULL, '**********')   C_TW_GUI_NUMBER,
TO_CHAR(TO_NUMBER(SUBSTRB(fnd_date.date_to_canonical(det.trx_date),1,4)) - 1911) || '/' || SUBSTRB(fnd_date.date_to_canonical(det.trx_date),6,5) C_TW_TRX_DATE,
DET.TAX_TYPE_MNG				C_TAX_RATE_CODE_TYPE_MEANING,
DET.TAX_LINE_ID                             C_TAX_LINE_ID,
(
SELECT ROUND(    (DET.TRX_LINE_AMT * nvl(DET.CURRENCY_CONVERSION_RATE,1)) /
           NVL(cur.minimum_accountable_unit,power(10,(-1* precision))))
       * NVL(cur.minimum_accountable_unit, power(10, (-1 * precision)))       
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = det.ledger_id
AND cur.currency_code = sob.currency_code
) -
nvl(DET.TAXABLE_AMT_FUNCL_CURR,det.taxable_amt)   C_EXEMPT_ACCT_AMOUNT,
(
SELECT ROUND(    (DET.TRX_LINE_AMT * nvl(DET.CURRENCY_CONVERSION_RATE,1)) /
               NVL(cur.minimum_accountable_unit,power(10,(-1* precision))))
        * NVL(cur.minimum_accountable_unit, power(10, (-1 * precision)))       
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = det.ledger_id
AND cur.currency_code = sob.currency_code
)                                                 C_LINE_FUN_AMT,
DECODE(ACT.GL_TRANSFER_FLAG, 'Y', nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt) , 0) POSTED_AMT,
DECODE(ACT.GL_TRANSFER_FLAG, 'N', nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt) , NULL,nvl(DET.TAX_AMT_FUNCL_CURR ,det.tax_amt),0) UNPOSTED_AMT,
tax_exemption_id c_exemption_id,
NVL(DET.HISTORICAL_FLAG,'N') C_HISTORICAL_FLAG,
DET.TAX_ONLY_FLAG         C_TAX_ONLY_FLAG
FROM      
    ZX_REP_TRX_JX_EXT_T   EXT,
    ZX_REP_CONTEXT_T CON,
    ZX_REP_TRX_DETAIL_T   DET,
    ZX_REP_ACTG_EXT_T       ACT WHERE DET.REQUEST_ID = :P_CONC_REQUEST_ID
AND DET.REQUEST_ID = CON.REQUEST_ID
AND DET.REP_CONTEXT_ID = CON.REP_CONTEXT_ID
and  ACT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID 
AND EXT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID
AND :P_REPORT_NAME <> 'ZXZZTVSR' 
AND :P_REPORT_NAME <> 'ZXXVATRN'
AND :P_REPORT_NAME <> 'ZXXSTVRR'
&LP_Order_by
Parameter Name SQL text Validation
Reporting Level
 
LOV Oracle
Reporting Context
 
LOV Oracle
Company Name
 
LOV Oracle
Register Type
 
LOV Oracle
Order By Tax Register
 
LOV Oracle
Summarization
 
LOV Oracle
GL Date Low
 
Date
GL Date High
 
Date
Transaction Date Low
 
Date
Transaction Date High
 
Date
Tax Regime Code
 
LOV Oracle
Tax
 
LOV Oracle
Tax Jurisdiction
 
LOV Oracle
Tax Status Code
 
LOV Oracle
Tax Code Low
 
LOV Oracle
Tax Code High
 
LOV Oracle
Currency Code Low
 
LOV Oracle
Currency Code High
 
LOV Oracle
Exemption Status
 
LOV Oracle
Accounting Status
 
LOV Oracle
Show Functional Amounts
 
LOV Oracle
Include Invoices
 
LOV Oracle
Include Applications
 
LOV Oracle
Include Adjustments
 
LOV Oracle
Include Miscellaneous receipts
 
LOV Oracle
Include Bills Receivables
 
LOV Oracle
Include Discounts
 
LOV Oracle
Debug Flag
 
LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: