Exception in ./ord/invoice_item_browse
System.Data.SqlClient.SqlException:
Invalid column name '_iitem_ini_qty'.
Invalid column name '_iitem_ini_unit_price'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at ivalua.platform.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, SqlCommandTimeout sqlCommandTimeout, String commandText, SqlTransaction sqlTransaction, SqlCacheDependency& dependency, Boolean useSqlDependency, SqlParameter[] commandParameters) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\platform\SQLHelper.cs:line 1173
ivalua.platform.SQLException:
DECLARE @timestamp DATETIME
SET @timestamp=CONVERT(DATETIME, '2019-01-17 18:06:55', 20)
DECLARE @invoice_id Int
SET @invoice_id='32'
DECLARE @deliv_id Int
DECLARE @default_currency VarChar (128)
SET @default_currency='usd'
DECLARE @deliv_invoiceable_status_list udtt_list
INSERT INTO @deliv_invoiceable_status_list VALUES (1,'end'),
(2,'bqa')
-- ./ord/util_ctrl

IF OBJECT_ID('tempdb..#temp_ord_item') IS NOT NULL
DROP TABLE tempdb..#temp_ord_item
CREATE TABLE #temp_ord_item (oitem_id int)

INSERT INTO #temp_ord_item
SELECT oitem_id
FROM t_ord_item oitem
WHERE oitem.status_code <> 'del'

AND EXISTS
(
-- InvoiceId CASE
SELECT 1
FROM t_ord_invoice_item iitem
WHERE iitem.oitem_id = oitem.oitem_id AND iitem.status_code <> 'del'
AND iitem.invoice_id = @invoice_id


)
--********************* Due items *********************************************************************************

IF OBJECT_ID('tempdb..#temp_ord_due_item') IS NOT NULL
DROP TABLE tempdb..#temp_ord_due_item
CREATE TABLE #temp_ord_due_item (oitem_id INT, due_id INT)

INSERT INTO #temp_ord_due_item
SELECT oitem.oitem_id, duitem.due_id
FROM #temp_ord_item oitem
INNER JOIN t_ord_due_item duitem ON duitem.oitem_id = oitem.oitem_id
INNER JOIN t_ord_due due ON due.due_id = duitem.due_id AND due.status_code <> 'del'

CREATE NONCLUSTERED INDEX IX_temp_ord_due_item ON #temp_ord_due_item(oitem_id DESC)

--*********************** delivered items *****************************************************************************

IF OBJECT_ID('tempdb..#temp_ord_delivered_item') IS NOT NULL
DROP TABLE tempdb..#temp_ord_delivered_item
CREATE TABLE #temp_ord_delivered_item (oitem_id INT, ditem_quantity DECIMAL(28, 10), ditem_total_quantity DECIMAL(28, 10), ditem_total_amount DECIMAL(28, 10), due_id INT)

DECLARE @status_list table (status_code varchar(128))
INSERT INTO @status_list (status_code)
SELECT CAST(delivStatus.MY_VALUE as varchar(128))
FROM @deliv_invoiceable_status_list as delivStatus

INSERT INTO #temp_ord_delivered_item
SELECT	delitm.oitem_id,
SUM(CASE WHEN 1 = 1 THEN ISNULL(CASE WHEN deliv.delivtype_code = 'ret' THEN - delitm.ditem_quantity ELSE delitm.ditem_quantity END, 0) ELSE 0 END) AS ditem_quantity,
SUM(ISNULL(CASE WHEN deliv.delivtype_code = 'ret' THEN - delitm.ditem_quantity ELSE delitm.ditem_quantity END, 0)) AS ditem_total_quantity,
SUM(ISNULL(CASE WHEN deliv.delivtype_code = 'ret' THEN - delitm.ditem_quantity * delitm.ditem_price_entry ELSE delitm.ditem_quantity * delitm.ditem_price_entry END, 0)) AS ditem_total_amount,
dueitem.due_id
FROM	#temp_ord_item oitem
INNER JOIN t_ord_delivery_item delitm ON delitm.oitem_id = oitem.oitem_id
INNER JOIN t_ord_delivery deliv ON deliv.deliv_id = delitm.deliv_id
LEFT JOIN #temp_ord_due_item dueitem ON dueitem.oitem_id = delitm.oitem_id AND dueitem.due_id = delitm.due_id
INNER JOIN @status_list AS delivStatus ON delivStatus.status_code = deliv.status_code
GROUP BY	delitm.oitem_id, dueitem.due_id

