Item Interface

CREATE OR REPLACE PROCEDURE apps.xx_items_interface_723 (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
)
AS
v_template_id NUMBER;

v_organization_id Number;

CURSOR c1
IS
SELECT *
FROM xx_items_723;
BEGIN
FOR i IN c1
LOOP
BEGIN
SELECT template_id
INTO v_template_id
FROM mtl_item_templates
WHERE template_id = i.template_id;
Exception
when others then
DBMS_OUTPUT.PUT_LINE('v_template_id error:' || v_template_id);
IF v_template_id IS NULL
THEN
DBMS_OUTPUT.put_line ('v_template_id:' || v_template_id);
END IF;


END;
Begin

select organization_id
into v_organization_id
From Org_organization_definitions
Where ORGANIZATION_ID=i.ORGANIZATION_ID;
Exception
when others then
DBMS_OUTPUT.PUT_LINE('v_organization_id errir:' || v_organization_id);
If V_organization_id is Null
Then
DBMS_OUTPUT.put_line ('v_organization_id' || v_organization_id);
END IF;
End;




IF v_template_id IS NOT NULL and V_organization_id IS NOT NULL
AND i.set_process_id = 1
AND i.process_flag = 1
THEN
INSERT INTO mtl_system_items_interface
(process_flag, transaction_type, set_process_id,
organization_id, segment1, description,
template_id
)
VALUES (i.process_flag, i.transaction_type, i.set_process_id,
i.organization_id, i.segment1, i.description,
i.template_id
);
ELSE
DBMS_OUTPUT.put_line ('I.SET_PROCESS_ID' || i.set_process_id);
DBMS_OUTPUT.put_line ('I.PROCESS_FLAG' || i.process_flag);
END IF;
END LOOP;

DBMS_OUTPUT.put_line ('FILES LOADED');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('sqlerrm' || SQLERRM);
END;
/
select * from mtl_system_items_interface where trunc(creation_date)=trunc(sysdate);




DECLARE
ERRBUF VARCHAR2(200);
RETCODE VARCHAR2(200);

BEGIN
ERRBUF := NULL;
RETCODE := NULL;

APPS.XX_ITEMS_INTERFACE_723 ( ERRBUF, RETCODE );
COMMIT;
END;

select * from mtl_system_items_interface where trunc(creation_date)=trunc(sysdate);

delete mtl_system_items_interface;