Script to create "Short Text" for Item master,Bill of Material,Routing

CREATE OR REPLACE PROCEDURE APPS.CLC_SHORT_TEXT_ATTAH_PRG (
   errbuf    OUT VARCHAR2,
   retcode   OUT NUMBER
)
AS

l_doc_category_id NUMBER;
l_document_id NUMBER;
l_attached_document_id NUMBER;
l_media_id NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
l_organization_id org_organization_definitions.organization_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_err     varchar2(4000);
l_entity_name  varchar2(4000);
l_seq_no Varchar2 (100);


cursor c1 is
select * /*regexp_replace(ENTITY_NAME, '(^[[:cntrl:]^\t]+)|([[:cntrl:]^\t]+$)',null)  ENTITY_NAME,
ORGANIZATION_CODE,
ITEM,
CATEGORY,
SEQ,DOC_DESC,
DOC_SHORT_TEXT,DOC_SHORT_TEXT,
PROCESS_FLAG,
ERROR_MESSAGE,TRANS_TYPE,
TITLE*/
 from CLC_SHORT_TEXT_ATTACHMENTS
where PROCESS_FLAG ='P';

BEGIN

--dbms_output.put_line ('prgoram start');
FND_FILE.PUT_LINE (FND_FILE.LOG,'Starting of CLC_SHORT_TEXT_ATTAH_PRG Procedure');
-- Select User_id

   FND_FILE.PUT_LINE (FND_FILE.LOG, 'Initializing Apps');
   FND_GLOBAL.Apps_Initialize (FND_GLOBAL.USER_ID,
                               FND_GLOBAL.RESP_ID,
                               FND_GLOBAL.RESP_APPL_ID);

   FND_FILE.PUT_LINE (FND_FILE.LOG, 'FND_GLOBAL.USER_ID'||FND_GLOBAL.USER_ID);
   FND_FILE.PUT_LINE (FND_FILE.LOG, 'FND_GLOBAL.RESP_ID'||FND_GLOBAL.RESP_ID);
   FND_FILE.PUT_LINE (FND_FILE.LOG, 'FND_GLOBAL.RESP_APPL_ID'||FND_GLOBAL.RESP_APPL_ID);
for  c1_rec in c1
loop

begin
l_organization_id := null;
l_inventory_item_id := null;

SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_id =FND_GLOBAL.USER_ID;


FND_FILE.PUT_LINE (FND_FILE.LOG,'l_fnd_user_id'|| l_fnd_user_id);
--dbms_output.put_line ('l_fnd_user_id'|| l_fnd_user_id);
-- Get Data type id for Short Text types of attachments

SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE name ='SHORT_TEXT'
and LANGUAGE = USERENV('LANG');

FND_FILE.PUT_LINE (FND_FILE.LOG,'l_short_datatype_id'|| l_short_datatype_id);
--dbms_output.put_line ('l_short_datatype_id'|| l_short_datatype_id);



-- Select Category id for "Vendor/To Supplier" Attachments
SELECT category_id
INTO l_doc_category_id
FROM apps.fnd_document_categories
WHERE name = c1_rec.CATEGORY;--'Item Internal';

FND_FILE.PUT_LINE (FND_FILE.LOG,'l_doc_category_id'|| l_doc_category_id);
--dbms_output.put_line ('l_doc_category_id'|| l_doc_category_id);

-- Select nexvalues of document id, attached document id and
-- l_media_id
/*SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL,
apps.fnd_documents_short_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id,
l_media_id
FROM DUAL;*/

l_document_id :=NULL;
l_attached_document_id  :=NULL;
l_media_id  :=NULL;

/*select max(DOCUMENT_ID)+1
into
l_document_id
from apps.fnd_documents;

select max(ATTACHED_DOCUMENT_ID)+1
into
l_attached_document_id
from apps.fnd_attached_documents;


select max(MEDIA_ID)+1
into
l_media_id
from apps.fnd_documents_short_text;*/

SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL,
apps.fnd_documents_short_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id,
l_media_id
FROM DUAL;




FND_FILE.PUT_LINE (FND_FILE.LOG,'l_document_id-l_attached_document_id-l_media_id'||'-'|| l_document_id ||'-'||l_attached_document_id ||'-'||l_media_id);
--dbms_output.put_line ('l_document_id-l_attached_document_id-l_media_id'||'-'|| l_document_id ||'-'||l_attached_document_id ||'-'||l_media_id);

select organization_id
into l_organization_id
from org_organization_definitions
where organization_code =c1_rec.organization_code;

l_entity_name:=NULL;

FND_FILE.PUT_LINE (FND_FILE.LOG,'l_organization_id:' ||l_organization_id);
if c1_rec.entity_name like '%MTL_SYSTEM_ITEMS%' then
l_entity_name:='MTL_SYSTEM_ITEMS';
elsif c1_rec.entity_name like '%BOM_BILL_OF_MATERIALS%' then
l_entity_name:='BOM_BILL_OF_MATERIALS';
elsif c1_rec.entity_name like '%BOM_OPERATIONAL_ROUTINGS%' then
l_entity_name:='BOM_OPERATIONAL_ROUTINGS';
end if;

l_seq_no :=NULL;

if l_entity_name = 'MTL_SYSTEM_ITEMS' then
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where segment1 =c1_rec.ITEM
and ORGANIZATION_ID =l_organization_id;
FND_FILE.PUT_LINE (FND_FILE.LOG,'test1');

begin

select pk2_value into l_seq_no from fnd_attached_documents
where pk2_value=l_inventory_item_id
and seq_num=c1_rec.seq;
exception
    when others then
        l_seq_no:=NULL;
end;


elsif l_entity_name = 'BOM_BILL_OF_MATERIALS' then
select bom.BILL_SEQUENCE_ID--, bom.*
into l_inventory_item_id
from BOM_BILL_OF_MATERIALS bom,
mtl_system_items_b msi
 where bom. ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
 and bom. ORGANIZATION_ID = msi.ORGANIZATION_ID
 and msi.segment1 =c1_rec.ITEM
 and msi.ORGANIZATION_ID =l_organization_id;
 FND_FILE.PUT_LINE (FND_FILE.LOG,'test2');

 begin

select pk1_value into l_seq_no from fnd_attached_documents
where pk1_value=l_inventory_item_id
and seq_num=c1_rec.seq;
exception
    when others then
        l_seq_no:=NULL;
end;


elsif l_entity_name like '%BOM_OPERATIONAL_ROUTINGS%' then
FND_FILE.PUT_LINE (FND_FILE.LOG,'test3');
select bor.ROUTING_SEQUENCE_ID
into l_inventory_item_id
from BOM_OPERATIONAL_ROUTINGS bor,
mtl_system_items_b msi
where bor. ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bor. ORGANIZATION_ID = msi.ORGANIZATION_ID
--and bom.ASSEMBLY_ITEM_ID=1089754
and msi.segment1 = c1_rec.ITEM
and msi.ORGANIZATION_ID = l_organization_id;

begin

select pk1_value into l_seq_no from fnd_attached_documents
where pk1_value=l_inventory_item_id
and seq_num=c1_rec.seq;
exception
    when others then
        l_seq_no:=NULL;
end;

end if;

if l_seq_no is null then

FND_FILE.PUT_LINE (FND_FILE.LOG,'l_inventory_item_id:' ||l_inventory_item_id);




FND_FILE.PUT_LINE (FND_FILE.LOG,'INSERT fnd_documents start');
--dbms_output.put_line ('fnd_documents start');
INSERT INTO apps.fnd_documents
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
datatype_id,
category_id,
media_id,
security_type,
security_id,
publish_flag,
usage_type
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
l_short_datatype_id, -- Datatype for 'SHORT_TEXT'
l_doc_category_id, -- Category_id
l_media_id,
1, -- 'Organization' Level Security
l_organization_id, -- Organization id for Inventory Item Master Org
'Y', -- Publish_flag
'O' -- Usage_type of 'One Time'
);

FND_FILE.PUT_LINE (FND_FILE.LOG,'INSERT fnd_documents_tl start');
--dbms_output.put_line ('fnd_documents_tl start');
INSERT INTO apps.fnd_documents_tl
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
language,
description,
TITLE,
media_id,
SOURCE_LANG
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
'US', -- language
c1_rec.doc_desc,--'EXTENDED DESCRIPTION', -- description
c1_rec.TITLE,  --title added
l_media_id, -- media_id
'US' -- translated
);

FND_FILE.PUT_LINE (FND_FILE.LOG,'INSERT fnd_attached_documents start');
--dbms_output.put_line ('fnd_attached_documents start');
INSERT INTO apps.fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
seq_num,
entity_name,
pk1_value,
pk2_value,
automatically_added_flag,
category_id
)
VALUES
(l_attached_document_id,
l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
c1_rec.seq,--40, -- Sequence Number of attachment.
l_entity_name,--c1_rec.entity_name,--'MTL_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment
decode(l_entity_name,'MTL_SYSTEM_ITEMS',l_organization_id,l_inventory_item_id),--104, -- Organization id for Inventory Item Master Org
decode(l_entity_name,'MTL_SYSTEM_ITEMS',l_inventory_item_id,null), -- Inventory Item Id
'N', -- Automatically_added_flag
l_doc_category_id
);

FND_FILE.PUT_LINE (FND_FILE.LOG,'INSERT fnd_documents_short_text start');
--dbms_output.put_line ('fnd_documents_short_text start');
INSERT INTO apps.fnd_documents_short_text
(media_id,
short_text
)
VALUES
(l_media_id,
c1_rec.DOC_SHORT_TEXT -- Notes/Attachments text
);


update CLC_SHORT_TEXT_ATTACHMENTS
set PROCESS_FLAG= 'S'
where item =c1_rec.item
and organization_code = c1_rec.organization_code
and entity_name =c1_rec.entity_name--l_entity_name--c1_rec.entity_name
and process_flag= 'P';
FND_FILE.PUT_LINE (FND_FILE.LOG,'short text attachments successfully created: '||l_entity_name||'-'||c1_rec.organization_code ||'-'||c1_rec.item||'-'||sqlerrm);

COMMIT;
else
update CLC_SHORT_TEXT_ATTACHMENTS
set PROCESS_FLAG= 'E',
ERROR_MESSAGE = 'Duplicate Seq No Exist'
where item =c1_rec.item
and organization_code = c1_rec.organization_code
and entity_name =c1_rec.entity_name--l_entity_name
and process_flag= 'P';

end if;

exception when others then
l_err:=sqlerrm;
update CLC_SHORT_TEXT_ATTACHMENTS
set PROCESS_FLAG= 'E',
ERROR_MESSAGE = l_err
where item =c1_rec.item
and organization_code = c1_rec.organization_code
and entity_name =c1_rec.entity_name--l_entity_name
and process_flag= 'P';
FND_FILE.PUT_LINE (FND_FILE.LOG,'for loop exception when others: '||l_entity_name||'-'||c1_rec.organization_code ||'-'||c1_rec.item||'-'||sqlerrm);
COMMIT;
--dbms_output.put_line ('exception when others:'||sqlerrm);
end;

end loop;

FND_FILE.PUT_LINE (FND_FILE.LOG,'program end ');
--dbms_output.put_line ('program end');


exception when others then
FND_FILE.PUT_LINE (FND_FILE.LOG,'MAIN exception when others: '||sqlerrm);
l_err:=sqlerrm;
update CLC_SHORT_TEXT_ATTACHMENTS
set PROCESS_FLAG= 'E',
ERROR_MESSAGE = l_err
where process_flag= 'P';

END CLC_SHORT_TEXT_ATTAH_PRG;
/