CREATE NONCLUSTERED INDEX IX_temp_ord_delivered_item ON #temp_ord_delivered_item(oitem_id DESC)

--************************ invoiced items ***********************************************************************************************

IF OBJECT_ID('tempdb..#temp_ord_invoiced_item') IS NOT NULL
DROP TABLE tempdb..#temp_ord_invoiced_item
CREATE TABLE #temp_ord_invoiced_item (oitem_id INT, due_id INT, iitem_quantity DECIMAL(28, 10), invoiced_amount DECIMAL(28, 10))

INSERT INTO #temp_ord_invoiced_item
SELECT	iitem.oitem_id, iitem.due_id, SUM(ISNULL(CASE WHEN invtype_code = 'INV' THEN iitem.iitem_quantity ELSE -iitem.iitem_quantity END, 0)),
SUM((CASE WHEN invtype_code = 'INV' THEN 1 ELSE - 1 END) * ISNULL( iitem.iitem_total_price_entry, 0))
FROM	#temp_ord_item oitem
INNER JOIN t_ord_invoice_item iitem ON iitem.oitem_id = oitem.oitem_id
INNER JOIN	t_ord_invoice inv ON inv.invoice_id = iitem.invoice_id
WHERE	inv.status_code NOT IN ('del', 'can') AND iitem.status_code <> 'del'
GROUP BY	iitem.oitem_id, iitem.due_id

