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

Stock Upload

Stock Upload insert into pran_item_stock_stg_M(SOURCE_CODE,SOURCE_LINE_ID,SOURCE_HEADER_ID,INVENTORY_ITEM_CODE,PROCESS_FLAG,VALIDATION_REQUIRED,TRANSACTION_MODE,TRANSACTION_QUANTITY,ORGANIZATION_CODE,SUBINVENTORY_CODE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
select 'Adjusted Stock Upload' SOURCE_CODE, 1 SOURCE_LINE_ID, 1 SOURCE_HEADER_ID, ITEM_CODE INVENTORY_ITEM_CODE, 1 PROCESS_FLAG, 2 VALIDATION_REQUIRED, 3 TRANSACTION_MODE, qty TRANSACTION_QUANTITY, ORG_CODE ORGANIZATION_CODE, SUBINV_CODE SUBINVENTORY_CODE, TO_DATE('29/05/2015','dd-mm-yy') LAST_UPDATE_DATE, '1110' last_updated_by, TO_DATE('29/05/2015','dd-mm-yy') CREATION_DATE, '1110' CREATED_BY
from apps.xxtemp_org where upper(file_name) like upper(&flnm)



For Stock Upload:
Crated by Mahfuz


CREATE TABLE APPS.PRAN_ITEM_STOCK_STG_M
(
TRANSACTION_INTERFACE_ID NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_LINE_ID NUMBER,
SOURCE_HEADER_ID NUMBER,
INVENTORY_ITEM_CODE VARCHAR2(10 BYTE),
PROCESS_FLAG NUMBER(1),
VALIDATION_REQUIRED NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
ORGANIZATION_CODE VARCHAR2(10 BYTE),
PRIMARY_QUANTITY NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSACTION_DATE DATE,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
LOCATOR_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_COST NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER NOT NULL,
FLAG VARCHAR2(2 BYTE)
);


insert into pran_item_stock_stg_M(SOURCE_CODE,SOURCE_LINE_ID,SOURCE_HEADER_ID,INVENTORY_ITEM_CODE,PROCESS_FLAG,VALIDATION_REQUIRED,TRANSACTION_MODE,TRANSACTION_QUANTITY,ORGANIZATION_CODE,SUBINVENTORY_CODE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY) values ('Adjusted Stock Upload',1,1,'32706',1,2,3,'1738', 'G03','G03.FG',to_date('31/01/2015','dd-mm-yy'),1110,to_date('31/01/2015','dd-mm-yy'),1110);


CREATE OR REPLACE PROCEDURE APPS.pran_item_stock_stg_proc as
cursor c1 is

select transaction_interface_id,source_code,source_line_id,
source_header_id,i.inventory_item_id, inventory_item_code , primary_uom_code ,process_flag,validation_required,
transaction_mode,transaction_quantity,o.organization_id, o.organization_code, primary_quantity,
primary_uom_code transaction_uom ,transaction_date ,subinventory_code,locator_id,
transaction_type_id,transaction_cost,distribution_account_id,
si.last_update_date,si.last_updated_by,si.creation_date,si.created_by from pran_item_stock_stg_M si, mtl_system_items_b i, org_organization_definitions o
where si.INVENTORY_ITEM_CODE=i.SEGMENT1 and si.ORGANIZATION_CODE=o.ORGANIZATION_CODE and o.ORGANIZATION_ID=i.ORGANIZATION_ID and FLAG is null

;

c1_rec c1%rowtype;
v_organization_id number(5);
v_inventory_item_id number(10);
v_primary_uom_code varchar2(6);
v_inventory_location_id number(10);
v_subinventory_code varchar2(10);
v_code_combination_id number(10);
v_transaction_type_id number(8);
Begin




for c1_rec in c1 loop
exit when c1%notfound;

begin

-- location
select
inventory_location_id,
subinventory_code
into v_inventory_location_id, v_subinventory_code
from mtl_item_locations
where subinventory_code =c1_rec.subinventory_code;



exception
when no_data_found then

v_organization_id :=null;
v_inventory_item_id :=null;
v_primary_uom_code :=null;
v_inventory_location_id :=null;
v_subinventory_code :=null;


end;



if

v_inventory_location_id is not null
and v_subinventory_code is not null then

update
pran_item_stock_stg_m set flag='Y' where
INVENTORY_ITEM_CODE=c1_rec.INVENTORY_ITEM_CODE
and organization_code =c1_rec.organization_code
and SUBINVENTORY_CODE =c1_rec.SUBINVENTORY_CODE
;

insert into mtl_transactions_interface
(
transaction_interface_id,
source_code,
source_header_id,
source_line_id,
inventory_item_id,
process_flag,
validation_required,
transaction_mode,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
transaction_date,
transaction_type_id,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id,
TRANSACTION_COST
)
values
(
mtl_material_transactions_s.NEXTVAL,
--c1_rec.source_code,
'Adjusted Stock Upload',
1,
1,
c1_rec.inventory_item_id,
1,
2,
3,
c1_rec.transaction_quantity,
sysdate,
1110,
sysdate,
1110,
c1_rec.organization_id,
--c1_rec.transaction_date,
to_date('31/01/2015','dd-mm-yy'),
--
42,
c1_rec.distribution_account_id,
--128305,
c1_rec.transaction_uom,
c1_rec.subinventory_code,
--c1_rec.locator_id,
v_inventory_location_id,
null
);
else

update pran_item_stock_stg_m set flag='N' where
inventory_item_code=c1_rec.inventory_item_code
and organization_code =c1_rec.organization_code
and transaction_uom =c1_rec.transaction_uom
and locator_id =c1_rec.locator_id
and subinventory_code =c1_rec.subinventory_code
and distribution_account_id =c1_rec.distribution_account_id
and transaction_type_id =c1_rec.transaction_type_id;

end if;

end loop;

commit;
End pran_item_stock_stg_proc;
/
Run Lunch process transaction manager.
Navigate: PFL Inventory Super User > Setup > Transactions > Interface Manager

select Material Transactions and then Tools > Launch Manager

OPM- Production, How to know Batch Status

Batch Status




SELECT o.ORGANIZATION_CODE, o.ORGANIZATION_NAME, o.ORGANIZATION_ID,
gbh.BATCH_NO,decode(gbh.BATCH_STATUS,3,'Completed',4,'Closed',2,'WIP',
'Just Created') Batch_Status
FROM GME_BATCH_HEADER gbh,
ORG_ORGANIZATION_DEFINITIONS o
WHERE CREATION_DATE >=TO_DATE('01/05/2015','dd-mm-yy')
AND gbh.ORGANIZATION_ID=o.ORGANIZATION_ID
ORDER BY BATCH_STATUS, o.ORGANIZATION_CODE

Uploaded Formula History

Uploaded Formula History
click here