IBY_XML_FNDCPT_DOC_LINE_V

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name
DOCUMENT_LINE LINE_NUMBER DOC_UNIQUE_REF

Query

SQL_Statement

SELECT XMLElement("DocumentLine", XMLElement("LineID",inv_line.Line_number), XMLElement("LineNumber",inv_line.line_number), XMLForest(inv_line.PO_NUMBER AS "PONumber"), XMLElement("LineType", XMLElement("Code",inv_line.line_type), XMLElement("Meaning",NULL) ), XMLElement("LineDescription",inv_line.description), XMLElement("LineAmount", XMLElement("Value", NVL(inv_line.extended_amount, 0)), XMLElement("Currency", XMLElement("Code",inv_line.document_currency_code) ) ), XMLElement("UnitRate", NVL(inv_line.unit_price, 0)), XMLElement("Quantity",inv_line.quantity), XMLElement("UnitOfMeasure",inv_line.unit_of_measure), XMLForest(inv_line.inventory_item_id AS "ProductCode"), XMLForest(NULL AS "CommodityCode"), XMLElement("Discount", XMLElement("Amount", XMLElement("Value", NVL(inv_line.discount_amount, 0)), XMLElement("Currency", XMLElement("Code",inv_line.document_currency_code) ) ), XMLForest(NULL AS "DiscountType") ), XMLElement("Tax", XMLElement("Amount", XMLElement("Value", NVL(total_tax.total_sales, 0)), XMLElement("Currency", XMLElement("Code",inv_line.document_currency_code) ) ), XMLElement("RatePercent", NVL(total_tax.total_rate, 0)), XMLForest('SALESTAX' AS "TaxType"), XMLForest(NULL AS "TaxJurisdiction") ), XMLElement("Tax", XMLElement("Amount", XMLElement("Value", NVL(total_tax.total_vat, 0)), XMLElement("Currency", XMLElement("Code",inv_line.document_currency_code) ) ), XMLElement("RatePercent", NVL(total_tax.total_rate, 0)), XMLForest('VAT' AS "TaxType"), XMLForest(NULL AS "TaxJurisdiction") ) ), inv_line.line_number, inv_line.doc_unique_ref FROM ar_document_lines_v inv_line, (SELECT doc_unique_ref, line_type, line_number, sum(tax_rate) total_rate, sum(sales_tax_amount) total_sales, sum(VAT_tax_amount) total_vat FROM ar_document_lines_v GROUP BY doc_unique_ref, line_number, line_type) total_tax WHERE inv_line.line_type = 'LINE' AND total_tax.line_type(+) = 'TAX' AND inv_line.doc_unique_ref = total_tax.doc_unique_ref(+) AND inv_line.line_number = total_tax.line_number(+)