Oracle Technology: February 2023

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)

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;

Oracle Technology

Total Pageviews

Pages

Monday, 27 February 2023

EBS: Oracle Forms : How to display message from database to Oracle Forms

Wednesday, 15 February 2023

EBS : AP Payment API : How to upload AP Payment using API (Oracle Payment API)

FUSION: Purchasing Query

Report Abuse