应付发票余额
来源:互联网 发布:js编写计算器 编辑:程序博客网 时间:2024/05/16 01:24
CREATE OR REPLACE FUNCTION MEW_GET_APINVOICE_BALANCE_F(P_INVOICE_ID NUMBER)
RETURN NUMBER IS
V_INVOICE_AMOUNT NUMBER;
V_PAYMENT_AMOUNT NUMBER;
V_PREPAID_AMOUNT NUMBER;
V_PREPAID_AMOUNT1 NUMBER;
V_INVOICE_TYPE_LOOKUP_CODE VARCHAR2(30);
BEGIN
--1发票金额
SELECT AIA.INVOICE_AMOUNT
,AIA.INVOICE_TYPE_LOOKUP_CODE
INTO V_INVOICE_AMOUNT
,V_INVOICE_TYPE_LOOKUP_CODE
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_ID = P_INVOICE_ID;
--2正常付款金额
BEGIN
SELECT SUM(NVL(AIP.AMOUNT
,0)) AMOUNT
INTO V_PAYMENT_AMOUNT
FROM AP_INVOICE_PAYMENTS_ALL AIP
,AP_INVOICES_ALL AI
,AP_INVOICES_ALL AI2
,AP_CHECKS_ALL AC
WHERE AIP.INVOICE_ID = AI.INVOICE_ID
AND AIP.OTHER_INVOICE_ID = AI2.INVOICE_ID(+)
AND AIP.CHECK_ID = AC.CHECK_ID
AND AIP.AMOUNT <> 0
AND AIP.INVOICE_ID = P_INVOICE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PAYMENT_AMOUNT := 0;
END;
IF V_INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
--预付款发票核销普通发票的金额
SELECT (-1) * SUM(AID1.AMOUNT) PREPAY_AMOUNT_APPLIED
INTO V_PREPAID_AMOUNT
FROM AP_INVOICES_ALL AI
,AP_INVOICE_DISTRIBUTIONS_ALL AID1
,AP_INVOICE_DISTRIBUTIONS_ALL AID2
WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID1.INVOICE_ID
AND AID1.AMOUNT < 0
AND NVL(AID1.REVERSAL_FLAG
,'N') != 'Y'
AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN
('PREPAYMENT'
,'CREDIT'
,'DEBIT')
AND AID2.INVOICE_ID = P_INVOICE_ID;
ELSE
--普通发票核销预付款发票的金额
SELECT (-1) * SUM(AID1.AMOUNT) PREPAY_AMOUNT_APPLIED
INTO V_PREPAID_AMOUNT
FROM AP_INVOICES_ALL AI
,AP_INVOICE_DISTRIBUTIONS_ALL AID1
,AP_INVOICE_DISTRIBUTIONS_ALL AID2
WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID2.INVOICE_ID
AND AID1.AMOUNT < 0
AND NVL(AID1.REVERSAL_FLAG
,'N') != 'Y'
AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AID1.INVOICE_ID = P_INVOICE_ID;
END IF;
DBMS_OUTPUT.PUT_LINE(' 1v_invoice_amount: ' || V_INVOICE_AMOUNT ||
' 2v_payment_amount: ' || V_PAYMENT_AMOUNT ||
' 3v_prepaid_amount: ' || V_PREPAID_AMOUNT);
IF V_INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
RETURN NVL(V_PAYMENT_AMOUNT
,0) - NVL(V_PREPAID_AMOUNT
,0);
ELSE
RETURN NVL(V_INVOICE_AMOUNT
,0) - NVL(V_PAYMENT_AMOUNT
,0) - NVL(V_PREPAID_AMOUNT
,0);
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
- 应付发票余额
- AP-应付发票余额
- AP_INVOICES_ALL应付发票表详解
- Oracle EBS 应付发票 接口导入 API
- AP未结应付发票一览
- vendor应付帐款余额的SQL.
- 预付发票未核销余额计算SQL.
- 预付发票未核销余额计算SQL.
- sbo中怎么不通过linkbutton跳出来指定的应付发票
- ORA-ERP:发票余额为零,无法结帐
- 用友NC总账辅助余额表与应收应付模块余额表对账技巧
- 应付
- 发票
- 发票
- 发票
- 发票
- Oracle EBS应付发票验证不了:Invoice Validation Errors with The sum of the Percentage Recovery Rates is great
- 应付作业
- C 结构体
- 写一个线程,每隔10秒钟标准输出到屏幕上一个”hello world” , 打印10次以后退出。
- 删除其他用户
- android中的Message类中的age1,age2和what有什么区别
- 反射实现 AOP 动态代理模式(Spring AOP 的实现 原理)
- 应付发票余额
- ABAP search help (搜索帮助) 五种方法
- Android 控件之Gallery图片集
- Android 创建与解析XML(三)—— Sax方式(源码)
- jquery实现弹出层遮罩效果
- 对 PInvoke 函数的调用导致堆栈不对称的解决方法
- 六种方式实现hibernate查询,及IDE推荐
- stl 学习笔记14 Reverse Iterator
- Java关键字及其作用