Errors Needed Tables in ERP R12

# Actual Cost Process Error  :
==============================================

Select * from CM_ACER_MSG WHERE REQUEST_ID=123456




# Cost Process Error :
==============================================

SELECT * FROM CM_CUPD_MSG WHERE REQUEST_ID=234567

 

# Per-Processor Error :
==============================================

SELECT * FROM GL_MESG_TBL WHERE REQUEST_ID=345678

 

# Create Accounting Error :
==============================================

SELECT * FROM XLA_ACCOUNTING_ERRORS WHERE REQUEST_ID=345678

ORG List as Per Location and Organization Type

ORG List as Per  Location and Organization Type:

select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'PF' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-PF-%'
--order by LOC desc
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'MAT' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16'-- and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-MAT-%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'ME' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-ME-%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'LCM' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '%-LCM-%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'SELLING' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME like '35%'
union all
select o.ORGANIZATION_CODE ORG_CODE, o.ORGANIZATION_NAME ORG_NAME, substr(o.ORGANIZATION_NAME,1,3) LOC, 'OTHERS' org_type --,o.USER_DEFINITION_ENABLE_DATE, p.CREATION_DATE
from org_organization_definitions o, org_acct_periods p
where o.ORGANIZATION_ID=p.ORGANIZATION_ID(+) and o.ORGANIZATION_CODE not in ('FMO','IMO')
and p.PERIOD_NAME(+)='May-15-16' --and p.ORGANIZATION_ID is null
and o.ORGANIZATION_NAME not like '%-MAT-%' and  o.ORGANIZATION_NAME not like '%-PF-%' and  o.ORGANIZATION_NAME not like '%-ME-%' and  o.ORGANIZATION_NAME not like '%-LCM-%' and  o.ORGANIZATION_NAME not like '35%'
order by LOC desc, org_type desc, ORG_CODE asc

Accounting Flexfield

Accounting Flexfield
select vs.FLEX_VALUE_SET_NAME, b.FLEX_VALUE,b.FLEX_VALUE_MEANING,b.DESCRIPTION
from fnd_flex_values_vl b, FND_FLEX_VALUE_SETS vs
where FLEX_VALUE in ('216200','126502') and
ENABLED_FLAG='Y'
and b.FLEX_VALUE_SET_ID= vs.FLEX_VALUE_SET_ID and vs.FLEX_VALUE_SET_NAME = 'PRG_ACC_VS'


Using Package :
Select GCC.CONCATENATED_SEGMENTS ACCOUNT, gcc.segment1, u.SHORT_CODE, u.NAME
,GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION(GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID) DESCRIPTION
from GL_CODE_COMBINATIONS_KFV GCC, hr_operating_units u
where gcc.SEGMENT1=u.ORGANIZATION_ID






http://oracleapps88.blogspot.com/2012/04/oracle-apps-important-tables.html
http://www.techmandate.com/tag/oracle-inventory/

Custom Trigger for Mktg Expense allocate, no COGS will generate, But Stock will raised.

Updated :

CREATE OR REPLACE TRIGGER APPS.rcv_transactions_cust_trigr
    after insert on RCV_TRANSACTIONS
    for each row
WHEN (
new.transaction_type      =  'DELIVER' and
       new.destination_type_code ='EXPENSE'
      )
     
     
declare

    A number(1);

      l_orgid  po_headers_all.org_id%type;
      l_params wf_parameter_list_t;
      l_clntyp varchar2(30);
      l_docnum varchar2(100);
     
 
    v_account_code_id number(6);
    v_inventory_item_id number(10);
    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);
    v_tran_Source_Name   mtl_transactions_interface.TRANSACTION_SOURCE_NAME%type;

 /*
  ||  Here initialising the PO_Received_new record to raised Stock but no cost will generate when received the Mktg PO,
  ||  type in the inline procedure transaction_type      =  'DELIVER' and destination_type_code ='EXPENSE'
  ||  Expense will allocate to Mktg Dept, or Company wise PO amount will allocate without cogs stock will raige.
  ||
  */
  /****************************************************************************
  *     Creation HISTORY:
  *
  *         25-Mar-2016  Shah Mahfuz for Requirment of PRAN File Version Mahfuz_103.2
  *    Last Updated By :
  *         19-Sep-2016  Shah Mahfuz for Requirment of PRAN File Version Mahfuz_103.3
  *
  ****************************************************************************/


