Sunday, 11 October 2015

Find Ebs Technology Stack Component Versions


1A very good report to collect versions of technology stack components is described in MOS article 601736.1. Below an example of how to run this report on unix/linux
 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next perl command:
perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass=<apps password> -outfile=$APPLTMP/Report_App_Inventory.html
2How to find E-Business Suite Version: 
Connect to the database with sqlplus as user apps and run next query:
SQL> select release_name from apps.fnd_product_groups;
3How to find the Workflow Version: 
Connect to the database with sqlplus as user apps and run next query:
SQL> select distinct TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION';
4How to find the Web Server or Application Server version: 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
5How to find the Forms & Reports version in EBS 11i 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
6How to find the Forms & Reports version in EBS R12 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
$ORACLE_HOME/bin/rwrun | grep Release
7How to find the Jinitiator version: 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
grep jinit_ver_comma $CONTEXT_FILE
8How to find the Oracle Java Plug-in version: 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
grep plugin $CONTEXT_FILE
9How to find the OA Framework Version (11i only): 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
10How to find the Database version: 
Connect to the database with sqlplus as sysdba and run next query:
SQL> select * from v$version;
11How to find the Weblogic server version in EBS R12.2: 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables and run next command:
cat $FMW_HOME/wlserver_10.3/.product.properties | grep WLS_PRODUCT_VERSION
12How to find the Fusion Middle Ware (FMW) version in EBS R12.2 
Logon the Applications Middle-tier as the Application owner, using a terminal emulator (E.g. Putty).
Set the correct environment variables.
Set the correct ORACLE_HOME variable:
E.g. export ORACLE_HOME=/u001/oracle/R122/FMW_Home/Oracle_EBS-app1
Run next command:
$ORACLE_HOME/OPatch/opatch lsinventory


Ref: http://www.bluegecko.dk/oracle-applications/how-to-find-ebs-technology-stack-component-versions/
Posted by at No comments:

Sunday, 13 September 2015

Submitting and Monitoring Concurrent Programs from Oracle Apps Self-Service Pages

We normally submit and monitor Concurrent Programs from standard request submission form (SRSFrom). There is another way to submit concurrent programs i.e. Submitting and Monitoring through Self-Service Pages. which is very useful in many scenarios. 

Method:
Define Function:
Navigation: System Administrator -> Application -> Function
Properties: SSWA jsp function

Web HTML Call:

To submit any concurrent program:

OA.jsp?akRegionApplicationId=0&akRegionCode=FNDCPPROGRAMPAGE&scheduleRegion=Hide&notifyRegion=Hide&printRegion=Hide

To Submit Particular Concurrent Program

OA.jsp?akRegionApplicationId=0&akRegionCode=FNDCPPROGRAMPAGE&programApplName=XXCUS&programName=XXSCM_LMS_RPT&programRegion=Hide&scheduleRegion=Hide&notifyRegion=Hide&printRegion=Hide

Where XXSCM_LMS_RPT is Concurrent Program Short Name

To View requests related to specific concurrent program

OA.jsp?akRegionCode=FNDCPREQUESTVIEWPAGE&akRegionApplicationId=0&progApplShortName=XXCUS&progShortName=XXSCM_LMS_RPT

Where XXSCM_LMS_RPT is Concurrent program Name


REF: http://shilpaviswanath.blogspot.ae/2012/03/submitting-and-monitoring-concurrent.html
Posted by at No comments:

Wednesday, 9 September 2015

