Hi All,
Below is my procedure which is working fine when executing in Oracle. I need your help to use this in excel macro. I need a macro where it should prompt me to enter PARTNO value and execute the output in sheet2.
In below procedure I'm PARTNO been given directly, but I want this to prompt and retrieve the report for given PARTNO. How to do this in excel. Kindly help.
@SeT SERVEROUTPUT ON;
@SeT HEADING ON;
PROCEDURE SOLIXBOMExp
AS
lRet NUMBER;
lExp NUMBER;
lErrors iapitype.ref_type;
lqBomExplosionItems iapiType.Ref_Type;
ltBomExplosionItems iapiType.BomExplosionListTab_Type;
lrBomExplosionItem iapiType.BomExplosionListRec_Type;
BEGIN
lRet := iapiGeneral.SetConnection ('RAM1');
COMMIT;

SELECT bom_explosion_seq.NEXTVAL INTO lExp FROM DUAL;

lRet :=
iapiSpecificationBom.Explode (lExp,'20273485',1,'Y012', --20273485 is the part no I need to prompt when run the procedure
1,
1,
1, -- 1 = multi level, 0 = single level
TRUNC (SYSDATE),
1,
0, -- 1 = yes, 0 = no
0, -- 1 = yes, 0 = no
0, -- 1 = yes, 0 = no
IAPICONSTANT.EXPLOSION_STANDARD,
NULL,
NULL,
NULL,
NULL,
lErrors);

IF lRet = iapiConstantDbError.DBERR_SUCCESS
THEN
-- Get data.
lRet := iapiSpecificationBom.GetExplosion (lExp, lqBomExplosionItems);

IF lRet = iapiConstantDbError.DBERR_SUCCESS
THEN
-- Fetch data from cursor variable.
FETCH lqBomExplosionItems
BULK COLLECT INTO ltBomExplosionItems;

-- Number of records in result set.
DBMS_OUTPUT.put_line (
'Number of records in result set <'
|| ltBomExplosionItems.COUNT
|| '>');

-- Rows in result set.
IF (ltBomExplosionItems.COUNT > 0)
THEN
FOR lnIndex IN ltBomExplosionItems.FIRST ..
ltBomExplosionItems.LAST
LOOP
lrBomExplosionItem := ltBomExplosionItems (lnIndex);
DBMS_OUTPUT.put_line (
lrBomExplosionItem.SEQUENCE
|| CHR (9)
|| lrBomExplosionItem.BOMLEVEL
|| CHR (9)
|| lrBomExplosionItem.PARTNO
|| CHR (9)
|| lrBomExplosionItem.REVISION
|| CHR (9)
|| lrBomExplosionItem.DESCRIPTION
|| CHR (9)
|| lrBomExplosionItem.UOM
|| CHR (9)
|| lrBomExplosionItem.QUANTITY
|| CHR (9));
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.put_line ('Error: ' || iapiGeneral.GetLastErrorText ());
END IF;
END IF;
END;
/

If above cant be done kindly advice how to do the BoM explosion in Excel I'm using oracle database.
Table info to execute BoM explosion has below info
Table - BoM
Fields -
PART_NO NOT NULL VARCHAR2(18 CHAR)
REVISION NOT NULL NUMBER(4)
PLANT NOT NULL VARCHAR2(8 CHAR)
ALTERNATIVE NOT NULL NUMBER(2)
ITEM_NUMBER NOT NULL NUMBER(4)
COMPONENT_PART NOT NULL VARCHAR2(18 CHAR)
QUANTITY NUMBER(16,6)
UOM NOT NULL VARCHAR2(40 CHAR)


TIA