cursor ch_1 is


select distinct i.INVENTORY_ITEM_ID item_id, i.PRIMARY_UOM_CODE uom_code from   rcv_shipment_lines l, mtl_system_items_b i
where l.po_header_id=:new.po_header_id and l.ITEM_ID=i.INVENTORY_ITEM_ID and i.ORGANIZATION_ID=102 and SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;



begin

   
    select distinct TO_ORGANIZATION_ID into l_orgid
    from  rcv_shipment_lines 
    where  po_header_id = :new.po_header_id;
   
  
  select distinct s.RECEIPT_NUM into v_tran_Source_Name from  rcv_shipment_lines sl,rcv_shipment_headers s
    where sl.SHIPMENT_HEADER_ID=s.SHIPMENT_HEADER_ID
    and  po_header_id =:new.po_header_id  and sl.SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;

      
    select material_account into v_account_code_id from mtl_parameters where organization_id=l_orgid;
   
  A:=1;
   
  begin
       select secondary_inventory_name into v_subinventory_code  from mtl_secondary_inventories where upper(secondary_inventory_name) like upper('%MK') and organization_id=l_orgid;
       
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
       A:=2;
      
   END;
    
   if (A=1) then
    
                select
                inventory_location_id into v_inventory_location_id
                from mtl_item_locations
                where subinventory_code =v_subinventory_code;
   
                for c1_rec in ch_1 loop
                  exit when ch_1%notfound;
                    begin
                    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,
                    TRANSACTION_SOURCE_NAME
                    )
                    values
                    (
                    mtl_material_transactions_s.nextval,
                    'MKTG Expense Rcving Stock',  --c1_rec.source_code
                    1,
                    1,
                    c1_rec.item_id,
                    1,
                    2,
                    3,
                    :new.QUANTITY,
                    sysdate,
                    1110,
                    sysdate,
                    1110,
                    l_orgid,
                    sysdate, --c1_rec.transaction_date,
                    42,
                    v_account_code_id, --c1_rec.distribution_account_id,
                    c1_rec.uom_code,
                    v_subinventory_code,
                    v_inventory_location_id,
                    null,
                    'Rcv No : ' || v_tran_Source_Name
                    );
               
                     exception
                        when no_data_found then
                        v_inventory_item_id :=null;
                        v_inventory_location_id :=null;
                        v_subinventory_code :=null;
                        v_tran_Source_Name:=null;
                       
                    end;

                
         /*         
          exception
                    when no_data_found then
                    v_inventory_item_id :=null;
                    v_inventory_location_id :=null;
                    v_subinventory_code :=null;
                    v_tran_Source_Name:=null;
                   
                end;
             */  
               
        end loop; 
           
 end if;

end;
--/


---------------Last one ---------------------------------------------------

CREATE OR REPLACE TRIGGER APPS.rcv_transactions_cust_trigr
after insert on RCV_TRANSACTIONS
for each row
WHEN (
new.transaction_type = 'DELIVER' and
new.destination_type_code ='EXPENSE'
)
declare

l_orgid po_headers_all.org_id%type;
l_params wf_parameter_list_t;
l_clntyp varchar2(30);
l_docnum varchar2(100);



v_account_code_id number(6);
v_inventory_item_id number(10);
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);
v_tran_Source_Name varchar2(20);
v_tran_Source_Name mtl_transactions_interface.TRANSACTION_SOURCE_NAME%type;


/*
|| Here initialising the PO_Received_new record to raised Stock but no cost will generate when received the Mktg PO,
|| type in the inline procedure transaction_type = 'DELIVER' and destination_type_code ='EXPENSE'
|| Expense will allocate to Mktg Dept, or Company wise PO amount will allocate without cogs stock will raige.
||
*/
/****************************************************************************
* Creation HISTORY:
*
* 25-Mar-2016 Shah Mahfuz for Requirment of PRAN File Version Mahfuz_103.1
*
****************************************************************************/


