ZX Taiwanese Sales Return and Discount Report- Not Supported: Reserved For Future Use
Description
- Categories
- BI Publisher
- Application
- E-Business Tax
- Source
- Taiwanese Sales Return and Discount Report (XML) - Not Supported: Reserved For Future Use
- Short Name
- ZXTWRSRD_XML
- DB package
- ZX_ZXARRECV_XMLP_PKG
Imported from BI Publisher
Taiwanese Sales Return and Discount Report
Taiwanese Sales Return and Discount Report
SQL 195 lines
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 |
Parameters 10
| Parameter | Type | List of values SQL |
|---|---|---|
| Reporting Level | LOV Oracle | |
| Reporting Context | LOV Oracle | |
| Company Name | LOV Oracle | |
| GL Date From | Date | |
| GL Date To | Date | |
| Transaction Date From | Date | |
| Transaction Date To | Date | |
| Original Number From | Char | |
| Original Number To | Char | |
| Customer Name | LOV Oracle |