Oracle ERP-Cost Module以前月結SQL

不三不四

  select * from org_organization_definitions
where organization_code='PTU'
select * from org_acct_periods
where organization_id=611
select * from mtl_material_transactions
where organization_id=611
select * from wip_cost_txn_interface
where organization_id=611
PTU=494 PGL=436

update  fnd_user_responsibility fur
set fur.end_date =null
where  fur.end_date =to_date('2006/02/28','yyyy/mm/dd')

update fnd_user_responsibility fur
set fur.end_date =to_date('2006/02/28','yyyy/mm/dd')
WHERE fur.responsibility_id IN
          (50522,
           50658,
           50882,
           50663,
           50668,
           51325,
           51348,
           51406,
           51464,
           51467,
           51341,
           51374,
           51486,
           51594,
           51646,
           52012,
           52629,
           52945,
           52961,
           53265,
           53267,
           54365,
           54366,
           51617,
           52132,
           52554,
           54624,
           53105,
           54487,
           53724,
           53744,
           53784,
           51667,
           51973,
           53384,
           54471,
           50692,
           51349,
           51468,
           51933,
           52574,
           52946,
           53266,
           52038,
           54488
          )
      AND fur.end_date IS NULL;
  
  
  
  
SELECT                  wdj.wip_entity_id,wdj.date_closed,wdj.creation_date,wdj.created_by,wdj.last_update_date,wdj.organization_id
        FROM wip_discrete_jobs wdj
       WHERE wdj.organization_id in ( 494,495,611,832,692,433,412,632,589,792,812)  
      AND wdj.status_type = 12
         AND wdj.date_closed BETWEEN SYSDATE - 26 AND SYSDATE
         and rownum <=300
         AND NOT EXISTS (SELECT 9 FROM wip_transactions wt
                         WHERE wt.wip_entity_id = wdj.wip_entity_id
                           And wt.transaction_type in(4,6) );



SELECT *
     FROM eproms.product_item_time@epromdb a
     WHERE  process_flag = '2';
        SELECT *
         FROM product_item_time_interface
         WHERE status = 'N';

     
        
declare
v_curr_date date;
cursor c_org
is
   select * from org_organization_definitions  ood
   where ood.operating_unit in
                               (435,393,591,733,414,587 )
  and (disable_date>sysdate or disable_date is null) ;

v_exists number;
begin
  if sysdate-trunc(sysdate,'mm')>10 then
     v_curr_date:=add_months(trunc(sysdate,'mm'),1);
  else
     v_curr_date:=  sysdate ;
  end if;
  
  dbms_output.put_line(to_char(v_curr_date,'yyyy-mm-dd'));

for rec in c_org loop
  SELECT count(*) into v_exists
  FROM org_acct_periods oap
  WHERE oap.organization_id = rec.organization_id
    and oap.open_flag='Y'
    and v_curr_date >=  period_start_date
and v_curr_date<=  oap.schedule_close_date;

  if  v_exists<=0 then
      dbms_output.put_line(rec.organization_code||'NEW Accounting Period Not Exists!')   ;
   end if;   
end loop;        
end;   


SELECT MAX (max_txid)
     INTO p_last_max_txid
     FROM mic_mtl_minmax_txid;

SELECT COUNT (*)
     INTO p_costed_count
     FROM mtl_material_transactions
    WHERE transaction_id > p_last_max_txid
      AND (costed_flag IS NULL OR costed_flag != 'N')
      AND transaction_type_id != 24 /*Standard cost update*/;
  
  SELECT MIN (transaction_id), MAX (transaction_id)
        INTO p_this_min_txid, p_this_max_txid
        FROM mtl_material_transactions mmt
       WHERE transaction_id > p_last_max_txid
         AND EXISTS (
                SELECT 'x'
                  FROM org_acct_periods oap
                 WHERE oap.organization_id = mmt.organization_id
                   AND oap.acct_period_id = mmt.acct_period_id
                   AND period_name = p_period_name);
      SELECT COUNT (*)
        INTO p_exist_error
        FROM mtl_material_transactions
       WHERE transaction_id BETWEEN p_this_min_txid AND p_this_max_txid
         AND transaction_type_id <> 24
         AND acct_period_id NOT IN (SELECT acct_period_id
                                      FROM org_acct_periods
                                     WHERE period_name = p_period_name);






SELECT oap.acct_period_id, wdj.wip_entity_id, NULL, SYSDATE, 0, SYSDATE, 0,
          0, wdj.organization_id, wac.class_type, 0, 0, 0, 0, 0, 0, 0, 0, 0,
          0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
     FROM  wip_accounting_classes wac,
             org_acct_periods oap,
             wip_discrete_jobs wdj
    WHERE wdj.status_type IN (3, 4, 5, 6, 7, 14, 15)
      AND wac.class_code = wdj.class_code
      AND wdj.organization_id = wac.organization_id
      AND oap.organization_id = wdj.organization_id
      AND oap.open_flag = 'Y'
      AND oap.period_close_date IS NULL
      AND oap.schedule_close_date >=
                                    NVL (wdj.date_released, wdj.creation_date)
      AND wac.class_type != 2
      AND NOT EXISTS (
             SELECT 'X'
               FROM wip_period_balances wpb
              WHERE wpb.repetitive_schedule_id IS NULL
                AND wpb.wip_entity_id = wdj.wip_entity_id
                AND wpb.organization_id = wdj.organization_id
                AND wpb.acct_period_id = oap.acct_period_id);
   
   
select mmt.* from mtl_material_transactions mmt,org_acct_periods oap
where mmt.ORGANIZATION_ID=oap.ORGANIZATION_ID
and mmt.ACCT_PERIOD_ID=oap.ACCT_PERIOD_ID
and mmt.TRANSACTION_TYPE_ID<>24
and oap.PERIOD_NAME='Dec-06'
select * from mtl_material_transactions
where TRANSACTION_TYPE_ID<>24 and transaction_date>=to_date('2006/12/01','yyyy/mm/dd')
SELECT * fROM MTL_SYSTEM_ITEMS
where ORGANIZATION_ID=436 AND SEGMENT1='SP01014029'

UPDATE cst_item_costs
SET ITEM_COST= (SELECT ITEM_COST FROM cst_item_costs
                where ORGANIZATION_ID=495 AND INVENTORY_ITEM_ID=2002667 AND COST_TYPE_ID=1)
where ORGANIZATION_ID=436 AND INVENTORY_ITEM_ID=2002667 AND COST_TYPE_ID=1
commit
PTU=495 PGL=436

select *  from mtl_material_transactions mmt where mmt.attribute15='GG226A001104' and inventory_item_id=1840445
select * from mtl_transaction_accounts mta where mta.transaction_id =62043924
update mtl_material_transactions set costed_flag='N',acct_period_id=2693,transaction_date=to_date('2006-11-1 1','yyyy-mm-dd HH'),
last_updated_by=12260,last_update_date=sysdate where transaction_id =62043924

commit

104
select  count(*)  from wip_cost_txn_interface
select  *  from wip_cost_txn_interface a,wip_discrete_jobs b
where   a.organization_id=b.organization_id     and a.wip_entity_id=b.wip_entity_id
and b.STATUS_TYPE=12
    Reference SQL1:
        SELECT * FROM MTL_INTERFACE_ERRORS  MIE ,wip_cost_txn_interface TI  
         WHERE TI.TRANSACTION_ID=MIE.TRANSACTION_ID;
    Reference SQL1:
        SELECT A.* FROM WIP_TXN_INTERFACE_ERRORS A,wip_cost_txn_interface B
         WHERE A.TRANSACTION_ID=B.TRANSACTION_ID;
---------------------------------------------------------------------------------------
update  wip_discrete_jobs wdj
set attribute13=status_type,
     attribute12='20061201'
where wip_entity_id in
            (select wip_entity_id   from wip_cost_txn_interface txn)
and status_type<>4
and status_type<>12;
rollback
update wip_discrete_jobs wdj
set  status_type = 4
where attribute12='20061201' and status_type <> 12;
update wip_cost_txn_interface
set GROUP_ID = null,process_status =1,process_phase=1,transaction_type=3
where resource_code like '%OSP%';
update wip_cost_txn_interface
set GROUP_ID = null,process_status =1,process_phase=2,transaction_type=2
where resource_code not like '%OSP%';
update mtl_material_transactions
set costed_flag = 'N',
request_id = NULL,
transaction_group_id = NULL,
error_code = NULL,
error_explanation = NULL
where costed_flag in ('N','E');
update wip_discrete_jobs wdj
set status_type = attribute13
where  attribute12='20061201';
commit;

433:5
select * from mtl_material_transactions mmt
where mmt.costed_flag  in ('E','N')
select mmt.costed_flag,mmt.attribute15,mmt.transaction_date,
mmt.transaction_id,mmt.move_transaction_id,mmt.transaction_source_id,
mmt.error_code,mmt.error_explanation,mmt.*
from mtl_material_transactions mmt
where mmt.costed_flag  in ('E','N')
select count(*) from mtl_material_transactions mmt
where mmt.costed_flag  ='E'
select * from wip_discrete_jobs
where wip_entity_id=487622
433 2702
select * from wip_period_balances
where wip_entity_id=487622
Select * from mtl_transaction_accounts mta
where reference_account is null
and   Mta.transaction_date >sysdate-32

select TRANSACTION_ID,sum(BASE_TRANSACTION_VALUE)
from wip_transaction_accounts wta
group by TRANSACTION_ID
having sum(BASE_TRANSACTION_VALUE) <> 0;
select mmt.transaction_id,sum(BASE_TRANSACTION_VALUE)
from mtl_transaction_accounts mta,mtl_material_transactions mmt
where mmt.transaction_id = mta.transaction_id
and acct_period_id between 1800 and 1823-This Period max id and Min Id
group by mmt.transaction_id
having sum(BASE_TRANSACTION_VALUE) <> 0;


update  fnd_user_responsibility fur
set fur.end_date =null
where  fur.end_date =to_date('2006/02/28','yyyy/mm/dd')

COMMIT

yuqing.tang

ddddddddddddddddddddddddd

wilson1966

update  fnd_user_responsibility fur
set fur.end_date =null
where  fur.end_date =to_date('2006/02/28','yyyy/mm/dd')

为什么要设这段,把所有人职责都设结束日,那他们就无法登入