[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 2. If invoice is selected for payment, return FALSE
| 3. If invoice is already cancelled, return FALSE
| 4. If invoice is credited invoice, return FALSE
| 5. If invoices have been applied against this invoice, return FALSE
| 6. If invoice is matched to Finally Closed PO's, return FALSE
| 7. If project related invoices have pending adjustments, return FALSE
| 8. If cancelling will cause qty_billed or amount_billed to less
| than 0, return FALSE
| 9. If none of above, invoice is cancellable return Ture
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
Function Is_Invoice_Cancellable(
P_invoice_id IN NUMBER,
P_error_code OUT NOCOPY VARCHAR2, /* Bug 5300712 */
P_debug_info IN OUT NOCOPY VARCHAR2,
P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR verify_no_pay_batch IS
SELECT checkrun_id
FROM ap_payment_schedules
WHERE invoice_id = P_invoice_id
FOR UPDATE NOWAIT;
SELECT count(*)
FROM ap_invoice_distributions AID,
po_distributions_ap_v POD,
po_line_locations PLL,
po_lines PL,
ap_invoices AIV
WHERE POD.po_distribution_id = AID.po_distribution_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.po_line_id = PL.po_line_id
AND AIV.invoice_id=AID.invoice_id
AND NVL(AID.reversal_flag, 'N') <> 'Y'
AND AID.invoice_id = P_invoice_id
-- Bug 5590826. For amount related decode
AND AID.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'IPV')
HAVING (DECODE(AIV.invoice_type_lookup_code,'PREPAYMENT',
SUM(NVL(POD.quantity_financed, 0)),SUM(NVL(POD.quantity_billed, 0))
) -
SUM(round(decode(AID.dist_match_type,
'PRICE_CORRECTION', 0,
'AMOUNT_CORRECTION', 0,
'ITEM_TO_SERVICE_PO', 0,
'ITEM_TO_SERVICE_RECEIPT', 0,
nvl( AID.quantity_invoiced, 0 ) +
nvl( AID.corrected_quantity,0 )
) *
po_uom_s.po_uom_convert(AID.matched_uom_lookup_code, --bug5844328
nvl(PLL.unit_meas_lookup_code,
PL.unit_meas_lookup_code),
PL.item_id), 15)
) < 0
OR DECODE(AIV.invoice_type_lookup_code,'PREPAYMENT',
SUM(NVL(POD.amount_financed, 0)),SUM(NVL(POD.amount_billed, 0))) -
SUM(NVL(AID.amount, 0)) < 0 )
GROUP BY AIV.invoice_type_lookup_code,AID.po_distribution_id;
SELECT accounting_date
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = P_invoice_id
AND NVL(AID.reversal_flag, 'N') <> 'Y';
/* bug 4942638. Move the next select here */
l_debug_info := 'Get the org_id for the invoice';
SELECT org_id
INTO l_org_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
/* SELECT count(*)
INTO l_payment_count
FROM ap_invoice_payments P,ap_payment_schedules PS
WHERE P.invoice_id=PS.invoice_id
AND P.invoice_id = P_invoice_id
AND PS.payment_status_flag <> 'N'
AND nvl(P.reversal_flag,'N') <> 'Y'
AND P.amount is not NULL
AND exists ( select 'non void check'
from ap_checks A
where A.check_id = P.check_id
and void_date is null);*/--Bug 6135172
SELECT COUNT(*)
INTO l_payment_count
FROM ap_invoice_payments P
WHERE P.invoice_id = P_invoice_id
AND NVL(P.reversal_flag,'N') <> 'Y'
AND P.amount IS NOT NULL
AND EXISTS
(SELECT 'non void check'
FROM ap_checks A
WHERE A.check_id = P.check_id
AND void_date IS NULL
);
| Step 2. If invoice is selected for payment, return FALSE |
+-----------------------------------------------------------------*/
l_debug_info := 'Check if invoice is selected for payment';
SELECT count(*)
INTO l_cancel_count
FROM ap_invoices
WHERE invoice_id = P_invoice_id
AND cancelled_date IS NOT NULL;
/* bug 4942638. Move the next select for l_org_id at the begining */
IF (FV_INSTALL.ENABLED (l_org_id)) THEN
BEGIN
SELECT 'N'
INTO l_allow_cancel
FROM ap_invoice_distributions AID,
po_distributions PD,
po_line_locations pll
WHERE aid.invoice_id = p_invoice_id
--AND aid.final_match_flag in ('N','Y') For Bug 3489536
AND aid.po_distribution_id = pd.po_distribution_id
AND pll.line_location_id = pd.line_location_id
AND decode(pll.final_match_flag, 'Y', 'D', aid.final_match_flag) in ('N','Y') --Bug 3489536
AND pll.closed_code = 'FINALLY CLOSED'
AND rownum = 1;
SELECT count(distinct pll.line_location_id)
INTO l_final_closed_shipment_count
FROM ap_invoice_distributions aid,
po_line_locations pll,
po_distributions pd
WHERE aid.invoice_id = p_invoice_id
AND aid.po_distribution_id = pd.po_distribution_id
AND pd.line_location_id = pll.line_location_id
--AND aid.final_match_flag = 'D' For bug 3489536
AND decode(pll.final_match_flag, 'Y', 'D', aid.final_match_flag) = 'D' --Bug 3489536
AND pll.closed_code = 'FINALLY CLOSED';
SELECT count(*)
INTO l_final_close_count
FROM ap_invoice_lines AIL,
po_line_locations_ALL PL
WHERE AIL.invoice_id = P_invoice_id
AND AIL.po_line_location_id = PL.line_location_id
AND AIL.org_id = PL.org_id
AND PL.closed_code = 'FINALLY CLOSED';
/* SELECT count(*)
INTO l_project_related_count
FROM ap_invoices AI
WHERE AI.invoice_id = P_invoice_id
AND (AI.project_id is not null OR
exists (select 'X'
from ap_invoice_distributions AIL
where AIL.invoice_id = AI.invoice_id
and project_id is not null) OR
exists (select 'X'
from ap_invoice_distributions AID
where AID.invoice_id = AI.invoice_id
and project_id is not null));
SELECT NVL(purch_encumbrance_flag,'N')
INTO l_enc_enabled
FROM financials_system_params_all
WHERE NVL(org_id, -99) = NVL(l_org_id, -99);
select 'Y'
into l_po_not_approved
from po_headers POH,
po_distributions POD,
ap_invoice_distributions AID,
ap_invoices AI
where AI.invoice_id = AID.invoice_id
and AI.invoice_id = P_invoice_id
and AID.po_distribution_id = POD.po_distribution_id
and POD.po_header_id = POH.po_header_id
and POH.approved_flag <> 'Y'
and rownum = 1;
| f. update Line level Cancelled information
| 6. Zero out the Invoice
| 7. Run AutoApproval for this invoice
| 8. check posting holds remain on this canncelled invoice
| a. if NOT exist - complete the cancellation by updating header
| level information set return value to TRUE
| b. if exist - no update, set the return valuse to FALSE, NO
| DATA rollback.
| 9. Commit Data
| 10. Populate the out parameters.
|
| NOTES
| 1. bug2328225 case of Matching a special charge only invoice to
| receipt so we check if the quantity invoiced is not null too
| 2. Events Project
| We no longer need to prevent the cancellation of an invoice
| just because the accounting of related payments has not been
| created. Therefore, bug fixes 902110 and 2237152 are removed.
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
FUNCTION Ap_Cancel_Single_Invoice(
P_invoice_id IN NUMBER,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_accounting_date IN DATE,
P_message_name OUT NOCOPY VARCHAR2,
P_invoice_amount OUT NOCOPY NUMBER,
P_base_amount OUT NOCOPY NUMBER,
P_temp_cancelled_amount OUT NOCOPY NUMBER,
P_cancelled_by OUT NOCOPY NUMBER,
P_cancelled_amount OUT NOCOPY NUMBER,
P_cancelled_date OUT NOCOPY DATE,
P_last_update_date OUT NOCOPY DATE,
P_original_prepayment_amount OUT NOCOPY NUMBER,
P_pay_curr_invoice_amount OUT NOCOPY NUMBER,
P_Token OUT NOCOPY VARCHAR2,
P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR Invoice_Lines_cur IS
SELECT *
FROM ap_invoice_lines
WHERE invoice_id = P_invoice_id
AND (NVL(discarded_flag, 'N' ) <> 'Y'
AND NVL(cancelled_flag, 'N') <> 'Y') -- Bug 6669048
ORDER BY line_type_lookup_code;
SELECT AH.hold_lookup_code
FROM AP_HOLDS AH,
AP_HOLD_CODES AHC
WHERE AH.invoice_id = P_invoice_id
AND AH.hold_lookup_code = AHC.hold_lookup_code
AND AH.release_lookup_code IS NULL
AND AH.hold_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE');
SELECT Reg_Application_ID
FROM AP_Invoices_All AI,
AP_Product_Registrations APR
WHERE AI.Invoice_ID = P_Invoice_ID
AND AI.Application_ID = APR.Reg_Application_ID
AND APR.Registration_Event_Type = 'INVOICE_CANCELLED';
Select *
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id;
SELECT TRUNC( NVL( MAX( AID.accounting_date ) , P_accounting_date ) )
INTO l_max_inv_dist_acc_date
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = P_invoice_id
AND AID.awt_invoice_payment_id IS NULL ;
SELECT TRUNC( NVL( MAX( AIP.accounting_date ) , P_accounting_date ) )
INTO l_max_pmt_acc_date
FROM ap_invoice_payments AIP
WHERE AIP.invoice_id = P_invoice_id ;
SELECT org_id
INTO l_org_id
FROM ap_invoices
WHERE invoice_id = P_invoice_id ;
SELECT count(*)
INTO l_count
FROM ap_invoice_distributions
WHERE invoice_id = P_invoice_id
AND NVL(awt_flag,'N') = 'A' --bug 9702328
AND NVL(reversal_flag,'N') <> 'Y';
P_Last_Updated_By,
P_Last_Update_Login,
NULL,
NULL,
NULL,
l_result_string);
UPDATE ap_payment_schedules
SET gross_amount = 0
,amount_remaining = 0
,payment_status_flag = 'N'
,hold_flag = 'N'
,last_updated_by = P_last_updated_by
,last_update_date = sysdate
,inv_curr_gross_amount =0 --Bug5446999
WHERE invoice_id = P_invoice_id;
| Step 4a. Delete all unprocessed bc events and update the |
| encumbered flag to 'R' |
+-----------------------------------------------------------------*/
--Start of bug 8733916
AP_FUNDS_CONTROL_PKG.Encum_Unprocessed_Events_Del
(p_invoice_id => p_invoice_id,
p_calling_sequence => l_curr_calling_sequence);
UPDATE ap_invoice_distributions aid
SET aid.encumbered_flag = 'R'
WHERE aid.invoice_id = p_invoice_id
AND nvl(aid.match_status_flag,'N') <> 'A'
AND nvl(aid.encumbered_flag,'N') <> 'Y'
AND aid.parent_reversal_id is null
AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX','TRV','TERV','TIPV')
AND nvl(aid.reversal_flag,'N')<>'Y'
AND EXISTS (SELECT 1
FROM financials_system_params_all fsp
WHERE fsp.org_id = aid.org_id
AND nvl(fsp.purch_encumbrance_flag, 'N') = 'Y');
SELECT invoice_type_lookup_code,
payment_status_flag,
invoice_amount
INTO l_invoice_type_lookup_code,
l_payment_status_flag,
l_invoice_amount
FROM ap_invoices
WHERE invoice_id =p_invoice_id;
P_last_updated_by => p_last_updated_by,
P_last_update_login => p_last_update_login,
P_error_code => l_error_code,
P_Token => l_token,
P_calling_sequence => l_curr_calling_sequence);
l_inv_line_rec_list.DELETE;
SELECT count(*)
INTO l_tax_lines_count
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'TAX'
AND NVL(cancelled_flag,'N') <> 'Y'
AND rownum =1;
SELECT count(*)
INTO l_self_assess_tax_count
FROM ap_self_assessed_tax_dist_all asat,
zx_rec_nrec_dist zx_dist
WHERE invoice_id = p_invoice_id
AND asat.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND zx_dist.self_assessed_flag = 'Y'
AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
AND line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
AND rownum =1;
SELECT count(*)
INTO l_tax_dist_count
FROM zx_rec_nrec_dist zx_dist,
ap_invoice_distributions ap_dist
WHERE ap_dist.invoice_id = p_invoice_id
AND ap_dist.detail_tax_dist_id = zx_dist.rec_nrec_tax_dist_id
AND nvl(zx_dist.reverse_flag, 'N') <> 'Y'
AND nvl(zx_dist.inclusive_flag, 'N') = 'Y'
AND ap_dist.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX')
AND rownum =1; */
SELECT count(*)
INTO l_tax_lines_count
FROM zx_lines_summary zls
WHERE zls.trx_id = p_invoice_id
and zls.application_id = 200
and zls.entity_code = 'AP_INVOICES'
and zls.event_class_code In ('STANDARD INVOICES',
'PREPAYMENT INVOICES',
'EXPENSE REPORTS')
and NVL(zls.reporting_only_flag, 'N') = 'N'
and nvl(zls.cancel_flag, 'N') <> 'Y'
and rownum =1;
UPDATE ap_invoice_lines
SET cancelled_flag = 'Y'
WHERE invoice_id = P_invoice_id
AND NVL(discarded_flag, 'N' ) <> 'Y';
For I in(select invoice_distribution_id
from ap_invoice_distributions aid1
where aid1.invoice_id=P_invoice_id
and aid1.parent_reversal_id is null --original dist
--for original dists there is no reversal dist created
and ( not exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_invoice_id
and aid2.invoice_line_number=aid1.invoice_line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id)
--the reversal dist does not reverse the amount correctly
or exists (select 1 from ap_invoice_distributions aid2
where aid1.invoice_id=aid2.invoice_id
and aid2.invoice_id=P_invoice_id
and aid2.invoice_line_number=aid1.invoice_line_number
and aid2.parent_reversal_id =aid1.invoice_distribution_id
and -1 * aid2.amount <> aid1.amount)))
LOOP
prob_dist_list := prob_dist_list||','||i.invoice_distribution_id;
UPDATE ap_invoices
SET invoice_amount = 0
,base_amount = 0
,temp_cancelled_amount = DECODE(temp_cancelled_amount, NULL,
invoice_amount,
DECODE(invoice_amount, 0,
temp_cancelled_amount,
invoice_amount))
,pay_curr_invoice_amount = 0
,last_updated_by = P_last_updated_by
,last_update_date = sysdate
WHERE invoice_id = P_invoice_id;
UPDATE ap_invoice_lines
SET cancelled_flag = 'Y'
WHERE invoice_id = P_invoice_id
AND NVL(discarded_flag, 'N' ) <> 'Y';
SELECT count(*)
INTO l_holds_count
FROM ap_holds AH
,ap_hold_codes AHC
WHERE AH.invoice_id = P_invoice_id
AND AH.hold_lookup_code = AHC.hold_lookup_code
AND AH.release_lookup_code IS NULL
AND AHC.postable_flag = 'N';
| 2. Update the invoice header information |
| 3. Release all the holds |
| 4. set return value to TRUE - indicate success |
+-----------------------------------------------------------------*/
l_debug_info := 'Check if invoice has any tax holds';
SELECT count(*)
INTO l_tax_holds_count
FROM ap_holds AH
,ap_hold_codes AHC
WHERE AH.invoice_id = P_invoice_id
AND AH.hold_lookup_code = AHC.hold_lookup_code
AND AH.release_lookup_code IS NULL
AND AH.hold_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE');
SELECT COUNT(invoice_distribution_id)
INTO l_cancel_dist_exists
FROM ap_invoice_distributions
WHERE invoice_id=p_invoice_id
AND cancellation_flag='Y'
AND rownum=1;
UPDATE ap_invoices
SET cancelled_by = P_last_updated_by
,cancelled_amount = temp_cancelled_amount
,cancelled_date = sysdate
,last_updated_by = P_last_updated_by
,last_update_date = sysdate
WHERE invoice_id = P_invoice_id;
SELECT COUNT(1)
INTO l_unsuccessful_cancel
FROM ap_invoice_distributions AID,
financials_system_params_all FSP
WHERE AID.invoice_id = P_invoice_id
AND FSP.org_id = AID.org_id
AND FSP.set_of_books_id = AID.set_of_books_id
AND ( ( NVL( FSP.purch_encumbrance_flag, 'N' ) = 'Y'
AND NVL( AID.match_status_flag, 'N' ) <> 'A'
)
OR ( NVL( FSP.purch_encumbrance_flag, 'N' ) = 'N'
AND NVL( AID.match_status_flag, 'N' ) NOT IN ( 'A', 'T' )
)
)
AND ROWNUM = 1 ;
SELECT invoice_num
INTO P_Token
FROM ap_invoices
WHERE invoice_id = p_invoice_id ;
FOR c_wf_status IN ( SELECT hold_id
FROM ap_holds
WHERE invoice_id = p_invoice_id
AND release_lookup_code IS NULL
AND wf_status IN ( 'STARTED', 'NEGOTIATE' )
)
LOOP
AP_WORKFLOW_PKG.abort_holds_workflow( c_wf_status.hold_id ) ;
UPDATE ap_holds
SET release_lookup_code = 'APPROVED'
,release_reason = ( SELECT description
FROM ap_hold_codes
WHERE hold_lookup_code = 'APPROVED')
,last_update_date = SYSDATE
,last_updated_by = P_last_updated_by
,last_update_login = P_last_update_login
WHERE invoice_id = P_invoice_id
AND release_lookup_code IS NULL ;
SELECT invoice_amount
,base_amount
,temp_cancelled_amount
,cancelled_by
,cancelled_amount
,cancelled_date
,last_update_date
,original_prepayment_amount
,pay_curr_invoice_amount
INTO P_invoice_amount
,P_base_amount
,P_temp_cancelled_amount
,P_cancelled_by
,P_cancelled_amount
,P_cancelled_date
,P_last_update_date
,P_original_prepayment_amount
,P_pay_curr_invoice_amount
FROM ap_invoices
WHERE invoice_id = P_invoice_id;
||' P_last_updated_by = ' || P_last_updated_by
||' P_last_update_login = ' || P_last_update_login
||' P_accounting_date = ' || P_accounting_date);
| 2. invoices that are selected for payment,
| 3. invoices that are already cancelled
| 4. invoices (prepayments) that have been used by other invoices
| 5. invoices that are matched to Finally Closed PO's)
| 6. invoices which were paid originally by check but whose payment
| was removed prior to the voiding of the check i.e. through an
| invoice adjustment are left unaffected.
|
| NOTES
| 1. AutoApproval is run for each invoice. If the invoice has posting
| holds, it is zeroed out by reversing all invoice distributions and
| PO matching, but the invoice is not cancelled.
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
PROCEDURE Ap_Cancel_Invoices(
P_check_id IN NUMBER,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_accounting_date IN DATE,
P_num_cancelled OUT NOCOPY NUMBER,
P_num_not_cancelled OUT NOCOPY NUMBER,
P_calling_sequence IN VARCHAR2)
IS
l_num_cancelled NUMBER := 0;
l_last_update_date DATE;
| Declare cursor to select all invoices associated with the |
| payment given by P_check_id and ensuring that the invoice was |
| effectively being paid by the check i.e. the invoice payment |
| wasn't already reversed. |
+-----------------------------------------------------------------*/
--bug 5182311 Modified the cursor to ignore already cancelled invoices
-- Bug 8257752. Commented out the reversal_flag condition.
CURSOR invoices_cursor IS
SELECT DISTINCT aip.invoice_id
FROM ap_invoice_payments aip,ap_invoices ai
WHERE aip.check_id = P_check_id
--AND nvl(aip.reversal_flag, 'N') <> 'Y'
AND ai.invoice_id=aip.invoice_id
AND ai.cancelled_date is null;
SELECT ai.gl_date
INTO l_invoice_gl_date
FROM ap_invoices ai
WHERE ai.invoice_id = l_invoice_id_list(i); */
P_last_updated_by,
P_last_update_login,
P_accounting_date, -- Bug 9497953 l_invoice_gl_date, --P_accounting_date,-- bug 6883407
l_message_name,
l_invoice_amount,
l_base_amount,
l_temp_cancelled_amount,
l_cancelled_by,
l_cancelled_amount,
l_cancelled_date,
l_last_update_date,
l_original_prepayment_amount,
l_pay_curr_invoice_amount,
l_token,
l_curr_calling_sequence);
l_invoice_id_list.DELETE;
||' P_last_updated_by = ' || P_last_updated_by
||' P_last_update_login = ' || P_last_update_login
||' P_accounting_date = ' || P_accounting_date);
SELECT Reg_Application_ID,
Registration_API
FROM AP_Product_Registrations
WHERE Reg_Application_ID = P_Application_ID
AND Registration_Event_Type = P_Event_Type;