查询发票是否已过账的语句
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;
