Item Categories Created Syntax

 declare

  V_COUNT NUMBER;

V_ERROR_FLAG VARCHAR2(40);

V_ERROR_MESSAGE VARCHAR2(4000);

v_inventory_item_id number;

v_CATEGORY_SET_ID number;

V_CATEGORY_ID number;

v_old_CATEGORY_ID number;

cursor c is

SELECT ROWID,item_code,CATEGORY_SET_NAME,SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5 INV_SEGMENTS,PROCESSED_FLAG FROM XXINV_CATEGRY_ASSIGN_STG

where PROCESSED_FLAG='N';




BEGIN
  
for i in c loop

V_ERROR_MESSAGE:=null;

V_ERROR_FLAG:='N';

V_CATEGORY_ID:=NULL;

v_inventory_item_id:=NULL;

v_CATEGORY_SET_ID:=NULL;

v_old_CATEGORY_ID:=NULL;




BEGIN
  


　
  select CATEGORY_ID INTO V_CATEGORY_ID from mtl_categories_v

where SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=i.INV_SEGMENTS

and STRUCTURE_NAME=i.CATEGORY_SET_NAME;




  DBMS_OUTPUT.put_line('V_CATEGORY_ID'||V_CATEGORY_ID);




 EXCEPTION WHEN OTHERS THEN

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE :='ERROR IN ITEM CATEGORY';




END;
  



begin
  
select inventory_item_id into v_inventory_item_id from mtl_system_items_b

where segment1=i.item_code

AND ORGANIZATION_ID=P_ORG_ID;

EXCEPTION WHEN OTHERS THEN

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE :=V_ERROR_MESSAGE;




END;
  


　
 begin

select CATEGORY_SET_ID into v_CATEGORY_SET_ID from mtl_category_sets_tl

where CATEGORY_SET_NAME=i.CATEGORY_SET_NAME

and rownum=1;




  DBMS_OUTPUT.put_line('v_CATEGORY_SET_ID'||v_CATEGORY_SET_ID);




  EXCEPTION WHEN OTHERS THEN

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE :=V_ERROR_MESSAGE||'CATEGORY_SET_NAME not existing in oracle';




END;
  



begin
  
select CATEGORY_ID into v_old_CATEGORY_ID from mtl_item_categories

where CATEGORY_SET_ID=v_CATEGORY_SET_ID

and INVENTORY_ITEM_ID=v_inventory_item_id

AND ORGANIZATION_ID=P_ORG_ID;

DBMS_OUTPUT.put_line('v_old_CATEGORY_ID'||v_old_CATEGORY_ID);




 EXCEPTION WHEN OTHERS THEN

v_old_CATEGORY_ID:=null;




END;
  
if V_ERROR_FLAG='N' THEN

if v_old_CATEGORY_ID is not null then

DBMS_OUTPUT.put_line('IN PROG');




DECLARE
  
v_return_status VARCHAR2(1) := NULL;

v_msg_count NUMBER := 0;

v_msg_data VARCHAR2(2000);




  v_errorcode VARCHAR2(1000);

v1_category_id NUMBER;

v1_old_category_id NUMBER;

v1_category_set_id NUMBER;

v1_inventory_item_id NUMBER;

v_organization_id NUMBER;

v_context VARCHAR2(2);




begin
  




--- context done ------------
   v1_old_category_id := v_old_CATEGORY_ID;

v1_category_id := V_CATEGORY_ID;

v1_category_set_id := v_CATEGORY_SET_ID;

v1_inventory_item_id := v_inventory_item_id;

v_organization_id := p_org_id;

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT

( p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_FALSE,

x_return_status => v_return_status,

x_errorcode => v_errorcode,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_old_category_id => v1_old_category_id,

p_category_id => v1_category_id,


p_category_set_id => v1_category_set_id,

p_inventory_item_id => v1_inventory_item_id,

p_organization_id => v_organization_id);

IF v_return_status = fnd_api.g_ret_sts_success THEN

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE=null,

PROCESSED_FLAG='Y'

where rowid=i.rowid;

COMMIT;

DBMS_OUTPUT.put_line ('Updation of category assigment is Sucessfull : '||v_category_id);

ELSE DBMS_OUTPUT.put_line ('Updation of category assigment failed:'||v_msg_data);

ROLLBACK;

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE='Updation of category assigment failed:'||v_msg_data,

PROCESSED_FLAG='N'

where rowid=i.rowid;

COMMIT;

FOR i IN 1 .. v_msg_count LOOP

v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

dbms_output.put_line( i|| ') '|| v_msg_data);

END LOOP;

END IF;

END;



　
  else




  DECLARE

v_return_status VARCHAR2(1) := NULL;

v_msg_count NUMBER := 0;

v_msg_data VARCHAR2(2000);




  v_errorcode VARCHAR2(1000);

v1_category_id NUMBER;

v1_old_category_id NUMBER;

v1_category_set_id NUMBER;

v1_inventory_item_id NUMBER;

v_organization_id NUMBER;

v_context VARCHAR2(2);




begin
  




--- context done ------------
   v1_category_id := V_CATEGORY_ID;

v1_category_set_id := v_CATEGORY_SET_ID;

v1_inventory_item_id := v_inventory_item_id;

v_organization_id := p_org_id;

INV_ITEM_CATEGORY_PUB.Create_Category_Assignment

( p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_FALSE,

x_return_status => v_return_status,

x_errorcode => v_errorcode,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_category_id => v1_category_id,

p_category_set_id => v1_category_set_id,

p_inventory_item_id => v1_inventory_item_id,

p_organization_id => v_organization_id);

IF v_return_status = fnd_api.g_ret_sts_success THEN

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE=null,

PROCESSED_FLAG='Y'

where rowid=i.rowid;

COMMIT;

DBMS_OUTPUT.put_line ('New category assigment is Sucessfull : '||v_category_id);

ELSE DBMS_OUTPUT.put_line ('New category assigment failed:'||v_msg_data);

ROLLBACK;

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE='New category assigment failed:'||v_msg_data,

PROCESSED_FLAG='N'

where rowid=i.rowid;

COMMIT;

FOR i IN 1 .. v_msg_count LOOP

v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

dbms_output.put_line( i|| ') '|| v_msg_data);

END LOOP;

END IF;

END;




  end if;




  else




  UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE= V_ERROR_MESSAGE,

PROCESSED_FLAG='N'

where rowid=i.rowid;

end if;

end loop;




end;
  


　

　

　

　
 end;

Link between ap_invoices_all and xla tables

 

SELECT aia.INVOICE_ID ,

aia.INVOICE_NUM ,

aia.INVOICE_DATE ,

aia.INVOICE_AMOUNT ,

aia.INVOICE_CURRENCY_CODE ,

aia.PAYMENT_CURRENCY_CODE ,

aia.GL_DATE ,

xah.PERIOD_NAME ,

ia.PAYMENT_METHOD_CODE ,

xah.JE_CATEGORY_NAME

FROM ap.ap_invoices_all aia,

xla.xla_transaction_entities XTE,

xla.xla_events xev,

xla.xla_ae_headers XAH,

xla.xla_ae_lines XAL,

GL_IMPORT_REFERENCES gir,

gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations gcc

WHERE aia.INVOICE_ID = xte.source_id_int_1

AND xev.entity_id = xte.entity_id

AND xah.entity_id = xte.entity_id

AND xah.event_id = xev.event_id

AND XAH.ae_header_id = XAL.ae_header_id

AND XAH.je_category_name = 'Purchase Invoices'

AND XAH.gl_transfer_status_code = 'Y'

AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID

AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID

AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID

AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID

AND gir.JE_LINE_NUM=gjl.JE_LINE_NUM

What are the benefits of using Oracle SCM cost management?

Oracle Cost Management in the Cloud provides the following benefits:

• Easy Management of Global Inter Company Transactions. ...
• Flexibility for Statutory and Management Cost Reporting. ...
• Graphical Views of Product Costs.

 

Is Oracle SCM part of ERP?

Oracle Enterprise Resource Planning (ERP) is a cloud-based application suite that manages business processes like accounting, project management, and procurement. Oracle Supply Chain Management (SCM) is a cloud-based application suite that manages your supply chain end-to-end.

What is Oracle SCM?

At the most fundamental level, supply chain management (SCM) is management of the flow of goods, data, and finances related to a product or service, from the procurement of raw materials to the delivery of the product at its final destination.

What is SCM module in Oracle Apps?

Oracle SCM (Supply Chain Management Cloud) is a cloud-based application built by Oracle. It unifies end-to-end business processes considering the changing supply chain needs of the modern enterprises.