[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payment_instruction_id,
paper_document_number
FROM iby_fd_payments_v
WHERE payment_id = p_payment_id;
UPDATE jl_ar_ap_awt_certif
set status = 'VOID'
where checkrun_id = l_checkrun_id
and check_number = l_check_number;
| The package will insert records into the table |
| JL_AR_AP_AWT_CERTIF. This table is used for create the |
| Withholding Certificates. |
| Check runname is the Payment Innstruction |
| Check ID id the payment ID |
| |
**********************************************************************/
FUNCTION JL_AR_AP_GEN_CERTIFICATES(
p_payment_instruction_id IN NUMBER,
p_calling_module IN VARCHAR2,
p_errmsg IN OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
-----------VARIABLES-----------
v_return BOOLEAN ;
SELECT
ipmt.process_type payment_type ,
ipmt.payment_id payment_identification,
aba.bank_account_num bank_account_num ,
nvl(ipmt.paper_document_number,ipmt.payment_reference_number) payment_document_name, --Bug6792997
nvl(ipmt.paper_document_number,ipmt.payment_reference_number) check_number, --Bug6792997
atc.global_attribute4 awt_type_code ,
pv.vendor_id vendor_id ,
'ipmt.call_app_pay_service_req_code' checkrun_name,
ipmt.payment_instruction_id checkrun_id,
aid.amount withholding_amount ,
aid.base_amount base_withholding_amount ,
aid.awt_gross_amount*nvl(aid.exchange_rate,1) taxable_base_amount,
ipmt.payment_date withholding_date,
atc.name tax_name,
docs.legal_entity_id legal_entity_id
FROM IBY_FD_PAYMENTS_V ipmt,
CE_BANK_ACCOUNTS aba,
IBY_FD_DOCS_PAYABLE_V docs,
AP_TAX_CODES_ALL atc,
AP_INVOICE_DISTRIBUTIONS_ALL aid,
AP_INVOICE_PAYMENTS_ALL aip,
PO_VENDORS pv
WHERE ipmt.payment_instruction_id = p_payment_instruction_id
and ipmt.process_type ='IMMEDIATE'
and ( ('GENERATE' = p_calling_module AND ipmt.payment_status ='INSTRUCTION_CREATED') OR
('GENERATE' = p_calling_module AND ipmt.payment_status ='FORMATTED') OR
('REPRINT' = p_calling_module AND ipmt.payment_status ='READY_TO_REPRINT'))
and ipmt.payment_id = docs.payment_id
and aip.invoice_id = docs.calling_app_doc_unique_ref2
and ipmt.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id
and atc.tax_id = aid.withholding_tax_code_id -- BUG 954106
and aid.amount <> 0
and aid.line_type_lookup_code = 'AWT'
and aid.awt_invoice_payment_id = aip.invoice_payment_id
and aid.invoice_id = aip.invoice_id
and ipmt.payee_party_id = pv.party_id
UNION ALL
SELECT ipmt.process_type payment_type,
ipmt.payment_id payment_identification,
aba.bank_account_num bank_account_num,
nvl(ipmt.paper_document_number,ipmt.payment_reference_number) payment_document_name, --bug6792997
nvl(ipmt.paper_document_number,ipmt.payment_reference_number) check_number, --Bug6792997
atc.global_attribute4 awt_type_code,
pv.vendor_id vendor_id,
'ipmt.call_app_pay_service_req_code' checkrun_name,
ipmt.payment_instruction_id checkrun_id,
aid.withholding_amount*(-1) withholding_amount,
aid.base_withholding_amount*(-1) base_withholding_amount,
aid.gross_amount taxable_base_amoun,
ipmt.payment_date withholding_date,
atc.name tax_name,
docs.legal_entity_id legal_entity_id
FROM iby_fd_payments_v ipmt,
ce_bank_accounts aba,
iby_fd_docs_payable_v docs,
ap_tax_codes_all atc,
AP_AWT_TEMP_DISTRIBUTIONS_ALL aid,
ap_selected_invoices_all api,
po_vendors pv
WHERE ipmt.payment_instruction_id = p_payment_instruction_id
AND ipmt.process_type ='STANDARD'
AND (('GENERATE' = p_calling_module AND ipmt.payment_status ='INSTRUCTION_CREATED') OR
('GENERATE' = p_calling_module AND ipmt.payment_status ='FORMATTED') OR
('REPRINT' = p_calling_module AND ipmt.payment_status ='READY_TO_REPRINT')) -- Dario
AND ipmt.payment_id = docs.payment_id
AND api.invoice_id = docs.calling_app_doc_unique_ref2
AND ipmt.internal_bank_account_id = aba.bank_account_id
--AND atc.tax_id = aid.withholding_tax_code_id -- BUG 954106
AND atc.name = aid.tax_name
AND aid.checkrun_name = api.checkrun_name
AND aid.invoice_id = api.invoice_id
AND aid.withholding_amount <> 0
AND aid.payment_num = api.payment_num
AND ipmt.payee_party_id = pv.party_id;
SELECT jl_ar_ap_awt_certif_s.nextval
INTO v_table_id
FROM dual ;
INSERT INTO jl_ar_ap_awt_certif_all
(certificate_id ,
certificate_number ,
bank_account_num ,
payment_document_name ,
check_number ,
location_id ,
awt_date ,
awt_type_code ,
tax_name ,
vendor_id ,
checkrun_name ,
credit_amount ,
taxable_base_amount ,
withholding_amount ,
status ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
checkrun_id,
legal_entity_id,
payment_instruction_id,
org_id)
VALUES(
v_table_id ,
v_next_certificate_number ,
v_bank_account_num ,
v_payment_document_name ,
v_check_number ,
v_location_id ,
v_withholding_date ,
v_awt_type_code ,
v_tax_name ,
v_vendor_id ,
v_checkrun_name ,
v_credit_amount ,
v_taxable_base_amount ,
v_withholding_amount ,
'NOT PRINTED' ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.login_id ,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id ,
fnd_global.conc_program_id ,
SYSDATE,
v_checkrun_id,
v_legal_entity_id,
v_payment_instruction_id,
v_org_id );
SELECT jaaac1.certificate_number
INTO max_number
FROM jl_ar_ap_awt_certif_all jaaac1
WHERE jaaac1.awt_type_code = p_awt_type_code
AND jaaac1.certificate_number = (select max(jaaac2.certificate_number)
from jl_ar_ap_awt_certif_all jaaac2
where jaaac2.awt_type_code = p_awt_type_code)
FOR UPDATE;
SELECT (jlts.calc_wh_amnt - jlts.act_wheld_amnt)
INTO v_amount
FROM jl_ar_ap_sup_awt_cr_lts jlts,
ap_tax_codes atc
WHERE
jlts.po_vendor_id = v_vendor_id
and jlts.awt_type_code = v_awt_type_code
and jlts.tax_id = atc.tax_id
and status = 'AA'
-- and For KI uptake
-- (( v_payment_type = 'BATCH'
-- and selected_check_id = v_payment_identification ) --this condition is NA but will leave it
-- or ( v_payment_type = 'QUICK'
-- and check_id = v_payment_identification ))
and atc.name = v_tax_name;
l_debug_info := 'Selecting Certificates ...';
l_debug_info := 'Selected Certificate Check:' ||
to_char(rec_batch.check_number) || ' Type:' || rec_batch.awt_type_code;