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-------------------------

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 .

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#';

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.

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;