Redundant Sql While Looping Through Order Line Items Using Escipt

(Doc ID 2325144.1)

Last updated on NOVEMBER 06, 2017

Applies to:

Siebel CRM - Version 16.7 [IP2016] and later
Information in this document applies to any platform.

Symptoms

On : 16.7 [IP2016] version, Order Management

ACTUAL BEHAVIOR
---------------
redundant sql while looping through order line items using escript


ObjMgrSqlCursorLog Trace 5 00000dd759f22668:0 2017-10-27 16:11:23 SQL Cursor with ID 1CBEAA10 Buscomp: Order Entry - Line Items, Forward Only: 0, Fetch Rows: 100, Fetch All Time: 0.106 seconds.
ObjMgrSqlCursorLog Trace 5 00000dd759f22668:0 2017-10-27 16:11:23 SQL Cursor with ID 1CBE82B0 Buscomp: Order Entry - Line Items, Forward Only: 0, Fetch Rows: 100, Fetch All Time: 0.096 seconds.
ObjMgrSqlCursorLog Trace 5 00000dd759f22668:0 2017-10-27 16:11:24 SQL Cursor with ID 1CBE8088 Buscomp: Order Entry - Line Items, Forward Only: 0, Fetch Rows: 100, Fetch All Time: 0.090 seconds.
ObjMgrSqlCursorLog Trace 5 00000dd759f22668:0 2017-10-27 16:11:24 SQL Cursor with ID 1CBE6D20 Buscomp: Order Entry - Line Items, Forward Only: 0, Fetch Rows: 100, Fetch All Time: 0.103 seconds.
ObjMgrSqlCursorLog Trace 5 00000dd759f22668:0 2017-10-27 16:11:24 SQL Cursor with ID 1CBE6F48 Buscomp: Order Entry - Line Items, Forward Only: 0, Fetch Rows: 100, Fetch All Time: 0.107 seconds.
ObjMgrSqlCursorLog Trace 5 00000dd759f22668:0 2017-10-27 16:11:24 SQL Cursor with ID 1CBE77E8 Buscomp: Order Entry - Line Items, Forward Only: 0, Fetch Rows: 100, Fetch All Time: 0.101 seconds.
 