cursor ch_1 is


select distinct i.INVENTORY_ITEM_ID item_id, i.PRIMARY_UOM_CODE uom_code from rcv_shipment_lines l, mtl_system_items_b i
where l.po_header_id=:new.po_header_id and l.ITEM_ID=i.INVENTORY_ITEM_ID and i.ORGANIZATION_ID=102 and
SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;







begin


select distinct TO_ORGANIZATION_ID into l_orgid
from rcv_shipment_lines
where po_header_id = :new.po_header_id;

select distinct s.RECEIPT_NUM into v_tran_Source_Name from rcv_shipment_lines sl,rcv_shipment_headers s
where sl.SHIPMENT_HEADER_ID=s.SHIPMENT_HEADER_ID
and po_header_id =:new.po_header_id and sl.SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;

select material_account into v_account_code_id from mtl_parameters where organization_id=l_orgid;

select secondary_inventory_name into v_subinventory_code from mtl_secondary_inventories where
upper(secondary_inventory_name) like upper('%MK') and organization_id=l_orgid;

select
inventory_location_id into v_inventory_location_id
from mtl_item_locations
where subinventory_code =v_subinventory_code;


for c1_rec in ch_1 loop
exit when ch_1%notfound;
begin
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,
TRANSACTION_SOURCE_NAME
)
values
(
mtl_material_transactions_s.nextval,
'MK Stock Upload', --c1_rec.source_code
1,
1,
c1_rec.item_id,
1,
2,
3,
:new.QUANTITY,
sysdate,
1110,
sysdate,
1110,
l_orgid,
sysdate, --c1_rec.transaction_date,
42,
v_account_code_id, --c1_rec.distribution_account_id,
c1_rec.uom_code,
v_subinventory_code,
v_inventory_location_id,
null,
'RCV_No : ' || v_tran_Source_Name
);

exception
when no_data_found then
v_inventory_item_id :=null;
v_inventory_location_id :=null;
v_subinventory_code :=null;
v_tran_Source_Name :=null;
end;

end loop;

end;
/
/

Custom Price Transfer accross the OU

CREATE OR REPLACE PROCEDURE APPS.PRG_COSTING_PERIOD_test(PERIODID IN VARCHAR2) IS
BEGIN


Insert into XX_UPDATE_TRANSFER_PRICE
(TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSFER_PRICE, NEW_TRANSFER_PRICE,
TRANSFER_ORGANIZATION_ID, XX_TRANSFER_PRICE, XX_PROCESS_FLAG, XX_PERIOD_ID,PROCESS_STATUS)
Values (148, 148, 148, 0, 0, 148, 0, 100, 148,'RUNNING');
COMMIT;

INSERT INTO XX_UPDATE_TRANSFER_PRICE

SELECT mtl.TRANSACTION_ID,mtl.INVENTORY_ITEM_ID,mtl.ORGANIZATION_ID ToOrg,mtl.TRANSFER_PRICE,0 NEW_TRANSFER_PRICE,
mtl.TRANSFER_ORGANIZATION_ID FromOrg, mtl.TRANSFER_PRICE,0 XX_PROCESS_FLAG, 148 XX_PERIOD_ID,NULL
FROM mtl_material_transactions mtl
,org_organization_definitions orgFrm1 -- From org
,org_organization_definitions orgTo -- To org
, cm_cmpt_dtl cst
WHERE mtl.source_code = 'RCV'
and mtl.TRANSACTION_DATE between (select trunc(start_date) from GMF_PERIOD_STATUSES where period_id =148-- PERIODID-- v_period_id
)
and (select trunc(end_date) from GMF_PERIOD_STATUSES where period_id =148-- PERIODID
)
and mtl.transaction_action_id = 12
and mtl.TRANSFER_ORGANIZATION_ID=orgFrm1.ORGANIZATION_ID
and mtl.ORGANIZATION_ID=orgTo.ORGANIZATION_ID
and orgFrm1.OPERATING_UNIT<>orgTo.OPERATING_UNIT
and mtl.TRANSFER_ORGANIZATION_ID=cst.ORGANIZATION_ID(+) and mtl.INVENTORY_ITEM_ID=cst.INVENTORY_ITEM_ID(+) and cst.PERIOD_ID(+)=148
and( mtl.TRANSFER_PRICE<>cst.CMPNT_COST or cst.CMPNT_COST is null)


;
COMMIT;


--FOR i IN 1 .. 1 LOOP

declare
cursor c_mmt is

SELECT * FROM XX_UPDATE_TRANSFER_PRICE
WHERE TRANSACTION_ID = 148 AND XX_PROCESS_FLAG=100 AND PROCESS_STATUS='RUNNING'
ORDER BY TRANSACTION_ID;

h_mmt c_mmt%ROWTYPE;
BEGIN
OPEN c_mmt;
LOOP
FETCH c_mmt INTO h_mmt;
EXIT WHEN c_mmt%NOTFOUND;

-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE---------------------------

declare
cursor c_mmtN is

select * from XX_UPDATE_TRANSFER_PRICE WHERE PROCESS_STATUS IS NULL
order by TRANSACTION_ID;


h_mmtN c_mmtN%ROWTYPE;
BEGIN
OPEN c_mmtN;
LOOP
FETCH c_mmtN INTO h_mmtN;
EXIT WHEN c_mmtN%NOTFOUND;
update XX_UPDATE_TRANSFER_PRICE
set NEW_TRANSFER_PRICE =(

select acctg_cost
from gl_item_cst gic,mtl_material_transactions mmt
where mmt.transaction_id = h_mmtN.transaction_id
and mmt.inventory_item_id=gic.INVENTORY_ITEM_ID
and gic.period_ID = PERIODID --v_period_ID
and final_flag = 0
and mmt.TRANSFER_ORGANIZATION_ID = gic.ORGANIZATION_ID

)
where XX_UPDATE_TRANSFER_PRICE.transaction_id = h_mmtN.transaction_id;
commit;
END LOOP;
exception
when others then
null;
CLOSE c_mmtN;
END;





---------------------------------------------
/*
--update mtl_material_transactions
-- set TRANSFER_PRICE = h_mmt.NEW_TRANSFER_PRICE
-- where mtl_material_transactions.TRANSACTION_ID = h_mmt.transaction_id;

IF i=1 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=990, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;
ELSIF I=2 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=991, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;

ELSIF I=3 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=992, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;

END IF;







Insert into XX_UPDATE_TRANSFER_PRICE
(TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSFER_PRICE, NEW_TRANSFER_PRICE,
TRANSFER_ORGANIZATION_ID, XX_TRANSFER_PRICE, XX_PROCESS_FLAG, XX_PERIOD_ID,PROCESS_STATUS)
Values (148, 148, 148, 0, 0, 148, 0, 101, 148,'RUNNING'||i);
COMMIT;


*/


--- mmt transfer price update-----

declare
cursor c_mmtZ is
select * from XX_UPDATE_TRANSFER_PRICE WHERE PROCESS_STATUS IS NULL
--where TRANSACTION_ID = 1227428
order by TRANSACTION_ID;
h_mmtZ c_mmtZ%ROWTYPE;
BEGIN
OPEN c_mmtZ;
LOOP
FETCH c_mmtZ INTO h_mmtZ;
EXIT WHEN c_mmtZ%NOTFOUND;
update mtl_material_transactions
set TRANSFER_PRICE = h_mmtZ.NEW_TRANSFER_PRICE
where mtl_material_transactions.TRANSACTION_ID = h_mmtZ.transaction_id;
commit;
END LOOP;
exception
when others then
null;
CLOSE c_mmtZ;
END;
----------------------------------

update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=990, PROCESS_STATUS='COMPLETED'; -- where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100 AND PROCESS_STATUS='RUNNING';
commit;
END LOOP;

exception
when others then
null;
CLOSE c_mmt;
END;



------------------------------------

--END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error - '||SQLERRM);
END;
/

Wrong Organization Parameters Check

Wrong Organization Parameters Check


select SEGMENT1,ORGANIZATION_CODE,ORGANIZATION_ID,ORGANIZATION_NAME,NAME,count(SEGMENT1) from
(
select distinct gc.SEGMENT1,--MATERIAL_ACCOUNT,

p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.MATERIAL_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,MATERIAL_OVERHEAD_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.MATERIAL_OVERHEAD_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,MATL_OVHD_ABSORPTION_ACCT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.MATL_OVHD_ABSORPTION_ACCT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,RESOURCE_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.RESOURCE_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,PURCHASE_PRICE_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.PURCHASE_PRICE_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,AP_ACCRUAL_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.AP_ACCRUAL_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,OVERHEAD_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.OVERHEAD_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,OUTSIDE_PROCESSING_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.OUTSIDE_PROCESSING_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,INTRANSIT_INV_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.INTRANSIT_INV_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,INTERORG_RECEIVABLES_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.INTERORG_RECEIVABLES_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,INTERORG_PRICE_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.INTERORG_PRICE_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,INTERORG_PAYABLES_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.INTERORG_PAYABLES_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,COST_OF_SALES_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.COST_OF_SALES_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,ENCUMBRANCE_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.ENCUMBRANCE_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,INTERORG_TRANSFER_CR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.INTERORG_TRANSFER_CR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,INVOICE_PRICE_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.INVOICE_PRICE_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,AVERAGE_COST_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.AVERAGE_COST_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,SALES_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.SALES_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,EXPENSE_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.EXPENSE_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,BORRPAY_MATL_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.BORRPAY_MATL_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,BORRPAY_MOH_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.BORRPAY_MOH_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,BORRPAY_RES_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.BORRPAY_RES_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,BORRPAY_OSP_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.BORRPAY_OSP_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,BORRPAY_OVH_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.BORRPAY_OVH_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,MO_APPROVAL_TIMEOUT_ACTION
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.MO_APPROVAL_TIMEOUT_ACTION=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,PROJECT_COST_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.PROJECT_COST_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,DEFERRED_COGS_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.DEFERRED_COGS_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,LCM_VAR_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.LCM_VAR_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
union all
select distinct gc.SEGMENT1--,CAT_WT_ACCOUNT
,p.ORGANIZATION_CODE,p.ORGANIZATION_ID , o.ORGANIZATION_NAME,u.NAME
from mtl_parameters_view p, gl_code_combinations gc, org_organization_definitions o, hr_operating_units u
where p.CAT_WT_ACCOUNT=gc.CODE_COMBINATION_ID and p.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.OPERATING_UNIT=u.ORGANIZATION_ID
)
group by SEGMENT1,ORGANIZATION_CODE,ORGANIZATION_ID,ORGANIZATION_NAME,NAME
having count(SEGMENT1)<18
order by ORGANIZATION_CODE

R12 Tables

APPS General Tables and its details

Responsibility Application Details

SELECT application_name, application_short_name, fa.application_id
FROM fnd_application fa, fnd_application_tl fat
WHERE (application_short_name LIKE 'AR'
or fa.APPLICATION_ID in (200,222,260,707,555))
AND fa.application_id = fat.application_id
ORDER BY fa.application_id


Order Management Tables and its details
Order Management Tables. Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms


AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.


Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers


Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments


Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes


End User Details
csi_t_party_details To capture End user Details



Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits




Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text




Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all





Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions




Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all





Hold Relaese
oe_hold_releases_all Hold released Sales Order.






Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.






Cancel Orders
oe_order_lines_all Cancel Order Details.

Mail Report SQL

select distinct t.transaction_type_ID,tp.TRANSACTION_TYPE_NAME, count(*) total_transaction
from mtl_material_transactions t, mtl_transaction_types tp
where trunc(transaction_date) between '01-JUN-2015' and '30-JUN-2015' and organization_id in
(select organization_id from mtl_parameters where process_enabled_flag='Y')
and t.TRANSACTION_TYPE_ID=tp.transaction_type_id
group by t.transaction_type_ID,TRANSACTION_TYPE_NAME

select * from mtl_material_transactions t, mtl_system_items_b i where trunc(transaction_date) between '01-MAY-2015' and
'31-MAY-2015' and t.organization_id =471
and t.inventory_item_id in (102863,103330)
and t.INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID and t.ORGANIZATION_ID=i.ORGANIZATION_ID