Hello friends,
Today I am going to tell you how to call pl/sql package from OAF page which has Table type input and output parameters.
First we will see create record and table type and then PL/SQL Package.
1) create or replace TYPE "XX_TONER_REC_TYPE" as OBJECT
( line_number number
,inventory_item_id number
,reason_code varchar2(200)
,reason_value number
,ordered_qty number
,quote_line_id number
);
create or replace TYPE "XX_TONER_TBL_TYPE" IS TABLE OF "XX_TONER_REC_TYPE";
create or replace TYPE "XX_TONER_REC_OUT_TYPE" AS OBJECT
( line_number number
,validation_flag varchar2(2)
,valid_err_msg varchar2(1000)
,new_recomm_qty number
);
create or replace TYPE "XX_TONER_TBL_OUT_TYPE" IS TABLE OF "XX_TONER_REC_OUT_TYPE";
2) create specification and body of the package
create or replace PACKAGE "XX_IBE_EXDRECQTY_RSN_VLD_PKG"
AUTHID CURRENT_USER AS
PROCEDURE xx_toner_reason_validation_p (p_source IN VARCHAR2,
p_in_quote_header_id in NUMBER,
p_Instance_id in NUMBER,
p_toner_tbl_details IN XX_TONER_TBL_TYPE,
p_toner_out_tbl OUT XX_TONER_TBL_OUT_TYPE
) ;
END XX_IBE_EXDRECQTY_RSN_VLD_PKG;
create or replace PACKAGE BODY "RAC_IBE_EXDRECQTY_RSN_VLD_PKG"
AS PROCEDURE xx_toner_reason_validation_p
(p_source IN VARCHAR2,
p_in_quote_header_id in NUMBER,
p_Instance_id in NUMBER,
p_toner_tbl_details IN xx_TONER_TBL_TYPE,
p_toner_out_tbl OUT xx_TONER_TBL_OUT_TYPE
) IS
ln_supply_rma_splcopy_control VARCHAR2(1) := fnd_profile.VALUE ('XX_SUPPLIES_RMA_SPLCPY_CONTROL');
ln_splcpy_days_back NUMBER := TO_NUMBER (fnd_profile.VALUE ('XX_SUP_LAST_ORDER_CHK_DAYS_SPLCPY'));
ln_spilled_days_back NUMBER := TO_NUMBER (fnd_profile.VALUE ('XX_SUP_LAST_ORDER_CHK_DAYS_SPILLED'));
lv_date_valid_date DATE := TO_DATE(fnd_profile.value('XX_IBE_OLD_HISTORY_VALID_DATE'));
ln_rma_err_flag VARCHAR2(2) := 'N';
ln_splcopy_err_flag VARCHAR2(2) := 'N';
ln_spilled_err_flag VARCHAR2(2) := 'N';
ln_sold_to_org_id NUMBER;
ln_rma_qty NUMBER;
ln_chk_rma_is_not_used VARCHAR2(2) := 'N';
ln_new_recom_qty NUMBER;
ln_notes_err_flag VARCHAR2(2) := 'N';
ln_out_notes_err_msg VARCHAR2(2000);
ln_indx NUMBER := 0;
ln_timeframe_cnt NUMBER := 0;
lv_timeframe VARCHAR2(40);
ln_created_by NUMBER;
ln_chk_splcpy_old VARCHAR2(2) := 'N';
ln_ordered_qty_for_rma NUMBER := 0;
ln_install_date DATE ;
lv_jtf_rma_reason VARCHAR2(2) := 'N';
BEGIN
--Initializing the collection with an appropriate constructor
p_toner_out_tbl := XX_TONER_TBL_OUT_TYPE();
ln_out_notes_err_msg := NULL;
ln_notes_err_flag := 'N';
ln_new_recom_qty := NULL;
-- Functionality to switch ON/OFF this validation using flag from profile
IF nvl(ln_supply_rma_splcopy_control,'Y') = 'Y' THEN
--Get the cust_account_id
BEGIN
SELECT cust_account_id into ln_sold_to_org_id
FROM aso_quote_headers_all
WHERE quote_header_id = p_in_quote_header_id ;
EXCEPTION
WHEN OTHERS THEN
ln_sold_to_org_id := NULL;
END;
--Get Install_date
BEGIN
SELECT INSTALL_DATE INTO ln_install_date
FROM csi_item_instances
WHERE INSTANCE_ID = p_Instance_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
ln_install_date := NULL;
END;
--Opening loop for the lines those has user entered reason notes
FOR i in 1 .. p_toner_tbl_details.count LOOP
-- RETURN reason validation start
IF p_toner_tbl_details(i).reason_code = 'RETURN'
AND p_toner_tbl_details(i).reason_value is not null THEN
--RAM is valid or not
BEGIN
SELECT sum(oohl.ORDERED_QUANTITY) into ln_rma_qty
FROM OE_ORDER_HEADERS_ALL ooha
,OE_ORDER_LINES_ALL oohl
,RCV_TRANSACTIONS rt
,OE_ORDER_LINES_ALL ool
WHERE 1=1
AND ooha.header_id = oohl.header_id
AND ooha.flow_status_code <> 'CANCELLED'
AND ooha.header_id = rt.OE_ORDER_HEADER_ID
AND rt.OE_ORDER_LINE_ID = oohl.line_id
AND ooha.ORDER_NUMBER = p_toner_tbl_details(i).reason_value -- RMA number
AND rt.SOURCE_DOCUMENT_CODE = 'RMA'
AND rt.TRANSACTION_TYPE = 'RECEIVE'
AND ooha.sold_to_org_id = ln_sold_to_org_id
--AND oohl.attribute13 = p_Instance_id
AND oohl.flow_status_code <> 'CANCELLED'
AND oohl.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND ooha.source_document_id = ool.header_id
AND ool.line_id = nvl(oohl.SOURCE_DOCUMENT_LINE_ID,oohl.REFERENCE_LINE_ID)
AND ool.inventory_item_id = oohl.inventory_item_id
AND ool.attribute13 = p_Instance_id ;
EXCEPTION
WHEN OTHERS THEN
ln_rma_qty := null;
END;
-- The RMA has not been used already in other order for the same serial item
BEGIN
SELECT 'Y'
INTO ln_chk_rma_is_not_used
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM aso_quote_headers_all aqh,
aso_quote_lines_all aql,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE aqh.quote_header_id <> p_in_quote_header_id
AND (aql.attribute6 like p_toner_tbl_details(i).reason_code||'%'||p_toner_tbl_details(i).reason_value||'%') -- RMA Number
AND aqh.quote_header_id = aql.quote_header_id
AND aql.attribute13 = p_Instance_id -- Instance_id
AND aql.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND aqh.cust_account_id = ln_sold_to_org_id
AND aql.quote_header_id <> p_in_quote_header_id
AND aql.LINE_QUOTE_PRICE = 0
--
AND ooh.ORDER_NUMBER != p_toner_tbl_details(i).reason_value -- RMA number
AND ooh.header_id = ool.header_id
AND ooh.source_document_id = aqh.quote_header_id
AND ooh.flow_status_code <> 'CANCELLED'
AND ool.flow_status_code <> 'CANCELLED'
AND ooh.sold_to_org_id = ln_sold_to_org_id
AND ool.attribute13 = aql.attribute13
AND ool.inventory_item_id = aql.inventory_item_id
AND ROWNUM = 1
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_chk_rma_is_not_used := 'N';
WHEN OTHERS THEN
ln_chk_rma_is_not_used := 'N';
END;
-- Check RMA used already for next 6 months and this execution is no longer
lv_jtf_rma_reason := 'N';
IF ln_chk_rma_is_not_used = 'Y' AND lv_date_valid_date > SYSDATE THEN
BEGIN
SELECT 'Y' into lv_jtf_rma_reason
FROM jtf_notes_b jnb,
jtf_notes_tl jtl,
fnd_lookup_values flv,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE jnb.source_object_id <> p_in_quote_header_id
AND ooh.ORDER_NUMBER != p_toner_tbl_details(i).reason_value -- RMA number
AND ooh.header_id = ool.header_id
AND ooh.flow_status_code <> 'CANCELLED'
AND ool.flow_status_code <> 'CANCELLED'
AND ooh.sold_to_org_id = ln_sold_to_org_id
AND ooh.CREATION_DATE >= SYSDATE - ln_splcpy_days_back
AND ool.attribute13 = p_Instance_id
AND ool.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND jnb.source_object_id = ooh.source_document_id
AND jnb.jtf_note_id = jtl.jtf_note_id
AND jnb.source_object_code = 'ASO_QUOTE'
AND flv.LOOKUP_TYPE = 'XX_IBE_SHOPCART_EXCEED_REASON'
AND flv.LOOKUP_CODE = 'RETURN'
AND nvl(flv.END_DATE_ACTIVE,sysdate) >= sysdate
AND (jtl.notes like '%'||flv.description||'%'||p_toner_tbl_details(i).reason_value||'%')
AND ool.unit_selling_price = 0
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_jtf_rma_reason := 'N';
WHEN OTHERS THEN
lv_jtf_rma_reason := 'N';
END;
END IF;
-- Added this logic to get total ordered qty already for entered RMA number
ln_ordered_qty_for_rma := 0;
IF ln_chk_rma_is_not_used = 'N' THEN
BEGIN
SELECT nvl(SUM (oeol.ordered_quantity),0)
INTO ln_ordered_qty_for_rma
FROM aso_quote_headers_all aqh1,
aso_quote_lines_all aql1,
oe_order_headers_all oeoh,
oe_order_lines_all oeol
WHERE aqh1.quote_header_id <> p_in_quote_header_id
AND (aql1.attribute6 like p_toner_tbl_details(i).reason_code||'%'||p_toner_tbl_details(i).reason_value||'%') -- RMA Number
AND aqh1.quote_header_id = aql1.quote_header_id
AND aql1.attribute13 = p_Instance_id -- Instance_id
AND aql1.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND aqh1.cust_account_id = ln_sold_to_org_id
AND aql1.quote_header_id <> p_in_quote_header_id
---
AND oeoh.ORDER_NUMBER != p_toner_tbl_details(i).reason_value -- RMA number
AND oeoh.source_document_id = aqh1.quote_header_id
AND oeoh.sold_to_org_id = ln_sold_to_org_id
AND oeoh.flow_status_code <> 'CANCELLED'
AND oeoh.header_id = oeol.header_id
AND oeol.flow_status_code <> 'CANCELLED'
AND oeol.attribute13 = p_Instance_id -- Instance_id
AND oeol.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND oeol.unit_selling_price = 0;
EXCEPTION
WHEN OTHERS THEN
ln_ordered_qty_for_rma := 0;
END;
END IF;
---------
IF nvl(ln_rma_qty,0) = 0 THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'RMA_INVALID';
END IF;
---------
IF (ln_chk_rma_is_not_used = 'N'
AND nvl(ln_rma_qty,0) != 0
AND nvl(ln_rma_qty,0) < p_toner_tbl_details(i).ordered_qty + ln_ordered_qty_for_rma) OR lv_jtf_rma_reason = 'Y' THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'RMA_USED_ALREADY';
ELSIF nvl(ln_rma_qty,0) != 0 AND nvl(ln_rma_qty,0) < p_toner_tbl_details(i).ordered_qty + ln_ordered_qty_for_rma THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'RMA_EXCEEDED_QTY';
END IF;
-----------
END IF;
-- RETURN reason validation END
-- When user selected Special copy/print project
-- SPLCOPY reason validation Start
IF p_toner_tbl_details(i).reason_code = 'SPLCOPY' AND p_toner_tbl_details(i).reason_value is not null THEN
IF p_source = 'TOPS' THEN -- Source IF start
-- Calculating new recomm_qty based on user entered no of copies or AMCV
BEGIN
SELECT
DECODE
(isbi.item_type,
'TONER', DECODE
(isbi.chargeable_inclusive,
'I', (ikn_sam_supplies_tool_pkg.get_allowed_qty_f
(p_toner_tbl_details(i).reason_value,
isbi.toner_yield,
isbi.component_item_id,
itii.external_reference,
nvl(ln_install_date,itii.install_date),
nvl(p_Instance_id,itii.instance_id)
))
,
NULL
),
NULL
) eid_allowed_qty INTO ln_new_recom_qty
FROM ikn_telesales_ib_info itii,
ikn_supplies_bom_info isbi
WHERE isbi.instance_id = itii.instance_id
AND itii.instance_id = p_Instance_id
AND isbi.equipment_inv_item_id = itii.inventory_item_id
AND isbi.component_item_id = p_toner_tbl_details(i).inventory_item_id
AND isbi.item_type = 'TONER'
AND parent_child_ind = 'S' ;
EXCEPTION
WHEN OTHERS THEN
ln_new_recom_qty := NULL;
END;
ELSIF p_source = 'MYRICOH' THEN -- Source else for MyRicoh
--Check if timeframe exists in XX_ibe_pricing_cart table. If yes, then it is MS user's order
BEGIN
SELECT COUNT(1)
INTO ln_timeframe_cnt
FROM XX_ibe_pricing_cart
WHERE quote_header_id = p_in_quote_header_id
AND timeframe IS NOT NULL;
EXCEPTION
WHEN OTHERS THEN
ln_timeframe_cnt := 0;
END;
IF ln_timeframe_cnt > 0 THEN
--get the timeframe
BEGIN
SELECT timeframe,
created_by
INTO lv_timeframe,
ln_created_by
FROM XX_ibe_pricing_cart
WHERE quote_header_id = p_in_quote_header_id
AND quote_line_id = p_toner_tbl_details(i).quote_line_id
AND timeframe IS NOT NULL;
EXCEPTION
WHEN OTHERS THEN
lv_timeframe := NULL;
BEGIN
SELECT CREATED_BY INTO ln_created_by
FROM aso_quote_lines_all
WHERE quote_header_id = p_in_quote_header_id
AND quote_line_id = p_toner_tbl_details(i).quote_line_id ;
EXCEPTION
WHEN OTHERS THEN
ln_created_by := NULL;
END;
END;
ln_new_recom_qty :=
XX_ibe_ms_utility_pkg.fetch_ms_rec_qty_f
(lv_timeframe,
p_toner_tbl_details(i).inventory_item_id,
p_Instance_id,
ln_created_by,
p_toner_tbl_details(i).reason_value);
ELSE -- For Non MS User
BEGIN
SELECT
DECODE
(isbi.item_type,
'TONER', DECODE
(isbi.chargeable_inclusive,
'I', (XX_ibe_supply_workflow_pkg.get_allowed_qty_for_splcopy_f
(p_toner_tbl_details(i).reason_value, --AMCV
isbi.toner_yield,
isbi.component_item_id,
--itii.external_reference,
nvl(p_Instance_id,itii.instance_id),
nvl(ln_install_date,itii.install_date)
))
,
NULL
),
NULL
) eid_allowed_qty INTO ln_new_recom_qty
FROM ikn_telesales_ib_info itii,
ikn_supplies_bom_info isbi
WHERE isbi.instance_id = itii.instance_id
AND itii.instance_id = p_Instance_id
AND isbi.equipment_inv_item_id = itii.inventory_item_id
AND isbi.component_item_id = p_toner_tbl_details(i).inventory_item_id
AND isbi.item_type = 'TONER'
AND parent_child_ind = 'S' ;
EXCEPTION
WHEN OTHERS THEN
ln_new_recom_qty := NULL;
END;
END IF;
END IF; -- Source IF end
--There is not another Special project request order of the same item in the last 6 months
BEGIN
SELECT 'Y'
INTO ln_chk_splcpy_old
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM aso_quote_headers_all aqh,
aso_quote_lines_all aql,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE aqh.quote_header_id <> p_in_quote_header_id
AND (aql.attribute6 like p_toner_tbl_details(i).reason_code||'%') -- Special Project
AND aqh.quote_header_id = aql.quote_header_id
AND aql.attribute13 = p_Instance_id -- Instance_id
AND aql.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND aql.quote_header_id <> p_in_quote_header_id
AND aqh.cust_account_id = ln_sold_to_org_id
AND aql.LINE_QUOTE_PRICE = 0
--
AND ooh.CREATION_DATE >= SYSDATE - ln_splcpy_days_back -- 6 months
AND ooh.header_id = ool.header_id
AND ooh.source_document_id = aqh.quote_header_id
AND ooh.flow_status_code <> 'CANCELLED'
AND ool.flow_status_code <> 'CANCELLED'
AND ooh.sold_to_org_id = ln_sold_to_org_id
AND ool.attribute13 = aql.attribute13
AND ool.inventory_item_id = aql.inventory_item_id
AND ROWNUM = 1
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_chk_splcpy_old := 'N';
WHEN OTHERS THEN
ln_chk_splcpy_old := 'N';
END;
---------------
IF p_toner_tbl_details(i).ordered_qty > nvl(ln_new_recom_qty,0)+1 THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'SPLCPY_EXCEEDED_QTY';
END IF;
----------------
IF ln_chk_splcpy_old = 'Y' THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'SPLCPY_ORD_EXISTS_IN_180_DAYS';
END IF;
END IF;
-- SPLCOPY reason validation End
ln_indx := ln_indx+1;
p_toner_out_tbl.EXTEND; --For next record
IF ln_notes_err_flag = 'Y' THEN
/*p_toner_out_tbl(ln_indx).line_number := p_toner_tbl_details(i).line_number;
p_toner_out_tbl(ln_indx).validation_flag := 'Y';
p_toner_out_tbl(ln_indx).valid_err_msg := TRIM('|' from ln_out_notes_err_msg);
p_toner_out_tbl(ln_indx).new_recomm_qty := ln_new_recom_qty ;*/
p_toner_out_tbl(ln_indx):=XX_TONER_REC_OUT_TYPE (
p_toner_tbl_details(i).line_number
,'Y'
,TRIM('|' from ln_out_notes_err_msg)
,NVL(ln_new_recom_qty,0) );
ELSE
/*p_toner_out_tbl(ln_indx).line_number := p_toner_tbl_details(i).line_number;
p_toner_out_tbl(ln_indx).validation_flag := 'N';
p_toner_out_tbl(ln_indx).valid_err_msg := NULL;
p_toner_out_tbl(ln_indx).new_recomm_qty := NULL ; */
p_toner_out_tbl(ln_indx):=XX_TONER_REC_OUT_TYPE (
p_toner_tbl_details(i).line_number
,'N'
,NULL
,NVL(ln_new_recom_qty,0) );
END IF;
ln_notes_err_flag := 'N';
ln_out_notes_err_msg := '';
ln_new_recom_qty := NULL;
lv_jtf_rma_reason := 'N';
END LOOP; -- Loop End
END IF;
EXCEPTION
WHEN OTHERS THEN
ln_notes_err_flag := 'N';
ln_out_notes_err_msg := '';
ln_new_recom_qty := NULL;
END XX_toner_reason_validation_p;
END XX_IBE_EXDRECQTY_RSN_VLD_PKG;
3) Now in OAF first we have to call this procedure on the event of the field and show the output parameter value in the page .
below code should be written in the controller:
if ("validateRmaSplEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM)))
{
String quoteHdrId = pageContext.getParameter("Quote_Header_Id");
String instanceId = pageContext.getParameter("EID_Model");
String rowReference = pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE);
Serializable[] params = {quoteHdrId,instanceId, rowReference };
am.invokeMethod("validateRmaSplCopyFields",params );
pageContext.writeDiagnostics(this, "after validateRmaSplEvent method", 1);
}
4) below method should be created inside AM.
public void validateRmaSplCopyFields(String quoteHdrId,
String instanceId,
String rowReference)
{
getOADBTransaction().writeDiagnostics(this," values passed quoteHdrId:"+quoteHdrId+" instanceId:"+instanceId ,1);
if( !(quoteHdrId.equals("")) && !(instanceId.equals("")))
{
String typeInToner = "APPS.RAC_TONER_REC_TYPE";
String typeTableInToner = "APPS.RAC_TONER_TBL_TYPE";
String typeTableOutToner = "APPS.RAC_TONER_TBL_OUT_TYPE";
String validationFlag = "N";
Object[] outPutdata = null;
Object[] getResults = null;
String errMsg="n";
String error="n";
Struct InclRow = null;
STRUCT[] structArr = new STRUCT[1];
Connection conn = getOADBTransaction().getJdbcConnection();
Number quoteHdrIdNum = new Number(Integer.parseInt(quoteHdrId));
Number instanceIdNum = new Number(Integer.parseInt(instanceId));
OARow row = (OARow)findRowByRef(rowReference);
if(!(row.getAttribute("RmaSplCopies").equals("")))
{
Number invItemId = new Number(Integer.parseInt(row.getAttribute("UniqueValue").toString()));
String reasonCode = (String)row.getAttribute("Recomreason");
Number reasonCodeValue = new Number(Integer.parseInt(row.getAttribute("RmaSplCopies").toString()));
Number orderedQuantity = new Number(Integer.parseInt(row.getAttribute("Quantity").toString()));
try
{
getOADBTransaction().writeDiagnostics(this,"inside try block:",1);
StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeInToner.toUpperCase(), conn);
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(typeTableInToner.toUpperCase(),conn);
structArr[0] = new STRUCT(structDescriptor, conn, new Object[] { null,
invItemId,
reasonCode,
reasonCodeValue,
orderedQuantity,
null});
ARRAY tonerInArray = new ARRAY(arrayDescriptor, conn, structArr);//IN parameter table type : RAC_TONER_TBL_TYPE
OracleCallableStatement statement =(OracleCallableStatement)conn.prepareCall("{call RAC_IBE_EXDRECQTY_RSN_VLD_PKG.rac_toner_reason_validation_p(?,?,?,?,?)}");
statement.setString(1, "TOPS");
statement.setNUMBER(2, quoteHdrIdNum);
statement.setNUMBER(3, instanceIdNum);
statement.setArray(4, tonerInArray);
statement.registerOutParameter(5, Types.ARRAY, typeTableOutToner);
getOADBTransaction().writeDiagnostics(this,"statement:"+statement.toString() ,1);
statement.execute();
getOADBTransaction().writeDiagnostics(this,"after executing the statement:" ,1);
outPutdata = (Object[])((java.sql.Array)statement.getObject(5)).getArray();
getOADBTransaction().writeDiagnostics(this,"after output data:" ,1);
for (Object tmp: outPutdata) {
InclRow = (Struct)tmp;
getOADBTransaction().writeDiagnostics(this,"after InclRow" ,1);
getResults = InclRow.getAttributes();
getOADBTransaction().writeDiagnostics(this,"after getResults:" ,1);
getOADBTransaction().writeDiagnostics(this,"Value1:"+getResults[0] ,1);
getOADBTransaction().writeDiagnostics(this,"Value2:"+getResults[1] ,1);
getOADBTransaction().writeDiagnostics(this,"Value3:"+getResults[2] ,1);
getOADBTransaction().writeDiagnostics(this,"Value4:"+getResults[3] ,1);
validationFlag=(String)getResults[1];
if(validationFlag.equals("N"))
{
row.setAttribute("NewRecQty",(new Number(getResults[3])));
}
else
{
error = (String)getResults[2];
}
InclRow = null;
getResults = null;
}
}
catch(Exception e)
{
throw new OAException("Error in validateRmaSplCopyFields method:"+e,OAException.ERROR);
}
if(validationFlag.equals("Y"))
{
if ("RMA_INVALID".equals(error))
{
errMsg = "IKNONT_RMA_INVALID_MES";
}
else if ("RMA_USED_ALREADY".equals(error))
{
errMsg = "IKNONT_RMA_USED_ALREADY_MES";
}
else if ("RMA_EXCEEDED_QTY".equals(error))
{
errMsg = "IKNONT_RMA_EXCEEDED_QTY_MES";
}
else if ("SPLCPY_ORD_EXISTS_IN_180_DAYS".equals(error))
{
errMsg = "IKNONT_SPLCOPY_ORD_EXISTS_MES";
}
else if ("SPLCPY_EXCEEDED_QTY".equals(error))
{
errMsg = "IKNONT_SPLCOPY_EXC_QTY_MES";
}
throw new OAAttrValException(OAException.TYP_VIEW_OBJECT,
"IKN_InclusiveSuppliesVO1",
row.getKey(),
"ReasonCodeVal",
row.getAttribute("RmaSplCopies").toString(),
"IKNONT",
errMsg
);
}
}
}
}
Today I am going to tell you how to call pl/sql package from OAF page which has Table type input and output parameters.
First we will see create record and table type and then PL/SQL Package.
1) create or replace TYPE "XX_TONER_REC_TYPE" as OBJECT
( line_number number
,inventory_item_id number
,reason_code varchar2(200)
,reason_value number
,ordered_qty number
,quote_line_id number
);
create or replace TYPE "XX_TONER_TBL_TYPE" IS TABLE OF "XX_TONER_REC_TYPE";
create or replace TYPE "XX_TONER_REC_OUT_TYPE" AS OBJECT
( line_number number
,validation_flag varchar2(2)
,valid_err_msg varchar2(1000)
,new_recomm_qty number
);
create or replace TYPE "XX_TONER_TBL_OUT_TYPE" IS TABLE OF "XX_TONER_REC_OUT_TYPE";
2) create specification and body of the package
create or replace PACKAGE "XX_IBE_EXDRECQTY_RSN_VLD_PKG"
AUTHID CURRENT_USER AS
PROCEDURE xx_toner_reason_validation_p (p_source IN VARCHAR2,
p_in_quote_header_id in NUMBER,
p_Instance_id in NUMBER,
p_toner_tbl_details IN XX_TONER_TBL_TYPE,
p_toner_out_tbl OUT XX_TONER_TBL_OUT_TYPE
) ;
END XX_IBE_EXDRECQTY_RSN_VLD_PKG;
create or replace PACKAGE BODY "RAC_IBE_EXDRECQTY_RSN_VLD_PKG"
AS PROCEDURE xx_toner_reason_validation_p
(p_source IN VARCHAR2,
p_in_quote_header_id in NUMBER,
p_Instance_id in NUMBER,
p_toner_tbl_details IN xx_TONER_TBL_TYPE,
p_toner_out_tbl OUT xx_TONER_TBL_OUT_TYPE
) IS
ln_supply_rma_splcopy_control VARCHAR2(1) := fnd_profile.VALUE ('XX_SUPPLIES_RMA_SPLCPY_CONTROL');
ln_splcpy_days_back NUMBER := TO_NUMBER (fnd_profile.VALUE ('XX_SUP_LAST_ORDER_CHK_DAYS_SPLCPY'));
ln_spilled_days_back NUMBER := TO_NUMBER (fnd_profile.VALUE ('XX_SUP_LAST_ORDER_CHK_DAYS_SPILLED'));
lv_date_valid_date DATE := TO_DATE(fnd_profile.value('XX_IBE_OLD_HISTORY_VALID_DATE'));
ln_rma_err_flag VARCHAR2(2) := 'N';
ln_splcopy_err_flag VARCHAR2(2) := 'N';
ln_spilled_err_flag VARCHAR2(2) := 'N';
ln_sold_to_org_id NUMBER;
ln_rma_qty NUMBER;
ln_chk_rma_is_not_used VARCHAR2(2) := 'N';
ln_new_recom_qty NUMBER;
ln_notes_err_flag VARCHAR2(2) := 'N';
ln_out_notes_err_msg VARCHAR2(2000);
ln_indx NUMBER := 0;
ln_timeframe_cnt NUMBER := 0;
lv_timeframe VARCHAR2(40);
ln_created_by NUMBER;
ln_chk_splcpy_old VARCHAR2(2) := 'N';
ln_ordered_qty_for_rma NUMBER := 0;
ln_install_date DATE ;
lv_jtf_rma_reason VARCHAR2(2) := 'N';
BEGIN
--Initializing the collection with an appropriate constructor
p_toner_out_tbl := XX_TONER_TBL_OUT_TYPE();
ln_out_notes_err_msg := NULL;
ln_notes_err_flag := 'N';
ln_new_recom_qty := NULL;
-- Functionality to switch ON/OFF this validation using flag from profile
IF nvl(ln_supply_rma_splcopy_control,'Y') = 'Y' THEN
--Get the cust_account_id
BEGIN
SELECT cust_account_id into ln_sold_to_org_id
FROM aso_quote_headers_all
WHERE quote_header_id = p_in_quote_header_id ;
EXCEPTION
WHEN OTHERS THEN
ln_sold_to_org_id := NULL;
END;
--Get Install_date
BEGIN
SELECT INSTALL_DATE INTO ln_install_date
FROM csi_item_instances
WHERE INSTANCE_ID = p_Instance_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
ln_install_date := NULL;
END;
--Opening loop for the lines those has user entered reason notes
FOR i in 1 .. p_toner_tbl_details.count LOOP
-- RETURN reason validation start
IF p_toner_tbl_details(i).reason_code = 'RETURN'
AND p_toner_tbl_details(i).reason_value is not null THEN
--RAM is valid or not
BEGIN
SELECT sum(oohl.ORDERED_QUANTITY) into ln_rma_qty
FROM OE_ORDER_HEADERS_ALL ooha
,OE_ORDER_LINES_ALL oohl
,RCV_TRANSACTIONS rt
,OE_ORDER_LINES_ALL ool
WHERE 1=1
AND ooha.header_id = oohl.header_id
AND ooha.flow_status_code <> 'CANCELLED'
AND ooha.header_id = rt.OE_ORDER_HEADER_ID
AND rt.OE_ORDER_LINE_ID = oohl.line_id
AND ooha.ORDER_NUMBER = p_toner_tbl_details(i).reason_value -- RMA number
AND rt.SOURCE_DOCUMENT_CODE = 'RMA'
AND rt.TRANSACTION_TYPE = 'RECEIVE'
AND ooha.sold_to_org_id = ln_sold_to_org_id
--AND oohl.attribute13 = p_Instance_id
AND oohl.flow_status_code <> 'CANCELLED'
AND oohl.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND ooha.source_document_id = ool.header_id
AND ool.line_id = nvl(oohl.SOURCE_DOCUMENT_LINE_ID,oohl.REFERENCE_LINE_ID)
AND ool.inventory_item_id = oohl.inventory_item_id
AND ool.attribute13 = p_Instance_id ;
EXCEPTION
WHEN OTHERS THEN
ln_rma_qty := null;
END;
-- The RMA has not been used already in other order for the same serial item
BEGIN
SELECT 'Y'
INTO ln_chk_rma_is_not_used
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM aso_quote_headers_all aqh,
aso_quote_lines_all aql,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE aqh.quote_header_id <> p_in_quote_header_id
AND (aql.attribute6 like p_toner_tbl_details(i).reason_code||'%'||p_toner_tbl_details(i).reason_value||'%') -- RMA Number
AND aqh.quote_header_id = aql.quote_header_id
AND aql.attribute13 = p_Instance_id -- Instance_id
AND aql.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND aqh.cust_account_id = ln_sold_to_org_id
AND aql.quote_header_id <> p_in_quote_header_id
AND aql.LINE_QUOTE_PRICE = 0
--
AND ooh.ORDER_NUMBER != p_toner_tbl_details(i).reason_value -- RMA number
AND ooh.header_id = ool.header_id
AND ooh.source_document_id = aqh.quote_header_id
AND ooh.flow_status_code <> 'CANCELLED'
AND ool.flow_status_code <> 'CANCELLED'
AND ooh.sold_to_org_id = ln_sold_to_org_id
AND ool.attribute13 = aql.attribute13
AND ool.inventory_item_id = aql.inventory_item_id
AND ROWNUM = 1
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_chk_rma_is_not_used := 'N';
WHEN OTHERS THEN
ln_chk_rma_is_not_used := 'N';
END;
-- Check RMA used already for next 6 months and this execution is no longer
lv_jtf_rma_reason := 'N';
IF ln_chk_rma_is_not_used = 'Y' AND lv_date_valid_date > SYSDATE THEN
BEGIN
SELECT 'Y' into lv_jtf_rma_reason
FROM jtf_notes_b jnb,
jtf_notes_tl jtl,
fnd_lookup_values flv,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE jnb.source_object_id <> p_in_quote_header_id
AND ooh.ORDER_NUMBER != p_toner_tbl_details(i).reason_value -- RMA number
AND ooh.header_id = ool.header_id
AND ooh.flow_status_code <> 'CANCELLED'
AND ool.flow_status_code <> 'CANCELLED'
AND ooh.sold_to_org_id = ln_sold_to_org_id
AND ooh.CREATION_DATE >= SYSDATE - ln_splcpy_days_back
AND ool.attribute13 = p_Instance_id
AND ool.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND jnb.source_object_id = ooh.source_document_id
AND jnb.jtf_note_id = jtl.jtf_note_id
AND jnb.source_object_code = 'ASO_QUOTE'
AND flv.LOOKUP_TYPE = 'XX_IBE_SHOPCART_EXCEED_REASON'
AND flv.LOOKUP_CODE = 'RETURN'
AND nvl(flv.END_DATE_ACTIVE,sysdate) >= sysdate
AND (jtl.notes like '%'||flv.description||'%'||p_toner_tbl_details(i).reason_value||'%')
AND ool.unit_selling_price = 0
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_jtf_rma_reason := 'N';
WHEN OTHERS THEN
lv_jtf_rma_reason := 'N';
END;
END IF;
-- Added this logic to get total ordered qty already for entered RMA number
ln_ordered_qty_for_rma := 0;
IF ln_chk_rma_is_not_used = 'N' THEN
BEGIN
SELECT nvl(SUM (oeol.ordered_quantity),0)
INTO ln_ordered_qty_for_rma
FROM aso_quote_headers_all aqh1,
aso_quote_lines_all aql1,
oe_order_headers_all oeoh,
oe_order_lines_all oeol
WHERE aqh1.quote_header_id <> p_in_quote_header_id
AND (aql1.attribute6 like p_toner_tbl_details(i).reason_code||'%'||p_toner_tbl_details(i).reason_value||'%') -- RMA Number
AND aqh1.quote_header_id = aql1.quote_header_id
AND aql1.attribute13 = p_Instance_id -- Instance_id
AND aql1.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND aqh1.cust_account_id = ln_sold_to_org_id
AND aql1.quote_header_id <> p_in_quote_header_id
---
AND oeoh.ORDER_NUMBER != p_toner_tbl_details(i).reason_value -- RMA number
AND oeoh.source_document_id = aqh1.quote_header_id
AND oeoh.sold_to_org_id = ln_sold_to_org_id
AND oeoh.flow_status_code <> 'CANCELLED'
AND oeoh.header_id = oeol.header_id
AND oeol.flow_status_code <> 'CANCELLED'
AND oeol.attribute13 = p_Instance_id -- Instance_id
AND oeol.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND oeol.unit_selling_price = 0;
EXCEPTION
WHEN OTHERS THEN
ln_ordered_qty_for_rma := 0;
END;
END IF;
---------
IF nvl(ln_rma_qty,0) = 0 THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'RMA_INVALID';
END IF;
---------
IF (ln_chk_rma_is_not_used = 'N'
AND nvl(ln_rma_qty,0) != 0
AND nvl(ln_rma_qty,0) < p_toner_tbl_details(i).ordered_qty + ln_ordered_qty_for_rma) OR lv_jtf_rma_reason = 'Y' THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'RMA_USED_ALREADY';
ELSIF nvl(ln_rma_qty,0) != 0 AND nvl(ln_rma_qty,0) < p_toner_tbl_details(i).ordered_qty + ln_ordered_qty_for_rma THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'RMA_EXCEEDED_QTY';
END IF;
-----------
END IF;
-- RETURN reason validation END
-- When user selected Special copy/print project
-- SPLCOPY reason validation Start
IF p_toner_tbl_details(i).reason_code = 'SPLCOPY' AND p_toner_tbl_details(i).reason_value is not null THEN
IF p_source = 'TOPS' THEN -- Source IF start
-- Calculating new recomm_qty based on user entered no of copies or AMCV
BEGIN
SELECT
DECODE
(isbi.item_type,
'TONER', DECODE
(isbi.chargeable_inclusive,
'I', (ikn_sam_supplies_tool_pkg.get_allowed_qty_f
(p_toner_tbl_details(i).reason_value,
isbi.toner_yield,
isbi.component_item_id,
itii.external_reference,
nvl(ln_install_date,itii.install_date),
nvl(p_Instance_id,itii.instance_id)
))
,
NULL
),
NULL
) eid_allowed_qty INTO ln_new_recom_qty
FROM ikn_telesales_ib_info itii,
ikn_supplies_bom_info isbi
WHERE isbi.instance_id = itii.instance_id
AND itii.instance_id = p_Instance_id
AND isbi.equipment_inv_item_id = itii.inventory_item_id
AND isbi.component_item_id = p_toner_tbl_details(i).inventory_item_id
AND isbi.item_type = 'TONER'
AND parent_child_ind = 'S' ;
EXCEPTION
WHEN OTHERS THEN
ln_new_recom_qty := NULL;
END;
ELSIF p_source = 'MYRICOH' THEN -- Source else for MyRicoh
--Check if timeframe exists in XX_ibe_pricing_cart table. If yes, then it is MS user's order
BEGIN
SELECT COUNT(1)
INTO ln_timeframe_cnt
FROM XX_ibe_pricing_cart
WHERE quote_header_id = p_in_quote_header_id
AND timeframe IS NOT NULL;
EXCEPTION
WHEN OTHERS THEN
ln_timeframe_cnt := 0;
END;
IF ln_timeframe_cnt > 0 THEN
--get the timeframe
BEGIN
SELECT timeframe,
created_by
INTO lv_timeframe,
ln_created_by
FROM XX_ibe_pricing_cart
WHERE quote_header_id = p_in_quote_header_id
AND quote_line_id = p_toner_tbl_details(i).quote_line_id
AND timeframe IS NOT NULL;
EXCEPTION
WHEN OTHERS THEN
lv_timeframe := NULL;
BEGIN
SELECT CREATED_BY INTO ln_created_by
FROM aso_quote_lines_all
WHERE quote_header_id = p_in_quote_header_id
AND quote_line_id = p_toner_tbl_details(i).quote_line_id ;
EXCEPTION
WHEN OTHERS THEN
ln_created_by := NULL;
END;
END;
ln_new_recom_qty :=
XX_ibe_ms_utility_pkg.fetch_ms_rec_qty_f
(lv_timeframe,
p_toner_tbl_details(i).inventory_item_id,
p_Instance_id,
ln_created_by,
p_toner_tbl_details(i).reason_value);
ELSE -- For Non MS User
BEGIN
SELECT
DECODE
(isbi.item_type,
'TONER', DECODE
(isbi.chargeable_inclusive,
'I', (XX_ibe_supply_workflow_pkg.get_allowed_qty_for_splcopy_f
(p_toner_tbl_details(i).reason_value, --AMCV
isbi.toner_yield,
isbi.component_item_id,
--itii.external_reference,
nvl(p_Instance_id,itii.instance_id),
nvl(ln_install_date,itii.install_date)
))
,
NULL
),
NULL
) eid_allowed_qty INTO ln_new_recom_qty
FROM ikn_telesales_ib_info itii,
ikn_supplies_bom_info isbi
WHERE isbi.instance_id = itii.instance_id
AND itii.instance_id = p_Instance_id
AND isbi.equipment_inv_item_id = itii.inventory_item_id
AND isbi.component_item_id = p_toner_tbl_details(i).inventory_item_id
AND isbi.item_type = 'TONER'
AND parent_child_ind = 'S' ;
EXCEPTION
WHEN OTHERS THEN
ln_new_recom_qty := NULL;
END;
END IF;
END IF; -- Source IF end
--There is not another Special project request order of the same item in the last 6 months
BEGIN
SELECT 'Y'
INTO ln_chk_splcpy_old
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM aso_quote_headers_all aqh,
aso_quote_lines_all aql,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE aqh.quote_header_id <> p_in_quote_header_id
AND (aql.attribute6 like p_toner_tbl_details(i).reason_code||'%') -- Special Project
AND aqh.quote_header_id = aql.quote_header_id
AND aql.attribute13 = p_Instance_id -- Instance_id
AND aql.inventory_item_id = p_toner_tbl_details(i).inventory_item_id
AND aql.quote_header_id <> p_in_quote_header_id
AND aqh.cust_account_id = ln_sold_to_org_id
AND aql.LINE_QUOTE_PRICE = 0
--
AND ooh.CREATION_DATE >= SYSDATE - ln_splcpy_days_back -- 6 months
AND ooh.header_id = ool.header_id
AND ooh.source_document_id = aqh.quote_header_id
AND ooh.flow_status_code <> 'CANCELLED'
AND ool.flow_status_code <> 'CANCELLED'
AND ooh.sold_to_org_id = ln_sold_to_org_id
AND ool.attribute13 = aql.attribute13
AND ool.inventory_item_id = aql.inventory_item_id
AND ROWNUM = 1
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_chk_splcpy_old := 'N';
WHEN OTHERS THEN
ln_chk_splcpy_old := 'N';
END;
---------------
IF p_toner_tbl_details(i).ordered_qty > nvl(ln_new_recom_qty,0)+1 THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'SPLCPY_EXCEEDED_QTY';
END IF;
----------------
IF ln_chk_splcpy_old = 'Y' THEN
ln_notes_err_flag := 'Y';
ln_out_notes_err_msg := 'SPLCPY_ORD_EXISTS_IN_180_DAYS';
END IF;
END IF;
-- SPLCOPY reason validation End
ln_indx := ln_indx+1;
p_toner_out_tbl.EXTEND; --For next record
IF ln_notes_err_flag = 'Y' THEN
/*p_toner_out_tbl(ln_indx).line_number := p_toner_tbl_details(i).line_number;
p_toner_out_tbl(ln_indx).validation_flag := 'Y';
p_toner_out_tbl(ln_indx).valid_err_msg := TRIM('|' from ln_out_notes_err_msg);
p_toner_out_tbl(ln_indx).new_recomm_qty := ln_new_recom_qty ;*/
p_toner_out_tbl(ln_indx):=XX_TONER_REC_OUT_TYPE (
p_toner_tbl_details(i).line_number
,'Y'
,TRIM('|' from ln_out_notes_err_msg)
,NVL(ln_new_recom_qty,0) );
ELSE
/*p_toner_out_tbl(ln_indx).line_number := p_toner_tbl_details(i).line_number;
p_toner_out_tbl(ln_indx).validation_flag := 'N';
p_toner_out_tbl(ln_indx).valid_err_msg := NULL;
p_toner_out_tbl(ln_indx).new_recomm_qty := NULL ; */
p_toner_out_tbl(ln_indx):=XX_TONER_REC_OUT_TYPE (
p_toner_tbl_details(i).line_number
,'N'
,NULL
,NVL(ln_new_recom_qty,0) );
END IF;
ln_notes_err_flag := 'N';
ln_out_notes_err_msg := '';
ln_new_recom_qty := NULL;
lv_jtf_rma_reason := 'N';
END LOOP; -- Loop End
END IF;
EXCEPTION
WHEN OTHERS THEN
ln_notes_err_flag := 'N';
ln_out_notes_err_msg := '';
ln_new_recom_qty := NULL;
END XX_toner_reason_validation_p;
END XX_IBE_EXDRECQTY_RSN_VLD_PKG;
3) Now in OAF first we have to call this procedure on the event of the field and show the output parameter value in the page .
below code should be written in the controller:
if ("validateRmaSplEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM)))
{
String quoteHdrId = pageContext.getParameter("Quote_Header_Id");
String instanceId = pageContext.getParameter("EID_Model");
String rowReference = pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE);
Serializable[] params = {quoteHdrId,instanceId, rowReference };
am.invokeMethod("validateRmaSplCopyFields",params );
pageContext.writeDiagnostics(this, "after validateRmaSplEvent method", 1);
}
4) below method should be created inside AM.
public void validateRmaSplCopyFields(String quoteHdrId,
String instanceId,
String rowReference)
{
getOADBTransaction().writeDiagnostics(this," values passed quoteHdrId:"+quoteHdrId+" instanceId:"+instanceId ,1);
if( !(quoteHdrId.equals("")) && !(instanceId.equals("")))
{
String typeInToner = "APPS.RAC_TONER_REC_TYPE";
String typeTableInToner = "APPS.RAC_TONER_TBL_TYPE";
String typeTableOutToner = "APPS.RAC_TONER_TBL_OUT_TYPE";
String validationFlag = "N";
Object[] outPutdata = null;
Object[] getResults = null;
String errMsg="n";
String error="n";
Struct InclRow = null;
STRUCT[] structArr = new STRUCT[1];
Connection conn = getOADBTransaction().getJdbcConnection();
Number quoteHdrIdNum = new Number(Integer.parseInt(quoteHdrId));
Number instanceIdNum = new Number(Integer.parseInt(instanceId));
OARow row = (OARow)findRowByRef(rowReference);
if(!(row.getAttribute("RmaSplCopies").equals("")))
{
Number invItemId = new Number(Integer.parseInt(row.getAttribute("UniqueValue").toString()));
String reasonCode = (String)row.getAttribute("Recomreason");
Number reasonCodeValue = new Number(Integer.parseInt(row.getAttribute("RmaSplCopies").toString()));
Number orderedQuantity = new Number(Integer.parseInt(row.getAttribute("Quantity").toString()));
try
{
getOADBTransaction().writeDiagnostics(this,"inside try block:",1);
StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeInToner.toUpperCase(), conn);
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(typeTableInToner.toUpperCase(),conn);
structArr[0] = new STRUCT(structDescriptor, conn, new Object[] { null,
invItemId,
reasonCode,
reasonCodeValue,
orderedQuantity,
null});
ARRAY tonerInArray = new ARRAY(arrayDescriptor, conn, structArr);//IN parameter table type : RAC_TONER_TBL_TYPE
OracleCallableStatement statement =(OracleCallableStatement)conn.prepareCall("{call RAC_IBE_EXDRECQTY_RSN_VLD_PKG.rac_toner_reason_validation_p(?,?,?,?,?)}");
statement.setString(1, "TOPS");
statement.setNUMBER(2, quoteHdrIdNum);
statement.setNUMBER(3, instanceIdNum);
statement.setArray(4, tonerInArray);
statement.registerOutParameter(5, Types.ARRAY, typeTableOutToner);
getOADBTransaction().writeDiagnostics(this,"statement:"+statement.toString() ,1);
statement.execute();
getOADBTransaction().writeDiagnostics(this,"after executing the statement:" ,1);
outPutdata = (Object[])((java.sql.Array)statement.getObject(5)).getArray();
getOADBTransaction().writeDiagnostics(this,"after output data:" ,1);
for (Object tmp: outPutdata) {
InclRow = (Struct)tmp;
getOADBTransaction().writeDiagnostics(this,"after InclRow" ,1);
getResults = InclRow.getAttributes();
getOADBTransaction().writeDiagnostics(this,"after getResults:" ,1);
getOADBTransaction().writeDiagnostics(this,"Value1:"+getResults[0] ,1);
getOADBTransaction().writeDiagnostics(this,"Value2:"+getResults[1] ,1);
getOADBTransaction().writeDiagnostics(this,"Value3:"+getResults[2] ,1);
getOADBTransaction().writeDiagnostics(this,"Value4:"+getResults[3] ,1);
validationFlag=(String)getResults[1];
if(validationFlag.equals("N"))
{
row.setAttribute("NewRecQty",(new Number(getResults[3])));
}
else
{
error = (String)getResults[2];
}
InclRow = null;
getResults = null;
}
}
catch(Exception e)
{
throw new OAException("Error in validateRmaSplCopyFields method:"+e,OAException.ERROR);
}
if(validationFlag.equals("Y"))
{
if ("RMA_INVALID".equals(error))
{
errMsg = "IKNONT_RMA_INVALID_MES";
}
else if ("RMA_USED_ALREADY".equals(error))
{
errMsg = "IKNONT_RMA_USED_ALREADY_MES";
}
else if ("RMA_EXCEEDED_QTY".equals(error))
{
errMsg = "IKNONT_RMA_EXCEEDED_QTY_MES";
}
else if ("SPLCPY_ORD_EXISTS_IN_180_DAYS".equals(error))
{
errMsg = "IKNONT_SPLCOPY_ORD_EXISTS_MES";
}
else if ("SPLCPY_EXCEEDED_QTY".equals(error))
{
errMsg = "IKNONT_SPLCOPY_EXC_QTY_MES";
}
throw new OAAttrValException(OAException.TYP_VIEW_OBJECT,
"IKN_InclusiveSuppliesVO1",
row.getKey(),
"ReasonCodeVal",
row.getAttribute("RmaSplCopies").toString(),
"IKNONT",
errMsg
);
}
}
}
}
No comments:
Post a Comment