oracle 欠料报表,EBS ORACLE工单齐套率的计算程序

最新推荐文章于 2023-05-11 23:24:00 发布
最新推荐文章于 2023-05-11 23:24:00 发布
阅读量496 收藏 1
点赞数
文章标签： oracle 欠料报表

Oracle数据库 库存管理 订单处理 游标 供应链
关键词由CSDN通过智能技术生成

PROCEDURE Get_wip_accept_item_date(p_use_id in number,

p_org_id IN NUMBER,

p_start_date IN DATE,

p_end_date IN DATE)

is

cursor accept_cursor(p_org number, p_inventory_item_id number) is

select wro.required_quantity,

wro.quantity_issued,

wro.quantity_per_assembly,

wro.wip_entity_id,

wro.organization_id,

wro.inventory_item_id,

wro.required_quantity - NVL(wro.quantity_issued, 0) quantity,

wdj.description

-- wro.mps_required_quantity quantity --净需求

from wip_discrete_jobs wdj, wip_requirement_operations wro

where wdj.wip_entity_id = wro.wip_entity_id

and wdj.organization_id = wro.organization_id

and wro.inventory_item_id = p_inventory_item_id

and wro.organization_id = nvl(p_org, wro.organization_id)

and wdj.status_type in (1, 3) --已发放，未发放

and wdj.class_code not in ('BLA2', 'BLP2')

and wro.wip_supply_type!=6

and wro.required_quantity > NVL(wro.quantity_issued, 0)

-- and wro.date_required between p_start_date and p_end_date

and wdj.scheduled_start_date between p_start_date and p_end_date --modify by lmh0525

-- and wdj.wip_entity_id=169287

--and wro.inventory_item_id =20360

order by wdj.scheduled_start_date

;

--进检

cursor jinjian_cursor(p_org number,

p_inventory_item_id number,

p_tran_id number) is

SELECT RT.TRANSACTION_DATE,

rt.quantity -

nvl(CUX_INV_ARRIVE_ITEM_DATE_new.get_deliver_qty(rt.transaction_id),0) -

nvl(CUX_INV_ARRIVE_ITEM_DATE_new.get_reject_qty(rt.transaction_id),0) -

nvl(tt.qty,0) quantity,

rt.quantity fyquantity,

nvl(CUX_INV_ARRIVE_ITEM_DATE_new.get_reject_qty(rt.transaction_id),0) reject_qty,

rt.transaction_id

FROM RCV_TRANSACTIONS RT,

po_lines_all pll,

(select object_id, sum(quantity) qty

from WIP.CUX_QUANTITY_STATUS

where request_id = p_tran_id

and status='T6'

and Create_By =p_use_id

group by object_id) tt

WHERE RT.TRANSACTION_TYPE = 'RECEIVE'

and rt.po_line_id = pll.po_line_id

and pll.item_id = p_inventory_item_id

and rt.organization_id = p_org

and rt.transaction_id = tt.object_id(+)

and rt.quantity -

nvl(CUX_INV_ARRIVE_ITEM_DATE_new.get_deliver_qty(rt.transaction_id),0) -

nvl(CUX_INV_ARRIVE_ITEM_DATE_new.get_reject_qty(rt.transaction_id),0) -

nvl(tt.qty, 0) > 0

order by RT.TRANSACTION_DATE;

---在途 ship

cursor shipment_cursor(p_org_id number,

p_item_id number,

p_tran_id number) is

SELECT a.Shipment_Line_Id,

a.EXPECTED_RECEIPT_DATE, --预计到货时间*/

a.QUANTITY_SHIPPED - a.QUANTITY_RECEIVED - nvl(tt.qty,0) quantity

FROM MEG_CUX_PO_SHIPMENT_V a,

(select object_id, sum(quantity) qty

from WIP.CUX_QUANTITY_STATUS

where request_id = p_tran_id

and status='SHIP'

and Create_By =p_use_id

group by object_id) tt

WHERE RECEVIED_FLAG = '1'

and SHIP_TO_ORGANIZATION_ID = p_org_id

and ITEM_ID = p_item_id

and a.STAGE <> 'INTERFACE'

and a.Shipment_Line_Id = tt.object_id(+)

and a.QUANTITY_SHIPPED - a.QUANTITY_RECEIVED - nvl(tt.qty,0) > 0;

--采购

cursor po_cursor(p_org_id number, p_item_id number, p_tran_id number) is

SELECT pll.line_location_id,

pll.need_by_date, --需求日期

pll.promised_date, --承诺日期

pll.creation_date, --下单日期

(case when pll.promised_date is null then 'N'

ELSE 'Y' end ) Trflase,--是否更新承诺日期

decode(pll.closed_code,

'CLOSED FOR RECEIVING',

decode(sign(PLL.QUANTITY - PLL.QUANTITY_CANCELLED -

pll.quantity_received),

1,

pll.quantity_received,

PLL.QUANTITY - PLL.QUANTITY_CANCELLED),

PLL.QUANTITY - PLL.QUANTITY_CANCELLED) -

nvl(rsp.QUANTITY_SHIPPED, 0) - nvl(pll.quantity_received, 0) -

nvl(t3.qty, 0) quantity,

decode(nvl(pll.po_release_id, 0),

0,

pha.segment1,

pha.segment1 || '-' || pra.release_num) PO_NO,

PLA.LINE_NUM,PLL.SHIPMENT_NUM,

pv.VENDOR_NAME,nvl(pv.VENDOR_NAME_ALT, pv.VENDOR_NAME) VENDOR_NAME_ALT

FROM po_line_locations_all PLL,

PO_LINES_ALL PLA,

(SELECT a.PO_LINE_LOCATION_ID,

sum(nvl(QUANTITY_SHIPPED, 0) -

nvl(QUANTITY_RECEIVED, 0)) QUANTITY_SHIPPED

FROM MEG_CUX_PO_SHIPMENT_V a

WHERE a.RECEVIED_FLAG = '1'

group by a.PO_LINE_LOCATION_ID) rsp,

(select object_id, sum(quantity) qty

from WIP.CUX_QUANTITY_STATUS cqs

where cqs.status='PO'

AND request_id = p_tran_id

and Create_By =p_use_id

group by object_id) t3,

po_headers_all pha,

po_releases_all pra,

po_vendors pv

WHERE 1 = 1

AND PLL.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PLL.PO_LINE_ID = PLA.PO_LINE_ID

AND pll.line_location_id = rsp.PO_LINE_LOCATION_ID(+)

and pll.shipment_type <> 'PRICE BREAK'

and nvl(pll.closed_code, 'OPEN') not in

('CLOSED', 'FINALLY CLOSED')

AND pla.item_id = p_item_id

and decode(pll.closed_code,

'CLOSED FOR RECEIVING',

decode(sign(PLL.QUANTITY - PLL.QUANTITY_CANCELLED -

pll.quantity_received),

1,

pll.quantity_received,

PLL.QUANTITY - PLL.QUANTITY_CANCELLED),

PLL.QUANTITY - PLL.QUANTITY_CANCELLED) -

nvl(rsp.QUANTITY_SHIPPED, 0) - nvl(pll.quantity_received, 0) -

nvl(t3.qty,0) > 0

and pll.ship_to_organization_id = p_org_id

and pll.line_location_id = t3.object_id(+)

and pha.po_header_id = pll.po_header_id

and pha.org_id = pll.org_id

and pll.po_release_id = pra.po_release_id(+)

and pll.org_id = pra.org_id(+)

and pha.vendor_id = pv.VENDOR_ID

order by pll.promised_date;

--PR数量

cursor pr_cursor(p_org_id number, p_item_id number, p_tran_id number) is

select prh.requisition_header_id,

prl.quantity - nvl(prl.quantity_cancelled, 0) quantity,

prh.creation_date creation_date,

prh.description --

from po_requisition_headers_all prh,

po_requisition_lines_all prl,

mtl_system_items_b msi,

(select object_id, sum(quantity) qty

from WIP.CUX_QUANTITY_STATUS

where request_id = p_tran_id

AND status='PR'

and Create_By =p_use_id

group by object_id) tt

where prl.item_id = msi.inventory_item_id

and prh.requisition_header_id = prl.requisition_header_id

and prl.destination_organization_id = msi.organization_id

and prl.destination_organization_id=p_org_id

and prl.item_id =p_item_id

and prh.requisition_header_id = tt.object_id(+)

and prl.reqs_in_pool_flag ='Y'

and nvl(prl.cancel_flag, 'N')= 'N'

order by prh.creation_date ;

--获取料号

cursor org_item is

select wro.organization_id, wro.inventory_item_id

from wip_discrete_jobs wdj, wip_requirement_operations wro

where wdj.wip_entity_id = wro.wip_entity_id

and wdj.organization_id = wro.organization_id

and wro.organization_id = nvl(p_org_id, wro.organization_id)

and wdj.status_type in (1, 3)

and wdj.class_code not in ('BLA2', 'BLP2')

and wro.required_quantity > NVL(wro.quantity_issued, 0)

--and wro.date_required between p_start_date and p_end_date

and wdj.scheduled_start_date between p_start_date and p_end_date

and wro.wip_supply_type!=6

-- and wdj.wip_entity_id =172093

--and wro.inventory_item_id =20360

--and wro.inventory_item_id = 16750--直接测试某个料号 1213

--and wdj.wip_entity_id in (158327,168572,168577)

--and wro.inventory_item_id = 77234--直接测试某个料号 1213

group by wro.organization_id,

wro.inventory_item_id

;

--dbms_output.put_line('开始'||to_date(p_start_date,'YYYY-MM-DD'));

x_quantity_recv number; -- 合计进检

--x_quantity_stock number; -- 合计库存

x_quantity_ship number; --合计发运

x_quantity_on_order number; -- 合计在途

x_req_qty number;

-- x_quantity_issued number;

x_req_date date;

x_status varchar2(20);

x_subinventory_code varchar2(10);

stock_qty number := 0;

x_L21_ORGQTY number := 0;

p_tran_id number;

x_quantity number := 0; --不同状态对应的数量

x_sum_lack_quantity number := 0;

p_require_id number := 0;

x_inv_quantity number := 0;--需求数量

-- x_required_quantity number := 0;

x_po_no varchar2(50);

x_LINE_NUM number := 0; --行号

x_SHIPMENT_NUM number := 0; --发运号

x_p_vendor varchar2(50);--供应商

l_po_sumqty number := 0;--po总数量

x_sum_inv_check_qty number := 0;

x_pr_qty number := 0;

l_pr_sumqty number := 0;--PR总数量

x_tflase varchar2(20);

x_ship_qty number := 0;

--x_Shipment_Line_Id number := 0;--发运id

l_ent_pending number := 0;

l_nocheck_pending number := 0;

l_reject_qty number := 0;

l_line_location_id number := 0;

l_other_inv_qty number := 0;

x_other_wip_qty number := 0;

l_min_qty number :=0;

begin

delete from WIP.CUX_WIP_ACCEPT_ITEM_DATE where create_by =p_use_id;

delete from WIP.CUX_QUANTITY_STATUS where create_by =p_use_id ;

commit;

SELECT for_CUX_WIP_ACCEPT_ITEM_SEQ.NEXTVAL into p_require_id from dual; --作为请求的id

for l_c in org_item loop

begin

select SUBSTR(MOV.ORGANIZATION_CODE, 2, 2) ||

DECODE(mc.description,

'原材料',

'01',

'半成品/组件',

'02',

'成品',

'03',

'01')

into x_subinventory_code

from mtl_item_categories mic,

meg_cux_org_v mov,

mtl_categories_b mc

where mic.organization_id = mov.ORGANIZATION_ID

and mic.inventory_item_id = l_c.inventory_item_id

and mic.category_set_id = 1100000041

and mic.category_id = mc.category_id

and mc.structure_id = 50355

and mic.organization_id = l_c.organization_id;

-- dbms_output.put_line('组织：'||l_c.organization_id);

-- dbms_output.put_line('料号：'||l_c.inventory_item_id);

exception

when others then

null;

end;

--库存

/* x_quantity_stock := mg_comm_pub.get_inv_qty(l_c.inventory_item_id,

l_c.organization_id, --只考虑力慧组织

0);

*/

--可用库存

stock_qty := Cux_Inv_Arrive_Item_Date_New.get_inv_prdt1_qty(l_c.organization_id,

l_c.inventory_item_id);

x_inv_quantity :=stock_qty;

/* dbms_output.put_line('料号：'||l_c.inventory_item_id);

dbms_output.put_line('库存：'||stock_qty); */

x_L21_ORGQTY := cux_inv_arrive_item_date_NEW.get_inv_l21_prdt1_qty(l_c.inventory_item_id);

