How to upload AP Payment using API
Using Below program AP
Payment can be uploaded. It upload using API
CREATE OR REPLACE
procedure APPS.XX0024PRX(P_PAY_ADV_NO in VARCHAR2,P_VOUCHER_ID in
VARCHAR2,P_ORG_ID in NUMBER,P_MSG OUT VARCHAR2)
Is
V_Rowid Varchar2(50);
Lv_Doc
Number;
Lv_Period_Name
Varchar2(30);
Lv_Accounting_Event_Id
Number;
L_Transaction_Type
Varchar2(20):= 'PAYMENT CREATED';
lv_check_id
Number;
lv_doc_seq_val
Number;
lv_doc_seq_id
Number ;
lv_doc_seq_name
varchar2(100);
lv varchar2(50);
lv_doc_category Varchar2(240);
Lv_Str varchar2(60);
lv_sql varchar2(60);
lv_chk_rec_exists number;
lv_month number;
lv_year number;
lv_year_next number;
lv_from_date date;
lv_to_date date;
------------*prc :Used to generate payments-------------------
Cursor cur_voucher Is
SELECT phdr.LEGAL_ENTITY_ID
,(select SUM (amount)
from xxfah_pay_adv_lines
where PAY_ADV_NO
=phdr.PAY_ADV_NO and org_id= phdr.ORG_ID ) total_amount
,
phdr.bank_acct_use_id,
phdr.bank_account_id,cb.BANK_ACCOUNT_NAME,cb.BANK_ACCOUNT_NUM,cb.BANK_ACCOUNT_TYPE
,
phdr.payment_date,phdr.PAYMENT_METHOD,phdr.payment_profile_id---,phdr.PAYMENT_TYPE_FLAG
,
phdr.PAYMENT_DOCUMENT_ID
, phdr.currency_code,
phdr.cheque_num
, phdr.created_by,
phdr.creation_date,phdr.last_update_date, phdr.last_updated_by
,
ap.PARTY_ID,aps.PARTY_SITE_ID,ap.VENDOR_NAME,aps.VENDOR_SITE_CODE,phdr.VENDOR_ID,phdr.VENDOR_SITE_ID,
phdr.PAY_ADV_NO,phdr.VOUCHER_NO
FROM xxfah_pay_adv_hdr
phdr
, ap_suppliers ap
, ap_supplier_sites_ALL
aps
, ce_bank_accounts cb
WHERE phdr.pay_adv_no =
p_pay_adv_no
AND phdr.voucher_id = p_voucher_id
AND phdr.org_id = p_org_id
AND
phdr.VENDOR_ID=ap.VENDOR_ID
AND
phdr.VENDOR_SITE_ID=aps.VENDOR_SITE_ID
AND
ap.VENDOR_ID=aps.VENDOR_ID
AND
phdr.BANK_ACCOUNT_ID=cb.BANK_ACCOUNT_ID;
Cursor Cur_invoice Is
SELECT ppvl.invoice_id, aia.invoice_type_lookup_code,
ppvl.payment_num,
pdtl.amount,
aia.set_of_books_id, accts_pay_code_combination_id,
pdtl.last_update_date, pdtl.last_updated_by, pdtl.created_by,
pdtl.creation_date
FROM xxfah_pay_adv_lines
pdtl,
ap_invoices_all aia,
pwcss_payment_voucher_lines ppvl
WHERE pdtl.pay_adv_no =
P_PAY_ADV_NO
AND pdtl.voucher_line_id
= ppvl.voucher_line_id
AND aia.invoice_id =
ppvl.invoice_id
Order by aia.INVOICE_ID;
Begin
BEGIN
mo_global.set_policy_context
('S', p_org_id);
END;
Lv_Month :=
To_Char(Sysdate,'MM');
If Lv_Month In
('01','02','03') Then --- Following codes check the current Financial
Year....Kalyan Mitra...06-Aug-2013....
Select
To_Char(Sysdate,'YY')-1 Into Lv_Year From Dual;
Else
Select
To_Char(Sysdate,'YY') Into Lv_Year From Dual;
End If;
Lv_Year_Next := Lv_Year+1;
Lv_From_Date :=
To_Char('01-Apr-'||Lv_Year) ;
Lv_To_Date := To_Char('31-Mar-'||Lv_Year_Next);
P_MSG := 'Inserting';
/* Begin
select PAY_ADV_NO into lv
from xxfah_pay_adv_hdr a
where a.PAY_ADV_NO=1;
Exception When Others
then
P_MSG := 'Data Not
Found'||SQLERRM;
APP_EXCEPTION.RAISE_EXCEPTION;
End;
*/
select FND_DOC_SEQ_1237_S.nextval into lv_doc_seq_val from dual;
For i in Cur_Voucher
Loop
Select
count(attribute9) into lv_chk_rec_exists
from ap_checks_all
where trim(attribute9)
=i.pay_adv_no
and check_number=i.cheque_num;
If lv_chk_rec_exists
> 0 then
fnd_message.set_name
('FND', 'FND_GENERIC_MESSAGE');
fnd_message.set_token
('MESSAGE', 'Record already exists');
fnd_message.raise_error;
End If;
SELECT cep.payment_doc_category
INTO
lv_doc_category
FROM
ce_payment_documents cep
WHERE
payment_document_id = i.payment_document_id;
--added on 010114
faheiz---------fetching doc dequence id against doc category------
---application 200 is
for payables-----------------
select
x.doc_sequence_id into lv_doc_seq_id
from (
SELECT
doc_sequence_id --fetching autmatic
type sequence id
FROM
fnd_doc_sequence_assignments
WHERE category_code
= lv_doc_category
AND start_date
>= Lv_From_Date
AND end_date
<= Lv_To_Date
AND method_code =
'A'
AND
application_id = 200
UNION
SELECT
doc_sequence_id --if autmatic not found
then fetch method type null-----
FROM
fnd_doc_sequence_assignments
WHERE category_code
= lv_doc_category
AND start_date
>= Lv_From_Date
AND end_date
<= Lv_To_Date
AND method_code
IS NULL
AND
application_id = 200) x;
--fetching doc seq
name against doc seq id-------
select
DB_SEQUENCE_NAME into lv_doc_seq_name
from
fnd_document_sequences
where DOC_SEQUENCE_ID=lv_doc_seq_id;
--generating seq
value------
Lv_Str :=
lv_doc_seq_name||'.nextval';
Execute immediate
'Select '|| Lv_Str || ' from dual ' into
lv_doc_seq_val ;
------------calling
ap_checks_pkg.insert_row PKG to insert Records in the AP_Checks_All-------------------------
P_MSG := 'Insert
into ap_checks';
ap_checks_pkg.insert_row (
X_Rowid => v_rowid ,
X_Amount => i.total_amount,
X_Ce_Bank_Acct_Use_Id =>
i.bank_acct_use_id,
X_Bank_Account_Name => i.BANK_ACCOUNT_NAME,
X_Check_Date => i.payment_date,
X_Check_Id => ap_checks_s.NEXTVAL ,
X_Check_Number => i.cheque_num,
X_Currency_Code => i.currency_code,
X_Last_Updated_By => i.last_updated_by,
X_Last_Update_Date => i.last_update_date,
X_Payment_Type_Flag => 'Q', ---Q = Quick Payment
X_Address_Line1 => null,
X_Address_Line2 => null,
X_Address_Line3 => null,
X_Checkrun_Name => 'Quick Payment:
ID='||ap_checks_s.CURRVAL,
X_Check_Format_Id => null,
X_Check_Stock_Id => null,
X_City => null,
X_Country => null,
X_Created_By => i.Created_By,
X_Creation_Date => i.Creation_Date,
X_Last_Update_Login => FND_GLOBAL.USER_ID,
X_Status_Lookup_Code => 'NEGOTIABLE',
X_Vendor_Name => i.Vendor_Name,
X_Vendor_Site_Code => i.Vendor_Site_Code ,
X_External_Bank_Account_Id =>
null,
X_Zip => null,
X_Bank_Account_Num => i.BANK_ACCOUNT_NUM,
X_Bank_Account_Type => i.BANK_ACCOUNT_TYPE,
X_Bank_Num => null,
X_Check_Voucher_Num => null,
X_Cleared_Amount => null,
X_Cleared_Date => null,
X_Doc_Category_Code => lv_doc_category,--- 'AXIS BANK -
910020035884475',
X_Doc_Sequence_Id => lv_doc_seq_id,
X_Doc_Sequence_Value => lv_doc_seq_val,
X_Province => null,
X_Released_Date => null,
X_Released_By => null,
X_State => null,
X_Stopped_Date => null,
X_Stopped_By => null,
X_Void_Date => null,
X_Attribute1 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute2 => null,
X_Attribute3 => i.VOUCHER_NO,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => i.PAY_ADV_NO,
X_Attribute_Category => null,
X_Future_Pay_Due_Date => null,
X_Treasury_Pay_Date => null,
X_Treasury_Pay_Number =>
null,
X_Withholding_Status_Lkup_Code =>
null, --'i' ,
X_Reconciliation_Batch_Id
=> null,
X_Cleared_Base_Amount
=> null,
X_Cleared_Exchange_Rate
=> null,
X_Cleared_Exchange_Date
=> null,
X_Cleared_Exchange_Rate_Type
=> null,
X_Address_Line4
=> null,
X_County
=> null,
X_Address_Style
=> null,
X_Org_Id => p_org_id,
X_Vendor_Id
=> i.Vendor_Id,
X_Vendor_Site_Id
=> i.Vendor_Site_Id,
X_Exchange_Rate
=> null,
X_Exchange_Date => null,
X_Exchange_Rate_Type
=> null,
X_Base_Amount
=> null,
X_Checkrun_Id
=> null,
X_global_attribute_category =>
null,
X_global_attribute1
=> null,
X_global_attribute2
=> null,
X_global_attribute3
=> null,
X_global_attribute4 => null,
X_global_attribute5
=> null,
X_global_attribute6
=> null,
X_global_attribute7
=> null,
X_global_attribute8
=> null,
X_global_attribute9
=> null,
X_global_attribute10
=> null,
X_global_attribute11 => null,
X_global_attribute12
=> null,
X_global_attribute13
=> null,
X_global_attribute14
=> null,
X_global_attribute15 =>
null,
X_global_attribute16
=> null,
X_global_attribute17
=> null,
X_global_attribute18
=> null,
X_global_attribute19
=> null,
X_global_attribute20
=> null,
X_transfer_priority
=> null,
X_maturity_exchange_rate_type
=> null,
X_maturity_exchange_date
=> null,
X_maturity_exchange_rate
=> null,
X_description
=> null,
X_anticipated_value_date
=> null,
X_actual_value_date => null,
x_payment_method_code
=> i.PAYMENT_METHOD,
x_payment_profile_id
=> i.payment_profile_id,
x_bank_charge_bearer
=> null,
x_settlement_priority
=> null,
x_payment_document_id
=> i.PAYMENT_DOCUMENT_ID,
x_party_id
=> i.party_id,
x_party_site_id => i.party_site_id ,
x_legal_entity_id
=> 23273,
--i.legal_entity_id,
x_payment_id
=> IBY_PAYMENTS_ALL_S.nextval
,
X_calling_sequence => 'APXPAWKB',
X_Remit_To_Supplier_Name
=> null,
X_Remit_To_Supplier_Id
=> null,
X_Remit_To_Supplier_Site
=> null,
X_Remit_To_Supplier_Site_Id =>
null,
X_Relationship_Id
=> null,
X_paycard_authorization_number =>
null,
X_paycard_reference_id
=> null
);
P_MSG := 'Insert into
ap_payment_history';
P_MSG :=
FND_MESSAGE.GET;
AP_RECONCILIATION_PKG.insert_payment_history(
X_CHECK_ID
=> ap_checks_s.CURRVAL,
X_TRANSACTION_TYPE
=> l_transaction_type,
X_ACCOUNTING_DATE
=> I.PAYMENT_DATE,
X_TRX_BANK_AMOUNT
=> NULL ,
X_ERRORS_BANK_AMOUNT
=> NULL ,
X_CHARGES_BANK_AMOUNT
=> NULL ,
X_BANK_CURRENCY_CODE
=> NULL ,
X_BANK_TO_BASE_XRATE_TYPE =>
NULL ,
X_BANK_TO_BASE_XRATE_DATE =>
NULL ,
X_BANK_TO_BASE_XRATE
=> NULL ,
X_TRX_PMT_AMOUNT
=> i.total_amount,
X_ERRORS_PMT_AMOUNT
=> NULL ,
X_CHARGES_PMT_AMOUNT => NULL ,
X_PMT_CURRENCY_CODE
=> i.currency_code,
X_PMT_TO_BASE_XRATE_TYPE
=> NULL ,
X_PMT_TO_BASE_XRATE_DATE
=> NULL ,
X_PMT_TO_BASE_XRATE => NULL ,
X_TRX_BASE_AMOUNT
=> NULL ,
X_ERRORS_BASE_AMOUNT
=> NULL ,
X_CHARGES_BASE_AMOUNT
=> NULL ,
X_MATCHED_FLAG
=> NULL ,
X_REV_PMT_HIST_ID
=> NULL ,
X_ORG_ID
=> P_ORG_ID ,
X_CREATION_DATE
=> i.CREATION_DATE,
X_CREATED_BY
=> i.CREATED_BY ,
X_LAST_UPDATE_DATE
=> i.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY
=> i.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
=> FND_GLOBAL.USER_ID ,
X_PROGRAM_UPDATE_DATE =>
NULL ,
X_PROGRAM_APPLICATION_ID
=> NULL ,
X_PROGRAM_ID
=> NULL ,
X_REQUEST_ID
=> NULL ,
X_CALLING_SEQUENCE
=> 'APXPAWKB
(pay_sum_folder_pkg_i.insert_row)',
X_ACCOUNTING_EVENT_ID
=> NULL ,
x_invoice_adjustment_event_id=>NULL
);
lv_check_id :=
ap_checks_s.CURRVAL; --assigning curent check id value to local variable
BEGIN ---fetching acount event id
SELECT
accounting_event_id
INTO
lv_accounting_event_id
FROM
ap_payment_history_all
WHERE check_id
=lv_check_id
AND
transaction_type = l_transaction_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_accounting_event_id := Null;
P_MSG :=
'Accounting Event id not created'||SQLERRM;
APP_EXCEPTION.RAISE_EXCEPTION;
WHEN OTHERS
THEN
lv_accounting_event_id := Null;
P_MSG :=
'Accounting Event id not created' ||SQLERRM;
END;
BEGIN ---Fetching Period name--------
SELECT period_name
INTO lv_period_name
FROM
gl_period_statuses glp, ap_system_parameters_all aps
WHERE application_id
= 200
AND i.payment_date
BETWEEN start_date AND end_date
AND
glp.set_of_books_id = aps.set_of_books_id
AND closing_status
= 'O'
AND NVL
(adjustment_period_flag, 'N') = 'N'
AND aps.org_id =
p_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_period_name :=
null;
P_MSG := 'Error
Fetching Period Name' ||SQLERRM;
END;
For j in Cur_invoice
Loop
P_MSG := 'Insert into
ap_invoice_payments';
P_MSG :=
FND_MESSAGE.GET;
ap_pay_invoice_pkg.ap_pay_invoice(
P_invoice_id
=> j.invoice_id ,
P_check_id => ap_checks_s.CURRVAL ,
P_payment_num
=> j.payment_num ,
P_invoice_payment_id
=>
ap_invoice_payments_s.nextval ,
P_old_invoice_payment_id =>
NULL ,
P_period_name
=> lv_period_name ,
P_invoice_type
=>
j.invoice_type_lookup_code ,
P_accounting_date
=> i.payment_date ,
P_amount
=> j.amount ,
P_discount_taken
=> 0 ,
P_discount_lost
=> NULL ,
P_invoice_base_amount => NULL
,
P_payment_base_amount
=> NULL ,
P_accrual_posted_flag
=> 'N' ,
P_cash_posted_flag
=> 'N' ,
P_posted_flag
=> 'N' ,
P_set_of_books_id
=> j.set_of_books_id ,
P_last_updated_by
=> j.last_updated_by ,
P_last_update_login
=> FND_GLOBAL.USER_ID ,
P_currency_code => i.currency_code ,
P_base_currency_code
=> NULL,
P_exchange_rate
=> NULL ,
P_exchange_rate_type
=> NULL ,
P_exchange_date => NULL
,
P_ce_bank_acct_use_id
=> i.bank_acct_use_id ,
P_bank_account_num
=> i.bank_account_num ,
P_bank_account_type
=> i.bank_account_type ,
P_bank_num
=> NULL ,
P_future_pay_posted_flag
=> NULL ,
P_exclusive_payment_flag
=> NULL ,
P_accts_pay_ccid =>
j.accts_pay_code_combination_id ,
P_gain_ccid
=> NULL ,
P_loss_ccid
=> NULL ,
P_future_pay_ccid
=> NULL ,
P_asset_ccid => NULL
,
P_payment_dists_flag
=> 'N' ,
P_payment_mode
=> 'PAY' ,
P_replace_flag
=> 'N' ,
P_attribute1 => NULL
,
P_attribute2
=> NULL ,
P_attribute3
=> NULL ,
P_attribute4
=> NULL ,
P_attribute5 => NULL
,
P_attribute6
=> NULL ,
P_attribute7
=> NULL ,
P_attribute8
=> NULL ,
P_attribute9 => NULL
,
P_attribute10
=> NULL ,
P_attribute11
=> NULL ,
P_attribute12
=> NULL ,
P_attribute13 => NULL
,
P_attribute14
=> NULL ,
P_attribute15
=> NULL ,
P_attribute_category
=> NULL ,
P_global_attribute1 => NULL
,
P_global_attribute2
=> NULL ,
P_global_attribute3
=> NULL ,
P_global_attribute4
=> NULL ,
P_global_attribute5 => NULL
,
P_global_attribute6
=> NULL ,
P_global_attribute7
=> NULL ,
P_global_attribute8
=> NULL ,
P_global_attribute9
=> NULL ,
P_global_attribute10
=> NULL ,
P_global_attribute11
=> NULL ,
P_global_attribute12
=> NULL ,
P_global_attribute13
=> NULL ,
P_global_attribute14
=> NULL ,
P_global_attribute15
=> NULL ,
P_global_attribute16
=> NULL ,
P_global_attribute17
=> NULL ,
P_global_attribute18
=> NULL ,
P_global_attribute19
=> NULL ,
P_global_attribute20
=> NULL
,
P_global_attribute_category =>
NULL ,
P_calling_sequence
=> 'Pay Invoice Forms <-
Pre_inser trigger' ,
P_accounting_event_id
=>
lv_accounting_event_id ,
P_org_id
=> p_org_id );
End Loop;
End Loop;
End;
/
No comments:
Post a Comment