EXPECTED BEHAVIOR
-----------------------
expecting only one sql statement to fetch line items in while loop

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Introduce button in line item list applet(CalcTax)
2. Write following code behind this button to calculate tax for all the line items
function JobCardCalcTax(Inputs,Outputs)
{
var VATAmt : Number;
var AddVATAmt : Number;
var CSTAmt : Number;
var SurchargeAmt : Number;
var NetPrice : Number;
var AddDiscAmt : Number;
var SchemeDiscAmt : Number;
var RetailDiscountAmt : Number;
var DealerDiscountAmt : Number;
var BaseDiscountAmt : Number;
var CashDiscountAmt : Number;
var TotDiscountAmt : Number;
var TotTaxAmt : Number;
var InsTotalTax : Number;
var SGSTAmt : Number;
var CGSTAmt : Number;
var IGSTAmt : Number;
var InsSGSTAmt : Number;
var InsCGSTAmt : Number;
var InsIGSTAmt : Number;
var isRecord;
var ExtNetPrice;
var InsAmt;
var pId = Inputs.GetProperty("Order Id");
var BC = Inputs.GetProperty("BC");
var SaleType = Inputs.GetProperty("Sale Type");
var InsSaleType = Inputs.GetProperty("Ins Sale Type");
with (BC)
{
ClearToQuery();
SetSearchSpec("Order Header Id",pId);
ExecuteQuery(0);
isRecord = FirstRecord();

while(isRecord)
{
/*if(this.GetFieldValue("Calc Customer") != "") ***Removed for Line Total***
{

ExtNetPrice = ToNumber(this.GetFieldValue("Calc Customer"));
AddDiscAmt = (ExtNetPrice/100)* ToNumber(this.GetFieldValue("DICV Add Discount"));
this.SetFieldValue("DICV Add Discount Amt",AddDiscAmt.toFixed(6));
SchemeDiscAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV Scheme Discount"));
this.SetFieldValue("DICV Scheme Discount Amt",SchemeDiscAmt.toFixed(6));

BaseDiscountAmt = (ExtNetPrice/100)* ToNumber(this.GetFieldValue("DICV Base Discount"));
this.SetFieldValue("DICV Base Discount Amt",BaseDiscountAmt.toFixed(6));
CashDiscountAmt = (ExtNetPrice/100)* ToNumber(this.GetFieldValue("DICV Cash Discount"));
this.SetFieldValue("DICV Cash Discount Amt",CashDiscountAmt.toFixed(6));


TotDiscountAmt = AddDiscAmt + SchemeDiscAmt + CashDiscountAmt + BaseDiscountAmt;
this.SetFieldValue("DICV Total Discount Amt",TotDiscountAmt.toFixed(6));
ExtNetPrice = ExtNetPrice - (AddDiscAmt + SchemeDiscAmt + CashDiscountAmt + BaseDiscountAmt);

VATAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV Vat"));
this.SetFieldValue("DICV Vat Amt",VATAmt.toFixed(6));
AddVATAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV Add Vat"));
this.SetFieldValue("DICV Add Vat Amt",AddVATAmt.toFixed(6));
SurchargeAmt = ((VATAmt + AddVATAmt)/100)* ToNumber(this.GetFieldValue("DICV Surchg Vat"));
this.SetFieldValue("DICV Surchg Vat Amt",SurchargeAmt.toFixed(6));

TotTaxAmt = VATAmt + AddVATAmt + SurchargeAmt;
this.SetFieldValue("DICV Total Tax Amt",TotTaxAmt.toFixed(6));
ExtNetPrice = ExtNetPrice + (VATAmt + AddVATAmt + SurchargeAmt);
this.SetFieldValue("Net Price",ExtNetPrice.toFixed(6));
isRecord = NextRecord();
ExtNetPrice = 0;
TotDiscountAmt = 0;
TotTaxAmt = 0;
/* }
else
{
isRecord = NextRecord(); ***Removed for Line Total***
}
}*/

   this.ActivateField("Calc Customer");
this.ActivateField("Calc Dealer");
if(this.GetFieldValue("Bill To")== "Dealer - FOC")
{
ExtNetPrice = ToNumber(this.GetFieldValue("Calc Dealer"));
}
else
{
ExtNetPrice = ToNumber(this.GetFieldValue("Calc Customer"));
InsAmt = ToNumber(this.GetFieldValue("Calc Insurance"));
}

this.ActivateField("DICV Add Discount");
AddDiscAmt = (ExtNetPrice/100)* ToNumber(this.GetFieldValue("DICV Add Discount"));
this.ActivateField("DICV Add Discount Amt");
this.SetFieldValue("DICV Add Discount Amt",AddDiscAmt.toFixed(6));

this.ActivateField("DICV Scheme Discount");
SchemeDiscAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV Scheme Discount"));
this.ActivateField("DICV Scheme Discount Amt");
this.SetFieldValue("DICV Scheme Discount Amt",SchemeDiscAmt.toFixed(6));

this.ActivateField("DICV Base Discount");
BaseDiscountAmt = (ExtNetPrice/100)* ToNumber(this.GetFieldValue("DICV Base Discount"));
this.ActivateField("DICV Base Discount Amt");
this.SetFieldValue("DICV Base Discount Amt",BaseDiscountAmt.toFixed(6));

this.ActivateField("DICV Cash Discount");
CashDiscountAmt = (ExtNetPrice/100)* ToNumber(this.GetFieldValue("DICV Cash Discount"));
this.ActivateField("DICV Cash Discount Amt");
this.SetFieldValue("DICV Cash Discount Amt",CashDiscountAmt.toFixed(6));

TotDiscountAmt = AddDiscAmt + SchemeDiscAmt + CashDiscountAmt + BaseDiscountAmt;
this.ActivateField("DICV Total Discount Amt");

this.SetFieldValue("DICV Total Discount Amt",TotDiscountAmt.toFixed(6));
ExtNetPrice = ExtNetPrice - (AddDiscAmt + SchemeDiscAmt + CashDiscountAmt + BaseDiscountAmt);
if(SaleType == "VAT")
{
this.ActivateField("DICV Vat");
VATAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV Vat"));
this.ActivateField("DICV Vat Amt");
this.SetFieldValue("DICV Vat Amt",VATAmt.toFixed(6));
this.ActivateField("DICV Add Vat");
AddVATAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV Add Vat"));
this.ActivateField("DICV Add Vat Amt");
this.SetFieldValue("DICV Add Vat Amt",AddVATAmt.toFixed(6));
this.ActivateField("DICV Surchg Vat");
SurchargeAmt = ((VATAmt + AddVATAmt)/100)* ToNumber(this.GetFieldValue("DICV Surchg Vat"));
this.ActivateField("DICV Surchg Vat Amt");
this.SetFieldValue("DICV Surchg Vat Amt",SurchargeAmt.toFixed(6));
TotTaxAmt = VATAmt + AddVATAmt + SurchargeAmt;
this.ActivateField("DICV Total Tax Amt");
this.SetFieldValue("DICV Total Tax Amt",TotTaxAmt.toFixed(6));
this.ActivateField("DICV CST Amt");
this.SetFieldValue("DICV CST Amt", 0);
}


if(SaleType == "C-Form")
{
this.ActivateField("DICV CST");
CSTAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV CST"));
this.ActivateField("DICV CST Amt");
this.SetFieldValue("DICV CST Amt",CSTAmt.toFixed(6));
TotTaxAmt = CSTAmt;
this.ActivateField("DICV Total Tax Amt");
this.SetFieldValue("DICV Total Tax Amt",TotTaxAmt.toFixed(6));
this.ActivateField("DICV Vat Amt");
this.SetFieldValue("DICV Vat Amt", 0);
this.ActivateField("DICV Add Vat Amt");
this.SetFieldValue("DICV Add Vat Amt", 0);
this.ActivateField("DICV Surchg Vat Amt");
this.SetFieldValue("DICV Surchg Vat Amt", 0);


}
if(SaleType == "SGST/CGST")
{
this.ActivateField("DICV SGST");
this.ActivateField("DICV CGST");
var sSGST = this.GetFieldValue("DICV SGST");
var sCGST = this.GetFieldValue("DICV CGST");
if(sSGST <= 0 || sCGST <= 0)
{
  TheApplication().RaiseErrorText("Please check the SGST/CGST Tax %");
}
SGSTAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV SGST"));
this.ActivateField("DICV SGST Amt");
this.SetFieldValue("DICV SGST Amt",SGSTAmt.toFixed(6));
this.ActivateField("DICV CGST");
CGSTAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV CGST"));
this.ActivateField("DICV CGST Amt");
this.SetFieldValue("DICV CGST Amt",CGSTAmt.toFixed(6));

TotTaxAmt = SGSTAmt + CGSTAmt;
this.ActivateField("DICV Total Tax Amt");
this.SetFieldValue("DICV Total Tax Amt",TotTaxAmt.toFixed(6));
this.ActivateField("DICV IGST");
this.SetFieldValue("DICV IGST", 0);
this.ActivateField("DICV IGST Amt");
this.SetFieldValue("DICV IGST Amt", 0);
this.SetFieldValue("DICV Vat Amt", 0);
this.ActivateField("DICV Add Vat Amt");
this.SetFieldValue("DICV Add Vat Amt", 0);
this.ActivateField("DICV Surchg Vat Amt");
this.SetFieldValue("DICV Surchg Vat Amt", 0);
this.ActivateField("DICV CST Amt");
this.SetFieldValue("DICV CST Amt", 0);
}
if(SaleType == "IGST") //HKUNDA:Added for GST
{
this.ActivateField("DICV IGST");
var sIGST = this.GetFieldValue("DICV IGST");
if(sIGST <= 0)
{
  TheApplication().RaiseErrorText("Please check the IGST Tax %");
}
IGSTAmt = (ExtNetPrice/100) * ToNumber(this.GetFieldValue("DICV IGST"));
this.ActivateField("DICV IGST Amt");
this.SetFieldValue("DICV IGST Amt",IGSTAmt.toFixed(6));
TotTaxAmt = IGSTAmt;
this.ActivateField("DICV Total Tax Amt");
this.SetFieldValue("DICV Total Tax Amt",TotTaxAmt.toFixed(6));
this.ActivateField("DICV SGST");
this.SetFieldValue("DICV SGST", 0);
this.ActivateField("DICV CGST");
this.SetFieldValue("DICV CGST", 0);
this.ActivateField("DICV SGST Amt");
this.SetFieldValue("DICV SGST Amt", 0);
this.ActivateField("DICV CGST Amt");
this.SetFieldValue("DICV CGST Amt", 0);
this.SetFieldValue("DICV Vat Amt", 0);
this.ActivateField("DICV Add Vat Amt");
this.SetFieldValue("DICV Add Vat Amt", 0);
this.ActivateField("DICV Surchg Vat Amt");
this.SetFieldValue("DICV Surchg Vat Amt", 0);
this.ActivateField("DICV CST Amt");
this.SetFieldValue("DICV CST Amt", 0);

}
ExtNetPrice = ExtNetPrice + TotTaxAmt;
this.ActivateField("Net Price");
this.SetFieldValue("Net Price",ExtNetPrice.toFixed(6));



if(InsSaleType == "SGST/CGST")
{
this.ActivateField("DICV CGST Insurer");
this.ActivateField("DICV SGST Insurer");
var InsSGST = this.GetFieldValue("DICV CGST Insurer");
var InsCGST = this.GetFieldValue("DICV SGST Insurer");
if(InsSGST == "" || InsSGST == null || InsCGST == "" || InsCGST == null)
{
  TheApplication().RaiseErrorText("Please check the SGST/CGST Tax %");
}
InsSGSTAmt = (InsAmt/100) * ToNumber(this.GetFieldValue("DICV SGST Insurer"));
//InsSGSTAmt = InsSGSTAmt.toFixed(6);
this.SetFieldValue("DICV SGST Amt Insurer",InsSGSTAmt.toFixed(6));


InsCGSTAmt = (InsAmt/100) * ToNumber(this.GetFieldValue("DICV CGST Insurer"));
//InsCGSTAmt = InsCGSTAmt.toFixed(6);
this.SetFieldValue("DICV CGST Amt Insurer",InsCGSTAmt.toFixed(6));

InsTotalTax = InsSGSTAmt + InsCGSTAmt;
this.ActivateField("DICV Total Tax Amt Insurer");
this.SetFieldValue("DICV Total Tax Amt Insurer",InsTotalTax.toFixed(6));
this.ActivateField("DICV IGST Insurer");
this.SetFieldValue("DICV IGST Insurer", 0);
this.ActivateField("DICV IGST Amt Insurer");
this.SetFieldValue("DICV IGST Amt Insurer", 0);
this.WriteRecord();
}

if(InsSaleType == "IGST")
{
this.ActivateField("DICV IGST Insurer");
var InsIGST = this.GetFieldValue("DICV IGST Insurer");
if(InsIGST == "" || InsIGST == null)
{
  TheApplication().RaiseErrorText("Please check the IGST Tax %");
}
InsIGSTAmt = (InsAmt/100) * ToNumber(this.GetFieldValue("DICV IGST Insurer"));
this.ActivateField("DICV IGST Amt Insurer");
this.SetFieldValue("DICV IGST Amt Insurer",InsIGSTAmt.toFixed(6));
InsTotalTax = InsIGSTAmt;
this.ActivateField("DICV Total Tax Amt Insurer");
this.SetFieldValue("DICV Total Tax Amt Insurer",InsTotalTax.toFixed(6));
this.ActivateField("DICV CGST Insurer");
this.SetFieldValue("DICV CGST Insurer", 0);
this.ActivateField("DICV SGST Insurer");
this.SetFieldValue("DICV SGST Insurer", 0);
this.ActivateField("DICV CGST Amt Insurer");
this.SetFieldValue("DICV CGST Amt Insurer", 0);
this.ActivateField("DICV SGST Amt Insurer");
this.SetFieldValue("DICV SGST Amt Insurer", 0);
this.WriteRecord();
}
InsAmt = InsAmt + InsTotalTax;
ActivateField("DICV Net Price Insurer");
SetFieldValue("DICV Net Price Insurer",InsAmt.toFixed(6));
isRecord = NextRecord();
ExtNetPrice = 0;
TotDiscountAmt = 0;
TotTaxAmt = 0;
   }

  
  

}
3. Compile thee changes
4. click on CalcTax button and see the number fo queries fired on S_ORDER_ITEM table behind this button click

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot .

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms