ZX Taiwanese Pro Forma 401 - 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 Pro Forma 401 Report (XML) - Not Supported: Reserved For Future Use
Short Name: ZXTWSPRF_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
GL Date From
 
Date
GL Date To
 
Date
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
P_INCLUDE_AP_STD_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AP_DM_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AP_CM_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AP_PREP_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AP_MIX_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AP_EXP_TRX_CLASS
 
LOV Oracle
P_INCLUDE_AP_INT_TRX_CLASS
 
LOV Oracle
P_INCLUDE_GL_MANUAL_LINES
 
LOV Oracle
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: