CURSOR C1 is
SELECT a.inventory_item_id, a.segment1 ,a.organization_id , b.organization_code, C.hsn_code
FROM mtl_system_items_b a , mtl_parameters b , xxx.xxx_item_hns_codes C
WHERE a.organization_id = b.organization_id
and C.inventory_item_id is not null
and b.master_organization_id = <Give the organizatioin ID>
--and a.organization_id = C.organization_id
AND A.inventory_item_id = C.inventory_item_id ;
;
CURSOR C2 is
SELECT lookup_code attribute_code
FROM ja_lookups
WHERE lookup_type = 'JAI_ITEM_ATTRIBUTE'
and lookup_code <> 'ITEM_CLASS'
AND lookup_code NOT IN ('APPLICABLE','TRADABLE') ;
l_template_hdr_id NUMBER ;
l_count NUMBER ;
l_count_det NUMBER ;
l_report_association_id NUMBER ;
l_template_dtl_id NUMBER ;
l_template_hdr_id_value NUMBER ;
l_count_rep NUMBER ;
l_jai_reporting_value NUMBER ;
l_report_code_id NUMBER ;
BEGIN
FOR I in C1 loop
l_template_hdr_id := NULL ;
l_count := NULl ;
l_template_hdr_id_value := NULL ;
l_count_det := NULL ;
l_report_code_id := NULL ;
l_jai_reporting_value := NULL ;
SELECT jai_item_templ_hdr_s.nextval
INTO l_template_hdr_id
FROM dual;
SELECT count(1)
INTO l_count
FROM jai_item_templ_hdr
WHERE inventory_item_id = i.inventory_item_id
AND organization_id = i.organization_id
AND entity_type_code IN ( 'ITEM' , 'ITEM_TEMPL_ASGN' )
AND item_classification = 'PRFG' ;
IF l_count = 0 THEN
BEGIN
INSERT INTO jai_item_templ_hdr( template_hdr_id
,template_name
,template_desc
,entity_id
,entity_type_code
,item_classification
,inventory_item_id
,organization_id
,creation_date
,created_by
,last_update_date
,last_update_login
,last_updated_by
,record_type_code)
VALUES(l_template_hdr_id
,''
,''
,308308 --10080 -- 308308
,'ITEM' -- 'ITEM_TEMPL_ASGN' -- ITEM
,'PRFG'
,i.inventory_item_id
,i.organization_id
,SYSDATE
,5562 --fnd_global.user_id
,SYSDATE
,-1 --FND_GLOBAL.LOGIN_ID
,5562 --FND_GLOBAL.USER_ID
,'DEFINED');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while inserting into table jai_item_templ_hdr -- '|| sqlerrm);
END ;
END IF ;
COMMIT ;
BEGIN
SELECT template_hdr_id
INTO l_template_hdr_id_value
FROM jai_item_templ_hdr
WHERE inventory_item_id = i.inventory_item_id
AND organization_id = i.organization_id
AND entity_type_code IN ( 'ITEM_TEMPL_ASGN', 'ITEM' )
AND item_classification = 'PRFG' ;
EXCEPTION
WHEN OTHERS THEN
l_template_hdr_id_value := NULL ;
END ;
IF l_template_hdr_id_value IS NOT NULL THEN
SELECT count(1)
INTO l_count_det
FROM jai_item_templ_dtls
WHERE template_hdr_id = l_template_hdr_id_value ;
IF l_count_det = 0 THEN
BEGIN
FOR k in C2 LOOP
SELECT jai_item_templ_dtls_s.nextval
INTO l_template_dtl_id
FROM dual;
BEGIN
INSERT INTO jai_item_templ_dtls(template_dtl_id
,template_hdr_id
,attribute_code
,attribute_value
,regime_id
,copied_from_template_id
,user_overridden_flag
,applicable_flag
,creation_date
,created_by
,last_update_date
,last_update_login
,last_updated_by
,record_type_code)
VALUES(l_template_dtl_id
,l_template_hdr_id_value
,k.attribute_code
,decode(k.attribute_code , 'RECOVERABLE' ,'Y' ,'') -- Y
,10000
,'' --10080 -- 308308 or null
,''
,'Y'
,SYSDATE
,5562 --FND_GLOBAL.USER_ID
,SYSDATE
,-1 -- FND_GLOBAL.LOGIN_ID
,5562 -- FND_GLOBAL.USER_ID
,'DEFINED');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while inserting into table jai_item_templ_dtls -- '|| sqlerrm);
END ;
END LOOP ;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END ;
END IF ;
END IF ;
COMMIT ;
BEGIN
SELECT template_hdr_id
INTO l_jai_reporting_value
FROM jai_item_templ_hdr
WHERE inventory_item_id = i.inventory_item_id
AND organization_id = i.organization_id
AND entity_type_code IN ( 'ITEM_TEMPL_ASGN' , 'ITEM')
AND item_classification = 'PRFG' ;
EXCEPTION
WHEN OTHERS THEN
l_jai_reporting_value := NULL ;
END ;
IF l_jai_reporting_value IS NOT NULL THEN
SELECT count(1)
INTO l_count_rep
FROM jai_reporting_associations
WHERE entity_id = l_jai_reporting_value ;
IF l_count_rep =0 THEN
SELECT jai_reporting_associations_s.nextval
INTO l_report_association_id
FROM dual;
SELECT jai_reporting_codes_s.nextval
INTO l_report_code_id
FROM dual;
DBMS_OUTPUT.put_line('before inserting in jai_reporting_associations');
BEGIN
INSERT INTO jai_reporting_associations
(reporting_association_id
,reporting_type_name
,reporting_usage
,reporting_code_description
,reporting_code
,entity_code
,entity_id
,entity_source_table
,effective_from
,effective_to
,creation_date
,created_by
,last_update_date
,last_update_login
,last_updated_by
,record_type_code
,reporting_code_id
,reporting_type_id
,regime_id
,stl_hdr_id)
VALUES
(l_report_association_id
,'HSN Code Type for GST'
,'LR'
,I.hsn_code
,I.hsn_code
,'ITEM'
,l_jai_reporting_value
,'JAI_ITEM_TEMPL_HDR'
,'02-JAN-2017'
,''
,SYSDATE
,5562 --FND_GLOBAL.USER_ID
,SYSDATE
,-1 -- FND_GLOBAL.LOGIN_ID
,5562 --FND_GLOBAL.USER_ID
,'DEFINED'
,l_report_code_id
,10000
,'' --10000
,''
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while inserting into table jai_reporting_associations -- '|| sqlerrm);
END ;
END IF ;
END IF ;
END LOOP ;
END ;
