Item Cost Update API & Tables

DROPTABLEXXAPL.APL_ITEM_COST_UPDATECASCADECONSTRAINTS; CREATETABLEXXAPL.APL_ITEM_COST_UPDATE ( ITEM_CODEVARCHAR2(30BYTE), ORGANIZATION_CODEVARCHAR2(3BYTE), UOMVARCHAR2(3BYTE), QUANTITYNUMBER, ITEM_COSTNUMBER, PROCESS_FLAGVARCHAR2(2BYTE), MESSAGEVARCHAR2(2000BYTE), PERIODVARCHAR2(20BYTE) ) TABLESPACECUST_TS_TX_DATA PCTUSED0 PCTFREE10 INITRANS1 MAXTRANS255 STORAGE( INITIAL40K NEXT96K MINEXTENTS1 MAXEXTENTSUNLIMITED PCTINCREASE0 BUFFER_POOLDEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; DROPSYNONYMAPPS.APL_ITEM_COST_UPDATE; CREATESYNONYMAPPS.APL_ITEM_COST_UPDATEFORX CREATESYNONYMAPPS.AP L_ITEM_COST_UPDATEFORXXAPL.APL_ITEM_COST_UPDATE XAPL.APL_ITEM_COST_UPDATE; ; GRANTALTER,DELETE,INDEX,INSERT,REFERENCES,SELECT,UPDATE,ONCOMM GRANTALTER,DELETE,INDEX,INSERT,REFERENCES ,SELECT,UPDATE,ONCOMMITREFRES ITREFRES H,QUERYREWRITE,DEBUG,FLASHBACKONXXAPL.AP H,QUERYREWRITE,DEBU G,FLASHBACKONXXAPL.APL_ITEM_COST_UPDATETOAPP L_ITEM_COST_UPDATETOAPPS; S;

CREATEORREPLACEPROCEDUREAPPS.XXAPL_SOLVENT_COST_UPDATEIS l_cost_update_recgmf_actual_cost_adjustment_pub.adjustment_rec_type; l_cost_mthd_codevarchar2(20); l_cost_typenumber; l_cost_type_idnumber; l_period_idnumber; l_calendar_codevarchar2(30); l_period_codevarchar2(30); l_cost_cmpntcls_idnumber; l_cost_cmpntcls_codevarchar2(16); l_cost_adjust_idnumber; l_last_update_loginnumber:=fnd_profile.value('LOGIN_ID'); l_user_idnumber:=fnd_profile.value('USER_ID'); l_user_namevarchar2(20):=fnd_profile.value('USER_NAME'); l_statusvarchar2(1); l_msg_countnumber; l_msg_datavarchar2(2000); l_out_indexNumber:=0; cursorc1isselectmp.organization_code,period,item_code,uom,quantity,item_cos t,ccm.period_id,mp.organization_id,msi.inventory_item_id fromapl_item_cost_updateaic,CM_CLDR_MST_Vccm,mtl_parametersmp,m tl_system_itemsmsi whereperiod='DEC-10'and --nvl(PROCESS_FLAG,'N')!='S'and --rownum=1 --anditem_code='C4030782' upper(ccm.PERIOD_DESC)=upper(aic.period) andmp.organization_code=aic.organization_code andmsi.organization_id=mp.organization_id andmsi.segment1=aic.item_code; begin begin selectcost_mthd_code,cost_type,cost_type_id intol_cost_mthd_code,l_cost_type,l_cost_type_idfromcm_mthd_mstWHEREcost_m thd_code='APMA'; exceptionwhenothersthen --fnd_file.put_line(fnd_file.LOG,'ErrorWhileFetchingCostMethodCode'); null; end; /* begin selectperiod_id,calendar_code,period_code

intol_period_id,l_calendar_code,l_period_code fromcm_cldr_mst_v whereupper(period_desc)='OCT-10'; exceptionwhenothersthen fnd_file.put_line(fnd_file.LOG,'ErrorWhileFetchingPeriodDetails'); end; */ begin selectcost_cmpntcls_id,cost_cmpntcls_code intol_cost_cmpntcls_id,l_cost_cmpntcls_code fromcm_cmpt_mstwherecost_cmpntcls_code='MATERIAL'; exceptionwhenothersthen --fnd_file.put_line(fnd_file.LOG,'ErrorWhileFetchingcost_cmpntcls_code'); null; end;

Foriinc1loop  BEGIN SELECTCALENDAR_CODEintol_calendar_code FROMCM_CLDR_DTLWHEREUPPER(PERIOD_DESC)=UPPER(i.period); exceptionwhenothersthen null; END; SELECTgem5_cost_adjust_id_s.nextvalintol_cost_adjust_idFROMdual; l_cost_update_rec.organization_id:=i.organization_id; l_cost_update_rec.organization_code:=i.organization_code; l_cost_update_rec.inventory_item_id:=i.inventory_item_id; l_cost_update_rec.item_number:=i.item_code; l_cost_update_rec.cost_type_id:=l_cost_type_id; l_cost_update_rec.cost_mthd_code:=l_cost_mthd_code; l_cost_update_rec.period_id:=i.period_id; l_cost_update_rec.calendar_code:=l_calendar_code; l_cost_update_rec.period_code:=i.period; l_cost_update_rec.cost_cmpntcls_id:=l_cost_cmpntcls_id; l_cost_update_rec.cost_cmpntcls_code:=l_cost_cmpntcls_code; l_cost_update_rec.cost_analysis_code:='MAC'; l_cost_update_rec.cost_adjust_id:=l_cost_adjust_id; l_cost_update_rec.adjust_qty:=i.quantity; l_cost_update_rec.adjust_qty_uom:=i.uom; l_cost_update_rec.adjust_cost:=i.item_cost; l_cost_update_rec.reason_code:='OPB'; l_cost_update_rec.adjust_status:=0;---0-NotApplied,1-Appli ed2-Modified l_cost_update_rec.creation_date:=sysdate; l_cost_update_rec.last_update_login:=l_last_update_login; l_cost_update_rec.created_by:=l_user_id; l_cost_update_rec.last_update_date:=sysdate; l_cost_update_rec.last_updated_by:=l_user_id; l_cost_update_rec.text_code:=NULL; l_cost_update_rec.trans_cnt:=1;

l_cost_update_rec.delete_mark:=0; l_cost_update_rec.request_id:=null; l_cost_update_rec.program_application_id:=null; l_cost_update_rec.program_id:=null; l_cost_update_rec.program_update_date:=null; l_cost_update_rec.attribute_category:=null; l_cost_update_rec.attribute1:=null; l_cost_update_rec.attribute2:=null; l_cost_update_rec.attribute3:=null; l_cost_update_rec.attribute4:=null; l_cost_update_rec.attribute5:=null; l_cost_update_rec.attribute6:=null; l_cost_update_rec.attribute7:=null; l_cost_update_rec.attribute8:=null; l_cost_update_rec.attribute9:=null; l_cost_update_rec.attribute10:=null; l_cost_update_rec.attribute11:=null; l_cost_update_rec.attribute12:=null; l_cost_update_rec.attribute13:=null; l_cost_update_rec.attribute14:=null; l_cost_update_rec.attribute15:=null; l_cost_update_rec.attribute16:=null; l_cost_update_rec.attribute17:=null; l_cost_update_rec.attribute18:=null; l_cost_update_rec.attribute19:=null; l_cost_update_rec.attribute20:=null; l_cost_update_rec.attribute21:=null; l_cost_update_rec.attribute22:=null; l_cost_update_rec.attribute23:=null; l_cost_update_rec.attribute24:=null; l_cost_update_rec.attribute25:=null; l_cost_update_rec.attribute26:=null; l_cost_update_rec.attribute27:=null; l_cost_update_rec.attribute28:=null; l_cost_update_rec.attribute29:=null; l_cost_update_rec.attribute30:=null; l_cost_update_rec.adjustment_ind:=0;--0-AverageCostAdjustment2-Uni tCostAdjustment, l_cost_update_rec.subledger_ind:=0; l_cost_update_rec.adjustment_date:=null; l_cost_update_rec.user_name:=5362;--l_user_name; GMF_ACTUAL_COST_ADJUSTMENT_PUB.create_actual_cost_adjustment( --UPDATE_ACTUAL_COST_ADJUSTMENT( p_api_version=>1, p_init_msg_list=>'T', p_commit=>'T', x_return_status=>l_status, x_msg_count=>l_msg_count, x_msg_data=>l_msg_data, p_adjustment_rec=>l_cost_update_rec); Ifl_status='S'Then updateapl_item_cost_updatesetprocess_flag='S'WHEREorganization_cod e=i.organization_codeandperiod=i.period anditem_code=i.item_code; else updateapl_item_cost_updatesetprocess_flag='N'WHEREorganization_code =i.organization_codeandperiod=i.period anditem_code=i.item_code;

endif; dbms_output.put_line('l_status'||l_status||i.item_code); dbms_output.put_line('l_msg_count'||l_msg_count); dbms_output.put_line('l_msg_data'||l_msg_data);  ForjIN1..l_msg_countLoop FND_MSG_PUB.get(p_msg_index=>j, p_encoded=>'F', p_data=>l_msg_data, p_msg_index_out=>l_out_index); dbms_output.put_line(l_msg_data); EndLoop;  endloop; --:APL_SOLVENT_COST_HDR.STATUS:='COMPLETED';  endXXAPL_SOLVENT_COST_UPDATE; /