Command to create soft link in oracle apps

Command to create soft link in oracle apps

In this post , we will be discuss about the command to create the soft link in oracle apps. We need to create the soft link in oracle apps, if we are using the shell scripting prog file as a type ‘host’ in concurrent program executable. To execute the shell scripting from oracle concurrent program, we need to create the soft link for that prog file in oracle apps. Then need to register this prog file as executable type ‘Host’. Please find below the detail steps to create the soft link in oracle apps.

Detail steps to create the soft link in oracle apps

Step1:- For Example , We are registering the Shell script prog file in Payables Application as a Execution method 'Host'.

We need to register this prog file as a host file for concurrent programs in oracle apps.

Step2:- We need to place the shell script XX_TEST.prog file under $XXAP_TOP\bin folder

Step3:-   Create a SOFT LINK for the .prog files using below commands

          ln -s $FND_TOP/bin/fndcpesr $XXAP_TOP/bin/XX_TEST

Step4 :- Give the 777 permission to the new created SOFT LINK program

api for resetting password in oracle ebs

api for resetting password in oracle ebs

In this post , We will be discussing about api for resetting password in oracle ebs. This API helps to reset the user account password from backend in oracle abs. We can mass update the user account password with the help of this api in oracle EBS. This is the standard API shared by oracle for resetting password. We need to know the User Name for resetting password using API. Here below is the complete script using api for resetting password in oracle ebs.

Standard API for resetting password in oracle ebs

Here below is the package name uses to reset the password in oracle ebs

fnd_user_pkg.changepassword

PLSQL Script for resetting password in oracle ebs

DECLARE

     P_CHANGE BOOLEAN;

V_USER VARCHAR2(400):=’TEST_USER’;

V_NEW_PASSWORD VARCHAR2(400):=’P@1234’;