CREATE NONCLUSTERED INDEX IX_temp_ord_invoiced_item ON #temp_ord_invoiced_item(oitem_id DESC)
SELECT
CAST(iitem.iitem_id AS VARCHAR) AS iitem_id, iitem.invoice_id, iitem.iitem_free_budget, iitem.iitem_label_en AS iitem_label, iitem.iitem_price,
iitem.iitem_price_entry, iitem._iitem_ini_qty, iitem._iitem_ini_unit_price, iitem.unit_code_currency, iitem.iitem_quantity,
iitem.unit_code, unit.unit_short_label_en AS unit_short_label, iitem.tva_code, tva.tva_label_en AS tva_label, tva.tva_value,
tva.tva_code + ';' + CAST(tva.tva_value AS varchar(18)) AS tva_pkval,
iitem.ditem_id, iitem.oitem_id, iitem.ord_id,
iitem.sup_id, iitem.created, iitem.modified, iitem.deleted,
iitem.login_name_created, iitem.login_name_modified, iitem.login_name_deleted,
iitem.iitem_total_price_entry, iitem.deliv_id, iitem.fam_level, iitem.fam_node,
invoice.unit_code_currency AS invoice_unit_code_currency,
CASE WHEN invtype_code = 'INV' THEN 1 ELSE -1 END * COALESCE(iitem.iitem_total_price_entry, iitem.iitem_price_entry * iitem.iitem_quantity) AS iitem_net_total,
(100 + COALESCE(tva.tva_value, 0))
* COALESCE(iitem.iitem_total_price_entry, iitem.iitem_price_entry * iitem.iitem_quantity) / 100 AS iitem_tax_total
, iitem.iitem_vat_incl_price_entry,
oitem.oitem_id AS oitem_id_order, ord.ord_code_calculated + ISNULL(' - ' + ord.ord_label_en, '') + ISNULL('/' + CAST(oitem.oitem_seq AS VARCHAR), '') as label,
oitem.oitem_discounted_price_entry AS oitem_price_entry, oitem.oitem_price,
oitem.unit_code_currency AS oitem_unit_code_currency,
oitem.oitem_quantity, oitem.unit_code AS oitem_unit_code,
ord.ord_label_en AS ord_label, due_item.due_id, due_item.due_begin_date, due_item.due_end_date,
due_item.due_value_entry, due_item.due_value,
CASE WHEN due_item.due_id IS NOT NULL THEN 1.00 ELSE NULL END AS due_quantity,
NULL AS due_unit_code, ctr.ctr_id, ctr.unit_code AS due_unit_code_currency, ctr.ctr_label_en AS ctr_label,
delivery_item.ditem_label_en AS ditem_label, delivery_item.ditem_price_entry,
delivery_item.unit_code_currency AS ditem_unit_code_currency,
ISNULL(ordInvDue.ditem_quantity, ordInv.ditem_quantity) AS ditem_quantity, NULL AS ditem_unit_code, delivery.deliv_label_en AS deliv_label,
COALESCE(oitem.unit_code, '') AS unit_code_origin,
COALESCE(oitem.unit_code_currency, ctr.unit_code, delivery_item.unit_code_currency)
AS unit_code_currency_origin,
CASE
WHEN COALESCE(due_item.iitem_id, delivery_item.iitem_id, oitem.iitem_id) IS NULL
THEN 0
ELSE 1 END AS complete, sf.fam_label_en AS fam_label,
COALESCE(CAST(iitem.oitem_id AS VARCHAR) + '_' + CAST(ISNULL(iitem.due_id, '0') AS VARCHAR),CAST(iitem.iitem_id AS VARCHAR)) AS pk,
'PO'+dbo.pad_left(ord.ord_id_origin,6,'0') + ISNULL('-' + CONVERT(VARCHAR(128),CASE WHEN ord.ord_amendment_num = 0 THEN NULL ELSE ord.ord_amendment_num END), '') + ISNULL('/' + CAST(oitem.oitem_seq AS VARCHAR), '') AS order_item_code, oitem.oitem_seq,
invoice.inv_code_calculated AS invoice_code,
due_item.due_label_en AS due_label,
--*************** Control gap calculation ***********************************************************************************************************************
ISNULL(ordInvDue.delivered_amount, ordInv.delivered_amount) AS total_delivered_amount,
ISNULL(ordInvDue.ordered_amount, ordInv.ordered_amount) AS total_ordered_amount,
ISNULL(ordInvDue.invoiced_quantity, ordInv.invoiced_quantity) AS total_invoiced_quantity,
ISNULL(ordInvDue.invoiced_amount, ordInv.invoiced_amount) AS total_invoiced_amount,
ISNULL(ordInvDue.invoiceable_amount, ordInv.invoiceable_amount) AS order_deliv_invoice_gap,
--***************************************************************************************************************************************************************
ord.ord_id_origin, ord.ord_amendment_num, oitem.oitem_delivered_in_amount
FROM t_ord_invoice_item AS iitem
LEFT JOIN t_bas_unit AS unit ON unit.unit_code = iitem.unit_code
LEFT JOIN t_ord_invoice AS invoice ON invoice.invoice_id = iitem.invoice_id
LEFT JOIN t_buy_tva tva ON tva.tva_code=iitem.tva_code
LEFT JOIN t_ord_item AS oitem ON oitem.oitem_id = iitem.oitem_id AND oitem.status_code <> 'del'
LEFT JOIN t_ord_order AS ord ON ord.ord_id = oitem.ord_id
LEFT JOIN t_ord_due AS due_item ON due_item.due_id = iitem.due_id
LEFT JOIN t_ctr_contract_due AS ctr_due ON ctr_due.due_id = due_item.due_id
LEFT JOIN t_ctr_contract AS ctr ON ctr.ctr_id = ctr_due.ctr_id
LEFT JOIN t_ord_delivery_item AS delivery_item ON delivery_item.ditem_id = iitem.ditem_id
LEFT JOIN t_ord_delivery AS delivery ON delivery_item.deliv_id = delivery.deliv_id

