--debit pcdi内部调整
PROCEDURE get_pcdi_debit_adj(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
is
--需冲销负数的物料
cursor cn_item is
select da.item_id,da.sbc_id,da.qtyinstock,da.debitqty,da.totalqtyinstock,da.unit_price
from secom.secom_pcdi_debit_adj da
where da.qtyinstock<0;
--可核销正数的物料
cursor ca_item(cp_item_id numeric,cp_unit_price numeric) is
select *
from(
select abs(da.unit_price-cp_unit_price) abs_prices ,da.creation_date , da.avagopartnum,da.item_id,da.sbc_id,da.qtyinstock,da.debitqty,da.totalqtyinstock
,da.unit_price
from secom.secom_pcdi_debit_adj da
where da.item_id=cp_item_id and da.qtyinstock>0
) dav
order by dav.abs_prices,dav.creation_date;
--生成报表
cursor c_item is
select
da.avagopartnum ,da.sbc,da.qtyinstock,da.debitqty,da.totalqtyinstock
,da.unit_price,da.amount,da.currency_code,da.brank,da.product_line
from secom.secom_pcdi_debit_adj da order by da.item_id,da.unit_price;
--创建动态游标
TYPE ref_cursor_type IS REF CURSOR;
p_r_item ref_cursor_type;
v_sql varchar2(1000);
p_item_id numeric;
p_sbc_id numeric;
p_qtyinstock numeric;
--临时变量
p_tmp_a_qty numeric;
p_remain_n_qty numeric;
p_sum_n_qty numeric;
begin
errbuf := NULL ;
retcode := '0' ;
SAVEPOINT get_pcdi_debit_adj;
--=====================================================
--先清空原来的表,再读取最新的库数据
delete from secom.secom_pcdi_debit_adj;
insert into secom.secom_pcdi_debit_adj(
ITEM_ID,avagopartnum,SBC_ID,vendor_code,vendor_name
,qtyinstock,debitqty,totalqtyinstock,unit_price
,currency_code,brank,product_line,creation_date,sbc
)
select
pfsv.ITEM_ID
,pfsv.avagopartnum
,pfsv.SBC_ID
,pfsv.vendor_code
,pfsv.vendor_name
,pfsv.totalqtyinstock qtyinstock
,null debitqty
,null totalqtyinstock
,sl.UNIT_PRICE unit_price
,sl.currency_code
,mtl.brank
,mtl.product_line
,sl.creation_date
,sl.sbc
from
(
select pfs.ITEM_ID ,msi.segment1 avagopartnum, pfs.SBC_ID
, pv.segment1 vendor_code
, pv.vendor_name
, sum( --汇总同一物料同一SBC的数量
case when (nvl(poqty.QUANTITY,0)-nvl(pomqty.QUANTITY,0))> nvl(poqty.QUANTITY,0)
then nvl(poqty.QUANTITY,0)
else (nvl(poqty.QUANTITY,0)-nvl(pomqty.QUANTITY,0)) end
) totalqtyinstock --即为 nomatch_quantity 参考函数 secom_so_debit_pkg.get_shipment_nomatchqty 优化的逻辑
from SECOM_PPC_FROM_SBC_V pfs
,PO_HEADERS_ALL PHA
,po_line_locations_all plla
,mtl_system_items_b msi
,po_vendors pv
,(--调整后的PO数量
SELECT PFS.LINE_LOCATION_ID ,pfs.SBC_ID , nvl(SUM(PFS.QUANTITY),0) QUANTITY
FROM SECOM_PPC_FROM_SBC_V PFS
group by PFS.LINE_LOCATION_ID ,pfs.SBC_ID
) poqty
,(--核销的PO数量
SELECT SSM.Line_Location_Id ,ssm.sbc_list_id , nvl(SUM(SSM.QUANTITY_MATCHED),0) QUANTITY
FROM SECOM_SOPO_MATCHES SSM
WHERE MATCH_ID <> nvl(null,-1)
group by SSM.Line_Location_Id ,ssm.sbc_list_id
)pomqty
where pfs.PO_HEADER_ID=pha.po_header_id(+)
and pfs.LINE_LOCATION_ID = plla.line_location_id(+)
and msi.Organization_Id = plla.ship_to_organization_id
and msi.inventory_item_id = pfs.ITEM_ID
and pha.vendor_id=pv.vendor_id(+)
and pfs.LINE_LOCATION_ID=poqty.LINE_LOCATION_ID(+)
and pfs.SBC_ID=poqty.SBC_ID(+)
and pfs.LINE_LOCATION_ID=pomqty.Line_Location_Id(+)
and pfs.SBC_ID=pomqty.sbc_list_id(+)
and pha.vendor_id in (120 ) --322,6
and PFS.COST_MODE = 'RESALES_DEBIT' --售后折扣的
and nvl((--库存需大于0的物料
case when (nvl(poqty.QUANTITY,0)-nvl(pomqty.QUANTITY,0))> nvl(poqty.QUANTITY,0)
then nvl(poqty.QUANTITY,0)
else (nvl(poqty.QUANTITY,0)-nvl(pomqty.QUANTITY,0)) end
),0)<>0
group by pfs.ITEM_ID ,msi.segment1 , pfs.SBC_ID ,pv.segment1 ,pv.vendor_name
) pfsv
, (--取得物料的厂牌产品线 参考 secom_sotopo_pkg.get_item_category 的逻辑处理
SELECT distinct MIC.INVENTORY_ITEM_ID ,mc.SEGMENT1 brank,mc.SEGMENT2 product_line
FROM MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_TL MCST,
MTL_CATEGORY_SETS_B MCS,
MFG_LOOKUPS ML,
MTL_CATEGORIES_B_KFV MC
WHERE MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCS.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND MCST.LANGUAGE = USERENV('LANG')
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MCS.CONTROL_LEVEL = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'ITEM_CONTROL_LEVEL_GUI'
AND MIC.ORGANIZATION_ID = 84
AND MIC.CATEGORY_SET_ID = 1
) mtl
, secom_sbc_lists sl
where pfsv.ITEM_ID=mtl.INVENTORY_ITEM_ID(+)
and pfsv.SBC_ID=sl.sbc_list_id(+);
--=====================================================
--以价格最接近的原则进行调整
FOR cn_item_r IN cn_item LOOP
p_sum_n_qty:=0;
p_remain_n_qty:=abs(cn_item_r.qtyinstock);
----------------------------------------------
--动态游标
v_sql := '
select da.item_id,da.sbc_id,da.qtyinstock
from secom.secom_pcdi_debit_adj da
where da.item_id='|| cn_item_r.item_id ||' and da.qtyinstock>0
order by abs(da.unit_price-'||to_char( cn_item_r.unit_price) ||') ,da.creation_date
';
--dbms_output.put_line(v_sql);
open p_r_item for v_sql ;
loop
fetch p_r_item into p_item_id,p_sbc_id,p_qtyinstock;
exit when p_r_item%notfound;
if p_remain_n_qty >p_qtyinstock then
p_tmp_a_qty:=p_qtyinstock;
else
p_tmp_a_qty:=p_remain_n_qty;
end if;
--正的核销负的数量
if p_tmp_a_qty>0 then
update secom.secom_pcdi_debit_adj set debitqty=-(nvl(debitqty,0) + p_tmp_a_qty)
where item_id=p_item_id and sbc_id=p_sbc_id;
p_remain_n_qty:=nvl(p_remain_n_qty,0)-nvl(p_tmp_a_qty,0);
p_sum_n_qty:=nvl(p_sum_n_qty,0)+nvl(p_tmp_a_qty,0);
end if;
end loop;
close p_r_item;
--//--------------------------------------------
--负的与正的数量冲底
update secom.secom_pcdi_debit_adj set debitqty=p_sum_n_qty
where item_id=cn_item_r.item_id and sbc_id=cn_item_r.sbc_id;
end loop;
--计算留存库存的数量以及金额
update secom.secom_pcdi_debit_adj
set totalqtyinstock=nvl(qtyinstock,0) + nvl(debitqty,0)
,amount=round( ((nvl(qtyinstock,0) + nvl(debitqty,0))*UNIT_PRICE),2);
commit;
end get_pcdi_debit_adj;
