ZX Singaporean Input Taxes Gain/Loss - draft

Description
Categories: BI Publisher
Columns: C Rep Context Lvl Mng, C Ledger Id, C Ledger Name, C Rep Context Entity Name, C Chart Of Accounts Id, C Functional Currency Code, C Func Currency Description, C Bank Account Num, C Taxpayer Id, C Activity Code ...
Application: E-Business Tax
Source: Singaporean Input Taxes Gain/Loss Report (XML) - Not Supported: Reserved For Future Use
Short Name: ZXSGAGAL_XML
DB package: ZX_ZXAPJAJL_XMLP_PKG
SELECT
            CON.REP_CONTEXT_LVL_MNG               C_REP_CONTEXT_LVL_MNG,
            DET.LEDGER_ID        C_Ledger_ID,
            DET.LEDGER_NAME  C_Ledger_Name,
            CON.REP_CONTEXT_ENTITY_NAME C_Rep_Context_Entity_Name,
            DET.CHART_OF_ACCOUNTS_ID                        C_Chart_Of_Accounts_ID,
            DET.FUNCTIONAL_CURRENCY_CODE            C_Functional_Currency_Code,
            DET.CURRENCY_CODE_DESC              C_Func_Currency_Description,
            DET.BANK_ACCOUNT_NUM                C_Bank_Account_Num,
            CON.TAXPAYER_ID                 C_Taxpayer_ID,
            CON.ACTIVITY_CODE    C_Activity_Code,
            CON.REP_CONTEXT_ENTITY_NAME         C_Organization_Unit_Name,
                    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,
                DET.TERRITORY_SHORT_NAME            C_Territory_Short_Name,
                CON.REP_CONTEXT_ENTITY_COUNTRY          C_Rep_Context_Country,
                CON.REP_CONTEXT_ENTITY_CITY         C_Rep_Context_City,
                CON.REP_CONTEXT_ENTITY_POSTAL_CODE      C_Rep_Context_Postal_Code,
                CON.LEGAL_CONTACT_PARTY_NAME            C_Taxable_Person,
                    CON.LEGAL_CONTACT_TITLE             C_Designation,
                    CON.LEGAL_CONTACT_PRE_NAME_ADJUNCT              C_Title,
                    DET.BANK_BRANCH_ID              C_Branch_Code,
                DET.EXTRACT_SOURCE_LEDGER           C_Extract_Source_Legder,
                DET.TRX_ID                  C_Invoice_ID,
                DET.TRX_NUMBER                  C_Invoice_Number,
                DET.APPLIED_TO_TRX_NUMBER           C_Original_Number,
                    DET.APPLIED_TO_TRX_NUMBER           C_Applied_to_Trx_number,
                TO_CHAR(DET.TRX_DATE,'YYYY-MM-DD')                  C_Invoice_Date,
                DET.EVENT_CLASS_CODE                C_Invoice_Type,
                    DET.TRX_CURRENCY_CODE               C_Trx_Currency_Code,
                DET.BILLING_TP_NUMBER               C_Supplier_Number,
                DET.BILLING_TRADING_PARTNER_ID          C_Supplier_ID,
                DET.BILLING_TP_NAME             C_Supplier_Name,
                DET.BILLING_TP_SITE_NAME            C_Supplier_Site_Name,
                DET.BILLING_TP_TAXPAYER_ID                      C_Supplier_Taxpayer_ID,
                DET.BILLING_TP_TAX_REG_NUM              C_Supplier_Tax_Reg_Num,
                DET.BILLING_TP_ADDRESS1             C_Supplier_Address1,
                DET.BILLING_TP_ADDRESS2             C_Supplier_Address2,
                DET.BILLING_TP_ADDRESS3             C_Supplier_Address3,
                DET.BILLING_TP_PROVINCE             C_Supplier_Province,
                DET.BILLING_TP_CITY             C_Supplier_City,
                    DET.REGISTRATION_STATUS_CODE            C_Supplier_VAT_Reg_Status_Code,
                substrb(DET.DOCUMENT_SUB_TYPE,10,2)     C_GUI_Type,
                EXT.DOCUMENT_SUB_TYPE_MNG           C_GUI_Type_Description,
                EXT.TAX_STATUS_MNG              C_Government_Tax_Type,
                EXT.TRX_BUSINESS_CATEGORY_MNG           C_Deductible_Type,
                    DET.SUPPLIER_EXCHANGE_RATE          C_Supplier_Exchange_Rate,
                TO_CHAR(DET.TAX_INVOICE_DATE,'YYYY-MM-DD')  C_Custom_Doc_Date,
                DET.TAX_INVOICE_NUMBER              C_Custom_Doc_Number,
                DET.SUPPLIER_TAX_INVOICE_NUMBER         C_Supplier_Tax_Invoice_Number,
                    TO_CHAR(DET.SUPPLIER_TAX_INVOICE_DATE,'YYYY-MM-DD')         C_Supplier_Tax_Invoice_Date,
                EXT.ATTRIBUTE16                 C_CL_Order_by_Doc_Type,
                substrb(DET.DOCUMENT_SUB_TYPE,15)       C_CL_DOCUMENT_SUB_TYPE,
                EXT.DOCUMENT_SUB_TYPE_MNG           C_CL_DOC_TYPE_MEANING,
                DET.PRODUCT_DESCRIPTION             C_Item,
                to_number(det.trx_line_quantity)                                 C_Quantity,
                TO_CHAR(DET.CANCELLED_DATE,'YYYY-MM-DD')                C_Cancelled_Date,
                nvl(DET.CURRENCY_CONVERSION_RATE,1)     C_Exchange_Rate,
                DET.PORT_OF_ENTRY_CODE              C_Port_of_Entry_Code,
                DET.LINE_INTENDED_USE               C_Line_Intended_Use,
                EXT.GDF_AP_INVOICES_ATT11           C_GDF_AP_INVOICES_ATT11,
                EXT.GDF_AP_INVOICES_ATT12           C_GDF_AP_INVOICES_ATT12,
                    EXT.GDF_AP_INVOICES_ATT13           C_GDF_AP_INVOICES_ATT13,
                DET.TAX_RATE_ID                 C_Tax_ID,
                    DET.TAX_RATE_CODE                               C_Tax_Code,
                DET.TAX_RATE_CODE_NAME              C_Tax_Code_Name,
                nvl(DET.TAX_RATE,0)             C_Tax_Rate,
                DET.TAX_TYPE_CODE               C_Tax_Rate_Code_Type_Code,
                DET.TAX_TYPE_MNG                C_Tax_Rate_Code_Type_Meaning,
                EXT.ATTRIBUTE4                  C_DGI_Trans_Code,
                DET.TAX_REGIME_CODE             C_DGI_Tax_Regime_Code,
                DET.TRX_LINE_TYPE               C_Taxable_Line_Type,
                nvl(NVL(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT),0)          C_Taxable_Accounted_Amount,
                DET.TAXABLE_AMT                 C_Taxable_Entered_Amount,
                nvl(NVL(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT),0)              C_Tax_Accounted_Amount,
                nvl(DET.TAX_AMT,0)                  C_Tax_Entered_Amount,
                EXT.NUMERIC12                   C_JA_Invoice_Amount,
                        EXT.NUMERIC1                    C_SG_Inhouse_Inv_Amount,
                EXT.NUMERIC2                    C_SG_Supplier_Inv_Amount,
                EXT.NUMERIC3                    C_SG_Inhouse_Tax_Amount,
                EXT.NUMERIC4                    C_SG_Supplier_Tax_Amount,
                EXT.NUMERIC5                    C_SG_Taxable_GainLoss,
                EXT.NUMERIC6                    C_SG_GainLoss,
                EXT.NUMERIC2                    C_JL_VAT_Exempt_Amount_Total,
                EXT.NUMERIC3                    C_JL_Perc_Tax_Amount_Total,
                EXT.NUMERIC4                    C_JL_Prov_Tax_Amount_Total,
                EXT.NUMERIC5                    C_JL_Muni_Tax_Amount_Total,
                EXT.NUMERIC6                    C_JL_Excise_Tax_Amount_Total,
                EXT.NUMERIC7                    C_JL_Other_Tax_Total,
                EXT.NUMERIC8                    C_JL_Non_Taxable_Amount_Total,
                EXT.NUMERIC9                    C_JL_VAT_Tax_Amount_Total,
                    EXT.NUMERIC10                   C_JL_Taxable_Amount_Total,
                EXT.NUMERIC14                   C_JL_Exempt_VAT_Accted_Amount,
                0                       C_JL_Other_Fed_Perc_Tax,
                EXT.NUMERIC1                    C_AR_Number_of_Rate_by_Invoice,
                EXT.NUMERIC14                   C_AR_Number_of_Rates,
                EXT.numeric2                    C_JL_EXEMPT_AMT,
                EXT.numeric10                   C_JL_EFFECTIVE_AMT,
                EXT.numeric9                    C_JL_VAT_AMT,
                EXT.numeric7                    C_JL_OTHER_TAX_AMT,
                EXT.NUMERIC1                    C_JL_TOTAL_DOC_AMT,
                EXT.NUMERIC1                    C_CL_Net_Amount,
                EXT.NUMERIC3                    C_CL_Number_of_Invoice,
                EXT.NUMERIC4                    C_CL_Total_Exempt_by_Doc_Type,
                EXT.NUMERIC5                    C_CL_Total_Effect_by_Doc_Type,
                EXT.NUMERIC6                    C_CL_Total_VAT_by_Doc_Type,
                EXT.NUMERIC11                   C_CL_Total_Other_by_Doc_Type,
                EXT.NUMERIC8                    C_CL_Total_Amount_by_Doc_Type,
                DET.GDF_FND_CURRENCIES_ATT1         C_DGI_Currency_Code,
                EXT.ATTRIBUTE16                 C_Fiscal_Printer,
                DET.PRECISION                   C_Precision_1,
                DET.MINIMUM_ACCOUNTABLE_UNIT            C_Min_Acct_Units,
                EXT.GDF_AP_INVOICES_ATT4                        C_Print_Date ,
                nvl(DET.TAX_AMT,0) + nvl(DET.TAXABLE_AMT,0)         C_TOT_AMOUNT ,
                nvl(nvl(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT),0) + nvl(NVL(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT),0)  C_TOT_FUNC_AMT,
                ACT.PERIOD_NAME                     C_PERIOD_NAME ,
                EXT.ATTRIBUTE9                      C_TRX_CURRENCY_DESC ,
                EXT.ATTRIBUTE8                      C_BATCH_NAME ,
                DET.TRX_DESCRIPTION                 C_TRX_DESCRIPTION ,
                EXT.ATTRIBUTE3                      C_ALTERNATE_TERRITORY_CODE ,
                EXT.ATTRIBUTE7                      C_ATTRIBUTE3 ,
                DET.OFFSET_FLAG                     C_OFFSET_FLAG ,
                EXT.ATTRIBUTE6                      C_CREATED_BY,
                ACT.ACCOUNT_FLEXFIELD               C_TAX_ACCT_FLEXFIELD,
                EXT.ATTRIBUTE1                      C_HOLD,
                EXT.ATTRIBUTE5                      C_MATCH,
                DET.HQ_ESTB_REG_NUMBER              C_FIRST_PARTY_TAX_REG_NUM,
                EXT.ATTRIBUTE1                      C_ATTRIBUTE1,
                DET.UNIT_PRICE                  C_UNIT_PRICE,
                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_INVOICE_DATE,
                CON.ORG_INFORMATION2                C_ORG_INFORMATION2,
            EXT.ATTRIBUTE28                C_SG_BANK_ID,
            EXT.ATTRIBUTE29                C_SG_BRANCH_CODE,
            EXT.ATTRIBUTE30                C_SG_ACCOUNT_NUM,
            DET.TRX_LINE_ID         C_TRANSACTION_LINE,
            DET.ADJUSTED_DOC_NUMBER    C_ADJUSTED_DOC_NUMBER,
            ZX_ZXAPJAJL_XMLP_PKG.CF_TAXABLE_ACC_AMTFormula(DET.TRX_ID, DET.TRX_LINE_ID) CF_TAXABLE_ACC_AMT,
            ZX_ZXAPJAJL_XMLP_PKG.CF_TAXABLE_AMTFormula(DET.TRX_ID, DET.TRX_LINE_ID) CF_TAXABLE_AMT,
            ZX_ZXAPJAJL_XMLP_PKG.CF_TOT_AMOUNT_P CF_TOT_AMOUNT,
            ZX_ZXAPJAJL_XMLP_PKG.CF_TOT_FUNC_AMT_P CF_TOT_FUNC_AMT,
            ZX_ZXAPJAJL_XMLP_PKG.CP_TRX_ID_P CP_TRX_ID,
            ZX_ZXAPJAJL_XMLP_PKG.CP_TRX_ID_ACC_P CP_TRX_ID_ACC,
            ZX_ZXAPJAJL_XMLP_PKG.CP_TRX_LINE_ID_P CP_TRX_LINE_ID,
            ZX_ZXAPJAJL_XMLP_PKG.CP_TRX_LINE_ID_ACC_P CP_TRX_LINE_ID_ACC
            FROM    ZX_REP_CONTEXT_T    CON,
                ZX_REP_TRX_DETAIL_T DET,
                ZX_REP_TRX_JX_EXT_T EXT,
                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
Ledger Currency
 
LOV Oracle
Start Accounting Period
 
LOV Oracle
End Accounting Period
 
LOV Oracle
Tax Regime Code
 
LOV Oracle
Tax
 
LOV Oracle
Tax Jurisdiction
 
LOV Oracle
Tax Rate Code
 
LOV Oracle
Tax Rate Code
 
LOV Oracle
Supplier Name
 
LOV Oracle
Report Name
 
P_REGISTER_TYPE
 
LOV Oracle
P_PRODUCT
 
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
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: