Hi,
where i am running below mentioned package i am getting error message invalid column index but not getting where exactly error is
any help to identified this .
how to identify on which row column index is missing as it is not gibing any row num of column in error message'
/*===========================================================================+
| Copyright (c) 1995 Oracle Corporation Belmont, California, USA |
| All rights reserved |
+===========================================================================+
| |
| FILENAME |
| OPIOBIABOMB.pls |
| |
| DESCRIPTION |
| This stored package is used by OBIA to explode BOM structure |
| Version 1.0 for EBS 12.x. Sept 2009 |
| |
| PUBLIC PROCEDURES |
| OBIA_SP_CALL |
| |
| PUBLIC FUNCTIONS |
| <None> |
| |
| PRIVATE PROCEDURES |
| <None> |
| |
| PRIVATE FUNCTIONS |
| <None> |
| |
| HISTORY |
| 02/09/2009 PRNEDUMK creation |
+===========================================================================*/
SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
CREATE OR REPLACE PACKAGE "APPS"."OPI_OBIA_BOMPEXPL_WRAPPER_PKG" as
PROCEDURE OBIA_SP_CALL
(
commit_point IN NUMBER DEFAULT 5000,
verify_flag IN NUMBER DEFAULT 0,
order_by IN NUMBER DEFAULT 1,
levels_to_explode IN NUMBER DEFAULT 10,
bom_or_eng IN NUMBER DEFAULT 1,
impl_flag IN NUMBER DEFAULT 1,
plan_factor_flag IN NUMBER DEFAULT 2,
explode_option IN NUMBER DEFAULT 2,
module IN NUMBER DEFAULT 2,
cst_type_id IN NUMBER DEFAULT 0,
std_comp_flag IN NUMBER DEFAULT 0,
expl_qty IN NUMBER DEFAULT 1,
comp_code IN VARCHAR2 DEFAULT '',
unit_number IN VARCHAR2 DEFAULT '',
release_option IN NUMBER DEFAULT 0,
SP_CALL_RESULT OUT NOCOPY VARCHAR2
);
END OPI_OBIA_BOMPEXPL_WRAPPER_PKG;
/
CREATE OR REPLACE PACKAGE BODY OPI_OBIA_BOMPEXPL_WRAPPER_PKG as
PROCEDURE OBIA_SP_CALL(commit_point IN NUMBER DEFAULT 5000,
verify_flag IN NUMBER DEFAULT 0,
order_by IN NUMBER DEFAULT 1,
levels_to_explode IN NUMBER DEFAULT 10,
bom_or_eng IN NUMBER DEFAULT 1,
impl_flag IN NUMBER DEFAULT 1,
plan_factor_flag IN NUMBER DEFAULT 2,
explode_option IN NUMBER DEFAULT 2,
module IN NUMBER DEFAULT 2,
cst_type_id IN NUMBER DEFAULT 0,
std_comp_flag IN NUMBER DEFAULT 0,
expl_qty IN NUMBER DEFAULT 1,
comp_code IN VARCHAR2 DEFAULT '',
unit_number IN VARCHAR2 DEFAULT '',
release_option IN NUMBER DEFAULT 0,
SP_CALL_RESULT OUT NOCOPY VARCHAR2) AS
UPD_ERR_MSG VARCHAR2(200);
UPD_ERROR_CODE NUMBER;
v_GROUP_ID NUMBER;
count_error NUMBER default 0;
count_Total NUMBER default 0;
count_NoExp NUMBER default 0;
commit_cnt NUMBER default 0;
TYPE TYP_BOM_NUMBER IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.BOM_NUMBER%TYPE;
TYPE TYP_INVENTORY_ORG_ID IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.INVENTORY_ORG_ID%TYPE;
TYPE TYP_ALT_BOM_DESIGNATOR IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.ALTERNATE_BOM_DESIGNATOR%TYPE;
TYPE TYP_PRODUCT_ID IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.PRODUCT_ID%TYPE;
TYPE TYP_EXPLOSION_DT IS TABLE OF VARCHAR2(50);
TYPE TYP_EXPLOSION_MSG IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.EXPLOSION_MSG%TYPE;
TYPE TYP_EXPLOSION_CODE IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.EXPLOSION_CODE%TYPE;
TYPE TYP_GROUP_ID IS TABLE OF OPI_OBIA_W_BOM_HEADER_DS.GROUP_ID%TYPE;
LV_BOM_NUMBER TYP_BOM_NUMBER;
LV_INVENTORY_ORG_ID TYP_INVENTORY_ORG_ID;
LV_ALT_BOM_DESIGNATOR TYP_ALT_BOM_DESIGNATOR;
LV_PRODUCT_ID TYP_PRODUCT_ID;
LV_EXPLOSION_DT TYP_EXPLOSION_DT;
LV_EXPLOSION_MSG TYP_EXPLOSION_MSG := TYP_EXPLOSION_MSG();
LV_EXPLOSION_CODE TYP_EXPLOSION_CODE := TYP_EXPLOSION_CODE();
LV_GROUP_ID TYP_GROUP_ID := TYP_GROUP_ID();
BEGIN
execute immediate 'Truncate table OPI.OPI_OBIA_BOM_EXPLOSION';
execute immediate 'Truncate table bom.BOM_EXPLOSION_TEMP';
SELECT BOM_NUMBER,
INVENTORY_ORG_ID,
ALTERNATE_BOM_DESIGNATOR,
PRODUCT_ID,
TO_CHAR(EXPLOSION_DT, 'YYYY/MM/DD HH24:MI:SS')
BULK COLLECT INTO LV_BOM_NUMBER,LV_INVENTORY_ORG_ID,LV_ALT_BOM_DESIGNATOR,LV_PRODUCT_ID,LV_EXPLOSION_DT
FROM OPI.OPI_OBIA_W_BOM_HEADER_DS
WHERE ( EXPLOSION_CODE IS NULL OR EXPLOSION_CODE <> 0 ) AND PARTITION_ID >= 0;
IF LV_BOM_NUMBER.count <>0 THEN
LV_EXPLOSION_MSG.extend(LV_BOM_NUMBER.count);
LV_EXPLOSION_CODE.extend(LV_BOM_NUMBER.count);
LV_GROUP_ID.extend(LV_BOM_NUMBER.count);
END IF;
commit_cnt:=0;
For cnt in 1..LV_BOM_NUMBER.count loop
select OPI_OBIA_BOM_EXPLOSION_S.nextval into v_group_id from dual;
BOMPEXPL.EXPLODER_USEREXIT (
verify_flag,
LV_INVENTORY_ORG_ID(cnt),
order_by,
v_GROUP_ID,
v_GROUP_ID,
levels_to_explode ,
bom_or_eng ,
impl_flag ,
plan_factor_flag ,
explode_option ,
module ,
cst_type_id ,
std_comp_flag ,
expl_qty ,
LV_PRODUCT_ID(cnt),
LV_ALT_BOM_DESIGNATOR(cnt),
comp_code ,
LV_EXPLOSION_DT(cnt),
unit_number,
release_option,
UPD_ERR_MSG ,
UPD_ERROR_CODE );
LV_EXPLOSION_MSG(cnt) := UPD_ERR_MSG;
LV_EXPLOSION_CODE(cnt) := UPD_ERROR_CODE;
LV_GROUP_ID(cnt) := v_GROUP_ID;
count_total := count_total+1;
if UPD_ERROR_CODE is null then count_noExp:=Count_noexp+1;
elsif UPD_ERROR_CODE !=0 then count_error:=count_error+1;
end if;
if commit_cnt = commit_point then
commit_cnt:=0;
insert /*+ APPEND */ into OPI.OPI_OBIA_BOM_EXPLOSION select * from BOM_EXPLOSION_TEMP;
execute immediate 'Truncate table bom.BOM_EXPLOSION_TEMP';
commit;
else commit_cnt := commit_cnt+1;
end if;
end loop;
insert into OPI.OPI_OBIA_BOM_EXPLOSION select * from BOM_EXPLOSION_TEMP;
commit;
IF LV_BOM_NUMBER.count <>0 THEN
FORALL cnt in 1..LV_BOM_NUMBER.count
update OPI_OBIA_W_BOM_HEADER_DS set EXPLOSION_MSG = LV_EXPLOSION_MSG(cnt) ,
EXPLOSION_CODE = LV_EXPLOSION_CODE(cnt),
group_id = LV_GROUP_ID(cnt)
where integration_id=LV_BOM_NUMBER(cnt);
END IF;
commit;
SP_CALL_RESULT := 'Total Bom for explosion = '||count_total||'; Number of Bom NOT exploded = '||count_noExp||'; Number of BOM explosion failed = '||count_error;
END OBIA_SP_CALL;
END OPI_OBIA_BOMPEXPL_WRAPPER_PKG;
/
COMMIT;
Exit;
output
Connecting to the database apps.
Invalid column index
Process exited.
Disconnecting from the database apps.