--******************************** Control gap calculation **************************************************************
LEFT JOIN (
SELECT

'PO'+dbo.pad_left(ord.ord_id_origin,6,'0') + ISNULL('-' + CONVERT(VARCHAR(128),CASE WHEN ord.ord_amendment_num = 0 THEN NULL ELSE ord.ord_amendment_num END), '') AS order_code,
CAST(oitem.oitem_id AS VARCHAR) + '_' + CAST(ISNULL(dueitem.due_id, '0') AS VARCHAR) AS pk, oitem.ord_id,
oitem.oitem_seq, oitem.oitem_label, oitem.oitem_quantity, oitem.oitem_discounted_price_entry,
CASE WHEN due.due_id IS NOT NULL
THEN oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)
ELSE NULL
END AS oitem_discounted_price_entry_due,
ISNULL(oitem.item_id, 0) AS item_id, oitem.oitem_id, oitem.oitem_delivered_in_amount,
ISNULL(oitem.oitem_quantity, 0) * (oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)) AS ordered_amount,
COALESCE(delivItemDue.ditem_quantity, delivItem.ditem_quantity, 0) AS ditem_quantity,
COALESCE(delivItemDue.ditem_total_amount, delivItem.ditem_total_amount, 0) AS delivered_amount,
COALESCE(invitemDue.iitem_quantity, invitem.iitem_quantity, 0) AS invoiced_quantity,
COALESCE(invitemDue.invoiced_amount, invitem.invoiced_amount, 0) AS invoiced_amount,
CASE WHEN oitem.oitem_delivered_in_amount = 1
THEN 1
ELSE
CASE WHEN COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0) - COALESCE(invitemDue.iitem_quantity, invitem.iitem_quantity, 0) < 0
THEN 0
ELSE COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0) - COALESCE(invitemDue.iitem_quantity, invitem.iitem_quantity, 0)
END
END AS invoiceable_quantity,

(COALESCE(
delivItemDue.ditem_total_amount,
delivItem.ditem_total_amount,
COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0)
* ( CASE WHEN delivItemDue.ditem_total_quantity IS NOT NULL THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100) END ),
0)
- COALESCE(invitemDue.invoiced_amount, invitem.invoiced_amount, 0)
)
AS invoiceable_amount,
CASE WHEN oitem.oitem_delivered_in_amount = 1
THEN

(COALESCE(
delivItemDue.ditem_total_amount,
delivItem.ditem_total_amount,
COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0)
* ( CASE WHEN delivItemDue.ditem_total_quantity IS NOT NULL THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100) END ),
0)
- COALESCE(invitemDue.invoiced_amount, invitem.invoiced_amount, 0)
)

