if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED) as [VenteLigne. Qté],
max(UNIT_STANDARD_PRICE) as [VenteLigne. Prix_Theorique],
UNIT_SELLING_PRICE as [VenteLigne. Prix_de_Vente],
(UNIT_STANDARD_PRICE)-UNIT_SELLING_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Remise],
UNIT_STANDARD_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Montant Brut],
// SALES_ORDER,
// SALES_ORDER_REVISION,
// SALES_ORDER_LINE,
// SALES_ORDER_DATE,
// ACCOUNTING_RULE_ID,
// ACCOUNTING_RULE_DURATION,
// LINE_TYPE,
// ATTRIBUTE_CATEGORY,
// ATTRIBUTE1,
// ATTRIBUTE2,
// ATTRIBUTE3,
// ATTRIBUTE4,
// ATTRIBUTE5,
// ATTRIBUTE6,
// ATTRIBUTE7,
// ATTRIBUTE8,
// ATTRIBUTE9,
// ATTRIBUTE10,
// REQUEST_ID,
// PROGRAM_APPLICATION_ID,
// PROGRAM_ID,
// PROGRAM_UPDATE_DATE,
// RULE_START_DATE,
// INITIAL_CUSTOMER_TRX_LINE_ID,
if(INTERFACE_LINE_CONTEXT='INTERCOMPANY','Groupe','Hors Groupe') as [VenteLigne. Context],
INTERFACE_LINE_ATTRIBUTE1 as [VenteLigne. Num Cde],
// INTERFACE_LINE_ATTRIBUTE2,
// INTERFACE_LINE_ATTRIBUTE3,
// INTERFACE_LINE_ATTRIBUTE4,
// INTERFACE_LINE_ATTRIBUTE5,
// INTERFACE_LINE_ATTRIBUTE6,
// INTERFACE_LINE_ATTRIBUTE7,
// INTERFACE_LINE_ATTRIBUTE8,
// SALES_ORDER_SOURCE,
// TAXABLE_FLAG,
EXTENDED_AMOUNT as [VenteLigne. Montant],
// REVENUE_AMOUNT,
// AUTORULE_COMPLETE_FLAG,
// LINK_TO_CUST_TRX_LINE_ID,
// ATTRIBUTE11,
// ATTRIBUTE12,
// ATTRIBUTE13,
// ATTRIBUTE14,
// ATTRIBUTE15,
// TAX_PRECEDENCE,
// TAX_RATE,
// ITEM_EXCEPTION_RATE_ID,
// TAX_EXEMPTION_ID,
// MEMO_LINE_ID,
// AUTORULE_DURATION_PROCESSED,
// UOM_CODE,
// DEFAULT_USSGL_TRANSACTION_CODE,
// DEFAULT_USSGL_TRX_CODE_CONTEXT,
// INTERFACE_LINE_ATTRIBUTE10,
// INTERFACE_LINE_ATTRIBUTE11,
// INTERFACE_LINE_ATTRIBUTE12,
// INTERFACE_LINE_ATTRIBUTE13,
// INTERFACE_LINE_ATTRIBUTE14,
// INTERFACE_LINE_ATTRIBUTE15,
// INTERFACE_LINE_ATTRIBUTE9,
// VAT_TAX_ID,
// AUTOTAX,
// LAST_PERIOD_TO_CREDIT,
// ITEM_CONTEXT,
// TAX_EXEMPT_FLAG as [VenteLigne. Exoneration],
// TAX_EXEMPT_NUMBER as [VenteLigne. Num_Exoneration],
// TAX_EXEMPT_REASON_CODE,
// TAX_VENDOR_RETURN_CODE,
// SALES_TAX_ID,
// LOCATION_SEGMENT_ID,
// MOVEMENT_ID,
ORG_ID as [VenteLigne. SociétéID]
// WH_UPDATE_DATE,
// GLOBAL_ATTRIBUTE1,
// GLOBAL_ATTRIBUTE2,
// GLOBAL_ATTRIBUTE3,
// GLOBAL_ATTRIBUTE4,
// GLOBAL_ATTRIBUTE5,
// GLOBAL_ATTRIBUTE6,
// GLOBAL_ATTRIBUTE7,
// GLOBAL_ATTRIBUTE8,
// GLOBAL_ATTRIBUTE9,
// GLOBAL_ATTRIBUTE10,
// GLOBAL_ATTRIBUTE11,
// GLOBAL_ATTRIBUTE12,
// GLOBAL_ATTRIBUTE13,
// GLOBAL_ATTRIBUTE14,
// GLOBAL_ATTRIBUTE15,
// GLOBAL_ATTRIBUTE16,
// GLOBAL_ATTRIBUTE17,
// GLOBAL_ATTRIBUTE18,
// GLOBAL_ATTRIBUTE19,
// GLOBAL_ATTRIBUTE20,
// GLOBAL_ATTRIBUTE_CATEGORY,
// GROSS_UNIT_SELLING_PRICE,
// GROSS_EXTENDED_AMOUNT,
// AMOUNT_INCLUDES_TAX_FLAG,
// TAXABLE_AMOUNT,
// WAREHOUSE_ID,
// TRANSLATED_DESCRIPTION,
// EXTENDED_ACCTD_AMOUNT,
// BR_REF_CUSTOMER_TRX_ID,
// BR_REF_PAYMENT_SCHEDULE_ID,
// BR_ADJUSTMENT_ID,
// MRC_EXTENDED_ACCTD_AMOUNT,
// PAYMENT_SET_ID,
// CONTRACT_LINE_ID,
// SOURCE_DATA_KEY1,
// SOURCE_DATA_KEY2,
// SOURCE_DATA_KEY3,
// SOURCE_DATA_KEY4,
// SOURCE_DATA_KEY5,
// INVOICED_LINE_ACCTG_LEVEL,
// OVERRIDE_AUTO_ACCOUNTING_FLAG,
// SHIP_TO_CUSTOMER_ID,
// SHIP_TO_ADDRESS_ID,
// SHIP_TO_SITE_USE_ID,
// SHIP_TO_CONTACT_ID,
// HISTORICAL_FLAG,
// TAX_LINE_ID,
// LINE_RECOVERABLE,
// TAX_RECOVERABLE,
// TAX_CLASSIFICATION_CODE,
// AMOUNT_DUE_REMAINING,
// ACCTD_AMOUNT_DUE_REMAINING,
// AMOUNT_DUE_ORIGINAL,
// ACCTD_AMOUNT_DUE_ORIGINAL,
// CHRG_AMOUNT_REMAINING,
// CHRG_ACCTD_AMOUNT_REMAINING,
// FRT_ADJ_REMAINING,
// FRT_ADJ_ACCTD_REMAINING,
// FRT_ED_AMOUNT,
// FRT_ED_ACCTD_AMOUNT,
// FRT_UNED_AMOUNT,
// FRT_UNED_ACCTD_AMOUNT,
// DEFERRAL_EXCLUSION_FLAG,
// RULE_END_DATE,
// PAYMENT_TRXN_EXTENSION_ID,
// INTEREST_LINE_ID
FROM
[..\QVD_Sources\RA_CUSTOMER_TRX_LINES_ALL.QVD]
(qvd)
Where
ORG_ID='155'
//or ORG_ID='158'
and (DESCRIPTION<>'Timbre Fiscal' and DESCRIPTION<>'Report')
and LINE_TYPE='LINE'
and (INTERFACE_LINE_CONTEXT='ORDER ENTRY' or INTERFACE_LINE_CONTEXT='INTERCOMPANY')
group by CUSTOMER_TRX_ID,INVENTORY_ITEM_ID,DESCRIPTION,QUANTITY_CREDITED,QUANTITY_INVOICED,QUANTITY_INVOICED,INTERFACE_LINE_CONTEXT,INTERFACE_LINE_ATTRIBUTE1,EXTENDED_AMOUNT,ORG_ID
(UNIT_STANDARD_PRICE)-UNIT_SELLING_PRICE*(if(IsNull(QUANTITY_CREDITED),0,QUANTITY_CREDITED)+if(IsNull(QUANTITY_INVOICED),0,QUANTITY_INVOICED)) as [VenteLigne. Remise],
