Wednesday, 8 November 2017

Returning 'Warning' Status From Host Program

By default, 0 is for success, 1 for warning 2 for error. Where as in Unix both 1 and 2 are returns Error.

By adding below peace of code, we can return Warning status.


##---------------START---------------------------
     # set request status to warning:
           RESULT =`sqlplus -s ${FCP_LOGIN} <<!
      DECLARE
         b_success    BOOLEAN;
         n_session_id NUMBER;
      BEGIN
         fnd_global.initialize( session_id            => n_session_id
                               ,user_id               => NULL
                               ,resp_id               => NULL
                               ,resp_appl_id          => NULL
                               ,security_group_id     => NULL
                               ,site_id               => -1
                               ,login_id              => NULL
                               ,conc_login_id         => NULL
                               ,prog_appl_id          => NULL
                               ,conc_program_id       => NULL
                               ,conc_request_id       => ${4} – Request ID
                               ,conc_priority_request => NULL
                               ,form_id               => NULL
                               ,form_appl_id          => NULL
                               ,conc_process_id       => NULL
                               ,conc_queue_id         => NULL
                               ,queue_appl_id         => NULL
                               ,server_id             => -1
                              );
          b_success := fnd_concurrent.set_completion_status
                        (status  => 'WARNING'
                        ,message => 'See log file for warning details.'
                        );
          COMMIT;
          END;
          /
          exit;
          !`
                  ;;

     ##---------------END-------------------------

Monday, 4 September 2017

R12 Statement Generation Program (ARXSGP) Debugging

The Statements program use the following 2 temp tables to generate the statement:

The AR_STATEMENT_HEADERS and AR_STATEMENT_LINE_CLUSTERS are temp tables populated by the Statement Generation program. You can  check the data in tables only when debug_flag =Y


System Administrator
Concurrent > Program > Define
Query shortname = ARXSGP 

Click Parameters
Scroll to parameter debug_flag ,default  value set as 'N'

Change this to : 'Y'

Save
 

Set the profile Option AR: Enable Debug Message Output

System Administrator
Profile > System
Query for Application = Receivables
Responsibility = Receivables Manager (you need to put in the actual Responsibility name here)
user = <your user name>
Profile Option = AR: Enable Debug Message Output
Ensure it is set to Yes.


Enable debug_flag in Statements Form



Receivables Manager
Print Documents > Statements
Invoke the Menu: Help > Diagnostic > Examine 
Enter the following values:
Block = SUPST_BLK
Field = DEBUG_FLAG
Value = Y
Click OK


Now  from the two tables we can view the data .














Tuesday, 14 March 2017

Removing Table Lock in Oracle

run the following queries

SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;

Use this session id to find SERIAL# by using following SELECT statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)

Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

Wednesday, 25 January 2017

Namespace prefix 'ref' used but not declared-Issue



Generally when we create RTF using BI Publisher Desktop by default Fields will store in <xdo:xdo0002> format. But when we attach this template it will throw an error.So we need to replace those with actual Data fields.


Follow below steps to Resolve this issue..

The source of error is RTF Template.

1. Open RTF template. Double Click a field to display properties dialog.





















2. Click Add Help Text button
3. In Type your own section, write field name like this <?Brand?>, if  '<?ref:xdo0002?>' or similar found.
4. Click OK  Save template.



Friday, 6 January 2017

Payment Method Update at Supplier Site in R12



To update the supplier site payment method details we have to use the "iby_disbursement_setup_pub.update_external_payee" API instead of
"AP_VENDOR_PUB_PKG.Update_Vendor_Site".






