CREATE OR REPLACE PROCEDURE xx_apinv_wht_validate(errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_inv_date_from IN VARCHAR2
,p_inv_date_to IN VARCHAR2
)
AS
CURSOR cur_wht_inv(p_org_id IN NUMBER
,v_inv_date_from IN DATE
,v_inv_date_to IN DATE
)
IS
SELECT DISTINCT aia.invoice_id
,aia.org_id
,aia.invoice_num
,aia.invoice_type_lookup_code
FROM ap_invoices_all aia
,ap_invoice_lines_all aila
WHERE 1 = 1
AND aia.invoice_type_lookup_code = 'AWT'
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND NVL(aila.cancelled_flag,'Y') <> 'Y'
AND ap_invoices_pkg.get_approval_status(aia.invoice_id
,aia.invoice_amount
,aia.payment_status_flag
,aia.invoice_type_lookup_code
)NOT IN ('APPROVED', 'UNPAID')
AND aia.org_id = p_org_id
AND aia.invoice_date BETWEEN NVL(v_inv_date_from, aia.invoice_date) AND NVL(v_inv_date_to, aia.invoice_date)
;
l_org_id hr_operating_units.organization_id%TYPE;
l_hold_cnt NUMBER;
l_inv_from_date DATE;
l_inv_to_date DATE;
BEGIN
fnd_file.put_line (fnd_file.log , 'XX WHT Invoice Validation Start');
l_org_id := fnd_profile.value('ORG_ID');
l_inv_from_date := fnd_date.canonical_to_date(p_inv_date_from);
l_inv_to_date := fnd_date.canonical_to_date(p_inv_date_to);
mo_global.set_policy_context('S', l_org_id);
fnd_global.apps_initialize(user_id => fnd_global.user_id
,resp_id => fnd_global.resp_id
,resp_appl_id => fnd_global.resp_appl_id
);
FOR rec_wht_inv IN cur_wht_inv(l_org_id
,l_inv_from_date
,l_inv_to_date
)
LOOP
fnd_file.put_line (fnd_file.log ,'WHT invoice_num: '||rec_wht_inv.invoice_num);
BEGIN
l_hold_cnt := NULL;
IF (ap_approval_pkg.batch_approval(p_run_option => NULL
,p_sob_id => fnd_profile.VALUE('GL_SET_OF_BKS_ID')
,p_inv_start_date => NULL
,p_inv_end_date => NULL
,p_inv_batch_id => NULL
,p_vendor_id => NULL
,p_pay_group => NULL
,p_invoice_id => rec_wht_inv.invoice_id
,p_entered_by => NULL
,p_debug_switch => 'N'
,p_conc_request_id => fnd_profile.VALUE('CONC_REQUEST_ID')
,p_commit_size => NULL
,p_org_id => rec_wht_inv.org_id
,p_report_holds_count => l_hold_cnt
,p_transaction_num => NULL
)
)
THEN
fnd_file.put_line (fnd_file.log , 'Invoice Validated: '||rec_wht_inv.invoice_num);
ELSE
fnd_file.put_line (fnd_file.log , 'Invoice Validation Failed: '||rec_wht_inv.invoice_num);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log , rec_wht_inv.invoice_num ||' Invoice Validation failed with unhandled exception.Error: '||SQLERRM);
END;
END LOOP;
fnd_file.put_line (fnd_file.log , 'XX WHT Invoice Validation End');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log ,' XX_APINV_WHT_VALIDATE failed with Unhandled Exception.Error: '||SQLERRM);
END;
