查询发票是否已过账的语句
FUNCTION get_posting_status(l_invoice_id IN NUMBER)
RETURN VARCHAR2 IS
invoice_posting_flag VARCHAR2(1);
distribution_posting_flag VARCHAR2(1);
l_cash_basis_flag VARCHAR2(1);
l_org_id AP_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE;
CURSOR posting_cursor IS
SELECT cash_posted_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag = 'Y'
UNION
SELECT accrual_posted_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag <>'Y'
UNION
SELECT 'P'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'Y' AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'Y' AND l_cash_basis_flag <> 'Y'))
AND EXISTS
(SELECT 'An N is also in the valid flags'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y')) -- bug fix 6975868
UNION /*Added for bug 10039729*/
SELECT 'An N is also in the valid flags'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND posted_flag = 'N'
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
UNION
SELECT 'An N is also in the valid flags'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
)
-- bug fix 6975868 begin
UNION
SELECT cash_posted_flag
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag = 'Y'
UNION
SELECT accrual_posted_flag
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND l_cash_basis_flag <>'Y'
UNION
SELECT 'P'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'Y'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'Y'
AND l_cash_basis_flag <> 'Y'))
AND EXISTS
(SELECT 'An N is also in the valid flags'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
UNION /*Added for bug 10039729*/
SELECT 'An N is also in the valid flags'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
UNION
SELECT 'An N is also in the valid flags'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND posted_flag = 'N'
AND transaction_type = 'PREPAYMENT APPLICATION ADJ')
UNION
-- bug9440144
SELECT posted_flag
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
UNION /*Added for bug 10039729*/
SELECT 'P'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
AND posted_flag = 'Y'
AND EXISTS
(SELECT 'An N is also in the valid flags'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
UNION
SELECT 'An N is also in the valid flags'
FROM ap_prepay_history_all
WHERE invoice_id = l_invoice_id
AND posted_flag = 'N'
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
UNION
SELECT 'An N is also in the valid flags'
FROM ap_self_assessed_tax_dist_all
WHERE invoice_id = l_invoice_id
AND ((cash_posted_flag = 'N'
AND l_cash_basis_flag = 'Y')
OR
(accrual_posted_flag = 'N'
AND l_cash_basis_flag <> 'Y'))
); -- bug fix 6975868;
-- bug fix 6975868 end
BEGIN
/*-----------------------------------------------------------------+
| Get Accounting Methods |
| MOAC. Added org_id to select statement. |
+-----------------------------------------------------------------*/
SELECT nvl(sob.sla_ledger_cash_basis_flag, 'N'),
asp.org_id
INTO l_cash_basis_flag,
l_org_id
FROM ap_invoices_all ai,
ap_system_parameters_all asp,
gl_sets_of_books sob
WHERE ai.invoice_id = l_invoice_id
AND ai.org_id = asp.org_id
AND asp.set_of_books_id = sob.set_of_books_id;
invoice_posting_flag := 'X';
OPEN posting_cursor;
LOOP
FETCH posting_cursor INTO distribution_posting_flag;
EXIT WHEN posting_cursor%NOTFOUND;
IF (distribution_posting_flag = 'S') THEN
invoice_posting_flag := 'S';
ELSIF (distribution_posting_flag = 'P' AND
invoice_posting_flag <> 'S') THEN
invoice_posting_flag := 'P';
ELSIF (distribution_posting_flag = 'N' AND
invoice_posting_flag NOT IN ('S','P')) THEN
invoice_posting_flag := 'N';
ELSIF (distribution_posting_flag IS NULL) THEN
invoice_posting_flag := 'N';
END IF;
IF (invoice_posting_flag NOT IN ('S','P','N')) THEN
invoice_posting_flag := 'Y';
END IF;
END LOOP;
CLOSE posting_cursor;
if (invoice_posting_flag = 'X') then
invoice_posting_flag := 'N';
end if;
--bug6160540
if invoice_posting_flag = 'N' then
BEGIN
SELECT 'D'
INTO invoice_posting_flag
FROM ap_invoice_distributions_all AID,
xla_events XE
WHERE AID.invoice_id = l_invoice_id
AND AID.accounting_event_id = XE.event_id
AND ((AID.accrual_posted_flag = 'N' AND l_cash_basis_flag = 'N') OR
(AID.cash_posted_flag = 'N' AND l_cash_basis_flag = 'Y'))
AND XE.process_status_code = 'D'
AND rownum < 2;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
end if;
RETURN(invoice_posting_flag);
END get_posting_status;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库