Create Move Order

提前後表后，產生新的Move Order並打單去倉庫領料中的procedure(BY Part報表(p_org_id in number,p_component in varchar2,
                  p_JOB_LO in varchar2,JOB_HI in varchar2,
                  p_seq_number varchar2,p_user_id in number,
                  p_txn_action_id in number,p_txn_type_id in number,
                  p_reason1 in number,p_reason in varchar2,p_transaction_type in varchar2) is
 p_api_version_number  NUMBER;
 p_trohdr_rec   INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
 p_trohdr_val_rec  INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
 x_trohdr_rec   INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
 x_trohdr_val_rec  INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
 p_trolin_tbl   INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
 p_trolin_val_tbl  INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;

 x_trolin_tbl   INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
 x_trolin_val_tbl  INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;
 x_return_status                 VARCHAR2(2000);
 x_msg_count                     NUMBER;
 x_msg_data                      VARCHAR2(2000);
        p_msg                           VARCHAR2(2000):='TEST';
        p_num                           NUMBER:=1;
cursor move_order is
select tmp.organization_id,tmp.job,
       tmp.wip_entity_id,
       tmp.assembly,tmp.assembly_id,
    tmp.component,tmp.inventory_item_id,
       tmp.date_required,

    tmp.req_qty,tmp.issue_qty,
    tmp.req_qty-nvl(tmp.issue_qty,0) open_qty,
    tmp.source_inv,tmp.supply_inv,
    tmp.primary_uom_code
from
(select job.wip_entity_id,job.job,job.assembly,
       job.component,job.assembly_id,
       job.date_required,job.req_qty,
    job.primary_uom_code,
    job.inventory_item_id,
    job.organization_id,
    job.source_inv,
    job.supply_inv,

    abs(sum(txn.primary_quantity)) issue_qty
from
(select mmt.organization_id,mmt.inventory_item_id,
       mmt.subinventory_code,mmt.transfer_subinventory,
    mmt.primary_quantity/nvl(muc.conversion_rate,1) primary_quantity,mmt.transaction_reference
from   mtl_material_transactions mmt,mtl_uom_conversions muc
where   mmt.organization_id          =  p_org_id
AND     mmt.transaction_action_id    =  p_txn_action_id   --2
AND     mmt.inventory_item_id=muc.inventory_item_id(+)
AND     muc.disable_date is null
AND     mmt.transaction_type_id in (SELECT * FROM THE (select CAST( bg_fn_vostrtbl (p_transaction_type) AS voTableType ) FROM dual))  --204
AND     mmt.reason_id in (SELECT * FROM THE (select CAST( bg_fn_vostrtbl (p_reason) AS voTableType ) FROM dual))) txn,
--AND     mmt.reason_id               in  (p_reason1,p_reason2)) txn,--5 is 11 INV - Production Issue  528 is 66 INV - Reverse Adjust

/*ignore the txn reason id update by hermit on 20070531*/
(SELECT
    we.organization_id,
 we.wip_entity_id,
 we.wip_entity_name  Job,
 msi1.segment1  Assembly,
 wro.supply_subinventory         Supply_inv,
 NVL(wdj.primary_item_id, -1) Assembly_Id,
 wro.date_required  Date_Required,
 msi2.segment1  Component,
 wro.attribute5                  source_inv,
        msi2.inventory_item_id,
        wro.required_quantity/nvl(muc.conversion_rate,1)     Req_qty,

 decode(muc.unit_of_measure,null,msi2.primary_uom_code,muc.uom_code) primary_uom_code
FROM
 mtl_system_items  msi1,
 wip_entities  we,
 mtl_system_items  msi2,
 wip_requirement_operations wro,
 wip_discrete_jobs  wdj,
        mtl_uom_conversions muc
WHERE
 wdj.organization_id  =  P_Org_Id
AND wdj.status_type  NOT IN (7,12)
AND msi1.organization_id  =  P_Org_Id
AND msi1.inventory_item_id(+) =  wdj.primary_item_id

AND we.organization_id  =  P_Org_Id
AND we.wip_entity_id  =  wdj.wip_entity_id
AND we.entity_type  =  1
AND wro.organization_id  =  P_Org_Id
AND wro.wip_entity_id  =  wdj.wip_entity_id
AND wro.wip_supply_type <> 6
AND wro.required_quantity >  0
AND msi2.organization_id  =  P_Org_Id
AND msi2.inventory_item_id =  wro.inventory_item_id
AND     msi2.inventory_item_id=muc.inventory_item_id(+)
AND     muc.disable_date is null
) job
where   txn.organization_id(+)          =  p_org_id

AND     txn.inventory_item_id(+)        =  job.inventory_item_id
AND     txn.transfer_subinventory(+)    =  job.supply_inv
AND     txn.transaction_reference(+)    =  job.job
group by
job.wip_entity_id,job.job,job.assembly,
       job.component,job.assembly_id,
       job.date_required,job.req_qty,
    job.inventory_item_id,
    job.organization_id,
    job.source_inv,
    job.supply_inv,
    job.primary_uom_code
) tmp

where
    tmp.organization_id=p_org_id
and tmp.Component=p_Component;
--and tmp.req_qty-nvl(tmp.issue_qty,0)>0 ;
--consider the overisuue material transaction
BEGIN
-- p_trohdr_rec
---------------------
--- record contains information to be used to create the
-- move order header
-- p_trohdr_val_rec
---------------------
--  contains information values as supposed to internal

-- IDs used to create the move order header
 fnd_global.APPS_INITIALIZE(user_id => p_user_id,resp_id => 20634,resp_appl_id =>401);--2348
 p_trohdr_rec.request_number  := p_seq_number;
 --p_trohdr_rec.from_subinventory_code := p_source_inv;
 p_trohdr_rec.organization_id  :=  p_org_id;
 p_trohdr_rec.header_status:= 1; -- status- incomplete
 --p_trohdr_rec.to_subinventory_code := p_dest_inv;
 p_trohdr_rec.move_order_type  :=  1;
 p_trohdr_rec.transaction_type_id :=  p_txn_type_id;  --wip backflush Transfer
 p_trohdr_rec.Operation   :=  inv_globals.g_opr_create;
 --p_trohdr_val_rec.from_subinventory := p_source_inv;
 p_trohdr_val_rec.organization  := p_org_id;
 --p_trohdr_val_rec.to_subinventory := p_dest_inv;

 p_trohdr_val_rec.move_order_type := 1;
 p_api_version_number   := 1.0;
 inv_move_order_pub.create_move_order_header
 (
  p_api_version_number => p_api_version_number,
  p_init_msg_list  => FND_API.G_TRUE,
  p_return_values  => FND_API.G_FALSE,
  p_commit  => FND_API.G_FALSE,
  x_return_status  => x_return_status,
  x_msg_count  => x_msg_count,
  x_msg_data  => x_msg_data,
  p_trohdr_rec  => p_trohdr_rec,
  p_trohdr_val_rec => p_trohdr_val_rec,

  x_trohdr_rec  => x_trohdr_rec,
  x_trohdr_val_rec => x_trohdr_val_rec
 );
 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
  COMMIT;
         dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
         -- dbms_output.put_line('Request Number= '|| x_trohdr_rec.request_number);
 ELSE
  dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
  dbms_output.put_line('Msg Count     = '|| TO_CHAR(x_msg_count));
  dbms_output.put_line('Msg Data      = '|| SUBSTR (x_msg_data,1,255));
  IF x_msg_count >1 THEN
   FOR I IN 1..x_msg_count LOOP

    dbms_output.put_line(I||'. '|| SUBSTR (FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
   END LOOP;
  END IF;
 END IF;
 -- Line create --
        --delete from wippminr_move_order;
        FOR MOVE_REC IN MOVE_ORDER loop
        p_trolin_tbl(p_num).operation  := inv_globals.g_opr_create;
 p_trolin_tbl(p_num).line_number  := p_num;
 p_trolin_tbl(p_num).from_subinventory_code := move_rec.source_inv;
 p_trolin_tbl(p_num).header_id  := x_trohdr_rec.header_id;
 p_trolin_tbl(p_num).inventory_item_id := move_rec.inventory_item_id;-- Z-1001
 p_trolin_tbl(p_num).organization_id := p_org_id;

 p_trolin_tbl(p_num).quantity  := ABS(move_rec.open_qty);
 p_trolin_tbl(p_num).to_subinventory_code:= move_rec.supply_inv;
 p_trolin_tbl(p_num).transaction_type_id := p_txn_type_id;--204;  --wip backflush Transfer
 p_trolin_tbl(p_num).date_required := SYSDATE;
 p_trolin_tbl(p_num).uom_code  := move_rec.primary_uom_code;
        p_trolin_tbl(p_num).reason_id           := p_reason1;  --5
 p_trolin_tbl(p_num).reference   := move_rec.job;
        p_trolin_tbl(p_num).line_status  := 1;
        p_num:=p_num+1;
--/*
    insert into bg_wippminr_move_order
    values(p_org_id,move_rec.wip_entity_id,
           move_rec.Assembly_Id,move_rec.job,move_rec.inventory_item_id,p_component,

           move_rec.primary_uom_code,move_rec.source_inv,move_rec.Supply_inv,
           move_rec.Date_Required,move_rec.req_qty,move_rec.issue_qty,
           p_seq_number); --*/
end loop;
 p_api_version_number   := 1.0;
 inv_move_order_pub.create_move_order_lines
 (
  p_api_version_number => p_api_version_number,
  p_init_msg_list  => FND_API.G_TRUE,
  p_return_values  => FND_API.G_FALSE,
  p_commit  => FND_API.G_FALSE,
  x_return_status  => x_return_status,
  x_msg_count  => x_msg_count,

  x_msg_data  => x_msg_data,
  p_trolin_tbl  => p_trolin_tbl,
  p_trolin_val_tbl => p_trolin_val_tbl,
  x_trolin_tbl  => x_trolin_tbl,
  x_trolin_val_tbl => x_trolin_val_tbl
 );
 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
         COMMIT;
         dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
         dbms_output.put_line('Header Id     = '|| x_trolin_tbl(1).header_id);
 ELSE
  dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
  dbms_output.put_line('Msg Count     = '|| TO_CHAR(x_msg_count));

  dbms_output.put_line('Msg Data      = '|| SUBSTR (x_msg_data,1,255));
  IF x_msg_count >1 THEN
   FOR I IN 1..x_msg_count LOOP
    dbms_output.put_line(I||'. '|| SUBSTR (FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
   END LOOP;
  END IF;
 END IF;
END;
/
)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7960369/viewspace-219176/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7960369/viewspace-219176/