只要传入个对账单号，然后跑数据抛到接口表，运行接口请求，就可以自动生成发票
create or replace package body pkg_ap_check_by_po is
--创建ap发票
procedure ap_invoices_ap_check(p_ap_no varchar2,
p_import_status OUT number) is
--V_INVOICE_ID number := 0;
--V_INVOICE_LINE_ID NUMBER := 0;
--P_LINE_NUMBER NUMBER := 1;
l_big_zero NUMBER := 0; --T6开头，并且数量大于0
l_small_zero NUMBER := 0; --接收单号是T6开头，并且数量小于0
l_cut_quantity NUMBER := 0; --品质扣款
l_no_po NUMBER := 0; --无PO退货
begin
--代表接收单号是T6开头，并且数量大于0
select count(*)
into l_big_zero
from meg_ap_checklist mac
where mac.receipt_num like 'T6%'
and mac.match_dsp > 0
and mac.ap_no = p_ap_no;
--代表接收单号是T6开头，并且数量小于0
select count(*)
into l_small_zero
from meg_ap_checklist mac
where mac.receipt_num like 'T6%'
and mac.match_dsp < 0
and mac.ap_no = p_ap_no;
--品质扣款
SELECT count(*)
into l_cut_quantity
from meg_ap_checklist mac
where /*mac.receipt_num like 'CM%'
and*/ mac.match_dsp < 0
and mac.item_no in ( '索赔','折让','品质调整','超损扣款')
and mac.ap_no = p_ap_no;
--无PO退货
SELECT count(*)
into l_no_po
from meg_ap_checklist mac
where mac.match_dsp < 0
and mac.receipt_num = '无PO退货'
and mac.ap_no = p_ap_no;
--因为如果存在多种情况，发票编号后缀要加，-1，-2，-3
if l_big_zero > 0 then
--代表接收单号是T6开头，并且数量大于0
pkg_ap_check_by_po.insert_ap_invoices_t6_plus(p_ap_no);
end if;
if l_small_zero > 0 then
--接收单号是T6开头,数量小于0
pkg_ap_check_by_po.insert_ap_invoices_t6_minus(p_ap_no, 1);
ELSif l_small_zero > 0 and l_big_zero = 0 then
pkg_ap_check_by_po.insert_ap_invoices_t6_minus(p_ap_no, 0);
end if;
--品质扣款
if l_cut_quantity > 0 then
if l_small_zero > 0 and l_big_zero > 0 then
--品质扣款
pkg_ap_check_by_po.insert_ap_invoices_cut_qty(p_ap_no, 2);
elsif l_small_zero = 0 and l_big_zero = 0 then
--品质扣款
pkg_ap_check_by_po.insert_ap_invoices_cut_qty(p_ap_no, 0);
else
pkg_ap_check_by_po.insert_ap_invoices_cut_qty(p_ap_no, 1);
end if;
end if;
--无PO
if l_no_po > 0 then
if l_big_zero > 0 and l_small_zero > 0 and l_cut_quantity > 0 then
--无PO退货
pkg_ap_check_by_po.insert_ap_invoices_no_po(p_ap_no, 3);
elsif (l_big_zero > 0 and l_small_zero > 0 and l_cut_quantity = 0) or
(l_big_zero > 0 and l_small_zero = 0 and l_cut_quantity > 0) or
(l_big_zero = 0 and l_small_zero > 0 and l_cut_quantity > 0) then
pkg_ap_check_by_po.insert_ap_invoices_no_po(p_ap_no, 2);
elsif (l_small_zero = 0 and l_cut_quantity = 0 and l_big_zero = 0) then
pkg_ap_check_by_po.insert_ap_invoices_no_po(p_ap_no, 0);
else
pkg_ap_check_by_po.insert_ap_invoices_no_po(p_ap_no, 1);
end if;
end if;
-- commit;
P_IMPORT_STATUS := 1;
EXCEPTION
WHEN OTHERS THEN
P_IMPORT_STATUS := 0;
end;
procedure insert_ap_invoices_t6_plus(p_ap_no varchar2) as
cursor cur_ap(p_ap_no varchar2) is
select rt.transaction_id,
pha.po_header_id,
pla.po_line_id,
pll.line_location_id,
PLL.PO_RELEASE_ID,
pha.org_id,
pla.item_id,
mac.up2, -- mac.up_agree,
mac.match_dsp,
mac.line_num,
mac.match_amt_agree,
mac.rem
FROM RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS SH,
RCV_SHIPMENT_LINES SL,
PO_LINE_LOCATIONS_ALL PLL,
PO_RELEASES_ALL PRA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
MTL_SYSTEM_ITEMS_B MSI,
meg_ap_checklist mac
WHERE 1 = 1
AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH')
AND MG_COMM_PUB.GETTRAN_ID(RT.TRANSACTION_ID) > 0
AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
AND MSI.ORGANIZATION_ID = 82
AND SH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND RT.SOURCE_DOCUMENT_CODE = 'PO'
AND RT.PO_HEADER_ID = PLL.PO_HEADER_ID(+)
AND RT.PO_LINE_ID = PLL.PO_LINE_ID(+)
AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND RT.SHIPMENT_HEADER_ID = SL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID
AND SL.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID
AND PLL.PO_HEADER_ID = PHA.PO_HEADER_ID
AND SL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_LINE_ID = PLA.PO_LINE_ID
AND PLL.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID(+)
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND PVSA.VENDOR_ID = PHA.VENDOR_ID
AND PVSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
and rt.transaction_id = mac.transaction_id
and mac.receipt_num like 'T6%'
and mac.match_dsp > 0
and mac.ap_no = p_ap_no;
V_AP_INV_IFC_REC AP_INVOICES_INTERFACE%rowtype;
V_AP_INV_LINE_IFC_TBL_C AP_INVOICE_LINES_INTERFACE%ROWTYPE; --发票行
V_INVOICE_ID number := 0;
V_INVOICE_LINE_ID NUMBER := 0;
P_LINE_NUMBER NUMBER := 1;
begin
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL INTO V_INVOICE_ID FROM DUAL;
select vendor_site_id
INTO V_AP_INV_IFC_REC.VENDOR_SITE_ID
from (select itm,pha.vendor_site_id
from MEG_AP_CHECKLIST mac, po_headers_all pha
where pha.segment1 = mac.po_no
and ap_no = p_ap_no
order by itm)
where rownum=1;
--付款方式
begin
SELECT iepp.payment_method_code
INTO V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE
FROM iby_payment_methods_vl ipmv
, iby_ext_party_pmt_mthds iepp
, iby_external_payees_all iepa
WHERE ipmv.payment_method_code = iepp.payment_method_code(+)
AND iepp.payment_flow = 'DISBURSEMENTS'
AND NVL (ipmv.inactive_date, TRUNC (SYSDATE + 1)) > TRUNC (SYSDATE)
AND iepp.payment_function = 'PAYABLES_DISB'
AND iepp.ext_pmt_party_id = iepa.ext_payee_id
AND iepa.org_id =137
and iepp.primary_flag='Y'
AND iepa.supplier_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
EXCEPTION
WHEN OTHERS THEN
V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
end ;
select mch.invoice_num,
sum(mac.match_amt_agree),
-- sum(mac.match_amt_agree),
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE
into V_AP_INV_IFC_REC.INVOICE_NUM,
V_AP_INV_IFC_REC.INVOICE_AMOUNT,
V_AP_INV_IFC_REC.ORG_ID,
V_AP_INV_IFC_REC.VENDOR_ID,
V_AP_INV_IFC_REC.Vendor_Name,
V_AP_INV_IFC_REC.Vendor_Num,
V_AP_INV_IFC_REC.INVOICE_CURRENCY_CODE
from meg_ap_checklist_header mch, meg_ap_checklist mac, po_vendors pv --meg_ap_checklist_header mch, po_vendors pv
where mch.ap_no = p_ap_no
and mch.ap_no = mac.ap_no
and mch.vendor_no = pv.SEGMENT1
and mac.receipt_num like 'T6%'
and mac.match_dsp > 0
group by mch.invoice_num,
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE;
V_AP_INV_IFC_REC.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_IFC_REC.INVOICE_TYPE_LOOKUP_CODE := 'STANDARD'; --发票行类型
V_AP_INV_IFC_REC.SOURCE := 'USE'; --发票来源
V_AP_INV_IFC_REC.INVOICE_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --发票日期
V_AP_INV_IFC_REC.CONTROL_AMOUNT := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE_TYPE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_DATE := NULL;
V_AP_INV_IFC_REC.TERMS_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --条件日期
V_AP_INV_IFC_REC.DESCRIPTION := '应付' ||
V_AP_INV_IFC_REC.Vendor_Name
|| p_ap_no||'入库'; --null; --备注
V_AP_INV_IFC_REC.GROUP_ID := V_INVOICE_ID; --对账单处理批次
-- V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
V_AP_INV_IFC_REC.GL_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --总账日期
V_AP_INV_IFC_REC.CALC_TAX_DURING_IMPORT_FLAG := 'Y';
V_AP_INV_IFC_REC.ADD_TAX_TO_INV_AMT_FLAG := 'Y';
V_AP_INV_IFC_REC.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_IFC_REC.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
V_AP_INV_IFC_REC.CREATION_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.CREATED_BY := fnd_global.USER_ID;
--付款条件，负债账户
select assa.terms_id, gccc.code_combination_id
into V_AP_INV_IFC_REC.TERMS_ID,
V_AP_INV_IFC_REC.ACCTS_PAY_CODE_COMBINATION_ID
from ap_suppliers ass,
ap_supplier_sites_all assa,
gl_code_combinations gccc,
gl_code_combinations gccr
where ass.vendor_id = assa.vendor_id
and assa.accts_pay_code_combination_id = gccc.code_combination_id(+)
and assa.prepay_code_combination_id = gccr.code_combination_id(+)
and assa.org_id = V_AP_INV_IFC_REC.ORG_ID
and ass.segment1 = V_AP_INV_IFC_REC.Vendor_Num
and assa.vendor_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
INSERT INTO AP_INVOICES_INTERFACE VALUES V_AP_INV_IFC_REC; --插入发票
for cursor_ap_no in cur_ap(p_ap_no) loop
SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
INTO V_INVOICE_LINE_ID
FROM DUAL;
V_AP_INV_LINE_IFC_TBL_C.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_LINE_IFC_TBL_C.INVOICE_LINE_ID := V_INVOICE_LINE_ID; --发票行ID
V_AP_INV_LINE_IFC_TBL_C.LINE_NUMBER := P_LINE_NUMBER; --p_LINE_NUMBER; --发票行号
V_AP_INV_LINE_IFC_TBL_C.LINE_TYPE_LOOKUP_CODE := 'ITEM'; --发票行类型
V_AP_INV_LINE_IFC_TBL_C.AMOUNT := cursor_ap_no.MATCH_AMT_AGREE; --p_AMOUNT; --金额
V_AP_INV_LINE_IFC_TBL_C.ACCOUNTING_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --记账日期
V_AP_INV_LINE_IFC_TBL_C.DESCRIPTION := '应付' ||
V_AP_INV_IFC_REC.Vendor_Name
|| p_ap_no||'入库';--cursor_ap_no.Rem; --'保险管/保险丝座/10A/磷青铜/镀镍/配套φ5*20mm/T=0.35mm/P=5.1mm/编带'; --发票行描述
V_AP_INV_LINE_IFC_TBL_C.AMOUNT_INCLUDES_TAX_FLAG := 'N';
V_AP_INV_LINE_IFC_TBL_C.TAX_CLASSIFICATION_CODE := '13%(外)'; --税分类代码
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_LOGIN := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATION_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.RCV_TRANSACTION_ID := cursor_ap_no.transaction_id;
V_AP_INV_LINE_IFC_TBL_C.PO_HEADER_ID := cursor_ap_no.PO_HEADER_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_ID := cursor_ap_no.PO_LINE_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_LOCATION_ID := cursor_ap_no.line_location_id;
V_AP_INV_LINE_IFC_TBL_C.PO_RELEASE_ID := cursor_ap_no.PO_RELEASE_ID;
V_AP_INV_LINE_IFC_TBL_C.ORG_ID := cursor_ap_no.org_id;
V_AP_INV_LINE_IFC_TBL_C.INVENTORY_ITEM_ID := cursor_ap_no.item_id;
V_AP_INV_LINE_IFC_TBL_C.UNIT_PRICE := cursor_ap_no.up2;
V_AP_INV_LINE_IFC_TBL_C.QUANTITY_INVOICED := cursor_ap_no.match_dsp;
INSERT INTO AP_INVOICE_LINES_INTERFACE
VALUES V_AP_INV_LINE_IFC_TBL_C;
P_LINE_NUMBER := P_LINE_NUMBER + 1;
end loop;
commit;
end;
--T6 数量<0
procedure insert_ap_invoices_t6_minus(p_ap_no varchar, p_number number) as
cursor cur_ap(p_ap_no varchar2) is
select rt.transaction_id,
pha.po_header_id,
pla.po_line_id,
pll.line_location_id,
PLL.PO_RELEASE_ID,
pha.org_id,
pla.item_id,
mac.UP2, --up_agree,
mac.match_dsp,
mac.line_num,
mac.match_amt_agree,
mac.rem
FROM RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS SH,
RCV_SHIPMENT_LINES SL,
PO_LINE_LOCATIONS_ALL PLL,
PO_RELEASES_ALL PRA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
MTL_SYSTEM_ITEMS_B MSI,
meg_ap_checklist mac
WHERE 1 = 1
AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH')
AND MG_COMM_PUB.GETTRAN_ID(RT.TRANSACTION_ID) > 0
AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
AND MSI.ORGANIZATION_ID = 82
AND SH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND RT.SOURCE_DOCUMENT_CODE = 'PO'
AND RT.PO_HEADER_ID = PLL.PO_HEADER_ID(+)
AND RT.PO_LINE_ID = PLL.PO_LINE_ID(+)
AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND RT.SHIPMENT_HEADER_ID = SL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID
AND SL.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID
AND PLL.PO_HEADER_ID = PHA.PO_HEADER_ID
AND SL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_LINE_ID = PLA.PO_LINE_ID
AND PLL.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID(+)
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND PVSA.VENDOR_ID = PHA.VENDOR_ID
AND PVSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
and rt.transaction_id = mac.transaction_id
and mac.receipt_num like 'T6%'
and mac.match_dsp < 0
and mac.ap_no = p_ap_no;
V_AP_INV_IFC_REC AP_INVOICES_INTERFACE%rowtype;
V_AP_INV_LINE_IFC_TBL_C AP_INVOICE_LINES_INTERFACE%ROWTYPE; --发票行
V_INVOICE_ID number := 0;
V_INVOICE_LINE_ID NUMBER := 0;
P_LINE_NUMBER NUMBER := 1;
--v_total_amount1 number:=0;
--v_total_amount2 number:=0;
v_price_diff number := 0;
begin
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL INTO V_INVOICE_ID FROM DUAL;
select vendor_site_id
INTO V_AP_INV_IFC_REC.VENDOR_SITE_ID
from (select itm,pha.vendor_site_id
from MEG_AP_CHECKLIST mac, po_headers_all pha
where pha.segment1 = mac.po_no
and ap_no = p_ap_no
order by itm)
where rownum=1;
--付款方式
begin
SELECT iepp.payment_method_code
INTO V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE
FROM iby_payment_methods_vl ipmv
, iby_ext_party_pmt_mthds iepp
, iby_external_payees_all iepa
WHERE ipmv.payment_method_code = iepp.payment_method_code(+)
AND iepp.payment_flow = 'DISBURSEMENTS'
AND NVL (ipmv.inactive_date, TRUNC (SYSDATE + 1)) > TRUNC (SYSDATE)
AND iepp.payment_function = 'PAYABLES_DISB'
AND iepp.ext_pmt_party_id = iepa.ext_payee_id
AND iepa.org_id =137
and iepp.primary_flag='Y'
AND iepa.supplier_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
EXCEPTION
WHEN OTHERS THEN
V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
end ;
select mch.invoice_num || '-' || p_number, --因为
sum(mac.match_amt_agree),
-- sum(mac.match_amt_agree),
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE
into V_AP_INV_IFC_REC.INVOICE_NUM,
V_AP_INV_IFC_REC.INVOICE_AMOUNT,
V_AP_INV_IFC_REC.ORG_ID,
V_AP_INV_IFC_REC.VENDOR_ID,
V_AP_INV_IFC_REC.Vendor_Name,
V_AP_INV_IFC_REC.Vendor_Num,
V_AP_INV_IFC_REC.INVOICE_CURRENCY_CODE
from meg_ap_checklist_header mch, po_vendors pv, meg_ap_checklist mac
where mch.ap_no = p_ap_no
and mch.ap_no = mac.ap_no
and mch.vendor_no = pv.SEGMENT1
and mac.receipt_num like 'T6%'
and mac.match_dsp < 0
group by mch.invoice_num,
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE;
V_AP_INV_IFC_REC.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_IFC_REC.INVOICE_TYPE_LOOKUP_CODE := 'CREDIT'; --发票行类型
V_AP_INV_IFC_REC.SOURCE := 'USE'; --发票来源
V_AP_INV_IFC_REC.INVOICE_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --发票日期
V_AP_INV_IFC_REC.CONTROL_AMOUNT := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE_TYPE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_DATE := NULL;
V_AP_INV_IFC_REC.TERMS_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --条件日期
V_AP_INV_IFC_REC.DESCRIPTION := '应付' ||
V_AP_INV_IFC_REC.Vendor_Name ||
p_ap_no||'退货' ; --null; --备注
V_AP_INV_IFC_REC.GROUP_ID := V_INVOICE_ID; --对账单处理批次
-- V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
V_AP_INV_IFC_REC.GL_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --总账日期
V_AP_INV_IFC_REC.CALC_TAX_DURING_IMPORT_FLAG := 'Y';
V_AP_INV_IFC_REC.ADD_TAX_TO_INV_AMT_FLAG := 'Y';
V_AP_INV_IFC_REC.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_IFC_REC.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
V_AP_INV_IFC_REC.CREATION_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.CREATED_BY := fnd_global.USER_ID;
--付款条件，负债账户
select ass.terms_id, gccc.code_combination_id
into V_AP_INV_IFC_REC.TERMS_ID,
V_AP_INV_IFC_REC.ACCTS_PAY_CODE_COMBINATION_ID
from ap_suppliers ass,
ap_supplier_sites_all assa,
gl_code_combinations gccc,
gl_code_combinations gccr
where ass.vendor_id = assa.vendor_id
and assa.accts_pay_code_combination_id = gccc.code_combination_id(+)
and assa.prepay_code_combination_id = gccr.code_combination_id(+)
and assa.org_id = V_AP_INV_IFC_REC.ORG_ID
and ass.segment1 = V_AP_INV_IFC_REC.Vendor_Num
and assa.vendor_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
INSERT INTO AP_INVOICES_INTERFACE VALUES V_AP_INV_IFC_REC; --插入发票
for cursor_ap_no in cur_ap(p_ap_no) loop
SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
INTO V_INVOICE_LINE_ID
FROM DUAL;
if abs((cursor_ap_no.UP2 * cursor_ap_no.match_dsp) -
cursor_ap_no.MATCH_AMT_AGREE) < 0.05 and
abs((cursor_ap_no.UP2 * cursor_ap_no.match_dsp) -
cursor_ap_no.MATCH_AMT_AGREE) > 0 then
v_price_diff := round(cursor_ap_no.MATCH_AMT_AGREE /
cursor_ap_no.match_dsp,6);
else
v_price_diff := cursor_ap_no.UP2;
end if;
V_AP_INV_LINE_IFC_TBL_C.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_LINE_IFC_TBL_C.INVOICE_LINE_ID := V_INVOICE_LINE_ID; --发票行ID
V_AP_INV_LINE_IFC_TBL_C.LINE_NUMBER := P_LINE_NUMBER; --p_LINE_NUMBER; --发票行号
V_AP_INV_LINE_IFC_TBL_C.LINE_TYPE_LOOKUP_CODE := 'ITEM'; --发票行类型
V_AP_INV_LINE_IFC_TBL_C.AMOUNT := cursor_ap_no.MATCH_AMT_AGREE; --p_AMOUNT; --金额
V_AP_INV_LINE_IFC_TBL_C.ACCOUNTING_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --记账日期
V_AP_INV_LINE_IFC_TBL_C.DESCRIPTION :='应付' ||
V_AP_INV_IFC_REC.Vendor_Name ||
p_ap_no||'退货' ;-- cursor_ap_no.Rem; --'保险管/保险丝座/10A/磷青铜/镀镍/配套φ5*20mm/T=0.35mm/P=5.1mm/编带'; --发票行描述
V_AP_INV_LINE_IFC_TBL_C.AMOUNT_INCLUDES_TAX_FLAG := 'N';
V_AP_INV_LINE_IFC_TBL_C.TAX_CLASSIFICATION_CODE := '13%(外)'; --税分类代码
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_LOGIN := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATION_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.RCV_TRANSACTION_ID := cursor_ap_no.transaction_id;
V_AP_INV_LINE_IFC_TBL_C.PO_HEADER_ID := cursor_ap_no.PO_HEADER_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_ID := cursor_ap_no.PO_LINE_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_LOCATION_ID := cursor_ap_no.line_location_id;
V_AP_INV_LINE_IFC_TBL_C.PO_RELEASE_ID := cursor_ap_no.PO_RELEASE_ID;
V_AP_INV_LINE_IFC_TBL_C.ORG_ID := cursor_ap_no.org_id;
V_AP_INV_LINE_IFC_TBL_C.INVENTORY_ITEM_ID := cursor_ap_no.item_id;
V_AP_INV_LINE_IFC_TBL_C.UNIT_PRICE := v_price_diff; --cursor_ap_no.UP2;--up_agree;
V_AP_INV_LINE_IFC_TBL_C.QUANTITY_INVOICED := cursor_ap_no.match_dsp;
INSERT INTO AP_INVOICE_LINES_INTERFACE
VALUES V_AP_INV_LINE_IFC_TBL_C;
P_LINE_NUMBER := P_LINE_NUMBER + 1;
end loop;
commit;
end;
--品质扣款
procedure insert_ap_invoices_cut_qty(p_ap_no varchar, p_number number) as
cursor cur_ap(p_ap_no varchar2) is
select mac.id transaction_id,
null po_header_id,
null po_line_id,
null line_location_id,
null PO_RELEASE_ID,
b.org_id org_id,
null item_id,
mac.UP2,
mac.match_dsp,
mac.line_num,
mac.match_amt_agree,
mac.rem,
mac.item_no
FROM meg_ap_checklist mac, meg_ap_checklist_header b
WHERE /*mac.receipt_num like 'CM%'
and*/ mac.item_no in ( '索赔','折让','品质调整','超损扣款')
and mac.match_dsp < 0
and mac.ap_no = b.ap_no
and mac.ap_no = p_ap_no;
V_AP_INV_IFC_REC AP_INVOICES_INTERFACE%rowtype;
V_AP_INV_LINE_IFC_TBL_C AP_INVOICE_LINES_INTERFACE%ROWTYPE; --发票行
V_INVOICE_ID number := 0;
V_INVOICE_LINE_ID NUMBER := 0;
P_LINE_NUMBER NUMBER := 1;
begin
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL INTO V_INVOICE_ID FROM DUAL;
select vendor_site_id
INTO V_AP_INV_IFC_REC.VENDOR_SITE_ID
from (select itm,pha.vendor_site_id
from MEG_AP_CHECKLIST mac, po_headers_all pha
where pha.segment1 = mac.po_no
and ap_no = p_ap_no
order by itm)
where rownum=1;
--付款方式
begin
SELECT iepp.payment_method_code
INTO V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE
FROM iby_payment_methods_vl ipmv
, iby_ext_party_pmt_mthds iepp
, iby_external_payees_all iepa
WHERE ipmv.payment_method_code = iepp.payment_method_code(+)
AND iepp.payment_flow = 'DISBURSEMENTS'
AND NVL (ipmv.inactive_date, TRUNC (SYSDATE + 1)) > TRUNC (SYSDATE)
AND iepp.payment_function = 'PAYABLES_DISB'
AND iepp.ext_pmt_party_id = iepa.ext_payee_id
AND iepa.org_id =137
and iepp.primary_flag='Y'
AND iepa.supplier_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
EXCEPTION
WHEN OTHERS THEN
V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
end ;
select mch.invoice_num || '-' || p_number,
sum(mac.match_amt_agree),
-- sum(mac.match_amt_agree),
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE
into V_AP_INV_IFC_REC.INVOICE_NUM,
V_AP_INV_IFC_REC.INVOICE_AMOUNT,
V_AP_INV_IFC_REC.ORG_ID,
V_AP_INV_IFC_REC.VENDOR_ID,
V_AP_INV_IFC_REC.Vendor_Name,
V_AP_INV_IFC_REC.Vendor_Num,
V_AP_INV_IFC_REC.INVOICE_CURRENCY_CODE
from meg_ap_checklist_header mch, po_vendors pv, meg_ap_checklist mac
where mch.ap_no = p_ap_no
and mch.ap_no = mac.ap_no
and mch.vendor_no = pv.SEGMENT1
-- and mac.receipt_num like 'CM%'
and mac.item_no in ( '索赔','折让','品质调整','超损扣款')
and mac.match_dsp < 0
group by mch.invoice_num || '-' || p_number,
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE;
V_AP_INV_IFC_REC.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_IFC_REC.INVOICE_TYPE_LOOKUP_CODE := 'CREDIT'; --发票行类型
V_AP_INV_IFC_REC.SOURCE := 'USE'; --发票来源
V_AP_INV_IFC_REC.INVOICE_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --发票日期
V_AP_INV_IFC_REC.CONTROL_AMOUNT := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE_TYPE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_DATE := NULL;
V_AP_INV_IFC_REC.TERMS_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --条件日期
V_AP_INV_IFC_REC.DESCRIPTION := '应付' ||
V_AP_INV_IFC_REC.Vendor_Name ||
p_ap_no||'品质扣款' ; --null; --备注
V_AP_INV_IFC_REC.GROUP_ID := V_INVOICE_ID; --对账单处理批次
-- V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
V_AP_INV_IFC_REC.GL_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --总账日期
V_AP_INV_IFC_REC.CALC_TAX_DURING_IMPORT_FLAG := 'Y';
V_AP_INV_IFC_REC.ADD_TAX_TO_INV_AMT_FLAG := 'Y';
V_AP_INV_IFC_REC.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_IFC_REC.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
V_AP_INV_IFC_REC.CREATION_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.CREATED_BY := fnd_global.USER_ID;
--付款条件，负债账户
select ass.terms_id, gccc.code_combination_id
into V_AP_INV_IFC_REC.TERMS_ID,
V_AP_INV_IFC_REC.ACCTS_PAY_CODE_COMBINATION_ID
from ap_suppliers ass,
ap_supplier_sites_all assa,
gl_code_combinations gccc,
gl_code_combinations gccr
where ass.vendor_id = assa.vendor_id
and assa.accts_pay_code_combination_id = gccc.code_combination_id(+)
and assa.prepay_code_combination_id = gccr.code_combination_id(+)
and assa.org_id = V_AP_INV_IFC_REC.ORG_ID
and ass.segment1 = V_AP_INV_IFC_REC.Vendor_Num
and assa.vendor_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
INSERT INTO AP_INVOICES_INTERFACE VALUES V_AP_INV_IFC_REC; --插入发票
for cursor_ap_no in cur_ap(p_ap_no) loop
SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
INTO V_INVOICE_LINE_ID
FROM DUAL;
V_AP_INV_LINE_IFC_TBL_C.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_LINE_IFC_TBL_C.INVOICE_LINE_ID := V_INVOICE_LINE_ID; --发票行ID
V_AP_INV_LINE_IFC_TBL_C.LINE_NUMBER := P_LINE_NUMBER; --p_LINE_NUMBER; --发票行号
V_AP_INV_LINE_IFC_TBL_C.LINE_TYPE_LOOKUP_CODE := 'ITEM'; --发票行类型
V_AP_INV_LINE_IFC_TBL_C.AMOUNT := cursor_ap_no.MATCH_AMT_AGREE; --p_AMOUNT; --金额
V_AP_INV_LINE_IFC_TBL_C.ACCOUNTING_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --记账日期
V_AP_INV_LINE_IFC_TBL_C.DESCRIPTION := '应付' ||
V_AP_INV_IFC_REC.Vendor_Name ||
p_ap_no||cursor_ap_no.item_no;--'品质扣款' ;--cursor_ap_no.Rem; --'保险管/保险丝座/10A/磷青铜/镀镍/配套φ5*20mm/T=0.35mm/P=5.1mm/编带'; --发票行描述
V_AP_INV_LINE_IFC_TBL_C.AMOUNT_INCLUDES_TAX_FLAG := 'N';
V_AP_INV_LINE_IFC_TBL_C.TAX_CLASSIFICATION_CODE := '13%(外)'; --税分类代码
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_LOGIN := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATION_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.RCV_TRANSACTION_ID := cursor_ap_no.transaction_id;
V_AP_INV_LINE_IFC_TBL_C.PO_HEADER_ID := null; --cursor_ap_no.PO_HEADER_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_ID := null; --cursor_ap_no.PO_LINE_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_LOCATION_ID := null; --cursor_ap_no.line_location_id;
V_AP_INV_LINE_IFC_TBL_C.PO_RELEASE_ID := null; -- cursor_ap_no.PO_RELEASE_ID;
V_AP_INV_LINE_IFC_TBL_C.ORG_ID := 137; --cursor_ap_no.org_id;
V_AP_INV_LINE_IFC_TBL_C.INVENTORY_ITEM_ID := null; --cursor_ap_no.item_id;
V_AP_INV_LINE_IFC_TBL_C.UNIT_PRICE := cursor_ap_no.UP2; --up_agree;
V_AP_INV_LINE_IFC_TBL_C.QUANTITY_INVOICED := cursor_ap_no.match_dsp;
INSERT INTO AP_INVOICE_LINES_INTERFACE
VALUES V_AP_INV_LINE_IFC_TBL_C;
P_LINE_NUMBER := P_LINE_NUMBER + 1;
end loop;
commit;
end;
--无po 退货
procedure insert_ap_invoices_no_po(p_ap_no varchar, p_number number) as
cursor cur_ap(p_ap_no varchar2) is
SELECT a.id transaction_id,
null po_header_id,
null po_line_id,
null line_location_id,
null PO_RELEASE_ID,
b.org_id org_id,
null item_id,
a.UP2,
a.match_dsp,
a.line_num,
a.match_amt_agree,
a.rem
FROM meg_ap_checklist a, meg_ap_checklist_header b
WHERE b.AP_NO = p_ap_no
and a.ap_no = b.ap_no
AND RECEIPT_NUM = '无PO退货';
V_AP_INV_IFC_REC AP_INVOICES_INTERFACE%rowtype;
V_AP_INV_LINE_IFC_TBL_C AP_INVOICE_LINES_INTERFACE%ROWTYPE; --发票行
V_INVOICE_ID number := 0;
V_INVOICE_LINE_ID NUMBER := 0;
P_LINE_NUMBER NUMBER := 1;
begin
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL INTO V_INVOICE_ID FROM DUAL;
select vendor_site_id
INTO V_AP_INV_IFC_REC.VENDOR_SITE_ID
from (select itm,pha.vendor_site_id
from MEG_AP_CHECKLIST mac, po_headers_all pha
where pha.segment1 = mac.po_no
and ap_no = p_ap_no
order by itm)
where rownum=1;
--付款方式
begin
SELECT iepp.payment_method_code
INTO V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE
FROM iby_payment_methods_vl ipmv
, iby_ext_party_pmt_mthds iepp
, iby_external_payees_all iepa
WHERE ipmv.payment_method_code = iepp.payment_method_code(+)
AND iepp.payment_flow = 'DISBURSEMENTS'
AND NVL (ipmv.inactive_date, TRUNC (SYSDATE + 1)) > TRUNC (SYSDATE)
AND iepp.payment_function = 'PAYABLES_DISB'
AND iepp.ext_pmt_party_id = iepa.ext_payee_id
AND iepa.org_id =137
and iepp.primary_flag='Y'
AND iepa.supplier_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
EXCEPTION
WHEN OTHERS THEN
V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
end ;
select mch.invoice_num || '-' || p_number,
sum(mac.match_amt_agree), --sum(mac.match_amt_agree),
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE
into V_AP_INV_IFC_REC.INVOICE_NUM,
V_AP_INV_IFC_REC.INVOICE_AMOUNT,
V_AP_INV_IFC_REC.ORG_ID,
V_AP_INV_IFC_REC.VENDOR_ID,
V_AP_INV_IFC_REC.Vendor_Name,
V_AP_INV_IFC_REC.Vendor_Num,
V_AP_INV_IFC_REC.INVOICE_CURRENCY_CODE
from meg_ap_checklist_header mch, po_vendors pv, meg_ap_checklist mac
where mch.ap_no = p_ap_no
and mch.ap_no = mac.ap_no
and mch.vendor_no = pv.SEGMENT1
and mac.match_dsp < 0
and mac.receipt_num = '无PO退货'
group by mch.invoice_num || '-' || p_number,
mch.org_id,
pv.VENDOR_ID,
pv.VENDOR_NAME,
pv.SEGMENT1,
pv.INVOICE_CURRENCY_CODE;
V_AP_INV_IFC_REC.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_IFC_REC.INVOICE_TYPE_LOOKUP_CODE := 'CREDIT'; --发票行类型
V_AP_INV_IFC_REC.SOURCE := 'USE'; --发票来源
V_AP_INV_IFC_REC.INVOICE_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --发票日期
V_AP_INV_IFC_REC.CONTROL_AMOUNT := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_RATE_TYPE := NULL;
V_AP_INV_IFC_REC.EXCHANGE_DATE := NULL;
V_AP_INV_IFC_REC.TERMS_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ -- trunc(sysdate); --条件日期
V_AP_INV_IFC_REC.DESCRIPTION := '应付' ||
V_AP_INV_IFC_REC.Vendor_Name
|| p_ap_no||'无PO退货'; --null; --备注
V_AP_INV_IFC_REC.GROUP_ID := V_INVOICE_ID; --对账单处理批次
--V_AP_INV_IFC_REC.PAYMENT_METHOD_CODE := 'WIRE';
V_AP_INV_IFC_REC.GL_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd'); */ -- trunc(sysdate); --总账日期
--V_AP_INV_IFC_REC.ORG_ID := 137; --组织
V_AP_INV_IFC_REC.CALC_TAX_DURING_IMPORT_FLAG := 'Y';
V_AP_INV_IFC_REC.ADD_TAX_TO_INV_AMT_FLAG := 'Y';
V_AP_INV_IFC_REC.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_IFC_REC.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
V_AP_INV_IFC_REC.CREATION_DATE := trunc(sysdate);
V_AP_INV_IFC_REC.CREATED_BY := fnd_global.USER_ID;
--付款条件，负债账户
select ass.terms_id, gccc.code_combination_id
into V_AP_INV_IFC_REC.TERMS_ID,
V_AP_INV_IFC_REC.ACCTS_PAY_CODE_COMBINATION_ID
from ap_suppliers ass,
ap_supplier_sites_all assa,
gl_code_combinations gccc,
gl_code_combinations gccr
where ass.vendor_id = assa.vendor_id
and assa.accts_pay_code_combination_id = gccc.code_combination_id(+)
and assa.prepay_code_combination_id = gccr.code_combination_id(+)
and assa.org_id = V_AP_INV_IFC_REC.ORG_ID
and ass.segment1 = V_AP_INV_IFC_REC.Vendor_Num
and assa.vendor_site_id = V_AP_INV_IFC_REC.VENDOR_SITE_ID;
INSERT INTO AP_INVOICES_INTERFACE VALUES V_AP_INV_IFC_REC; --插入发票
for cursor_ap_no in cur_ap(p_ap_no) loop
SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
INTO V_INVOICE_LINE_ID
FROM DUAL;
V_AP_INV_LINE_IFC_TBL_C.INVOICE_ID := V_INVOICE_ID; --发票ID
V_AP_INV_LINE_IFC_TBL_C.INVOICE_LINE_ID := V_INVOICE_LINE_ID; --发票行ID
V_AP_INV_LINE_IFC_TBL_C.LINE_NUMBER := P_LINE_NUMBER; --p_LINE_NUMBER; --发票行号
V_AP_INV_LINE_IFC_TBL_C.LINE_TYPE_LOOKUP_CODE := 'ITEM'; --发票行类型
V_AP_INV_LINE_IFC_TBL_C.AMOUNT := cursor_ap_no.MATCH_AMT_AGREE; --p_AMOUNT; --金额
V_AP_INV_LINE_IFC_TBL_C.ACCOUNTING_DATE := trunc(sysdate); /*TO_DATE(sysdate,--'2018.07.01',
'yyyy-mm-dd');*/ --trunc(sysdate); --记账日期
V_AP_INV_LINE_IFC_TBL_C.DESCRIPTION :='应付' ||
V_AP_INV_IFC_REC.Vendor_Name
|| p_ap_no||'无PO退货';-- cursor_ap_no.Rem; --'保险管/保险丝座/10A/磷青铜/镀镍/配套φ5*20mm/T=0.35mm/P=5.1mm/编带'; --发票行描述
V_AP_INV_LINE_IFC_TBL_C.AMOUNT_INCLUDES_TAX_FLAG := 'N';
V_AP_INV_LINE_IFC_TBL_C.TAX_CLASSIFICATION_CODE := '13%(外)'; --税分类代码
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.LAST_UPDATE_LOGIN := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATED_BY := fnd_global.USER_ID;
V_AP_INV_LINE_IFC_TBL_C.CREATION_DATE := trunc(sysdate);
V_AP_INV_LINE_IFC_TBL_C.RCV_TRANSACTION_ID := cursor_ap_no.transaction_id;
V_AP_INV_LINE_IFC_TBL_C.PO_HEADER_ID := cursor_ap_no.PO_HEADER_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_ID := cursor_ap_no.PO_LINE_ID;
V_AP_INV_LINE_IFC_TBL_C.PO_LINE_LOCATION_ID := cursor_ap_no.line_location_id;
V_AP_INV_LINE_IFC_TBL_C.PO_RELEASE_ID := cursor_ap_no.PO_RELEASE_ID;
V_AP_INV_LINE_IFC_TBL_C.ORG_ID := cursor_ap_no.org_id;
V_AP_INV_LINE_IFC_TBL_C.INVENTORY_ITEM_ID := cursor_ap_no.item_id;
V_AP_INV_LINE_IFC_TBL_C.UNIT_PRICE := cursor_ap_no.UP2; --up_agree;
V_AP_INV_LINE_IFC_TBL_C.QUANTITY_INVOICED := cursor_ap_no.match_dsp;
INSERT INTO AP_INVOICE_LINES_INTERFACE
VALUES V_AP_INV_LINE_IFC_TBL_C;
P_LINE_NUMBER := P_LINE_NUMBER + 1;
end loop;
commit;
end;
--检查发票有没有最终关闭
procedure get_last_close_by_ap_no(p_ap_no varchar,
P_IMPORT_STATUS OUT varchar) as
cursor CUR_CLOSE IS
select rsh.receipt_num || '第' || rsl.line_num || '行' receipt_line
from MEG_AP_CHECKLIST MAC,
po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where pll.line_location_id = rsl.po_line_location_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pll.closed_code = 'FINALLY CLOSED'
and rsh.receipt_num = MAC.RECEIPT_NUM
and rsl.line_num = MAC.LINE_NUM
and MAC.AP_NO = p_ap_no;
--po退货
cursor get_no_po is
select mac.receipt_num || '第' || mac.line_num || '行' no_po_line
from rcv_transactions rt, MEG_AP_CHECKLIST MAC
where exists
(select 1
from rcv_transactions rts
where rts.transaction_type = 'RETURN TO VENDOR'
START WITH rts.transaction_id = rt.transaction_id
connect by prior rts.transaction_id = rts.parent_transaction_id)
and rt.transaction_id = MAC.TRANSACTION_ID
AND ap_no = p_ap_no;
--
l_count number := 0; --最终关闭
l_count_re number := 0; --退货的笔数
begin
--最终关闭的判断
select count(*)
into l_count
from MEG_AP_CHECKLIST MAC,
po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where pll.line_location_id = rsl.po_line_location_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pll.closed_code = 'FINALLY CLOSED'
and rsh.receipt_num = MAC.RECEIPT_NUM
and rsl.line_num = MAC.LINE_NUM
and MAC.AP_NO = p_ap_no; --'530986-1706Z';
if l_count > 0 then
for cur_close_status in CUR_CLOSE loop
P_IMPORT_STATUS := '存在最终关闭的T6单号有';
P_IMPORT_STATUS := P_IMPORT_STATUS || cur_close_status.receipt_line || ',';
end loop;
else
P_IMPORT_STATUS := '1';
end if;
select count(*)
into l_count_re --MAC.*--mac.ap_no,mac.receipt_num,mac.line_num,mac.po_no
from rcv_transactions rt, MEG_AP_CHECKLIST MAC
where exists
(select 1
from rcv_transactions rts
where rts.transaction_type = 'RETURN TO VENDOR'
START WITH rts.transaction_id = rt.transaction_id
connect by prior rts.transaction_id = rts.parent_transaction_id)
and rt.transaction_id = MAC.TRANSACTION_ID
AND ap_no = p_ap_no; --'530873-1803Z';
-- return P_IMPORT_STATUS;
if l_count_re > 0 then
for cur_no_po in get_no_po loop
P_IMPORT_STATUS := '存在退货的T6单号有';
P_IMPORT_STATUS := P_IMPORT_STATUS || cur_no_po.no_po_line;
end loop;
else
P_IMPORT_STATUS := '1';
end if;
end;
end pkg_ap_check_by_po;