--stock_qty := x_quantity_stock;

--进检

x_quantity_recv := mg_comm_pub.get_po_on_order_inv(p_item_id => l_c.inventory_item_id,

p_org_id => l_c.organization_id,

p_subinventory_code => x_subinventory_code,

p_flag => 2);

-- dbms_output.put_line('进检：'||x_quantity_recv);

-- 发运

--x_sum_inv_check_qty :=stock_qty +x_quantity_recv; --库存+进检

x_quantity_ship := wwinv_pub_pkg.get_po_shipQty(p_item_id => l_c.inventory_item_id,

p_org_id => l_c.organization_id);

x_ship_qty :=x_quantity_ship;

--PO数量

x_quantity_on_order := mg_comm_pub.get_po_on_order_inv(p_item_id => l_c.inventory_item_id,

p_org_id => l_c.organization_id,

p_subinventory_code => x_subinventory_code,

p_flag => 0);

l_po_sumqty :=x_quantity_on_order;

--PR数量

x_pr_qty := cux_inv_arrive_item_date_new.get_pr_qty_by_item(l_c.organization_id,

l_c.inventory_item_id);

l_pr_sumqty :=x_pr_qty;

--进检待入库

l_ent_pending:=cux_inv_arrive_item_date_new.get_t6_noinv_by_item(l_c.organization_id,

l_c.inventory_item_id);

--待检数量

l_nocheck_pending:=cux_inv_arrive_item_date_new.get_re_nocheck_by_item(l_c.organization_id,

l_c.inventory_item_id);

l_reject_qty :=cux_inv_arrive_item_date_new.get_reject_qty_by_item(l_c.organization_id,

l_c.inventory_item_id);

l_other_inv_qty:=cux_inv_arrive_item_date_new.get_inv_other_prdt1_qty(l_c.organization_id,

l_c.inventory_item_id);

x_other_wip_qty:=cux_inv_arrive_item_date_new.get_wip_need_qty(l_c.organization_id,

l_c.inventory_item_id,

p_start_date,

p_end_date) ;

--最小订货量

l_min_qty:=cux_inv_arrive_item_date_new.get_min_order_qty(l_c.organization_id,

l_c.inventory_item_id);

-- end loop;

--x_quantity_stock 库存

for l_b in accept_cursor(l_c.organization_id, l_c.inventory_item_id) loop

-- x_req_qty : =l_b.quantity;

--库存 INV

x_req_qty := l_b.quantity;

if nvl(x_req_qty, 0) > 0 then

if stock_qty > 0 then

x_status := 'INV';

x_req_date := sysdate;

--x_inv_quantity :=stock_qty;

if nvl(x_req_qty, 0) <= stock_qty then

--库存满足需求数量

x_sum_lack_quantity :=stock_qty +x_quantity_recv-x_req_qty;--欠料情况

x_quantity := x_req_qty;

stock_qty := stock_qty - x_req_qty;

x_req_qty := 0;

else

x_sum_lack_quantity :=x_quantity_recv;--欠料情况

x_quantity := stock_qty;

x_req_qty := nvl(x_req_qty, 0) - nvl(stock_qty, 0);

stock_qty := 0;

end if;

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_ARRIVE_ITEM_DATE_COMMIT(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

SYSDATE,

x_inv_quantity,

x_L21_ORGQTY,

l_b.required_quantity,

l_b.quantity_issued,

l_b.quantity,

-- x_req_qty,

x_sum_lack_quantity,

x_req_date,

x_status,

x_quantity,

p_use_id,

NULL,

null,

null,

null,

l_b.description,

l_other_inv_qty ,

x_other_wip_qty,

null,

l_min_qty);

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_report_status_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

x_status,

x_quantity,

x_req_date,

p_require_id,

p_use_id );

end if;

end if;

--进检 T6

if nvl(x_req_qty, 0) > 0 then

-- dbms_output.put_line('需求数量：'||x_req_qty);

if x_quantity_recv > 0 then

for l_jinjian in jinjian_cursor(l_c.organization_id,

l_c.inventory_item_id,

p_require_id) loop

--if x_req_qty < x_quantity_recv then

x_status := 'T6';

x_req_date := l_jinjian.transaction_date +

CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

3);

-- x_inv_quantity :=stock_qty;

-- l_reject_qty :=l_jinjian.reject_qty;

if x_req_qty < l_jinjian.quantity then

x_sum_lack_quantity :=x_sum_lack_quantity-x_req_qty;

-- x_quantity_recv := l_jinjian.quantity - x_req_qty;

x_quantity_recv := x_quantity_recv - x_quantity;

x_quantity := x_req_qty;

x_req_qty := 0; --进检满足需求

else

x_sum_lack_quantity :=x_sum_lack_quantity-l_jinjian.quantity;

x_quantity := l_jinjian.quantity;

-- x_quantity_recv := x_quantity_recv - x_quantity;

x_req_qty := x_req_qty - x_quantity;

x_quantity_recv := 0;

end if;

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_ARRIVE_ITEM_DATE_COMMIT(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

SYSDATE,

x_inv_quantity,

x_L21_ORGQTY,

l_b.required_quantity,

l_b.quantity_issued,

l_b.quantity,

x_sum_lack_quantity,

x_req_date,

x_status,

x_quantity,

p_use_id,

NULL,

l_ent_pending,

l_nocheck_pending,

l_reject_qty,

l_b.description,

l_other_inv_qty,

x_other_wip_qty,

null,

l_min_qty);

--插入记录表

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_report_status_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

l_jinjian.transaction_id,

x_status,

x_quantity,

x_req_date,

p_require_id,

p_use_id );

end loop;

end if;

end if;

--发运 SHIP

if nvl(x_req_qty, 0) > 0 then

if x_quantity_ship > 0 then

for l_shipment in shipment_cursor(l_c.organization_id,

l_c.inventory_item_id,

p_require_id) loop

x_status := 'SHIP';

x_req_date := l_shipment.EXPECTED_RECEIPT_DATE +

CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

3);

x_sum_lack_quantity:= x_sum_lack_quantity-x_req_qty;

--x_Shipment_Line_Id :=l_shipment.Shipment_Line_Id;

if nvl(x_req_qty, 0) > 0 then

if x_req_qty < l_shipment.quantity then

x_quantity := x_req_qty;

x_quantity_ship := x_quantity_ship - x_req_qty;

x_req_qty := 0;

else

x_quantity := l_shipment.quantity;

x_req_qty := x_req_qty - x_quantity_ship;

x_quantity_ship := 0;

end if;

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_ARRIVE_ITEM_DATE_COMMIT(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

SYSDATE,

x_inv_quantity,

x_L21_ORGQTY,

l_b.required_quantity,

l_b.quantity_issued,

l_b.quantity,

x_sum_lack_quantity,

x_req_date,

x_status,

x_quantity,

p_use_id,

null,

null,

null,

null,

l_b.description,

l_other_inv_qty,

x_other_wip_qty,

x_ship_qty,

l_min_qty);

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_report_status_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

l_shipment.Shipment_Line_Id, --事物的id

x_status,

x_quantity,

x_req_date,

p_require_id,

p_use_id );

end if;

end loop;

end if;

end if;

--在途 PO

if nvl(x_req_qty, 0) > 0 then

if x_quantity_on_order > 0 then

for l_po_qty in po_cursor(l_c.organization_id,

l_c.inventory_item_id,

p_require_id) loop

x_status := 'PO';

--判断采购日期

-- x_sum_lack_quantity :=x_sum_inv_check_qty- nvl(x_req_qty, 0);

x_po_no :=l_po_qty.po_no;

x_LINE_NUM:=l_po_qty.LINE_NUM;

x_SHIPMENT_NUM:=l_po_qty.SHIPMENT_NUM;

x_p_vendor :=l_po_qty.VENDOR_NAME_ALT;

l_line_location_id :=l_po_qty.line_location_id;

if nvl(x_req_qty, 0) > 0 then

if l_po_qty.promised_date is not null then

--承诺日期不为空

x_req_date := l_po_qty.promised_date +

CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

3);

elsif (l_po_qty.promised_date is null and

l_po_qty.need_by_date >=

l_po_qty.creation_date +

CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

4)) then

--承诺日期为空，且PO需求日期>=(PO下单日期+预加工+加工中+后加工)

x_req_date := l_po_qty.need_by_date;

else

x_req_date := l_po_qty.creation_date +

CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

4);

end if;

-- x_sum_lack_quantity:= x_sum_lack_quantity-x_req_qty;

if x_req_qty < l_po_qty.quantity then

x_sum_lack_quantity :=x_sum_lack_quantity- nvl(x_req_qty, 0);

x_quantity := x_req_qty;

x_quantity_on_order := l_po_qty.quantity - x_req_qty;

x_req_qty :=0;

-- x_tflase :=l_po_qty.Trflase;

else

x_sum_lack_quantity :=x_sum_lack_quantity-l_po_qty.quantity;

x_quantity := l_po_qty.quantity;

x_req_qty := x_req_qty - l_po_qty.quantity;

x_quantity_on_order := 0;

-- x_tflase :=l_po_qty.Trflase;

-- x_po_qty_now :=-1;

end if;

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_po_pr_qty_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

SYSDATE,

x_inv_quantity,

x_L21_ORGQTY,

l_b.required_quantity,

l_b.quantity_issued,

l_b.quantity,

x_sum_lack_quantity,

x_req_date,

x_status,

x_quantity,

x_po_no,

x_LINE_NUM ,

x_SHIPMENT_NUM ,

x_p_vendor,

l_po_sumqty ,

p_use_id ,

l_po_qty.Trflase,

l_b.description,

l_line_location_id,

l_other_inv_qty,

x_other_wip_qty ,

l_min_qty );

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_report_status_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

l_po_qty.line_location_id, --事物的id

x_status,

x_quantity,

x_req_date,

p_require_id,

p_use_id );

end if;

end loop;

end if;

end if;

--请购 PR

if nvl(x_req_qty, 0) > 0 then

if x_pr_qty > 0 then

x_status := 'PR';

--l_pr_sumqty :=x_pr_qty;

for l_pr_qty in pr_cursor (l_c.organization_id,

l_c.inventory_item_id,

p_require_id) loop

x_req_date:= l_pr_qty.creation_date+CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

4);

if nvl(x_req_qty, 0) > 0 then

if x_req_qty < nvl(l_pr_qty.quantity,0) then

x_sum_lack_quantity:= x_sum_lack_quantity-x_req_qty;

x_quantity := x_req_qty;

x_pr_qty := x_pr_qty - x_req_qty;

x_req_qty :=0;

else

x_sum_lack_quantity:= x_sum_lack_quantity-l_pr_qty.quantity;

x_quantity := l_pr_qty.quantity;

x_quantity_recv := l_pr_qty.quantity - x_req_qty;

x_req_qty :=x_req_qty -l_pr_qty.quantity ;

--x_req_qty := x_req_qty - x_quantity_recv;

x_pr_qty := 0;

end if;

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_pr_qty_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

SYSDATE,

x_inv_quantity,

x_L21_ORGQTY,

l_b.required_quantity,

l_b.quantity_issued,

l_b.quantity,

x_sum_lack_quantity,

x_req_date,

x_status,

x_quantity,

l_pr_sumqty,

p_use_id ,

l_b.description,

l_pr_qty.description ,

l_min_qty );

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_report_status_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

l_pr_qty.requisition_header_id, --事物的id

x_status,

x_quantity,

x_req_date,

p_require_id,

p_use_id );

end if;

end loop ;

end if;

end if;

--L/T

if nvl(x_req_qty, 0) > 0 then

x_status := 'L/T';

x_req_date := sysdate +CUX_INV_ARRIVE_ITEM_DATE_NEW.get_preprocessing_leadtime(l_c.organization_id,

l_c.inventory_item_id,

4);

if x_sum_lack_quantity<0 then

x_sum_lack_quantity :=x_sum_lack_quantity-x_req_qty;

else x_sum_lack_quantity :=-x_req_qty;

end if ;

CUX_INV_ARRIVE_ITEM_DATE_NEW.insert_ARRIVE_ITEM_DATE_COMMIT(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id,

SYSDATE,

x_inv_quantity,

x_L21_ORGQTY,

l_b.required_quantity,

l_b.quantity_issued,

l_b.quantity,

x_sum_lack_quantity,

x_req_date,

x_status,

x_req_qty,

p_use_id,

NULL,

null,

null,

null,

l_b.description,

l_other_inv_qty,

x_other_wip_qty,

null,

l_min_qty

);

