ZX Taiwanese Output VAT - draft

Description
Categories: BI Publisher
Columns: C Ledger Id, C Ledger Name, C Func Currency Code, C Organization Unit Name, C Taxpayer Id, C Rep Context Address1, C Rep Context Address2, C Rep Context Address3, C Rep Context City, C Territory Short Name ...
Application: E-Business Tax
Source: Taiwanese Output VAT Report (XML) - Not Supported: Reserved For Future Use
Short Name: ZXTWRVAT_XML
DB package: ZX_ZXARRECV_XMLP_PKG
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,
            CON.ACTIVITY_CODE                           C_SERVICE_TYPE,
            CON.LEGAL_CONTACT_PARTY_NAME                C_LEGAL_REPRESENTATIVE,
            CON.LEGAL_CONTACT_PARTY_NUM                 C_REPRESENTATIVE_TAX_ID,
            DET.BILLING_TP_NUMBER                       C_CUSTOMER_TAXPAYER_ID,
            DET.BILLING_TP_NAME             C_CUST_NAME,
            DET.BILLING_TP_TAXPAYER_ID              C_BILLING_TP_TAXPAYER_ID,
            DET.BILLING_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,
            substrb(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_TYPE_MNG                            C_TRX_TYPE_MEANING,
            to_char(DET.TRX_DATE,'YYYY-MM-DD')                  C_TRX_DATE,
            to_char(DET.TRX_DUE_DATE,'YYYY-MM-DD')              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,
            EXT.ATTRIBUTE1                  C_TW_WINE_CIGARETTE,
            DET.POSTED_FLAG                 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,
            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,
            to_char(to_date(EXT.ATTRIBUTE15,'DD-MON-YYYY'),'RRRR/MM/DD') 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.TAX_EXEMPT_REASON_MNG 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,
            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,
            DET.GL_DATE C_GL_DATE,
            DET.SHIPPING_TP_ADDRESS1||DET.SHIPPING_TP_ADDRESS2||DET.SHIPPING_TP_ADDRESS2 C_SHIP_ADDRESS,
            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,
                ZX_ZXARRECV_XMLP_PKG.cf_tw_export_dateformula(EXT.GDF_RA_CUST_TRX_ATT8) CF_TW_EXPORT_DATE,
                ZX_ZXARRECV_XMLP_PKG.cf_taxable_acc_amtformula(DET.TRX_ID, DET.trx_line_id) CF_TAXABLE_ACC_AMT,
                ZX_ZXARRECV_XMLP_PKG.cf_taxable_amtformula(DET.TRX_ID, DET.trx_line_id) CF_TAXABLE_AMT,
                ZX_ZXARRECV_XMLP_PKG.CF_TOT_FUNC_AMT_p CF_TOT_FUNC_AMT,
                ZX_ZXARRECV_XMLP_PKG.CF_TOT_AMOUNT_p CF_TOT_AMOUNT,
                ZX_ZXARRECV_XMLP_PKG.CP_TRX_LINE_ID_p CP_TRX_LINE_ID,
                ZX_ZXARRECV_XMLP_PKG.CP_TRX_ID_p CP_TRX_ID,
                ZX_ZXARRECV_XMLP_PKG.CP_TRX_ID_ACC_p CP_TRX_ID_ACC,
                ZX_ZXARRECV_XMLP_PKG.CP_TRX_LINE_ID_ACC_p CP_TRX_LINE_ID_ACC
            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
             NVL(DET.REP_CONTEXT_ID ,CON.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
            ORDER BY DET.TRX_ID,DET.TRX_LINE_ID
Parameter Name SQL text Validation
Reporting Level
 
LOV Oracle
Reporting Context
 
LOV Oracle
Company Name
 
LOV Oracle
Start Accounting Period
 
LOV Oracle
End Accounting Period
 
LOV Oracle
Start GL Date
 
Date
End GL Date
 
Date
Start Transaction Date
 
Date
End Transaction Date
 
Date
Transaction Source Name
 
LOV Oracle
Include Referenced Source
 
LOV Oracle
Report Name
 
P_REGISTER_TYPE
 
LOV Oracle
P_PRODUCT
 
LOV Oracle
P_INCLUDE_AR_INV_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AR_APPL_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AR_ADJ_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AR_MISC_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AR_BR_TRX_CLASS
 
LOV Oracle
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: