Oracle Apps Ocean: ITEM TRANSACTION INTERFACE

/*========================================FINAL item transaction INTERFACE STARTs here =================================================*/

/******************************** HERE STAGING TABLE DEFINITION ****************************/

DROP TRIGGER ITC_ITI_T;

DROP SEQUENCE ITC_ITI_S;

DROP TABLE ITC_ITI_STG;

CREATE TABLE ITC_ITI_STG(

TRANSACTION_ID NUMBER,

STATUS_FLAG VARCHAR2(2),

ERROR_MESSAGE VARCHAR2(4000),

ORGANIZATION_NAME_LS VARCHAR2(240),

ORGANIZATION_ID NUMBER,

TRANSACTION_DATE_LS DATE,

TRANSACTION_TYPE_NAME_LS VARCHAR2(80),

TRANSACTION_TYPE_ID NUMBER,

ITEM_NAME_LS VARCHAR2(40),

INVENTORY_ITEM_ID NUMBER,

SUBINVENTORY_CODE_LS VARCHAR2(10), -- IF COUNT = 0 THEN INVLAIED CODE

TRANSACTION_UOM_LS VARCHAR2(25),

TRANSACTION_UOM VARCHAR2(3),

TRANSACTION_QUANTITY_LS NUMBER,

DISTRIBUTION_SEGMENT1_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT2_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT3_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT4_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT5_LS VARCHAR2(25),

distribution_account_id number,

SOURCE_CODE VARCHAR2(30), -- FROM HERE FOR OUR PURPOSE

SOURCE_LINE_ID NUMBER,

SOURCE_HEADER_ID NUMBER,

PROCESS_FLAG NUMBER,

TRANSACTION_MODE NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER,

CREATION_DATE DATE,

CREATED_BY NUMBER);

/******************************** HERE SEQUENCE DEFINITION ****************************/

CREATE SEQUENCE ITC_ITI_S;

/******************************** HERE TRIGGER DEFINITION ****************************/

CREATE OR REPLACE TRIGGER ITC_ITI_T

BEFORE INSERT ON ITC_ITI_STG FOR EACH ROW

BEGIN

SELECT ITC_ITI_S.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;

END ITC_ITI_T;

SELECT * FROM ITC_ITI_STG

truncate table itc_iti_stg

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_DATE_LS,TRANSACTION_TYPE_NAME_LS,

ITEM_NAME_LS,SUBINVENTORY_CODE_LS,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('Vision Operations','28-JUL-2012','Miscellaneous receipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_DATE_LS,TRANSACTION_TYPE_NAME_LS,

ITEM_NAME_LS,SUBINVENTORY_CODE_LS,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('Vision Operations','28-JUL-2012','Miscellaneous receipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,SUBINVENTORY_CODE_LS

,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('ITC OU','Miscellaneous receipt','Soft_Raghu','Stores','Eah',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,SUBINVENTORY_CODE_LS

,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('ORACLE IMAGES','Miscellaneoreceipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

commit;

select * from ITC_ITI_STG

select * from mtl_transactions_interface

delete mtl_transactions_interface

select count(*) from mtl_sysTEM_ITEMS_B WHERE costing_enabled_flag='Y'

AND inventory_asset_flag='Y'

AND segment1='Soft_Raghu'

AND organization_id=204;

/***************************** HERE PACKAGE DEFINITION STARTS ***********/

-- version:3-aug-2013

create or replace package ITC_ITI_PKG

procedure MAP(errbuf out varchar2, retcode out number);

procedure VALIDATE(errbuf out varchar2, retcode out number);

procedure LOAD(errbuf out varchar2, retcode out number);

procedure CALL_STDPROG(errbuf out varchar2, retcode out number);

procedure ERROR_REPORT(errbuf out varchar2, retcode out number);

procedure CALL_ALLPRODS(errbuf out varchar2, retcode out number);

end ITC_ITI_PKG;

/***************************** HERE PACKAGE DEFINITION ENDS ***********/

/***************************** HERE PACKAGE BODY DEFINITION STARTS ***********/

create or replace package body ITC_ITI_PKG

procedure MAP(errbuf out varchar2, retcode out number)

begin

/**************** UPDATEING STATING TABLE STARTS HERE ****************/

UPDATE ITC_ITI_STG SET

STATUS_FLAG='N',

ERROR_MESSAGE=NULL,

ORGANIZATION_ID=NULL,TRANSACTION_TYPE_ID=NULL,

INVENTORY_ITEM_ID=NULL,

SOURCE_CODE='LEGACY_RAGHU', --HERE OUR PURPOSE definations

SOURCE_LINE_ID=123,

SOURCE_HEADER_ID=123,

PROCESS_FLAG=1,

TRANSACTION_MODE=3,

LAST_UPDATE_DATE=TRUNC(SYSDATE),

LAST_UPDATED_BY=1318,

CREATION_DATE=TRUNC(SYSDATE),

CREATED_BY=1318;

UPDATE ITC_ITI_STG SET TRANSACTION_DATE_LS=TRUNC(SYSDATE)

WHERE TRANSACTION_DATE_LS IS NULL;

UPDATE ITC_ITI_STG SET

STATUS_FLAG='X',

ERROR_MESSAGE='Duplicate Record'

WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM ITC_ITI_STG

GROUP BY ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,

SUBINVENTORY_CODE_LS);

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE='Organization Name is Required'

WHERE ORGANIZATION_NAME_LS IS NULL;

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE=ERROR_MESSAGE

||',Transaction Type Name is Required' WHERE TRANSACTION_TYPE_NAME_LS IS NULL;

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE=ERROR_MESSAGE

||',Item Name is Requied' WHERE ITEM_NAME_LS IS NULL;

/**************** UPDATEING STATING TABLE ENDS HERE ****************/

COMMIT;

end MAP;

procedure VALIDATE(errbuf out varchar2, retcode out number)

v_organization_id number;

v_date_count number;

v_transaction_type_id number;

v_inventory_item_id number;

v_count number;

v_uom_code varchar2(3);

v_acct_id number;

v_acct_status number;

cursor ORG_CUR is

select organization_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null;

cursor TRAN_DATE_CUR is

select transaction_date_ls,transaction_id from ITC_ITI_STG where status_flag not in ('X','MM')

and organization_name_ls is not null;

cursor TRAN_TYPE_CUR is

select transaction_type_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null

and transaction_type_name_ls is not null;

cursor ITEM_CUR is

select organization_id,item_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null

and item_name_ls is not null;

cursor SUI_CODE_CUR is

select organization_id,subinventory_code_ls,transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and organization_name_ls is not null;

cursor TRAN_UOM_CUR is

select transaction_uom_ls,transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and transaction_uom_ls is not null;

cursor ACCT_CUR is

select organization_id,item_name_ls,distribution_segment1_ls,distribution_segment2_ls,distribution_segment3_ls,

distribution_segment4_ls,distribution_segment5_ls,distribution_account_id,

transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and organization_name_ls is not null and distribution_segment1_ls is not null;

begin

/**************** ORGANIZATION VALIDATION STARTS HERE ****************/

for ORG_REC in ORG_CUR

loop

begin

select organization_id into v_organization_id from ORG_ORGANIZATION_DEFINITIONS

where upper(organization_name)=upper(ORG_REC.organization_name_ls);

update ITC_ITI_STG set organization_id=v_organization_id

where transaction_id=ORG_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE' ,error_message='No Organization exist:'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message='Too Many Organizations with :'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message='Unexcepted error at Organization:'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

end;

end loop;

/**************** ORGANIZATION VALIDATION ENDS HERE ****************/

/**************** TRANSACTION DATE VALIDATION STARTS HERE ****************/

for TRAN_DATE_REC in TRAN_DATE_CUR

loop

begin

SELECT COUNT(*) into v_date_count FROM GL_PERIOD_STATUSES WHERE 1=1

AND CLOSING_STATUS='O'

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'YYYY') BETWEEN TO_CHAR(START_DATE,'YYYY')

AND TO_CHAR(END_DATE,'YYYY')

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'MM') BETWEEN TO_CHAR(START_DATE,'MM')

AND TO_CHAR(END_DATE,'MM')

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'DD') BETWEEN TO_CHAR(START_DATE,'DD')

AND TO_CHAR(END_DATE,'DD')

AND APPLICATION_ID in (401,101)

GROUP BY effective_period_num;

if v_date_count =0then

update ITC_ITI_STG set status_flag='VE',error_message=error_message

||'Period is not for: '

||transaction_date_ls where transaction_id=TRAN_DATE_REC.transaction_id;

end if;

exception

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message

||'Un-able check Periods Open/Close for: '

||transaction_date_ls where transaction_id=TRAN_DATE_REC.transaction_id;

end;

end loop;

/**************** TRANSACTION DATE VALIDATION ENDS HERE ****************/

/**************** Transaction Type VALIDATION STARTS HERE****************/

for TRAN_TYPE_REC in TRAN_TYPE_CUR

loop

begin

select transaction_type_id into v_transaction_type_id from MTL_TRANSACTION_TYPES

where upper(transaction_type_name)=upper(TRAN_TYPE_REC.transaction_type_name_ls);

update ITC_ITI_STG set transaction_type_id=v_transaction_type_id

where transaction_id=TRAN_TYPE_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||' ,No transaction type exist:'

||transaction_type_name_ls where transaction_id=TRAN_TYPE_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Too many transaction types:'

||transaction_type_name_ls

where transaction_id=TRAN_TYPE_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexpect error at transaction type:'

||transaction_type_name_ls where transaction_id=TRAN_TYPE_REC.transaction_id;

end;

end loop;

/**************** Transaction Type VALIDATION ENDS HERE ****************/

/**************** Item VALIDATION starts HERE ****************/

for ITEM_REC in ITEM_CUR

loop

begin

select inventory_item_id into v_inventory_item_id from MTL_SYSTEM_ITEMS_B

where upper(segment1)=upper(ITEM_REC.item_name_ls)

and organization_id=ITEM_REC.organization_id;

update ITC_ITI_STG set inventory_item_id=v_inventory_item_id

where transaction_id=ITEM_REC.transaction_id and organization_id is not null;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||', No Org/Item combination found:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',No many Items:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexcept error at Item:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

end;

end loop;

/**************** Item VALIDATION ENDS HERE ****************/

/**************** SubInventory Code VALIDATION starts HERE ****************/

for SUI_CODE_REC in SUI_CODE_CUR

loop

begin

select count(*) into v_count from MTL_SECONDARY_INVENTORIES

where organization_id=SUI_CODE_REC.organization_id

and upper(secondary_inventory_name)=upper(SUI_CODE_REC.subinventory_code_ls);

if v_count=0 then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Invalied org/subinventory:'

||subinventory_code_ls where transaction_id=SUI_CODE_REC.transaction_id;

end if;

end;

end loop;

/**************** SubInventory Code VALIDATION ENDS HERE ****************/

/**************** transaction UOM VALIDATION startsS HERE ****************/

for TRAN_UOM_REC in TRAN_UOM_CUR

loop

begin

select uom_code into v_uom_code from MTL_UNITS_OF_MEASURE_TL

where unit_of_measure=TRAN_UOM_REC.transaction_uom_ls;

update ITC_ITI_STG set transaction_uom=v_uom_code

where transaction_id=TRAN_UOM_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Invalied UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Too may UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexecpted at UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

end;

end loop;

/**************** transaction UOM VALIDATION endS HERE ****************/

/**************** Accounting VALIDATION starts HERE ****************/

for ACCT_REC in ACCT_CUR

loop

begin

select count(*) into v_acct_status from mtl_sysTEM_ITEMS_B WHERE costing_enabled_flag='Y'

AND inventory_asset_flag='Y'

AND segment1=ACCT_REC.item_name_ls

AND organization_id=ACCT_REC.organization_id;

if v_acct_status>0 then

select code_combination_id into v_acct_id from GL_CODE_COMBINATIONS where

segment1=ACCT_REC.distribution_segment1_ls

and segment2=ACCT_REC.distribution_segment2_ls

and segment3=ACCT_REC.distribution_segment3_ls

and segment4=ACCT_REC.distribution_segment4_ls

and segment5=ACCT_REC.distribution_segment5_ls;

update ITC_ITI_STG set DISTRIBUTION_ACCOUNT_ID=v_acct_id

where transaction_id=ACCT_REC.transaction_id;

end if;

exception

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||'account not found:'

where transaction_id=ACCT_REC.transaction_id;

end;

end loop;

/**************** Accounting VALIDATION ends HERE ****************/

update ITC_ITI_STG set status_flag='V' where status_flag='N' and error_message is null;

COMMIT;

end VALIDATE;

procedure LOAD(errbuf out varchar2, retcode out number)

cursor LOAD_CUR is

select * from ITC_ITI_STG where status_flag='V';

begin

/**************** loding VALIDATION starts HERE ****************/

for LOAD_REC in LOAD_CUR

loop

begin

INSERT INTO MTL_TRANSACTIONS_INTERFACE(

ORGANIZATION_ID,

TRANSACTION_TYPE_ID,

TRANSACTION_DATE,

INVENTORY_ITEM_ID,

SUBINVENTORY_CODE,

TRANSACTION_UOM,

TRANSACTION_QUANTITY,

DISTRIBUTION_ACCOUNT_ID,

SOURCE_CODE,

SOURCE_LINE_ID,

SOURCE_HEADER_ID,

PROCESS_FLAG,

TRANSACTION_MODE,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

CREATION_DATE,

CREATED_BY)

VALUES(load_rec.ORGANIZATION_ID,

load_rec.TRANSACTION_TYPE_ID,

load_rec.TRANSACTION_DATE_ls,

load_rec.INVENTORY_ITEM_ID,

load_rec.SUBINVENTORY_CODE_ls,

load_rec.TRANSACTION_UOM,

load_rec.TRANSACTION_QUANTITY_ls,

load_rec.DISTRIBUTION_ACCOUNT_ID,

load_rec.SOURCE_CODE,

load_rec.SOURCE_LINE_ID,

load_rec.SOURCE_HEADER_ID,

load_rec.PROCESS_FLAG,

load_rec.TRANSACTION_MODE,

load_rec.LAST_UPDATE_DATE,

load_rec.LAST_UPDATED_BY,

load_rec.CREATION_DATE,

load_rec.CREATED_BY);

exception

when others then

update ITC_ITI_STG set status_flag='LE',error_message=error_message

||',loading failed' where transaction_id=LOAD_REC.transaction_id;

end;

end loop;

/**************** loading VALIDATION ends HERE ****************/

commit;

end LOAD;

procedure CALL_STDPROG(errbuf out varchar2, retcode out number)

v_request_id number;

begin

fnd_global.APPS_INITIALIZE(

user_id=>fnd_global.USER_ID,

resp_id=>fnd_global.resp_id,

resp_appl_id=>fnd_global.RESP_APPL_ID);

v_request_id:=fnd_request.SUBMIT_REQUEST(

application =>'INV',

program =>'INCTCM',

description =>'ITC Concurrent Progarm From Package');

commit;

end CALL_STDPROG;

procedure ERROR_REPORT(errbuf out varchar2, retcode out number)

v_count number;

v_valied number;

v_failed number;

v_duplicate number;

cursor ERR_CUR is

select * from ITC_ITI_STG where status_flag not in ('V');

begin

fnd_file.put_line(fnd_file.output,' ITC ITEM TRANSACTION REPORT');

fnd_file.put_line(fnd_file.output,' ---------------------------');

fnd_file.put_line(fnd_file.output,'');

fnd_file.put_line(fnd_file.output,rpad('Orgnization Name', 25,' ')||

rpad('Item Name',15,' ')||

rpad('Transaction Date', 20,' ')||

rpad('Transaction Type Name',40,' ')||

rpad('Error',450,' '));

fnd_file.put_line(fnd_file.output,rpad('----------------', 25,' ')||

rpad('---------',15,' ')||

rpad('----------------', 20,' ')||

rpad('---------------------',40,' ')||

rpad('------',40,' '));

for ERR_REC in ERR_CUR

loop

begin

fnd_file.put_line(fnd_file.output,rpad(ERR_REC.Organization_Name_ls,25,' ')||

rpad(ERR_REC.Item_name_ls,15,' ')||

rpad(ERR_REC.Transaction_date_ls,20,' ')||

rpad(ERR_REC.Transaction_type_name_ls,40,' ')||

rpad(ERR_REC.Error_message,450,' '));

end;

end loop;

fnd_file.put_line(fnd_file.output,' ----------');

fnd_file.put_line(fnd_file.output,' END OF DATA');

fnd_file.put_line(fnd_file.output,' ----------');

fnd_file.put_line(fnd_file.output,'');

fnd_file.put_line(fnd_file.output,' -------');

fnd_file.put_line(fnd_file.output,' SUMMARY');

fnd_file.put_line(fnd_file.output,' -------');

select count(*) into v_count from ITC_ITI_STG;

fnd_file.put_line(fnd_file.output,' Total Number of Records : ' || v_count);

select count(*) into v_valied from ITC_ITI_STG where status_flag='V';

fnd_file.put_line(fnd_file.output,' Number of Valid Records : ' || v_valied);

select count(*) into v_failed from ITC_ITI_STG where statuS_flag='VE' and Error_message is not null;

fnd_file.put_line(fnd_file.output,' Total Number of invalid Records : ' ||v_failed);

select count(*) into v_failed from ITC_ITI_STG where statuS_flag='X' and Error_message is not null;

fnd_file.put_line(fnd_file.output,' Total Number of duplicate Records : ' ||v_failed);

end ERROR_REPORT;

procedure CALL_ALLPRODS(errbuf out varchar2, retcode out number)

v1 varchar2(90);

v2 number;

begin

ITC_ITI_PKG.MAP(V1,V2);

ITC_ITI_PKG.VALIDATE(V1,V2);

ITC_ITI_PKG.LOAD(V1,V2);

ITC_ITI_PKG.CALL_STDPROG(V1,V2);

ITC_ITI_PKG.ERROR_REPORT(V1,V2);

end CALL_ALLPRODS;

end;

/*========================================FINAL item transaction INTERFACE ends here =================================================*/

Oracle Apps Ocean

Wednesday, January 1, 2014

ITEM TRANSACTION INTERFACE

No comments:

Post a Comment