cux_inv_arrive_item_date_NEW.insert_report_status_commit(l_c.inventory_item_id,

l_c.organization_id,

l_b.wip_entity_id,

p_require_id, --事物的id

x_status,

x_quantity,

x_req_date,

p_require_id,

p_use_id );

end if;

end loop;

end loop;

commit;

end;

--

叶梵舒
关注
Oracle EBS WIP任务单批量发料功能开发
陈显平的专栏-Oracle EBS
04-12 1992
最近公司线材半成品需要分层成多个半成品，由一束线的半成品变成三四十多个半成品，该业务需求会带来哪些影响呢？增加半成品的物料类别、物料编码、工艺路线、资源和BOM清单等等会产生大量任务单，从任务单发料至完工各个环境工作量增加，导致管理成本上升仓库就提出任务单能不能批量发料功能？从技术角度来说，理论上是可以实现在的。经过对业务部关键用户的需求调研和分析得出如下:由于不同装配件所有用到的原材料或半成品在...
EBS ORACLE工单齐套率的计算程序
weixin_30414245的博客
06-19 864
PROCEDURE Get_wip_accept_item_date(p_use_id in number, p_org_id IN NUMBER, p_start_date IN DATE, ...
oracle wip 拆解工单 操作_总账工单 32问
weixin_36352523的博客
12-01 718
总账工单自2020年5月上线以来，大家提出了很多中肯的意见与建议，系统持续进行着更新与优化，今天跟大家聊聊总账工单在使用过程中经常碰到的问题。一、权限问题Q1无总账工单制单权限怎么办？答：联系本单位关键用户开通，关键用户一般为财务负责人。开通方式：直接在报账系统的系统管理中进行用户授权增加即可。Q2总账工单制单人无oracle系统制单权限，凭证是否可以推送？答：可以推送。二、所属部门Q1...
oracle wip 拆解工单 操作_ORACLE成本管理中WIP处理对成本计算的影响
weixin_39790686的博客
12-19 607
1.工单开立：与成本无关。只有工单状态为“Release”时，才可进入下一步的“工单发料” ，并将数量移到OP01的Queue状态Job Name: 可以自动编号，也可的工输入Assembly：需要制造的装配件Type：标准(正式，量产，大量的，BOM与ROUTING已经标准化了，会COPY BOM和routing)与非标准(upgrade, 修理，根据实际情况定，不COPY BOM和routin...
本人对Oracle Work In Process模块的一些了解
huangwenkangz的博客
11-26 1887
此系统中的制造有离散制造，流程制造重复制造，本人此处说的是离散制造 制造处在系统的最底层，也是最繁琐的一层，数据量大，物料多，自然工作量也大，出现错误的几率也大。 1、工单类型 分为标准工单和非标准工单 标准工单：是根据BOM展开，组件，替代料，工站都是跟BOM一样，但是可以手动对其进行更改 非标准工单：一般是返工返修的工单，不根据BOM展开，组件，工站都是自己手动添加。 2、工单状态
Oracle EBS工单的Start Date 和Completion计算测试(Simple)
Allen_tang的博客
03-16 673
前提： 通常，在手工建立一个Discrete Job时，我们会输入一个Start Date： 当我们点击保存是Completion会自动计算出来： Question：那个这个时间怎么计算出来的呢？ 首先我推荐大家读一读百*文库的一篇文章： ~https://wenku.baidu.com/view/0b1261fff705cc17552709c6.html~ 1，首先，我们看看Department：Line5： 只有一个组装工序，24小时可用，部门资源数=8， 提示一下：是.
oracle推式任务发料,工单发料态别（转）
weixin_34285757的博客
04-03 713
在定义BOM的时候，我们需要设定，工单供货的方式(Supply Type).Push推式发料，必须手工通过界面做发料，价格昂贵的，需严格控制数量，或者波动比较大的物料。推式组件根据需要可被直接发放至离散任务和重复性计划。虽然在制品管理系统会默认与组件关联的供应子库存，但您仍可以指定从中发放推式组件的子库存。Assembly Pull拉式发料，装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物...
Oracle关工单差异分析,ORACLE成本管理中WIP处理对成本计算的影响(zt)
weixin_28728715的博客
04-03 1135
1、工单开立：与成本无关。只有工单状态为“Release”时，才可进入下一步的“工单发料” ，并将数量移到OP01的Queue状态Job Name: 可以自动编号，也可的工输入Assembly：需要制造的装配件Type：标准(正式，量产，大量的，BOM与ROUTING已经标准化了，会COPY BOM和routing)与非标准(upgrade, 修理，根据实际情况定，不COPY BOM和routin...
OracleERP EBS表结构(INV、WIP、po、bom、om)
06-16
主流的国外ERP无非Oracle和SAP，本资源是Oracle对应的后台表结构，涵盖了库存INV Org、仓库Sub INV、制造WIP、采购PO、计划Plan、BOM、销售OM的后台明细（中文逐字段注释！） 还有财务部分的索引AR\AP\Cost\GL等。
计划采购订单
weixin_34414650的博客
05-11 254
计划采购订单    采购订单，有标准采购订单、计划类采购订单、一揽子采购协议等。    我们现在就分别来讲述这三者的差别，并在此章节着重讲述计划类采购订单。   一、采购订单    标准采购订单(从一供应商处做一次性物料或服务的采购)        建议:当你与供应商确认了采购对象(物料或服务), 数量, 发货计划, 但是不存在长期的约定时, 可使用标准采购订单.    合同采购订单...
库存单据计算逻辑和运费计算
最新发布
韬智信息
05-11 478
EAS 8.8 供应链库存单据 计量和库存常用判断逻辑脚本
Oracle财务系统常用标准报表
weixin_33692284的博客
03-07 490
总账系统 系统报表名 中文译名 用途描述 Journals-Voucher 日记账传票 通过提交该报表来打印日记账传票 Journals-Document Nummber 传票文档号 按传票的文档序列号来打印日记账传票列表,可作为装订每月传票的附件. Journals-Entry 日记账 按本位币或外币列示某一明细账户值(不管PL和Dept.)的...
Counting the on hand quantities by API inv_quantity_tree_pub.query_quantities
天狼岛
05-30 5425
Oracle ERP R11 DECLARE x_return_status VARCHAR2 (1); x_msg_data VARCHAR2 (4000); x_msg_count NUMBER; x_qoh NUMBER; x_rqoh NUMBER; x_qr NUMBER; x_qs NUMBER; x_att NUM
oracle 物料属性批次过期,ORACLE EBS 物料事务处理开发接口案例 - 其物料启用了序列和批次...
weixin_36197581的博客
04-05 469
DECLAREl_iface_rec inv.mtl_transactions_interface% ROWTYPE;l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE ;l_iface_serial_rec inv.mtl_serial_numbers_interface%ROWTYPE ;l_cur_mfg_...
Oracle EBS Interface/API(19)-PO修改行或发运行的单价、数量、需求日期和承诺日期等等
陈显平的专栏-Oracle EBS
12-08 2766
调用API修改标准订单或一揽子发放行或发运行的单价、数量、需求日期和承诺日期等等，实例参考如下： 一、修改发运行API实例： DECLARE Lt_Errors Po_Api_Errors_Rec_Type; Lt_Chg Po_Changes_Rec_Type; Lv_Return_Status V...
详解EBS接口开发之采购订单导入
weixin_39156230的博客
05-10 1706
采购订单常用标准表简介 1.1 常用标准表 如下表中列出了与采购订单导入相关的表和说明： 表名 说明 其他信息 po.po_headers_all 采购订单头 采购订单号，采购类型，供应商，地点，采购员，订单描述等基础信息 po_headers 对应视图 po.po_lines_all 采购订单行 具体向供应商采购的物料，物料的数量，单价，币种，汇率，交期等一系列信息 po_lines 对应视...
ebs查看服务状态_Oracle EBS - 工单状态
weixin_39594895的博客
12-19 354
Job status update1. Job的几种状态unreleased　--未核发released--已核发complete --完成complete no charges--完成不计费on hold --保留cancelled--取消closed--关闭2. 开立工单可以给的状态UnreleasedReleasedOn hold3. 关于unreleasedUnre...
Oracle EBS 11i 应用模块功能深度解析
车间控制跟踪生产进度，管理工单，而质量管理则确保产品符合既定标准。 文档中可能还包括了各个模块之间的集成，如采购订单与应付账款的联动，销售订单与应收账款的关联，以及生产活动与库存管理的协调。此外，EBS ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值