BEGIN

      P_CHANGE := fnd_user_pkg.changepassword(username=> V_USER

                                          ,newpassword => V_NEW_PASSWORD);

    IF P_CHANGE

     THEN

           DBMS_OUTPUT.PUT_LINE('Password has been successfully reset for user ‘||V_USER);

     ELSE

           DBMS_OUTPUT.PUT_LINE('Password reset has been failed for user ‘||V_USER);

     END IF;

END;

API for resetting password in oracle ebs

Pick release tables in oracle apps

Pick release tables in oracle apps

In this post , We will be discussing about the pick release tables in oracle apps. We do pick release the sales order when we want to request the material from Inventory for Shipping. Pick release internally create the Move order request for the FG inventory item from FGI Sub Inventory to Staging Sub inventory in oracle apps. When any sales order shipment pick release, we do get this information in wsh_delivery_details table. As such, there is no specific pick release tables in oracle apps but I will try to share some of the important pick release tables which do parts in the Pick release process in oracle apps.

Pick release Process tables in oracle apps

1.WSH_DELIVERY_DETAILS

2.WSH_NEW_DELIVERIES

3. MTL_TXN_REQUEST_HEADERS

4. MTL_TXN_REQUEST_LINES

WSH_DELIVERY_DETAILS :-

To find out the Pick release sales order lines or shipments , we need to use this table wsh_delivery_details using column Released_status=’S’. So the Delivery lines having Released_status=’S’ will be the pick released sales order lines in oracle apps. Here below is the Released_status short code values which do store in the wsh_delivery_details table. Using Released_status, we can identify the status of the current shipment in oracle apps.

B: Back ordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory

SQL Query using pick release tables in oracle apps

select A3.* from apps.WSH_DELIVERY_DETAILS A1,

APPS.MTL_TXN_REQUEST_LINES A2,

MTL_TXN_REQUEST_HEADERS A3

WHERE A1.MOVE_ORDER_LINE_ID=A2.LINE_ID

AND A2.HEADER_ID=A3.HEADER_ID

Query to find responsibility assigned to user

Query to find responsibility assigned to user

In this post , We will be discussing about query to find responsibility assigned to user. This Query will help to extract the list of User account having detail responsibility information’s. We will get the complete user responsibility data using this sql query in oracle apps r12.  This query will only extract the active users account having responsibility in r12. Please find below the Important tables for the sql query and the detail query to find responsibility assigned to user.

Most Important Tables used by query to find responsibility assigned to user

1.fnd_user_resp_groups_direct  

2.fnd_user

3.fnd_responsibility_tl

4.fnd_responsibility

5.fnd_application_tl

6.fnd_application            

Detail SQL query to find responsibility assigned to user

SELECT fu.user_name               ,

       frt.responsibility_name   ,

       furg.start_date           ,

       furg.end_date           ,      

       fr.responsibility_key      ,

       fa.application_short_name 

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

     AND FU.END_DATE is null

   and furg.END_DATE is null

Click To Know More

Legal entity and Ledger relationship table

Legal entity and Ledger relationship table

In this post , We will be discussing about legal entity and ledger relationship table. There is a direct link between the legal entity and ledger in the organization structure of oracle apps. Legal Entity comes second from top and ledger comes third from top. We do create the Ledgers under legal entity in oracle apps. Here below I will share the legal entity and ledger relationship table sql join in oracle apps. Please find below complete information about legal entity and ledger relationship table.

Important legal entity and ledger relationship table

1.XLE_ENTITY_PROFILES

2.XLE_REGISTRATIONS

3.HR_LOCATIONS_ALL

4.GL_LEDGERS

5.HR_OPERATING_UNITS

SQL Query using the legal entity and ledger relationship table

select

  LEP.LEGAL_ENTITY_ID,

  LEP.name LEGAL_ENTITY,

  gl.name ledger_name,

  HRO.name OU_NAME

from apps.XLE_ENTITY_PROFILES LEP,

  apps.XLE_REGISTRATIONS REG,

  apps.HR_LOCATIONS_ALL HRL,

  apps.gl_ledgers gl,

  apps.HR_OPERATING_UNITS HRO

where LEP.TRANSACTING_ENTITY_FLAG      = 'Y'

and LEP.LEGAL_ENTITY_ID                = REG.SOURCE_ID

and REG.SOURCE_TABLE                   = 'XLE_ENTITY_PROFILES'

and HRL.LOCATION_ID                    = REG.LOCATION_ID

and REG.IDENTIFYING_FLAG               = 'Y'

and HRO.SET_OF_BOOKS_ID=GL.LEDGER_ID

and LEP.LEGAL_ENTITY_ID                = HRO.DEFAULT_LEGAL_CONTEXT_ID

link between legal entity and operating unit in oracle apps

Link between legal entity and operating unit in oracle apps

In this post , We will be discussing about the link between legal entity and operating unit in oracle apps. As per the Organization structure in Oracle apps, Legal entity comes second from top and operating unit comes fourth from top. Both Legal entity and operating unit links with each other using GL ledger. After creating legal entity, we do create the GL Ledger under legal entity. Operating Unit, we do create Under the Ledgers in oracle apps. In Oracle apps, there is no direct link between the legal entity and operating unit. In this post, I will share the SQL table link between legal entity and operating unit. Please find below the detail information about link between legal entity and operating unit in oracle apps.

Organization Structural link between legal entity and operating unit in oracle apps

Important tables to link between legal entity and operating unit in oracle apps

1.XLE_ENTITY_PROFILES

2.XLE_REGISTRATIONS

3.HR_LOCATIONS_ALL

4.GL_LEDGERS

5.HR_OPERATING_UNITS

Detail SQL query to Extract legal entity and operating unit information using link between legal entity and operating unit in oracle apps

select

  LEP.LEGAL_ENTITY_ID,

  LEP.name LEGAL_ENTITY,

  gl.name ledger_name,

  HRO.name OU_NAME

from apps.XLE_ENTITY_PROFILES LEP,

  apps.XLE_REGISTRATIONS REG,

  apps.HR_LOCATIONS_ALL HRL,

  apps.gl_ledgers gl,

  apps.HR_OPERATING_UNITS HRO

where LEP.TRANSACTING_ENTITY_FLAG      = 'Y'

and LEP.LEGAL_ENTITY_ID                = REG.SOURCE_ID

and REG.SOURCE_TABLE                   = 'XLE_ENTITY_PROFILES'

and HRL.LOCATION_ID                    = REG.LOCATION_ID

and REG.IDENTIFYING_FLAG               = 'Y'

and HRO.SET_OF_BOOKS_ID=GL.LEDGER_ID

and LEP.LEGAL_ENTITY_ID                = HRO.DEFAULT_LEGAL_CONTEXT_ID

Legal entity table in Oracle Apps r12

Legal entity table in r12

In this post , We will be discussing about legal entity table in r12. Legal entity we do create in oracle apps while defining the organization structure. Legal entity represents the government registered entity of the organization having registration details. Legal entity tables help to store the registration details for the legal entity in oracle r12. We do get the information about registrations with complete registered address to government in r12. Here below I will try to share the legal entity table and complete sql query using legal entity table to extract the legal entity information’s.

Legal entity table in Oracle Apps r12

Important legal entity table in r12

1. xle_firstparty_information_v

Click To Know More

SQL query using legal entity table in r12 to extract the legal entity information’s in r12

Here below is the sql query to extract the legal entity information in r12

SELECT xle_firstpty.NAME,

       xle_firstpty.registration_number,

       xle_firstpty.effective_from,

       xle_firstpty.location_id,

       xle_firstpty.address_line_1,

       xle_firstpty.address_line_2,

       xle_firstpty.address_line_3,

       xle_firstpty.town_or_city,

       xle_firstpty.region_1,

       xle_firstpty.region_2,

       xle_firstpty.region_3,

       xle_firstpty.postal_code,

       xle_firstpty.country,

       xle_firstpty.address_style

  FROM apps.xle_firstparty_information_v xle_firstpty

 WHERE 1 = 1

Relation between hz tables

Relation between hz tables

In this post , We will be discussing about relation between hz tables. As we know, HZ tables related to tca in oracle apps. We do most commonly uses the hz tables for extracting the customer information’s in oracle apps because in TCA, Oracle treats everything in Oracle application as party, which could be become its potential customer in future, so it stores the information’s about Suppliers, customers in HZ tables as per the TCA architecture. TCA hz tables help to extract the customer sites, addresses and locations details from oracle apps. Every hz tables do relate with other logically in terms of data. Some hz tables do store information about customer header, others do store information about customer profiles, others do stores customer sites and others do stores site addresses information’s. Here below is the complete detail explanation about relation between hz tables.

HZ Tables having data relation

1.HZ_PARTIES

2.HZ_CUST_ACCOUNTS_ALL

3.HZ_CUST_SITE_ACCTS_ALL

4.HZ_CUST_SITE_USES_ALL

5.HZ_LOCATIONS_ALL

6.HZ_PARTY_USG_ASSIGNMENTS

7.HZ_PARTY_SITES_ALL

SQL and Data relation between hz tables

1.      HZ_PARTIES:- This tables stores all the customer and Suppliers information’s. In this table we do get the information as party for supplier and customer. HZ_PARTIES relate with HZ_CUST_ACCOUNTS_ALL table with PARTY_ID column.

2.      HZ_CUST_ACCOUNTS_ALL:- This table store the information specific to customer headers. We do get the customer number, Customer start date. HZ_CUST_ACCOUNTS_ALL relate with PARTY_ID to HZ_PARTIES

HZ_CUST_ACCOUNTS_ALL relate with CUST_ACCOUNT_ID with HZ_CUST_SITE_ACCTS_ALL Relation between hz tables

3.      HZ_CUST_SITE_ACCTS_ALL:- 

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

It relate with CUST_ACCOUNT_ID to HZ_CUST_ACCOUNTS_ALL table.

HZ_CUST_SITE_USES_ALL: - 

the HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table.

It related with CUST_ACCT_SITE_ID to HZ_CUST_ACCT_SITES_ALL table.

HZ_PARTY_SITES_ALL: - 

This table help to store the Customer Party sites information’s. We do get the party site number for the customer in this table. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID

It Join with PARTY_SITE_ID with HZ_CUST_ACCT_SITES_ALL table.

HZ_LOCATIONS_ALL: - 

This table stores the actual customer addresses information’s in oracle r12. We do get the complete address information in this table.

It Join with LOCATION_ID with HZ_PARTY_SITES_ALL table.

HZ_CUSTOMER_PROFILES: - 

HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

SQL Query using Relation between hz tables

SELECT  

 PARTY_NAME,

    ,hcsua.SITE_USE_CODE PURPOSE        -- *

    ,hcsua.location SITE

    ,hl.COUNTRY Country

    ,hl.ADDRESS1 Address_Line_1

      ,hl.ADDRESS2 Address_Line_2

      ,hl.ADDRESS3 Address_Line_3

      ,hl.ADDRESS4 Address_Line_4

      ,hl.CITY City

      ,hl.STATE STATE

      ,hl.PROVINCE Province

      ,hl.COUNTY County

      ,hl.POSTAL_CODE Postal_Code

      ,hl.LANGUAGE Location_Language

      ,hl.DESCRIPTION Description

FROM hz_parties hp

    ,hz_party_sites hps

    ,hz_cust_accounts hca

    ,hz_cust_acct_sites_all hcas

    ,hr_operating_units hou

    ,hz_party_usg_assignments hpua

    ,hz_cust_site_uses_all hcsua,

    HZ_LOCATIONS HL

WHERE hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hp.STATUS = 'A'

   

    AND hcsua.STATUS = 'A'

    AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id

    AND hca.STATUS = 'A'

    AND hcas.STATUS = 'A'

   

    AND hcas.org_id = hou.organization_id

     AND hcas.org_id=:P_ORG_ID

    AND hp.party_id = hpua.party_id

    AND hpua.PARTY_USAGE_CODE = 'CUSTOMER'

    AND hca.cust_account_id = hcas.cust_account_id

Click To Know More

HZ tables in oracle r12

HZ tables in oracle r12

In this post , We will be discuss about the HZ tables in oracle r12. HZ Tables we do calls the TCA tables in oracle r12. HZ tables do store the TCA informations in r12. In TCA, Oracle treats everything in Oracle application as party, which could be become its potential customer in future, So it stores the In formations about Suppliers , customers in HZ tables as per the TCA architecture.HZ tables most commonly used for Customers. If we want to extract the Customer In formations , we do use the HZ tables in oracle r12. Here below .  I will share some of the important hz tables in oracle r12 and details explanation about these tables in r12.

Most Commonly Used HZ tables in oracle r12

Here below is the list of HZ tables , we do commonly used for customers and suppliers.

1.HZ_PARTIES

2.HZ_CUST_ACCOUNTS_ALL

3.HZ_CUST_SITE_ACCTS_ALL

4.HZ_CUST_SITE_USES_ALL

5.HZ_PARTY_SITES_ALL

5.HZ_LOCATIONS_ALL

6.HZ_PARTY_USG_ASSIGNMENTS

Detail Explanation of HZ tables in oracle r12

HZ_PARTIES :- 

This table store the party registration for the supplier and customers. PARTY_ID is the key column in this tabe to link with AP_SUPPLIERS_ALL and HZ_CUST_ACCOUNTS_ALL

HZ_CUST_ACCOUNTS_ALL :- 

This table specifc to Customer only. It stores the Customer Registration informations.It stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person.

It join with PARTY_ID with HZ_PARTIES table in oracle r12.

HZ_CUST_SITE_ACCTS_ALL :- 

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

It Join with CUST_ACCOUNT_ID with HZ_CUST_ACCOUNTS_ALL table.

HZ_CUST_SITE_USES_ALL:- 

the HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table.

It Join with CUST_ACCT_SITE_ID with HZ_CUST_ACCT_SITES_ALL table.

HZ_PARTY_SITES_ALL :- 

This table help to store the Customer Party sites informations. We do get the party site number for the customer in this table.One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID

It Join with PARTY_SITE_ID with HZ_CUST_ACCT_SITES_ALL table.

HZ_LOCATIONS_ALL :- 

This table stores the actual customer addresses informations in oracle r12. We do get the complete address information in this table.

It Join with LOCATION_ID with HZ_PARTY_SITES_ALL table.

HZ_CUSTOMER_PROFILES :- 

he HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

Detail SQL Using the HZ tables in oracle r12

SELECT   

 PARTY_NAME,

    'DNB' ACCT_SITE_SRC_SYS

    ,hcas.ORIG_SYSTEM_REFERENCE ACCT_SITE_SRC_SYS_REF

    ,'DNB' ACCT_SITE_PUR_SRC_SYS

    ,hcsua.ORIG_SYSTEM_REFERENCE ACCT_SITE_PUR_SRC_SYS_REF

    ,hcsua.SITE_USE_CODE PURPOSE        -- *

    ,hcsua.primary_flag PRIMARY_INDICATOR

    ,'I' INSERT_UPDATE_INDICATOR

    ,hcsua.location SITE

    ,NULL ACCT_ADDR_PURPOSE_SET        -- *

    ,NULL PURPOSE_FROM_DATE

    ,NULL PURPOSE_TO_DATE

    ,hl.COUNTRY Country

    ,hl.ADDRESS1 Address_Line_1

      ,hl.ADDRESS2 Address_Line_2

      ,hl.ADDRESS3 Address_Line_3

      ,hl.ADDRESS4 Address_Line_4

      ,hl.CITY City

      ,hl.STATE STATE

      ,hl.PROVINCE Province

      ,hl.COUNTY County

      ,hl.POSTAL_CODE Postal_Code

      ,hl.POSTAL_PLUS4_CODE Postal_Code_Extension

      ,hl.LANGUAGE Location_Language

      ,hl.DESCRIPTION Description

      ,hl.SHORT_DESCRIPTION Short_Description

      ,hl.SALES_TAX_GEOCODE Sales_Tax_Geocode

      ,hl.SALES_TAX_INSIDE_CITY_LIMITS Sales_Tax_Inside_City_Limits

FROM hz_parties hp

    ,hz_party_sites hps

    ,hz_cust_accounts hca

    ,hz_cust_acct_sites_all hcas

    ,hr_operating_units hou

    ,hz_party_usg_assignments hpua

    ,hz_cust_site_uses_all hcsua,

    HZ_LOCATIONS HL

WHERE hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hp.STATUS = 'A'

    AND hca.STATUS = 'A'

    AND hcas.STATUS = 'A'

    AND hcsua.STATUS = 'A'

    AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id

    AND hp.party_id = hpua.party_id

    AND hpua.PARTY_USAGE_CODE = 'CUSTOMER'

    AND hcas.org_id = hou.organization_id

     AND hcas.org_id=:P_ORG_ID

    AND hca.cust_account_id = hcas.cust_account_id

ap_payment_schedules_all join ap_terms

ap_payment_schedules_all join ap_terms

In this post , we will be discuss about ap_payment_schedules_all join ap_terms. I will try to share the sql join between the  ap_payment_schedules_all and ap_terms. ap_payment_schedules_all stores the Information about due amount and due date. It help to store the AP Invoice information about when the payment will be due and due amount for the invoice. ap_terms is the master tables of Payment terms. We know that , Payment due date calculate based on the payment term. If the payment term is 30 days , then the due date will be invoice_date+30 days which will be store as a due date in  ap_payment_schedules_all . Here below is the detail explanation about ap_payment_schedules_all join ap_terms.

SQL Query using SQL Join Between ap_payment_schedules_all and ap_terms

select a2.invoice_num,a1.due_date,a3.name
from apps.ap_payment_schedules_all a1,
apps.ap_invoices_all a2,apps.ap_terms a3
where a1.invoice_id=a2.invoice_id
and a2.terms_id=a3.term_id

Released_status In wsh_delivery_details

Released_status In wsh_delivery_details

In this post , We will be discuss about the Released_status In wsh_delivery_details. When we do release the shipment or delivery in order management , then that shipment got pick released but if the stock is not available for that item in Oracle Inventory then that shipment got back ordered.If that shipment got confirm then shipment got ship confirmed. These all shipment status got stored in the released_status column in wsh_delivery_details table. System stored the short form of the shipment status in Released_status coloumn of the wsh_delivery_details. Here below , i will  try to explain more about Released_status In wsh_delivery_details.

Released_status Value meaning in wsh_delivery_details table.

Here below is the Released_status short code values which do store in the wsh_delivery_details table.Using Released_status , we can identify the status of the current shipment in oracle apps.

B: Back ordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory

Which utility is called in get_approval_status

Which utility is called in get_approval_status

In this post , we will be discuss about the utility which is called for get_approval_status. One AP Invoice have multiple Status but that doesn't stores in the Database tables. We cannot get the invoice approval status direct from the AP Invoices table. To get the Invoice status , Oracle has given the Standard utility called AP_INVOICES_PKG.GET_APPROVAL_STATUS to get the status for the AP invoices. This utility , drive the invoice status from multiple parameters in oracle apps. Here below i will describe about this utility is called in get_approval_status.

 Which utility is called in get_approval_status

How to get the Payables Invoice status from utility is called in get_approval_status

AP_INVOICES_PKG.GET_APPROVAL_STATUS :- This is the standard utility which helps to get the invoice status for AP Invoices. This is the standard API to get the Invoice approval status in oracle apps. 

These are the 4 AP Invoice Status.

1. Never Validated

2.Validated

3.Cancelled

4.Needs Re-validation

Working of utility is called in get_approval_status

Here below is the Sample of sql using utility is called in get_approval_status

SELECT invoice_num,invoice_date,APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS

            (

             AIA.INVOICE_ID

            ,AIA.INVOICE_AMOUNT

            ,AIA.PAYMENT_STATUS_FLAG

            ,AIA.INVOICE_TYPE_LOOKUP_CODE

            ) INVOICE_Approval_Status

    

FROM   APPS.AP_INVOICES_ALL AIA

AP invoice payment api in r12

AP invoice payment api in r12

In this post , We will be discuss about the AP invoice payment api in r12. This API will be create the AP Payments in oracle apps r12. One thing i will clear from the beginning , this is not an Standard Oracle provided AP Invoice payment api but this api is created by our side. This is an custom payment api in r12 m which helps to make the payment in oracle r12. To create the AP invoice payment api , we have took an example of Standard Invoice creation api. We have used the complete Standard Invoice creation api to create this custom Payment api but we did some changes in this api for ap invoice payments creation. Here below , i will try to share the complete working of AP invoice payment api in r12.

How to Developed the  AP invoice payment api in r12

Step 1 :- You have to open the Standard API ap_pay_single_invoice_pkg.ap_pay_invoice_in_full .

Step 2:- Copy the code from this standard Package ap_pay_single_invoice_pkg.ap_pay_invoice_in_full .

Step3:- After Copying , we need to create new custom Package with our custom name.

xx_Payment_creation_api.ap_pay_invoice_in_full

We need to do one small change in this custom api as below.

if l_next_check_number is null and l_seq_num_profile in ('P') and p_doc_category_code is null then

      l_next_check_number := -1;

end if;

Actual working of AP invoice payment api in r12

xx_Payment_creation_api.ap_pay_invoice_in_full (

            p_api_version           => 1.0,

            p_init_msg_list         => fnd_api.g_true,

            p_invoice_id            => l_invoice_id,

            p_payment_type_flag     => 'A',

            p_internal_bank_acct_id => l_bank_account_id,

            p_payment_method_code   => l_payment_method_code,

            p_payment_profile_id    => l_payment_profile_id,

            p_payment_document_id   => null,

            p_take_discount         => 'N',

            p_check_date            => trunc(sysdate),

            p_doc_category_code     => null,

            p_exchange_rate_type    => null,

            p_exchange_rate         => null,

            p_exchange_date         => null,

            x_return_status         => l_return_status,

            x_msg_count             => l_msg_count,

            x_msg_data              => l_msg_data,

            x_errorids              => l_error_ids

        );