ELSE
CASE WHEN delivItemDue.ditem_quantity IS NOT NULL
THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)
END
END AS unit_price_to_invoice,
CASE WHEN oitem.oitem_delivered_in_amount = 1
THEN
(COALESCE(
delivItemDue.ditem_total_amount,
delivItem.ditem_total_amount,
COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0)
* ( CASE WHEN delivItemDue.ditem_total_quantity IS NOT NULL THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100) END ),
0))
ELSE
CASE WHEN delivItemDue.ditem_quantity IS NOT NULL
THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)
END
END AS unit_price_to_credit,
due.due_id,
due.due_label_en AS due_label
FROM
#temp_ord_item AS temp_ord_item
INNER JOIN t_ord_item oitem ON oitem.oitem_id = temp_ord_item.oitem_id AND oitem.status_code <> 'del'
LEFT JOIN t_ord_order ord ON oitem.ord_id = ord.ord_id
LEFT JOIN #temp_ord_due_item dueitem ON dueitem.oitem_id = oitem.oitem_id
LEFT JOIN t_ord_due due ON due.due_id = dueitem.due_id
LEFT JOIN #temp_ord_delivered_item delivItemDue ON delivItemDue.oitem_id = dueitem.oitem_id AND delivItemDue.due_id = dueitem.due_id
LEFT JOIN #temp_ord_delivered_item delivItem ON delivItem.oitem_id = oitem.oitem_id AND delivItem.due_id IS NULL
LEFT JOIN #temp_ord_invoiced_item invitemDue ON invitemDue.oitem_id = dueitem.oitem_id AND invitemDue.due_id = dueitem.due_id
LEFT JOIN #temp_ord_invoiced_item invitem ON invitem.oitem_id = oitem.oitem_id AND invitem.due_id IS NULL

) AS ordInv ON ordInv.oitem_id = iitem.oitem_id AND ordInv.due_id is NULL
LEFT JOIN (
SELECT

'PO'+dbo.pad_left(ord.ord_id_origin,6,'0') + ISNULL('-' + CONVERT(VARCHAR(128),CASE WHEN ord.ord_amendment_num = 0 THEN NULL ELSE ord.ord_amendment_num END), '') AS order_code,
CAST(oitem.oitem_id AS VARCHAR) + '_' + CAST(ISNULL(dueitem.due_id, '0') AS VARCHAR) AS pk, oitem.ord_id,
oitem.oitem_seq, oitem.oitem_label, oitem.oitem_quantity, oitem.oitem_discounted_price_entry,
CASE WHEN due.due_id IS NOT NULL
THEN oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)
ELSE NULL
END AS oitem_discounted_price_entry_due,
ISNULL(oitem.item_id, 0) AS item_id, oitem.oitem_id, oitem.oitem_delivered_in_amount,
ISNULL(oitem.oitem_quantity, 0) * (oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)) AS ordered_amount,
COALESCE(delivItemDue.ditem_quantity, delivItem.ditem_quantity, 0) AS ditem_quantity,
COALESCE(delivItemDue.ditem_total_amount, delivItem.ditem_total_amount, 0) AS delivered_amount,
COALESCE(invitemDue.iitem_quantity, invitem.iitem_quantity, 0) AS invoiced_quantity,
COALESCE(invitemDue.invoiced_amount, invitem.invoiced_amount, 0) AS invoiced_amount,
CASE WHEN oitem.oitem_delivered_in_amount = 1
THEN 1
ELSE
CASE WHEN COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0) - COALESCE(invitemDue.iitem_quantity, invitem.iitem_quantity, 0) < 0
THEN 0
ELSE COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0) - COALESCE(invitemDue.iitem_quantity, invitem.iitem_quantity, 0)
END
END AS invoiceable_quantity,

(COALESCE(
delivItemDue.ditem_total_amount,
delivItem.ditem_total_amount,
COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0)
* ( CASE WHEN delivItemDue.ditem_total_quantity IS NOT NULL THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100) END ),
0)
- COALESCE(invitemDue.invoiced_amount, invitem.invoiced_amount, 0)
)
AS invoiceable_amount,
CASE WHEN oitem.oitem_delivered_in_amount = 1
THEN

(COALESCE(
delivItemDue.ditem_total_amount,
delivItem.ditem_total_amount,
COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0)
* ( CASE WHEN delivItemDue.ditem_total_quantity IS NOT NULL THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100) END ),
0)
- COALESCE(invitemDue.invoiced_amount, invitem.invoiced_amount, 0)
)