Deploy Custom Jar library and OAF pages in EBS R 12.2.3

    1)Move the class files to $JAVA_TOP

    2) Run XML Import script   follow below steps to  Generate Jar file.



    1. Create a temporary custom.zip file which contains all the custom application's directories/files at the non-standard location. The commands are:
      1. cd $JAVA_TOP
      2. zip -r customprod.zip <directory list> where the <directory list> is the list of all the directory paths, relative to $JAVA_TOP, for custom application's java files at the non-standard location.
    2. Generate and sign the customprod.jar file. Command: adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner
    3. Any update in the java class files   
                   Re-create the jar file:
    1.  Cd  $AD_TOP/bin
      1. Run:  adcgnjar
                                  Give apps username and password.
    1. Follow the steps below to make the custom jar file available for WebLogic Server:
      1. Back up the existing <FND_TOP>/admin/template/ebsProductManifest_xml.tmp
      2. Modify <FND_TOP>/admin/template/ebsProductManifest_xml.tmp to add the entry below for customprod.jar (after customall.jar):
        <library>customprod.jar</library>
      3. Run AutoConfig.
      4. Bounce the middle-tier services.
      5. NOTE: These changes will be lost if ebsProductManifest_xml.tmp is patched in future; changes will need to be done again.
    2. In order to to synchronize the changes (during the next prepare phase) between both the file systems fs1 and fs2, follow the steps below. Note that the custom synchronization driver file, located at <APPL_TOP_NE>/ad/custom/adop_sync.drv, should be used in these steps. This file has the required documentation on how to put an entry in for a file that needs to be synchronized between the two file systems.
      1. If there are custom java class files under <JAVA_TOP>/oracle/<cust_prod>/* directory and if the files under this directory needs to be synchronized between fs1 and fs2 , then put the following entry in the custom synchronization driver file as below:
        cp -r %s_current_base%/EBSapps/comn/java/classes/oracle/<cust_prod> %s_other_base%/EBSapps/comn/java/classes/oracle
      2. To copy the custom jar file, add the following entry:
        cp %s_current_base%/EBSapps/comn/java/classes/customprod.jar %s_other_base%/EBSapps/comn/java/classes
      3. To synchronize the custom changes done to the template, add the entry below:
        cp %s_current_base%/EBSapps/appl/fnd/12.0.0/admin/template/ebsProductManifest_xml.tmp %s_other_base%/EBSapps/appl/fnd/12.0.0/admin/template
      4. After changes are synchonized, ensure Autoconfig is run for the latest template changes to take effect.

    Note:Refer DOC ID:1577661.1 from meta link for more Info

Posted by at 3 comments:

Saturday, 5 September 2015

Delete Multiple rows from Table Region in OAF

1. Add one transient attribute "MultySelectFlag" of type string to your VO
2. create multiple selection based on this attribute
3. In table actions add one button called "Delete"

To handle delete functionality write following code:

    public void deleteLineRow() {
        XxscmPoLinesStgVOImpl vo =
            (XxscmPoLinesStgVOImpl)getXxscmPoLinesStgVO1();
        Row row[] = vo.getAllRowsInRange();
        for (int i = 0; i < row.length; i++) {
            XxscmPoLinesStgVORowImpl rowi = (XxscmPoLinesStgVORowImpl)row[i];
            if (rowi.getMultySelectFlag() != null &&
                rowi.getMultySelectFlag().equals("Y")) {
                rowi.remove();
            }
        }
    }


Posted by at No comments:

Tuesday, 16 June 2015

List of Credit Memos applied against AR Transaction and Vice-Versa

Background:
  • One or many Credit Memos can be applied against a single AR Transaction/Invoice
  • A single Credit Memo can be applied for One or many AR Transactions/Invoices

Scripts: 

1. Provide AR Transaction to the following script.
It lists all CM's applied against a single AR Transaction.

SELECT rcta2.trx_number CMs_Applied_to_AR_Trx_No
  FROM RA_CUSTOMER_TRX_ALL rcta1,
       RA_CUSTOMER_TRX_ALL rcta2,
       AR_RECEIVABLE_APPLICATIONS_ALL araa
 WHERE     rcta1.trx_number = '&Enter_AR_Txn_No'
       AND rcta1.customer_trx_id = araa.APPLIED_CUSTOMER_TRX_ID
       AND rcta2.customer_trx_id = araa.customer_trx_id;

2. Provide Credit Memo number to the following query
It lists all AR Transactions/Invoices applied with this Credit Memo given

SELECT rcta2.trx_number AR_Trx_Nos
  FROM RA_CUSTOMER_TRX_ALL rcta1,
       RA_CUSTOMER_TRX_ALL rcta2,
       AR_RECEIVABLE_APPLICATIONS_ALL araa
 WHERE     rcta1.trx_number = '&Enter_Credit_Memo'
       AND rcta1.customer_trx_id = araa.customer_trx_id
       AND rcta2.customer_trx_id = araa.APPLIED_CUSTOMER_TRX_ID;
ref: http://orclapp.blogspot.ae/2013/05/list-of-credit-memos-applied-against-ar.html
Posted by at No comments:

Monday, 13 April 2015

ego_item_pub.process_item

Updating the Item using the API ego_item_pub.process_item

DECLARE

   l_inventory_item_id   NUMBER;

   l_organization_id      NUMBER;

   l_desc                      VARCHAR2(100);  

   l_item_num              VARCHAR2 (50);

   l_long_description    VARCHAR2(200);

   l_so_tran_flag          VARCHAR2 (1);

   l_attribute5              VARCHAR2 (20); 

   x_inventory_item_id   NUMBER;

   x_organization_id       NUMBER;

   x_return_status           VARCHAR2 (300);

   x_msg_count               NUMBER;

   x_msg_data                 VARCHAR2 (4000);

BEGIN


          l_inventory_item_id   := 614040;

          l_organization_id     := 103;

          l_desc                := 'Aigner Ladies'||''''||' Gunmetal Toned Sunglasses -1 ';  

          l_item_num            :=  'M000000000013';

         apps.ego_item_pub.process_item

                      (p_api_version                => 1.0,

                       p_init_msg_list               => 'T',

                       p_commit                      => 'T',

                       p_transaction_type        => 'UPDATE',

                       p_inventory_item_id      => l_inventory_item_id,

                       p_organization_id          => l_organization_id,

                       p_segment1                  =>   l_item_num,

                       p_description               =>    l_desc,

                       x_inventory_item_id         => x_inventory_item_id,

                       x_organization_id           => x_organization_id,

                       x_return_status             => x_return_status,

                       x_msg_count                 => x_msg_count,

                       x_msg_data                  => x_msg_data

                      );

        IF (x_return_status <> apps.fnd_api.g_ret_sts_success)

        THEN

               DBMS_OUTPUT.PUT_LINE( 'Item Attribute Update API Error : '|| x_return_status );

        ELSE

               DBMS_OUTPUT.PUT_LINE('Item Attribute Update API Success : ' || x_return_status);

        END IF;

END;

Posted by at No comments:

Monday, 29 December 2014

AR - Receipt Creation in Receivables R12

API to be used :

Application.

Account Receivables AR.
Validations.

. Receipt Number should not be null.

. Receipt amount should not be null.

. Customer should be available.

. Receipt Date should not be null.

. Invoice number should not be null.

. Receipt amount and total amount of all invoices related to a particular receipt should match.

. Invoice amount should not be null.

. Check if receipt is already applied to the invoice.

. Receipt Apply date should not less than the Transaction Date.

. Transaction date should exist for the invoice.
Interface Tables.

We do not have interface table for Receipts.
Base Tables.

AR_CASH_RECEIPTS_ALL
API i.e. Application Program Interface.

Create Receipts:
ar_receipt_api_pub.create_cash

Apply Amount:
ar_receipt_api_pub.apply

Apply on Account:
ar_receipt_api_pub.apply_on_account




1. AR_RECEIPT_API_PUB

Required Tables :

1.  ar_receipt_methods
2.  ar_cash_receipts_all
3.  ar_receivable_applications_all 
4.  ar_payment_schedules_all 
5.  ra_customer_trx_all
6.  hz_parties
7.  hz_cust_accounts

Validations Need to Perform  :

1. Validating Receipt  Method :
   
  The receipt method ID is validated per the following conditions:
It must be a valid receipt method ID in the AR_RECEIPT_METHOD table.
Receipt date must lie between the receipt method start date and end date (if not
null).
 The creation method code for the receipt class of this particular receipt method
ID should be ’AUTOMATIC,’ the remit flag =’Y,’ and the confirm flag = ’N’ or
’MANUAL.’
 At least one remittance bank account associated with this receipt method ID
must have either the multi-currency flag set to ’Y’ or the same currency as the
receipt currency. In addition, this should have a bank account type =
’INTERNAL’ and its inactive date (if specified) greater than the receipt_date.

2.  Validating Receipt Number  to Avoid Duplication .


Find the Below Sample Code Used to Create Oracle AR Receipt .
---------------------------------------------------------------------------------------------------------------------------------

 CREATE OR REPLACE PACKAGE BODY APPS.xxfin_ar_receipts_pkg
AS
-----------validate Receipt Method
   FUNCTION validate_receipt_method (p_receipt_method_name   IN     VARCHAR2,
                                     p_receipt_date          IN     DATE,
                                     p_receipt_method_id        OUT NUMBER,
                                     p_error_loc                OUT VARCHAR2,
                                     p_error_msg                OUT VARCHAR2)
      RETURN BOOLEAN
   IS
   --lc_receipt_method_var VARCHAR2(1);
   BEGIN
      p_error_loc := 'Function: VALIDATE_RECEIPT_METHOD';

      BEGIN
         SELECT receipt_method_id
           INTO p_receipt_method_id
           FROM ar_receipt_methods
          WHERE UPPER (TRIM (NAME)) = TRIM (p_receipt_method_name)
           AND NVL (p_receipt_date, SYSDATE) BETWEEN start_date
                                                     AND NVL (end_date,
                                                              '31-DEC-4712');
       RETURN TRUE;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            p_error_msg :=
                  'Error: Receipt Method '
               || p_receipt_method_name
               || ' is not defined in the System';
            fnd_file.put_line (fnd_file.LOG, p_error_msg);
            DBMS_OUTPUT.put_line (p_error_msg);
            RETURN FALSE;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg :=
               'Error while validating Receipt Method: '
            || p_receipt_method_name
            || ' '
            || SUBSTR (SQLERRM, 1, 255);
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
   END;
 -------------Validating for duplicate Receipt  ------------------------------
   FUNCTION validate_receipt_number (p_receipt_number   IN     VARCHAR2,
                                     p_org_id           IN     NUMBER,
                                     p_error_loc           OUT VARCHAR2,
                                     p_error_msg           OUT VARCHAR2)
      RETURN BOOLEAN
   IS
      lc_receipt_var   VARCHAR2 (1);
   BEGIN
      p_error_loc := 'Function: VALIDATE_RECEIPT_NUMBER';

      BEGIN
         SELECT 'X'
           INTO lc_receipt_var
           FROM ar_cash_receipts_all
          WHERE receipt_number = p_receipt_number AND org_id = p_org_id; -- added by ranjeet 15/05/2012

         p_error_msg :=
               'Error: Receipt Number '
            || p_receipt_number
            || ' already in the System';
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            --p_error_msg := 'Error: Customer '||p_customer_name||' is not defined in the System';
            --Fnd_File.PUT_LINE(Fnd_File.LOG,p_error_msg);
            --DBMS_OUTPUT.PUT_LINE(p_error_msg);
            RETURN TRUE;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg :=
               'Error while validating Receipt: '
            || p_receipt_number
            || ' '
            || SUBSTR (SQLERRM, 1, 255);
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
   END;
 ------------ Validating for Receipt Existence  for invoice Number---------------------
   FUNCTION validate_receipt_existence (p_chr_trx_number   IN     VARCHAR2,
                                        p_org_id           IN     NUMBER,
                                        p_error_loc           OUT VARCHAR2,
                                        p_error_msg           OUT VARCHAR2)
      RETURN BOOLEAN
   IS
      lc_receipt_var   VARCHAR2 (1);
   BEGIN
      p_error_loc := 'Function: VALIDATE_RECEIPT_EXISTENCE';

      BEGIN
    
         SELECT 'X'
           INTO lc_receipt_var
           FROM ar_payment_schedules_all apsa,
                ar_receivable_applications_all araa,
                ar_cash_receipts_all acra,
                ra_customer_trx_all racta
          WHERE     apsa.payment_schedule_id = araa.payment_schedule_id
                AND apsa.cash_receipt_id = araa.cash_receipt_id
                AND acra.cash_receipt_id = apsa.cash_receipt_id
                AND racta.customer_trx_id = araa.applied_customer_trx_id
                AND racta.org_id = apsa.org_id
                AND araa.APPLICATION_TYPE = 'CASH'
                AND racta.trx_number = p_chr_trx_number
                AND apsa.org_id = p_org_id;

         p_error_msg :=
               'Error: Receipt  for invoice number '
            || p_chr_trx_number
            || '  is already in the System';
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN TRUE;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            --p_error_msg := 'Error: Customer '||p_customer_name||' is not defined in the System';
            --Fnd_File.PUT_LINE(Fnd_File.LOG,p_error_msg);
            --DBMS_OUTPUT.PUT_LINE(p_error_msg);
            RETURN FALSE;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg :=
               'Error while validating Receipt Existence: '
            || p_chr_trx_number
            || ' '
            || SUBSTR (SQLERRM, 1, 255);
         fnd_file.put_line (fnd_file.LOG, p_error_msg);
         DBMS_OUTPUT.put_line (p_error_msg);
         RETURN FALSE;
   END;
   PROCEDURE imp_values (p_trx_in_number   IN     VARCHAR2,
                         errbuf               OUT VARCHAR2,
                         retcode              OUT NUMBER)
   AS
      --              Variable Declaration
      lc_program_name        VARCHAR2 (150);
      ln_user_id             NUMBER;
      ln_login_id            NUMBER;
      ln_org_id              NUMBER;
      lc_error_flag          VARCHAR2 (1);
      ln_total_rec_cnt       NUMBER;                -- count for total records
      ln_success_rec_cnt     NUMBER;
      -- count for successfully processed records
      ln_error_rec_cnt       NUMBER;              -- count fro errored records
      lc_error_loc           VARCHAR2 (50);
      lc_error_msg           VARCHAR2 (500);
      ln_request_id          NUMBER;
      ld_request_date        DATE;
      ln_record_insert       NUMBER;
      lc_errbuf              VARCHAR2 (24000);
      v_n_msg_index_out      VARCHAR2 (32000);
      lc_retcode             VARCHAR2 (10);
      lc_return_status       VARCHAR2 (1);
      lc_msg_data            VARCHAR2 (255);
      ln_err_count           NUMBER := 0;
      ln_count               NUMBER;
      ln_receipt_method_id   NUMBER;
      ln_customer_number     NUMBER;
      ln_cr_id               NUMBER;
      ln_msg_count           NUMBER;
      lb_receipt_method      BOOLEAN;
      lb_receipt_exists      BOOLEAN;
      lb_invoice_number      BOOLEAN;
      lb_customer            BOOLEAN;
      ln_cust_site_use_id    NUMBER;
      ld_gl_date             DATE;
      lc_account_name        VARCHAR2 (240);
      ln_amt                 NUMBER (14, 3);
      l_receipt_number       VARCHAR2 (250);
      l_recpt_methd_name     VARCHAR2 (300);


      CURSOR lci_rec                        --(receipt_num varchar2,ou NUMBER)
      IS
           SELECT apsa.TRX_DATE AS "TRX_DATE",
                  apsa.GL_DATE AS "GL_DATE",
                  apsa.trx_number AS "INVOICE_NUMBER",
                  apsa.INVOICE_CURRENCY_CODE AS "INVOICE_CURRENCY_CODE",
                  apsa.AMOUNT_DUE_REMAINING AS "AMOUNT_DUE_REMAINING",
                  rcta.BILL_TO_CUSTOMER_ID AS "BILL_TO_CUSTOMER_ID",
                  hz.party_name AS "PARTY_NAME",
                  hz.PARTY_NUMBER "PARTY_NUMBER",
                  apsa.org_id AS "ORG_ID",
                  rcta.EXCHANGE_RATE AS "EXCHANGE_RATE",
                  rcta.EXCHANGE_RATE_TYPE AS "EXCHANGE_RATE_TYPE",
                  rcta.EXCHANGE_DATE AS "EXCHANGE_DATE"
             FROM ar_payment_schedules_all apsa,
                  ra_customer_trx_all rcta,
                  abpom.sd64_invoiceheader lsd,
                  hz_parties HZ,
                  hz_cust_accounts hca
            WHERE     apsa.customer_trx_id = rcta.customer_trx_id
                  AND apsa.org_id = rcta.org_id
                  AND TRIM (LSD.INVOICENUMBER) = TRIM (rcta.trx_number)
                  AND TRIM (lsd.INVOICENUMBER) =
                         NVL (p_trx_in_number, TRIM (rcta.trx_number))
                  AND HZ.PARTY_ID = HCA.PARTY_ID
                  AND HCA.CUST_ACCOUNT_ID = rcta.BILL_TO_CUSTOMER_ID
                  --AND TRUNC(apsa.TRX_DATE)       =TRUNC(SYSDATE)
                  AND lsd.CASH_INVOICE = 'Y'
         GROUP BY apsa.TRX_DATE,
                  apsa.GL_DATE,
                  apsa.trx_number,
                  apsa.INVOICE_CURRENCY_CODE,
                  apsa.AMOUNT_DUE_REMAINING,
                  rcta.BILL_TO_CUSTOMER_ID,
                  hz.party_name,
                  hz.PARTY_NUMBER,
                  apsa.org_id,
                   rcta.EXCHANGE_RATE,
                  rcta.EXCHANGE_RATE_TYPE,
                  rcta.EXCHANGE_DATE ;
   BEGIN
      -- Apps Initialize
      mo_global.init ('AR');
      mo_global.set_policy_context ('S',
                                    TO_NUMBER (fnd_profile.VALUE ('ORG_ID')));
      lc_program_name := 'Conversion Program Name: ABP Auto  Receipt Creation';
      ln_user_id := fnd_profile.VALUE ('USER_ID');
      ln_login_id := fnd_profile.VALUE ('LOGIN_ID');
      ln_org_id := fnd_profile.VALUE ('ORG_ID');
      --Hardcoding the Receipt mathod Name
  
      fnd_file.put_line (
         fnd_file.LOG,
         '---------------------------------------------------------------------- ');
      DBMS_OUTPUT.put_line (
         '---------------------------------------------------------------------- ');
      fnd_file.put_line (fnd_file.LOG, lc_program_name);
      DBMS_OUTPUT.put_line (lc_program_name);
      fnd_file.put_line (fnd_file.LOG, 'Start of Log Messages: ');
      DBMS_OUTPUT.put_line ('Start of Log Messages: ');
      fnd_file.put_line (
         fnd_file.LOG,
         '---------------------------------------------------------------------- '
         || CHR (10));
      DBMS_OUTPUT.put_line (
         '---------------------------------------------------------------------- '
         || CHR (10));

      FOR cur IN lci_rec
      LOOP
         lc_error_loc := NULL;
         lc_error_msg := NULL;
         lc_errbuf := NULL;
         lc_retcode := NULL;
         ln_cust_site_use_id := NULL;
         ld_gl_date := NULL;
         lc_account_name := NULL;
         lc_error_flag := 'N';
         ln_amt := NULL;
         fnd_file.put_line (
            fnd_file.LOG,
            'Insertion Process Start for Receipt  number:'
            || cur.INVOICE_NUMBER);
  l_recpt_methd_name := TRIM ('AUTO_CASH_RECEIPT');
         --Receipt Number

         BEGIN
            SELECT 'REC0' || xxabp_receipt_seq.NEXTVAL
              INTO l_receipt_number
              FROM DUAL;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
         END;

         --Check for Dulicate Recept Existence
         IF cur.INVOICE_NUMBER IS NOT NULL
         THEN
            lb_receipt_exists :=
               validate_receipt_existence (cur.INVOICE_NUMBER,
                                           cur.ORG_ID,
                                           lc_error_loc,
                                           lc_error_msg);

            IF lb_receipt_exists
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Receipt  already Exists for this Transaction'||cur.INVOICE_NUMBER);
                    lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
                 DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            ELSE
                lc_error_flag := 'N';
           
            END IF;
         ELSE
            lc_error_flag := 'Y';
            fnd_file.put_line (fnd_file.LOG, 'Receipt Number Cant be null.');
            lc_error_msg := (lc_error_msg || 'Receipt Number Cant be null.');
         END IF;

         --check for duplicate Receipt
         IF l_receipt_number IS NOT NULL
         THEN
            lb_invoice_number :=
               validate_receipt_number (l_receipt_number,
                                        cur.ORG_ID,
                                        lc_error_loc,
                                        lc_error_msg);

            IF lb_invoice_number
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Receipt Number Does not exists in Base table i.e no duplicasy');
            ELSE
               lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            END IF;
         ELSE
            lc_error_flag := 'Y';
            fnd_file.put_line (fnd_file.LOG, 'Receipt Number Cant be null.');
            lc_error_msg := (lc_error_msg || 'Receipt Number Cant be null.');
         END IF;



         --validate for receipts method
         IF l_recpt_methd_name IS NOT NULL
         THEN
            lb_receipt_method :=
               validate_receipt_method (l_recpt_methd_name,
                                        cur.TRX_DATE,
                                        ln_receipt_method_id,
                                        lc_error_loc,
                                        lc_error_msg);

            IF lb_receipt_method
            THEN
               IF ln_receipt_method_id IS NOT NULL
               THEN
                  --                  UPDATE xxran_ar_receipts_interim
                  --                     SET li_receipt_method_id = ln_receipt_method_id
                  --                   WHERE li_receipt_num = cur.receipt_number;
                  fnd_file.put_line (
                     fnd_file.LOG,
                     'The Receipt Method ' || l_recpt_methd_name || 'exists.');
                     DBMS_OUTPUT.put_line ( 'The Receipt Method ' || l_recpt_methd_name || 'exists.');
               ELSE
                  fnd_file.put_line (
                     fnd_file.LOG,
                        'The Receipt Method ID  for '
                     || l_recpt_methd_name
                     || ' does not exist.');
                  DBMS_OUTPUT.put_line (
                        'The Receipt Method ID  for '
                     || l_recpt_methd_name
                     || ' does not exist.');
                      lc_error_flag := 'Y';
                    lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               END IF;
            ELSE
               lc_error_flag := 'Y';
               lc_error_msg := lc_error_msg || SUBSTR (lc_error_msg, 1, 255);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            END IF;
         ELSE
            fnd_file.put_line (
               fnd_file.LOG,
               'Receipt Method does Not exists for Receipt Number: '
               || l_receipt_number);
         END IF;


         DBMS_OUTPUT.put_line (
            'ln_amt  ' || ln_amt || '  lc_error_flag: ' || lc_error_flag);

         -- calling create cash api
         IF lc_error_flag = 'N'
         THEN
            ln_cr_id := NULL;
            lc_return_status := NULL;
            ln_msg_count := NULL;
            lc_msg_data := NULL;
            DBMS_OUTPUT.put_line ('ln_amt  ' || ln_amt);
            ar_receipt_api_pub.create_cash (
               p_api_version          => 1.0,
               p_init_msg_list        => fnd_api.g_true,
               p_amount               => cur.AMOUNT_DUE_REMAINING, --cur.amount,
               p_receipt_number       => l_receipt_number,
               p_receipt_date         => cur.TRX_DATE,
               p_currency_code        => CUR.INVOICE_CURRENCY_CODE,
               --lc_currency_code,
               p_gl_date              => cur.GL_DATE,           --cur.gl_date,
               p_customer_number      => cur.PARTY_NAME,
               --ln_customer_number,
               --p_customer_site_use_id      => ln_cust_site_use_id,
               --   p_comments                  => lc_account_name,
               p_receipt_method_id    => ln_receipt_method_id,
               p_exchange_rate_type   => cur.exchange_rate_type,
               p_exchange_rate        => cur.exchange_rate,
               p_exchange_rate_date   => cur.exchange_date,
               p_org_id               => cur.ORG_ID,              --lc_org_id,
               p_cr_id                => ln_cr_id,
               x_return_status        => lc_return_status,
               x_msg_count            => ln_msg_count,
               x_msg_data             => lc_msg_data);

            IF ln_cr_id IS NOT NULL AND lc_msg_data IS NULL
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Successful Ceation Of AR Receipts:' || ln_cr_id);
               DBMS_OUTPUT.put_line (
                  'Successful Ceation Of AR Receipts:' || ln_cr_id);
               COMMIT;
            ELSE
               IF ln_msg_count >= 1
               THEN
                  FOR v_n_i IN 1 .. ln_msg_count
                  LOOP
                     pa_interface_utils_pub.get_messages (
                        p_msg_data        => lc_msg_data,
                        p_encoded         => 'F',
                        p_msg_index       => ln_msg_count,
                        p_data            => lc_msg_data,
                        p_msg_count       => ln_msg_count,
                        p_msg_index_out   => v_n_msg_index_out);
                  END LOOP;
               END IF;

               fnd_file.put_line (fnd_file.LOG,
                                  'Receipt Creation Error:' || lc_msg_data);
               DBMS_OUTPUT.put_line (
                  'Receipt Creation Error:' || lc_msg_data);
               lc_error_flag := 'Y';
               lc_error_msg :=
                  (lc_error_msg || 'Receipt Creation Error:' || lc_msg_data);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            --p_out_chr_retcode := 'E';
            --p_out_chr_errbuf :=p_out_chr_errbuf||'Party Creation Error: '||l_msg_data;
            END IF;
         ELSE
            fnd_file.put_line (
               fnd_file.LOG,
               'Before Receipt Creation Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (
               'Before Receipt Creation Error :' || lc_msg_data);
            lc_error_flag := 'Y';
            lc_error_msg :=
               (lc_error_msg || 'Receipt Creation Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
            fnd_file.put_line (fnd_file.LOG, lc_error_msg);
         END IF;

         ----  CALLING APPLY API
         IF lc_error_flag = 'N'
         THEN
            --ln_cr_id := NULL;
            lc_return_status := NULL;
            ln_msg_count := NULL;
            lc_msg_data := NULL;
            ar_receipt_api_pub.APPLY (
               p_api_version       => 1.0,
               p_init_msg_list     => fnd_api.g_true --,p_amount            => cur.amount
                                                    ,
               p_cash_receipt_id   => ln_cr_id,
               p_trx_number        => cur.INVOICE_NUMBER,
               p_apply_date        => cur.TRX_DATE,             --INvoice Date
               p_apply_gl_date     => cur.GL_DATE,                   --GL Date
               p_amount_applied    => cur.AMOUNT_DUE_REMAINING, --Amount Applied
               p_receipt_number    => l_receipt_number,
               p_org_id            => cur.org_id --,p_cr_id             => ln_cr_id
                                                ,
               x_return_status     => lc_return_status,
               x_msg_count         => ln_msg_count,
               x_msg_data          => lc_msg_data);

            IF ln_cr_id IS NOT NULL AND lc_msg_data IS NULL
            THEN
               fnd_file.put_line (
                  fnd_file.LOG,
                  'Successful Apply Of AR Receipts:' || l_receipt_number);
               DBMS_OUTPUT.put_line (
                  'Successful Apply Of AR Receipts:' || l_receipt_number);
               COMMIT;
            ELSE
               IF ln_msg_count >= 1
               THEN
                  FOR v_n_i IN 1 .. ln_msg_count
                  LOOP
                     pa_interface_utils_pub.get_messages (
                        p_msg_data        => lc_msg_data,
                        p_encoded         => 'F',
                        p_msg_index       => ln_msg_count,
                        p_data            => lc_msg_data,
                        p_msg_count       => ln_msg_count,
                        p_msg_index_out   => v_n_msg_index_out);
                  END LOOP;
               END IF;

               fnd_file.put_line (fnd_file.LOG,
                                  'Receipt Apply Error:' || lc_msg_data);
               DBMS_OUTPUT.put_line ('Receipt Apply Error:' || lc_msg_data);
               lc_error_flag := 'Y';
               lc_error_msg :=
                  (lc_error_msg || 'Receipt Apply Error:' || lc_msg_data||SQLERRM);
               DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
               fnd_file.put_line (fnd_file.LOG, lc_error_msg);
            --p_out_chr_retcode := 'E';
            --p_out_chr_errbuf :=p_out_chr_errbuf||'Party Creation Error: '||l_msg_data;
            END IF;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Before Receipt Apply Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (
               'Before Receipt Apply Error:' || lc_msg_data);
            lc_error_flag := 'Y';
            lc_error_msg :=
               (lc_error_msg || 'Receipt Apply Error:' || lc_msg_data);
            DBMS_OUTPUT.put_line (SUBSTR (lc_error_msg, 1, 255));
            fnd_file.put_line (fnd_file.LOG, lc_error_msg);
         END IF;

         --
         IF lc_error_flag = 'N'
         THEN
            INSERT INTO xxabp_ar_receipts_err (ERROR_MSG,
                                               STATUS,
                                               INVOICE_NUMBER,
                                               RECEIPT_NUMBER,
                                               ORG_ID,
                                                DATE_PROCESSED)
                 VALUES ('',
                         'S',
                         CUR.INVOICE_NUMBER,
                         l_receipt_number,
                         CUR.ORG_ID,
                         SYSDATE);

            COMMIT;
            ln_success_rec_cnt := ln_success_rec_cnt + 1;
         ELSE
            fnd_file.put_line (fnd_file.LOG,
                               'Validations failed: ' || lc_error_msg);
            DBMS_OUTPUT.put_line ('Validations failed');

            INSERT INTO xxabp_ar_receipts_err (ERROR_MSG,
                                               STATUS,
                                               INVOICE_NUMBER,
                                               RECEIPT_NUMBER,
                                               ORG_ID,
                                               DATE_PROCESSED)
                 VALUES (lc_error_msg,
                         'E',
                         CUR.INVOICE_NUMBER,
                         l_receipt_number,
                         CUR.ORG_ID,
                         SYSDATE);

            COMMIT;
            ln_error_rec_cnt := ln_error_rec_cnt + 1;
         END IF;

         fnd_file.put_line (
            fnd_file.LOG,
            '---------------------------------------------------------------------------------');
         COMMIT;
      END LOOP;

      DBMS_OUTPUT.put_line ('Total ' || TO_CHAR (ln_total_rec_cnt));
      DBMS_OUTPUT.put_line ('Success ' || TO_CHAR (ln_success_rec_cnt));
      DBMS_OUTPUT.put_line ('Error ' || TO_CHAR (ln_error_rec_cnt));
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (
            fnd_file.LOG,
            'Error executing the program : ' || SUBSTR (SQLERRM, 1, 255));
         DBMS_OUTPUT.put_line (
               'Error executing the program : '
            || lc_error_msg
            || SUBSTR (SQLERRM, 1, 255));
         errbuf := SUBSTR (SQLERRM, 1, 255);
         retcode := -1;
         ROLLBACK;
   END;
END xxfin_ar_receipts_pkg;
/


Ref : http://raghusarada.blogspot.in/2012/11/ar-receipt-creation-in-receivables-r12.html
Posted by at No comments:
Subscribe to: Posts (Atom)