ZX Singaporean GST F5 - 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 GST F5 Report (XML) - Not Supported: Reserved For Future Use
Short Name: ZXSGGF5_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.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,
		  ZX_ZXAPJAJL_XMLP_PKG.CF_TOT_AMOUNT_P CF_TOT_AMOUNT,
            ZX_ZXAPJAJL_XMLP_PKG.CF_TOT_FUNC_AMT_P CF_TOT_FUNC_AMT,
			'x' dummy
            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