ELSE
CASE WHEN delivItemDue.ditem_quantity IS NOT NULL
THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)
END
END AS unit_price_to_invoice,
CASE WHEN oitem.oitem_delivered_in_amount = 1
THEN
(COALESCE(
delivItemDue.ditem_total_amount,
delivItem.ditem_total_amount,
COALESCE(delivItemDue.ditem_total_quantity, delivItem.ditem_total_quantity, oitem.oitem_quantity, 0)
* ( CASE WHEN delivItemDue.ditem_total_quantity IS NOT NULL THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100) END ),
0))
ELSE
CASE WHEN delivItemDue.ditem_quantity IS NOT NULL
THEN oitem.oitem_discounted_price_entry
ELSE oitem.oitem_discounted_price_entry * (ISNULL(due.due_percent, 100)/100) * (ISNULL(due.due_index_percent, 100)/100)
END
END AS unit_price_to_credit,
due.due_id,
due.due_label_en AS due_label
FROM
#temp_ord_item AS temp_ord_item
INNER JOIN t_ord_item oitem ON oitem.oitem_id = temp_ord_item.oitem_id AND oitem.status_code <> 'del'
LEFT JOIN t_ord_order ord ON oitem.ord_id = ord.ord_id
LEFT JOIN #temp_ord_due_item dueitem ON dueitem.oitem_id = oitem.oitem_id
LEFT JOIN t_ord_due due ON due.due_id = dueitem.due_id
LEFT JOIN #temp_ord_delivered_item delivItemDue ON delivItemDue.oitem_id = dueitem.oitem_id AND delivItemDue.due_id = dueitem.due_id
LEFT JOIN #temp_ord_delivered_item delivItem ON delivItem.oitem_id = oitem.oitem_id AND delivItem.due_id IS NULL
LEFT JOIN #temp_ord_invoiced_item invitemDue ON invitemDue.oitem_id = dueitem.oitem_id AND invitemDue.due_id = dueitem.due_id
LEFT JOIN #temp_ord_invoiced_item invitem ON invitem.oitem_id = oitem.oitem_id AND invitem.due_id IS NULL

) AS ordInvDue ON ordInvDue.oitem_id = iitem.oitem_id AND ordInvDue.due_id = iitem.due_id
--*************************************************************************************************************************

LEFT JOIN x_fam_all AS sf ON sf.fam_level = iitem.fam_level
AND sf.fam_node = iitem.fam_node
WHERE 1=1 AND 1=1 AND iitem.status_code <> 'del' AND iitem.invoice_id = @invoice_id
ORDER BY CAST(iitem.iitem_id AS VARCHAR) DESC
   at ivalua.platform.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, SqlCommandTimeout sqlCommandTimeout, String commandText, SqlTransaction sqlTransaction, SqlCacheDependency& dependency, Boolean useSqlDependency, SqlParameter[] commandParameters) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\platform\SQLHelper.cs:line 1212
   at ivalua.platform.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, SqlCommandTimeout sqlCommandTimeout, String commandText, SqlParameter[] commandParameters) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\platform\SQLHelper.cs:line 1078
   at ivalua.platform.BaseUserControl.Query(String commandText, SqlConnection conn, SqlParameter[] commandParameters) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\platform\BaseUserControl_SQL.cs:line 306
   at ivalua.buyer_nyc.ord.util_ctrl.GetInvoiceItems(String invId, List`1 invItemIdList, Int32 nullLines, List`1 newLinesFromOrder, String whereClause, String delivId, String intypeCode) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\ord\util_ctrl.ascx.cs:line 1985
   at ivalua.buyer.ord.invoice_item_browse.BindGrid() in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\ord\invoice_item_browse.ascx.cs:line 913
   at ivalua.buyer.ord.invoice_item_browse.OnLoadData() in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\ord\invoice_item_browse.ascx.cs:line 272
   at ivalua.platform.BaseManageControl.InitializeControl(Call eventName) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\platform\BaseManageControl.cs:line 279
   at ivalua.platform.BaseManageControl.OnLoad(EventArgs e) in s:\net\factory\precompilation\buyer_nyc_V2_0_120_20190110_reqId_591762\buyer\platform\BaseManageControl.cs:line 233