private void fullAssociated() {
boolean associated = true;
String SQL =
"Select * "
+ "from XX_VMR_REFERENCEMATRIX "
+ "where M_product IS NULL AND XX_VMR_PO_LINEREFPROV_ID="
+ LineRefProv.get_ID();
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
associated = false;
}
rs.close();
pstmt.close();
} catch (Exception a) {
log.log(Level.SEVERE, SQL, a);
}
if (associated == true) {
String SQL10 =
"UPDATE XX_VMR_PO_LineRefProv "
+ " SET XX_ReferenceIsAssociated='Y'"
+ " WHERE XX_VMR_PO_LineRefProv_ID="
+ LineRefProv.getXX_VMR_PO_LineRefProv_ID();
DB.executeUpdate(null, SQL10);
// LineRefProv.setXX_ReferenceIsAssociated(true);
// LineRefProv.save();
int dialog = Env.getCtx().getContextAsInt("#Dialog_Associate_Aux");
if (dialog == 1) {
ADialog.info(m_WindowNo, m_frame, "MustRefresh");
Env.getCtx().remove("#Dialog_Associate_Aux");
}
} else {
String SQL10 =
"UPDATE XX_VMR_PO_LineRefProv "
+ " SET XX_ReferenceIsAssociated='N'"
+ " WHERE XX_VMR_PO_LineRefProv_ID="
+ LineRefProv.getXX_VMR_PO_LineRefProv_ID();
DB.executeUpdate(null, SQL10);
// LineRefProv.setXX_ReferenceIsAssociated(false);
// LineRefProv.save();
}
}
/** Dispose */
public void dispose() {
if (m_frame != null) m_frame.dispose();
m_frame = null;
MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);
String oS = order.getXX_OrderStatus();
String compS = order.getDocStatus();
if (!oS.equals("AN") && !compS.equals("CO")) {
if (isInMatrix()) {
fullAssociated();
} else {
String SQL10 =
"UPDATE XX_VMR_PO_LineRefProv "
+ " SET XX_ReferenceIsAssociated='N'"
+ " WHERE XX_VMR_PO_LineRefProv_ID="
+ LineRefProv.getXX_VMR_PO_LineRefProv_ID();
DB.executeUpdate(null, SQL10);
// LineRefProv.setXX_ReferenceIsAssociated(false);
// LineRefProv.save();
}
}
// pelle
String SQL =
("SELECT A.XX_REFERENCEISASSOCIATED AS ASOCIATE "
+ "FROM XX_VMR_PO_LINEREFPROV A , C_ORDER B "
+ "WHERE A.C_ORDER_ID = B.C_ORDER_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
try {
PreparedStatement pstmt = DB.prepareStatement(SQL, null);
ResultSet rs = pstmt.executeQuery();
BigDecimal cifTotal = new BigDecimal(0);
Boolean check = true;
Integer maritimo = 0;
Integer aereo = 0;
Integer terrestre = 0;
BigDecimal montoBs = new BigDecimal(0);
BigDecimal rate = new BigDecimal(0);
BigDecimal Seguro = new BigDecimal(0);
BigDecimal perfleteInt = new BigDecimal(0);
BigDecimal fleteInternac = new BigDecimal(0);
Integer productid = 0;
Integer poline = 0;
BigDecimal costoitem = new BigDecimal(0);
BigDecimal porcentitem = new BigDecimal(0);
BigDecimal cifuni = new BigDecimal(0);
BigDecimal percentarancel = new BigDecimal(0);
BigDecimal arancelitem = new BigDecimal(0);
BigDecimal rateseniat = new BigDecimal(0);
BigDecimal montoseniat = new BigDecimal(0);
BigDecimal ratetesoreria = new BigDecimal(0);
BigDecimal montotesoreria = new BigDecimal(0);
BigDecimal tasaaduana = new BigDecimal(0);
BigDecimal part1 = new BigDecimal(0);
BigDecimal part2 = new BigDecimal(0);
BigDecimal iva = new BigDecimal(0);
BigDecimal ivaactual = new BigDecimal(0);
BigDecimal part = new BigDecimal(0);
BigDecimal montesorerianac = new BigDecimal(0);
while (rs.next()) {
if (rs.getString("ASOCIATE").trim().equalsIgnoreCase("N")) {
check = false;
}
}
rs.close();
pstmt.close();
if (check == true) {
String SQL2 =
("SELECT (A.TOTALLINES * B.MULTIPLYRATE) + A.XX_INTNACESTMEDAMOUNT + A.XX_ESTEEMEDINSURANCEAMOUNT AS CIFTOTAL "
+ "FROM C_ORDER A, C_CONVERSION_RATE B "
+ "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' "
+ "AND A.AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ") "
+ "AND B.AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ")");
PreparedStatement pstmt2 = DB.prepareStatement(SQL2, null);
ResultSet rs2 = pstmt2.executeQuery();
if (rs2.next()) {
// Calculo del CIF Total
cifTotal = rs2.getBigDecimal("CIFTOTAL");
}
rs2.close();
pstmt2.close();
// Busco la via de despacho de la O/C
String SQL3 =
("SELECT XX_L_DISPATCHROUTE AS AEREO, XX_L_DISPATCHROUTEMAR AS MARITIMO, XX_L_DISPATCHROUTETER AS TERRESTRE "
+ "FROM XX_VSI_KEYNAMEINFO ");
PreparedStatement pstmt3 = DB.prepareStatement(SQL3, null);
ResultSet rs3 = pstmt3.executeQuery();
if (rs3.next()) {
maritimo = rs3.getInt("MARITIMO");
terrestre = rs3.getInt("TERRESTRE");
aereo = rs3.getInt("AEREO");
}
rs3.close();
pstmt3.close();
String SQL4 =
("SELECT (A.TOTALLINES * B.MULTIPLYRATE) AS MONTO "
+ "FROM C_ORDER A, C_CONVERSION_RATE B "
+ "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
PreparedStatement pstmt4 = DB.prepareStatement(SQL4, null);
ResultSet rs4 = pstmt4.executeQuery();
if (rs4.next()) {
// Monto de la O/C en Bs
montoBs = rs4.getBigDecimal("MONTO");
}
rs4.close();
pstmt4.close();
if (order.getXX_VLO_DispatchRoute_ID() == maritimo) {
String SQL5 =
("SELECT XX_RATE AS RATE "
+ "FROM XX_VLO_DispatchRoute "
+ "WHERE XX_VLO_DispatchRoute_ID = '"
+ maritimo
+ "' ");
PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
ResultSet rs5 = pstmt5.executeQuery();
if (rs5.next()) {
rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100));
// (new BigDecimal(100), 2, RoundingMode.HALF_UP);
// Calculo del Seguro con Via Maritimo
Seguro = montoBs.multiply(rate);
}
rs5.close();
pstmt5.close();
} else if (order.getXX_VLO_DispatchRoute_ID() == aereo) {
String SQL5 =
("SELECT XX_RATE AS RATE "
+ "FROM XX_VLO_DispatchRoute "
+ "WHERE XX_VLO_DispatchRoute_ID = '"
+ aereo
+ "' ");
PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
ResultSet rs5 = pstmt5.executeQuery();
if (rs5.next()) {
rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
// Calculo del Seguro con Via aereo
Seguro = montoBs.multiply(rate);
}
rs5.close();
pstmt5.close();
} else if (order.getXX_VLO_DispatchRoute_ID() == terrestre) {
String SQL5 =
("SELECT XX_RATE AS RATE "
+ "FROM XX_VLO_DispatchRoute "
+ "WHERE XX_VLO_DispatchRoute_ID = '"
+ terrestre
+ "' ");
PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
ResultSet rs5 = pstmt5.executeQuery();
if (rs5.next()) {
rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
// Calculo del Seguro con Via terrestre
Seguro = montoBs.multiply(rate);
}
rs5.close();
pstmt5.close();
}
// Busco el porcentaje del flete internacional segun el proveedor, puerto y pais de la O/C
String SQL6 =
("SELECT DISTINCT A.XX_INTERFREESTIMATEPERT AS PERTFLEINTER "
+ "FROM XX_VLO_COSTSPERCENT A, C_ORDER B "
+ "WHERE B.XX_VLO_ARRIVALPORT_ID = '"
+ order.getXX_VLO_ArrivalPort_ID()
+ "' "
+ "AND B.C_BPARTNER_ID = '"
+ order.getC_BPartner_ID()
+ "' "
+ "AND B.C_COUNTRY_ID = '"
+ order.getC_Country_ID()
+ "' "
+ "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
+ "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
+ "AND B.C_BPARTNER_ID = A.C_BPARTNER_ID "
+ "AND B.C_COUNTRY_ID = A.C_COUNTRY_ID ");
PreparedStatement pstmt6 = DB.prepareStatement(SQL6, null);
ResultSet rs6 = pstmt6.executeQuery();
if (rs6.next()) {
// Porcentaje del flete internacional
perfleteInt =
rs6.getBigDecimal("PERTFLEINTER")
.divide(new BigDecimal(100), 4, RoundingMode.HALF_UP);
// Flete Internacional
fleteInternac = montoBs.multiply(perfleteInt);
}
rs6.close();
pstmt6.close();
// Busco los productos que tienen asociados las Ref de la O/C
String SQL7 =
("SELECT C.M_PRODUCT AS PRODUCT, A.XX_VMR_PO_LINEREFPROV_ID AS POLINE "
+ "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C "
+ "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
+ "AND A.C_ORDER_ID = B.C_ORDER_ID "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
PreparedStatement pstmt7 = DB.prepareStatement(SQL7, null);
ResultSet rs7 = pstmt7.executeQuery();
while (rs7.next()) {
// Calculo del Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
productid = rs7.getInt("PRODUCT");
poline = rs7.getInt("POLINE");
// Costo de cada item Bs
/*String SQL8 = ("SELECT (A.XX_UNITPURCHASEPRICE / B.XX_UNITCONVERSION) AS COSTO " +
"FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_UNITCONVERSION B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D " +
"WHERE A.XX_VMR_UNITCONVERSION_ID = B.XX_VMR_UNITCONVERSION_ID " +
"AND A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID " +
"AND C.M_PRODUCT = D.M_PRODUCT_ID " +
"AND C.M_PRODUCT = '"+productid+"' " +
"AND A.XX_VMR_PO_LINEREFPROV_ID = '"+poline+"' ");*/
String SQL8 =
("SELECT (A.LINENETAMT * E.MULTIPLYRATE) AS COSTO "
+ "FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D, C_CONVERSION_RATE E, C_ORDER F "
+ "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
+ "AND C.M_PRODUCT = D.M_PRODUCT_ID "
+ "AND E.C_CONVERSION_RATE_ID = F.XX_CONVERSIONRATE_ID "
+ "AND A.C_ORDER_ID = F.C_ORDER_ID "
+ "AND C.M_PRODUCT = '"
+ productid
+ "' "
+ "AND A.XX_VMR_PO_LINEREFPROV_ID = '"
+ poline
+ "' ");
PreparedStatement pstmt8 = DB.prepareStatement(SQL8, null);
ResultSet rs8 = pstmt8.executeQuery();
if (rs8.next()) {
costoitem = rs8.getBigDecimal("COSTO");
// Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
porcentitem = (costoitem.divide(montoBs, 8, RoundingMode.HALF_UP));
// CIF Unitario = Porcentaje de cada Item * CIF total
cifuni = porcentitem.multiply(cifTotal);
// Busco Porcentaje Arancelario
String SQL9 =
("SELECT (D.XX_PERCENTAGETARIFF/100) AS PERARANCEL "
+ "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D "
+ "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
+ "AND A.C_ORDER_ID = B.C_ORDER_ID "
+ "AND C.M_PRODUCT = D.M_PRODUCT_ID "
+ "AND D.M_PRODUCT_ID = '"
+ productid
+ "' "
+ "AND A.C_ORDER_ID = '"
+ LineRefProv.getC_Order_ID()
+ "' ");
PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
ResultSet rs9 = pstmt9.executeQuery();
if (rs9.next()) {
// Porcentaje Arancelario
percentarancel = rs9.getBigDecimal("PERARANCEL");
// Arancel de cada item = CIF unitario * Porcentaje Arancelario
arancelitem = cifuni.multiply(percentarancel).add(arancelitem);
}
rs9.close();
pstmt9.close();
}
rs8.close();
pstmt8.close();
// cif total(Creo que no hace falta) t arancel item se usan abajo
} // end While rs7
rs7.close();
pstmt7.close();
String SQL9 =
("SELECT XX_RATE/100 AS RATESENIAT FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio de aduanas SENIAT' "
+ "And AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ")");
PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
ResultSet rs9 = pstmt9.executeQuery();
if (rs9.next()) {
rateseniat = rs9.getBigDecimal("RATESENIAT");
montoseniat = arancelitem.multiply(rateseniat);
String SQL10 =
("SELECT XX_RATE/100 AS RATETESORERIA FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio Aduana Tesorería' "
+ "And AD_Client_ID IN(0,"
+ Env.getCtx().getAD_Client_ID()
+ ")");
PreparedStatement pstmt10 = DB.prepareStatement(SQL10, null);
ResultSet rs10 = pstmt10.executeQuery();
if (rs10.next()) {
ratetesoreria = rs10.getBigDecimal("RATETESORERIA");
montotesoreria = arancelitem.multiply(ratetesoreria);
// Monto Tasa aduanera =monto tasa seniat + monto tasa tesoreria
tasaaduana = montoseniat.add(montotesoreria);
}
rs10.close();
pstmt10.close();
}
rs9.close();
pstmt9.close();
// Calculo del IVA = (CIF total + Impuesto de importación + tasa aduanera) *%iva actual
String SQL11 =
("SELECT MAX (VALIDFROM) AS FECHA, A.RATE/100 AS IVACT "
+ "FROM C_TAX A, C_TAXCATEGORY B "
+ "WHERE A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
+ "AND B.DESCRIPTION = 'Impuesto al Valor Agregado' "
+ "AND A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
+ "GROUP BY A.RATE ");
PreparedStatement pstmt11 = DB.prepareStatement(SQL11, null);
ResultSet rs11 = pstmt11.executeQuery();
if (rs11.next()) {
part1 = cifTotal.add(arancelitem);
part2 = part1.add(tasaaduana);
iva = part2.multiply(rs11.getBigDecimal("IVACT"));
}
rs11.close();
pstmt11.close();
// Monto tesorería Nacional = IVA + impuesto de importación + monto tasa tesoreria
part = iva.add(arancelitem);
montesorerianac = part.add(montotesoreria);
// redondeo los BigDecimal
montesorerianac = montesorerianac.setScale(2, BigDecimal.ROUND_UP);
montoseniat = montoseniat.setScale(2, BigDecimal.ROUND_UP);
String sql =
"UPDATE C_Order po"
+ " SET XX_NatTreasuryEstAmount="
+ montesorerianac
+ " , XX_SENIATESTEEMEDAMUNT="
+ montoseniat
+ " WHERE po.C_Order_ID="
+ LineRefProv.getC_Order_ID();
DB.executeUpdate(null, sql);
} // end if Check
else {
// alguna referencia no tiene producto asociado
String sql =
"UPDATE C_Order po"
+ " SET XX_NatTreasuryEstAmount="
+ 0
+ " , XX_SENIATESTEEMEDAMUNT="
+ 0
+ " WHERE po.C_Order_ID="
+ LineRefProv.getC_Order_ID();
DB.executeUpdate(null, sql);
}
} // end try
catch (Exception e) {
}
} // dispose