DECLARE
   x_return_status             VARCHAR2 (200) := NULL;
   x_msg_count                 NUMBER := 0;
   x_msg_data                  VARCHAR2 (200) := NULL;
   l_payee_upd_status          iby_disbursement_setup_pub.ext_payee_update_tab_type;
   p_external_payee_tab_type   iby_disbursement_setup_pub.external_payee_tab_type;
   p_ext_payee_id_tab_type     iby_disbursement_setup_pub.ext_payee_id_tab_type;
   p_ext_payee_id_rec          iby_disbursement_setup_pub.Ext_Payee_ID_Rec_Type;
   l_ext_payee_rec             IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type;
   i                           NUMBER := 0;
   g_org_id                    NUMBER := 5320;
   g_user_id                   NUMBER := 19087;
   g_resp_id                   NUMBER := 57148;
   g_resp_appl_id              NUMBER := 201;
BEGIN
   FOR j
   IN (SELECT   iepa.*
         FROM   ap_supplier_sites_all assa,
                ap_suppliers sup,
                iby_external_payees_all iepa,
                iby_ext_party_pmt_mthds ieppm
        WHERE    sup.vendor_id = assa.vendor_id
                AND assa.vendor_site_id = iepa.supplier_site_id
                AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
                AND sup.segment1 like '%156083'
                AND PRIMARY_FLAG='Y'
      )
   LOOP
      p_external_payee_tab_type (i).default_pmt_method := 'CHECK';
      p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';
      p_external_payee_tab_type (i).exclusive_pay_flag := 'N';
      p_external_payee_tab_type (i).payee_party_id := j.payee_party_id;
      p_external_payee_tab_type (i).payer_org_id := j.org_id;
      p_external_payee_tab_type (i).payer_org_type := 'OPERATING_UNIT';
      p_external_payee_tab_type (i).supplier_site_id := j.supplier_site_id;
      p_external_payee_tab_type (i).Payee_Party_Site_Id := j.party_site_id;
      p_ext_payee_id_tab_type (i).ext_payee_id := j.ext_payee_id;
      iby_disbursement_setup_pub.update_external_payee (
         p_api_version            => 1.0,
         p_init_msg_list          => 'T',
         p_ext_payee_tab          => p_external_payee_tab_type,
         p_ext_payee_id_tab       => p_ext_payee_id_tab_type,
         x_return_status          => x_return_status,
         x_msg_count              => x_msg_count,
         x_msg_data               => x_msg_data,
         x_ext_payee_status_tab   => l_payee_upd_status
      );
      EXCEPTION WHEN OTHERS
          THEN
          fnd_file.put_line(fnd_file.log,'Error While Updating external payee API'||x_msg_data);                 
          END;       
         
        fnd_file.put_line(fnd_file.log,'v_return_status IS'||x_return_status);
      IF x_return_status <> 'S'
            THEN
              FOR i IN 1 .. x_msg_count LOOP
                x_msg_data := fnd_msg_pub.get (p_msg_index      => i
                                              ,p_encoded        => 'F'
                                              );
             END LOOP;      
                  fnd_file.put_line(fnd_file.log,'Error While Updating external payee'||x_msg_data);
        END IF;        
    END LOOP;
END;







Monday, 28 November 2016

Java runtime version is 1.3 or later is installed correctly

1) Java runtime version is 1.3 or later is installed correctly
2) The Java executable is accessible through the Windows Path or the Java Home directory is entered in the Preview tab of the options dialog


fix: 

Go to BI Publisher menu in Word.
Tools - > options -> preview tab.

Java home should be selected there, if not select it.

Monday, 14 November 2016

PPR Document Validation error

This post explains the reason for following PPR Document validation error:

PPR Status: Failed Document Validation
Validation Error: Payment Profile on document is not compatible with payment format on document.

Cause of the error: This error occurs when the Payment Format assigned to the Payment Process Request  is different from the Payment Format assigned to Supplier Site.
fix : 

1. Make sure to use Payment Process Profile which has same Payment Format  as Supplier Site.
(Or)
2. Change the Payment Format on supplier site same as Payment Process Profile being used.

 
Process to update Payment Format for a Supplier Site:

Responsibility: Payables Super User
Navigation: Suppliers → Entry

Query the form with Supplier Name/Number.
1.  Click on Payment Details
2.  Click on Update Payment Details under Supplier Site
3.  Click on Payment Specifications Tab
4.  Update Payee Specified Payment Format