Cost Upload

Cost Upload For Cost Upload:
Crated by Mahfuz


CREATE TABLE APPS.PRAN_COST_ADJUSTMENT_STG
(
CALENDAR_CODE VARCHAR2(4 BYTE),
PERIOD_CODE VARCHAR2(4 BYTE),
COST_MTHD_CODE VARCHAR2(4 BYTE),
COST_CMPNTCLS_ID NUMBER(10),
COST_ANALYSIS_CODE VARCHAR2(32 BYTE),
COST_ADJUST_ID NUMBER(10),
ADJUST_QTY NUMBER,
ADJUST_COST NUMBER,
REASON_CODE VARCHAR2(4 BYTE),
ADJUST_STATUS NUMBER(5),
ADJUST_QTY_UOM VARCHAR2(3 BYTE),
COST_TYPE_ID NUMBER(15),
INVENTORY_ITEM_CODE VARCHAR2(12 BYTE),
ORGANIZATION_CODE VARCHAR2(12 BYTE),
PERIOD_ID NUMBER(15),-- GMF_PERIOD_STATUSES
FLAG VARCHAR2(2 BYTE),
ADJUSTMENT_DATE DATE DEFAULT SYSDATE,
ADJUSTMENT_IND NUMBER(1)
);



Insert into PRAN_COST_ADJUSTMENT_STG
(CALENDAR_CODE, PERIOD_CODE, COST_MTHD_CODE, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
COST_ADJUST_ID, ADJUST_QTY, ADJUST_COST, REASON_CODE, ADJUST_STATUS,
ADJUST_QTY_UOM, COST_TYPE_ID, INVENTORY_ITEM_CODE, ORGANIZATION_CODE, PERIOD_ID,
FLAG, ADJUSTMENT_DATE, ADJUSTMENT_IND)
select null A1,null A2,null A3,1 A4,'DIR' A5,2003 A6,QTY A7,RATE A8,'SUP' A9, 0 A10,NULL A11,1000 A12,ITEM_CODE, ORG_CODE, 124 PERIOD,NULL FLG,TO_DATE('31/01/2015','DD-MM-YY'), 2 A13 from apps.xxtemp_org where file_name like 'INV%PFG%ALL%'



SET DEFINE OFF;
Insert into PRAN_COST_ADJUSTMENT_STG
(CALENDAR_CODE, PERIOD_CODE, COST_MTHD_CODE, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
COST_ADJUST_ID, ADJUST_QTY, ADJUST_COST, REASON_CODE, ADJUST_STATUS,
ADJUST_QTY_UOM, COST_TYPE_ID, INVENTORY_ITEM_CODE, ORGANIZATION_CODE, PERIOD_ID,
FLAG, ADJUSTMENT_DATE, ADJUSTMENT_IND)
Values
(NULL, NULL, NULL, 1, 'DIR',
2003, 0, 90, 'SUP', 0,
NULL, 1000, '32706', 'G03', 184,
'Y', TO_DATE('05/19/2015 16:41:49', 'MM/DD/YYYY HH24:MI:SS'), 2);
COMMIT;


CREATE OR REPLACE PROCEDURE APPS.PRAN_COST_ADJUSTMENT_STG_proc as
cursor c1 is

select CALENDAR_CODE ,
PERIOD_CODE ,
COST_MTHD_CODE,
COST_CMPNTCLS_ID,
COST_ANALYSIS_CODE,
COST_ADJUST_ID ,
ADJUST_QTY ,
ADJUST_COST ,
REASON_CODE ,
ADJUST_STATUS ,
ADJUST_QTY_UOM ,
COST_TYPE_ID ,
I.INVENTORY_ITEM_ID,
S.inventory_item_CODE,
O.ORGANIZATION_ID ,
O.ORGANIZATION_CODE ,
PERIOD_ID,
ADJUSTMENT_DATE,
ADJUSTMENT_IND,
i.PRIMARY_UOM_CODE
from PRAN_COST_ADJUSTMENT_STG S, ORG_ORGANIZATION_DEFINITIONS O, MTL_SYSTEM_ITEMS_B I
WHERE S.ORGANIZATION_CODE=O.ORGANIZATION_CODE
AND S.INVENTORY_ITEM_CODE=I.SEGMENT1 AND O.ORGANIZATION_ID = I.ORGANIZATION_ID AND S.FLAG IS NULL

;

c1_rec c1%rowtype;
v_organization_id number(5);
v_inventory_item_id number(10);

Begin

for c1_rec in c1 loop
exit when c1%notfound;

begin

select
organization_id,
inventory_item_id
into v_organization_id, v_inventory_item_id
from mtl_system_items_b
where
inventory_item_id=c1_rec.inventory_item_id
and organization_id=c1_rec.organization_id;


end;



if
v_organization_id is not null
and v_inventory_item_id is not null
then
update PRAN_COST_ADJUSTMENT_STG
set flag='Y' where
inventory_item_CODE=c1_rec.inventory_item_CODE
and organization_CODE =c1_rec.organization_CODE;

insert into cm_adjs_dtl (
CALENDAR_CODE ,
PERIOD_CODE ,
COST_MTHD_CODE,
COST_CMPNTCLS_ID,
COST_ANALYSIS_CODE,
COST_ADJUST_ID ,
ADJUST_QTY ,
ADJUST_COST ,
REASON_CODE ,
ADJUST_STATUS ,
ADJUST_QTY_UOM ,
COST_TYPE_ID ,
INVENTORY_ITEM_ID,
ORGANIZATION_ID ,
PERIOD_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
delete_mark,
SUBLEDGER_IND,
ADJUSTMENT_DATE,
ADJUSTMENT_IND
)
values
(
C1_rec.CALENDAR_CODE ,
C1_rec.PERIOD_CODE ,
C1_rec.COST_MTHD_CODE,
C1_rec.COST_CMPNTCLS_ID,
C1_rec.COST_ANALYSIS_CODE,
--C1_rec.COST_ADJUST_ID ,
GEM5_COST_ADJUST_ID_S.nextval ,
C1_rec.ADJUST_QTY ,
C1_rec.ADJUST_COST ,
C1_rec.REASON_CODE ,
C1_rec.ADJUST_STATUS ,
--C1_rec.ADJUST_QTY_UOM ,
C1_rec.PRIMARY_UOM_CODE,
C1_rec.COST_TYPE_ID ,
C1_rec.INVENTORY_ITEM_ID,
C1_rec.ORGANIZATION_ID ,
C1_rec.PERIOD_ID,
sysdate,
1110,
sysdate,
1110,
0,
1,
TO_DATE( C1_rec.ADJUSTMENT_DATE,'DD-MM-YY'),
C1_rec.ADJUSTMENT_IND
);
else

update PRAN_COST_ADJUSTMENT_STG set flag='N' where
inventory_item_CODE=c1_rec.inventory_item_CODE
and organization_CODE =c1_rec.organization_CODE;


end if;
end loop;
commit;
End PRAN_COST_ADJUSTMENT_STG_proc;
/




Run actual Cost Process to see Item Cost