Customer Credit Limits API in Oracle Apps

DECLARE
l_cust_prof_amt_rec    hz_customer_profile_v2pub.cust_profile_amt_rec_type;
l_version_num          number;
BEGIN        
l_cust_prof_amt_rec.cust_acct_profile_amt_id :=hz_cust_profile_amts.cust_acct_profile_amt_id;
l_cust_prof_amt_rec.trx_credit_limit := 0;                                                
l_cust_prof_amt_rec.overall_credit_limit :=1000;
l_version_num := l_cbd_rec (l_cbd).object_version_number;                      
                      hz_customer_profile_v2pub.update_cust_profile_amt
                                                      ('T',
                                                       l_cust_prof_amt_rec,
                                                       l_version_num,
                                                       l_return_status,
                                                       l_msg_count,
                                                       l_msg_data
                                                       );
END;   

Customer RelationShips using API in Oracle

DECLARE
l_cust_acct_relate_rec   hz_cust_account_v2pub.cust_acct_relate_rec_type;
l_msg_count              NUMBER;
l_msg_data               VARCHAR2 (2000);
l_return_status          VARCHAR2 (2000);
BEGIN
l_cust_acct_relate_rec.cust_account_id :=l_main_rec (l_main).cust_account_id;
l_cust_acct_relate_rec.related_cust_account_id :=l_rel.cust_account_id;
l_cust_acct_relate_rec.relationship_type := 'ALL';
l_cust_acct_relate_rec.created_by_module := 'DELL_AP_DATA_MIGRATION';
l_cust_acct_relate_rec.CUSTOMER_RECIPROCAL_FLAG := 'Y';                                                    
              hz_cust_account_v2pub.create_cust_acct_relate
                                                     ('T',
                                                      l_cust_acct_relate_rec,
                                                      l_return_status,
                                                      l_msg_count,
                                                      l_msg_data
                                                      );
END;

Unix FTP from Oracle to Windows

########################################
#Transfer file to Windows from  Archive#
########################################

echo 'Trying to copy files into FTP folder'

   sftp -nv <<END_OF_REMOTE_DIR
   open 120.10.1.1
   user ${FTP_UNAME} ${FTP_PWD}
   lcd  $Oracle_DIR
   cd   $Windows_DIR 
   binary
   for  file in ${FILE_NAME}*.${FILE_EXT}
   do
   mput  *.txt
   done
   bye
END_OF_REMOTE_DIR

rename the file with Sysdate in Unix

########################################
#                  Rename the file with sysdate               #
########################################

 
 for file in ${FILE_NAME}*.${FILE_EXT}
 do
 newfile=$(date "+%d%b%Y_$file" -d "$(stat -c "%y" $file)")
 mv "$file" "$newfile" 

Standard API for Supplier Sites (R11 i)

DECLARE
   CURSOR cur_suppsites_r11 (p_vendor_site_id NUMBER)
   IS
      SELECT pvsa.*, pvsa.ROWID
        FROM po_vendor_sites_all pvsa
       WHERE 1 = 1
       AND pvsa.vendor_site_id = p_vendor_site_id;
   v_date             DATE;
   v_itemkey          VARCHAR2 (100);
   v_vendor_site_id   NUMBER;
   v_user_id          NUMBER;
   v_resp_id          NUMBER;

BEGIN
   v_user_id := 7240; --fnd_profile.VALUE ('USER_ID');
   v_resp_id := 51683;--fnd_profile.VALUE ('RESP_ID');
   v_vendor_site_id := 37425;

   fnd_global.apps_initialize (user_id           => v_user_id,
                               resp_id           => v_resp_id,
                               resp_appl_id      => 200
                              );

   FOR v_site IN cur_suppsites_r11 (v_vendor_site_id)
   LOOP
      ap_vendor_sites_pkg.update_row
         (x_rowid                                => v_site.rowid,
          x_vendor_site_id                   => v_site.vendor_site_id,
          x_last_update_date               => v_site.last_update_date,
          x_last_updated_by                => v_site.last_updated_by,
          x_vendor_id                          => v_site.vendor_id,
          x_vendor_site_code              => v_site.vendor_site_code,
          x_last_update_login               => v_site.last_update_login,
          x_creation_date                     => v_site.creation_date,
          x_created_by                        => v_site.created_by,
          x_purchasing_site_flag           => v_site.purchasing_site_flag,
          x_rfq_only_site_flag              => v_site.rfq_only_site_flag,
          x_pay_site_flag                     => v_site.pay_site_flag,
          x_attention_ar_flag                => v_site.attention_ar_flag,
          x_address_line1                    => v_site.address_line1,
          x_address_line2                    => v_site.address_line2,
          x_address_line3                    => v_site.address_line3,
          x_city                                   => v_site.city,
          x_state                                 => v_site.state,
          x_zip                                    => v_site.zip,
          x_province                           => v_site.province,
          x_country                             => v_site.country,
          x_area_code                        => v_site.area_code,
          x_phone                               => v_site.phone,
          x_customer_num                  => v_site.customer_num,
          x_ship_to_location_id          => v_site.ship_to_location_id,
          x_bill_to_location_id           => v_site.bill_to_location_id,
          x_ship_via_lookup_code    => v_site.ship_via_lookup_code,
          x_freight_terms_lookup_code         => v_site.freight_terms_lookup_code,
          x_fob_lookup_code            => v_site.fob_lookup_code,
          x_inactive_date                   => v_date,
          x_fax                                  => v_site.fax,
          x_fax_area_code                => v_site.fax_area_code,
          x_telex                               => v_site.telex,
          x_payment_method_lookup_code        => v_site.payment_method_lookup_code,
          x_bank_account_name       => v_site.bank_account_name,
          x_bank_account_num         => v_site.bank_account_num,
          x_bank_num                      => v_site.bank_num,
          x_bank_account_type        => v_site.bank_account_type,
          x_terms_date_basis           => v_site.terms_date_basis,
          x_current_catalog_num      => v_site.current_catalog_num,
          x_vat_code                       => v_site.vat_code,
          x_distribution_set_id         => v_site.distribution_set_id,
          x_accts_pay_ccid             => v_site.accts_pay_code_combination_id,
          x_future_dated_payment_ccid         => v_site.future_dated_payment_ccid,
          x_prepay_code_combination_id        => v_site.prepay_code_combination_id,
          x_pay_group_lookup_code             => v_site.pay_group_lookup_code,
          x_payment_priority                  => v_site.payment_priority,
          x_terms_id                          => v_site.terms_id,
          x_invoice_amount_limit              => v_site.invoice_amount_limit,
          x_pay_date_basis_lookup_code        => v_site.pay_date_basis_lookup_code,
          x_always_take_disc_flag             => v_site.always_take_disc_flag,
          x_invoice_currency_code             => v_site.invoice_currency_code,
          x_payment_currency_code             => v_site.payment_currency_code,
          x_hold_all_payments_flag            => v_site.hold_all_payments_flag,
          x_hold_future_payments_flag         => v_site.hold_future_payments_flag,
          x_hold_reason                       => v_site.hold_reason,
          x_hold_unmatched_invoices_flag      => v_site.hold_unmatched_invoices_flag,
          x_match_option                      => v_site.match_option,
          x_create_debit_memo_flag            => v_site.create_debit_memo_flag,
          x_exclusive_payment_flag            => v_site.exclusive_payment_flag,
          x_tax_reporting_site_flag           => v_site.tax_reporting_site_flag,
          x_attribute_category                => v_site.attribute_category,
          x_attribute1                        => v_site.attribute1,
          x_attribute2                        => v_site.attribute2,
          x_attribute3                        => v_site.attribute3,
          x_attribute4                        => v_site.attribute4,
          x_attribute5                        => v_site.attribute5,
          x_attribute6                        => v_site.attribute6,
          x_attribute7                        => v_site.attribute7,
          x_attribute8                        => v_site.attribute8,
          x_attribute9                        => v_site.attribute9,
          x_attribute10                       => v_site.attribute10,
          x_attribute11                       => v_site.attribute11,
          x_attribute12                       => v_site.attribute12,
          x_attribute13                       => v_site.attribute13,
          x_attribute14                       => v_site.attribute14,
          x_attribute15                       => v_site.attribute15,
          x_validation_number                 => v_site.validation_number,
          x_exclude_freight_from_disc         => v_site.exclude_freight_from_discount,
          x_vat_registration_num              => v_site.vat_registration_num,
          x_offset_tax_flag                   => v_site.offset_tax_flag,
          x_check_digits                      => v_site.check_digits,
          x_bank_number                       => v_site.bank_number,
          x_address_line4                     => v_site.address_line4,
          x_county                            => v_site.county,
          x_address_style                     => v_site.address_style,
          x_language                          => v_site.LANGUAGE,
          x_allow_awt_flag                    => v_site.allow_awt_flag,
          x_awt_group_id                      => v_site.awt_group_id,
          x_pay_on_code                       => v_site.pay_on_code,
          x_default_pay_site_id               => v_site.default_pay_site_id,
          x_pay_on_receipt_summary_code       => v_site.pay_on_receipt_summary_code,
          x_bank_branch_type                  => v_site.bank_branch_type,
          x_edi_id_number                     => v_site.edi_id_number,
          x_edi_payment_method                => v_site.edi_payment_method,
          x_edi_payment_format                => v_site.edi_payment_format,
          x_edi_remittance_method             => v_site.edi_remittance_method,
          x_edi_remittance_instruction        => v_site.edi_remittance_instruction,
          x_edi_transaction_handling          => v_site.edi_transaction_handling,
          x_auto_tax_calc_flag                => v_site.auto_tax_calc_flag,
          x_auto_tax_calc_override            => v_site.auto_tax_calc_override,
          x_amount_includes_tax_flag          => v_site.amount_includes_tax_flag,
          x_ap_tax_rounding_rule              => v_site.ap_tax_rounding_rule,
          x_vendor_site_code_alt              => v_site.vendor_site_code_alt,
          x_address_lines_alt                 => v_site.address_lines_alt,
          x_global_attribute_category         => v_site.global_attribute_category,
          x_global_attribute1                 => v_site.global_attribute1,
          x_global_attribute2                 => v_site.global_attribute2,
          x_global_attribute3                 => v_site.global_attribute3,
          x_global_attribute4                 => v_site.global_attribute4,
          x_global_attribute5                 => v_site.global_attribute5,
          x_global_attribute6                 => v_site.global_attribute6,
          x_global_attribute7                 => v_site.global_attribute7,
          x_global_attribute8                 => v_site.global_attribute8,
          x_global_attribute9                 => v_site.global_attribute9,
          x_global_attribute10                => v_site.global_attribute10,
          x_global_attribute11                => v_site.global_attribute11,
          x_global_attribute12                => v_site.global_attribute12,
          x_global_attribute13                => v_site.global_attribute13,
          x_global_attribute14                => v_site.global_attribute14,
          x_global_attribute15                => v_site.global_attribute15,
          x_global_attribute16                => v_site.global_attribute16,
          x_global_attribute17                => v_site.global_attribute17,
          x_global_attribute18                => v_site.global_attribute18,
          x_global_attribute19                => v_site.global_attribute19,
          x_global_attribute20                => v_site.global_attribute20,
          x_bank_charge_bearer                => v_site.bank_charge_bearer,
          x_ece_tp_location_code              => v_site.ece_tp_location_code,
          x_pcard_site_flag                   => v_site.pcard_site_flag,
          x_country_of_origin_code            => v_site.country_of_origin_code,
          x_calling_sequence                  => NULL,
          x_shipping_location_id              => NULL,
          x_supplier_notif_method             => v_site.supplier_notif_method,
          x_email_address                     => 'anilmca02@gmail.com',
          x_remittance_email                  => v_site.remittance_email,
          x_primary_pay_site_flag             => v_site.primary_pay_site_flag,
          x_shipping_control                  => v_site.shipping_control,
          x_gapless_inv_num_flag              => v_site.gapless_inv_num_flag,
          x_selling_company_identifier        => v_site.selling_company_identifier,
          x_duns_number                       => v_site.duns_number,
          x_tolerance_id                      => v_site.tolerance_id
         );
   END LOOP;
   COMMIT;
END;

How to Create Or Modify A Payment Format Using BI/XML Builder

Using XML file from Payment Process Request, how to modify or Create Payment Formats.


1) Responsibility:

Payments Setup Adminstrator 

Oracle Payments Set Up main page > Formats - Formats - Go to Task
Create a new Format and attach the template "Extract Identity"
Data Extract = Oracle Payments Funds Disbursement Payment Instruction Extract, Version 1.0

Navigate to:
Oracle Payments Set Up main page > Payment Process Profiles - Go to Task
Create a Payment Process Profile (PPP) and assign the format you just created on the Payment Instruction Format tab.

2) Responsibility:

Cash Management

Query up an existing Bank Account
Select the account and click on the Manage Payment Documents button
Create a new set of Payment Documents and assign the format you created above.

3) Responsibility:

Payables

Enter an invoice, and validate/approve it.
Run the Accounting Process for the Invoice.
Create a new Payment Process Request (PPR) to pay the invoice.
Use the new PPP and set of Payment Documents that you created above.
The output for this PPR should be in XML format, and will include all the field tags generated by the Data Extract.

4) In Microsoft Word

Load the XML template file, then open the template that you wish to modify.
Select the fields from the extract that you wish to add to your template.
The new Template (when finished) can be attached to a new Payment Format which can be used on a new PPP and Payment Document.

XML output for Payment Process Request

After finishing the Payment Process, We can get XML tags in log file of 'Format Payment Instructions with Text Output'.By using that we can develop a new Template.

In R12, is there a location where the backup file that contains the entire string of XML output for a particular Payment Instruction id.

i.e IBY_TRXN_DOCUMENTS

Take the Payment Instruction id associated with your Payment Process,then run the query

select * from IBY_TRXN_DOCUMENTS where PAYMENT_INSTRUCTION_ID=XXXXX

Difference between AP_CHECKS_ALL & IBY_PAYMENTS_ALL

AP_CHECKS_ALL

Column name	Description
CHECK_ID	This is a system generated unique number and primary key for AP_CHECKS_ALL table. This is only internal identifier of a payment record and does not have any business significance.
CHECK_NUMBER	This is payment number. Following is stored for printed and electronic payments: Printed Payments: Payment document number i.e. check number from IBY_PAYMENTS_ALL. PAPER_DOCUMENT_NUMBER Electronic Payments (if payment document number is generated): Same as printed payments Electronic Payments: Payment reference number from IBY_PAYMENTS_ALL.PAYMENT_REFERENCE_NUMBER
CHECKRUN_NAME	Following data is stored in this column: For single payments: System generated quick payment ID For batch payments: User given payment process request ID
PAYMENT_ID	Foreign key to IBY_PAYMENTS_ALL
PAYMENT_DOCUMENT_ID	Foreign key to CE_PAYMENT_DOCUMENTS

IBY_PAYMENTS_ALL

Column name	Description
PAYMENT_ID	This is a system-generated unique number and primary key for IBY_PAYMENTS_ALL table. This is only an internal identifier of a payment record and does not have any business significance.
PAYMENT_REFERENCE_NUMBER	This is a system-generated unique number as explained earlier. This number is very important for external representation of a payment, especially for electronic payments where the payment document number is not available.
PAPER_DOCUMENT_NUMBER	This is the payment document number ("check number"). For electronic payments, the value in this column will be null if the payment document is not configured.
PAYMENT_PROCESS_REQUEST_NAME	This has the same information as AP_CHECKS_ALL. CHECKRUN_NAME: For single payments: System-generated Quick Payment ID For batch payments: User-given Payment Process Request ID

Standard API for Supplier Contacts (R11 i)

declare
l_vendor_contact_id number;
l_first_name varchar2(30);
l_last_name varchar2(30);
l_status varchar2(3);
l_exception_msg varchar2(300);
l_middle_name varchar2(30);
l_prefix varchar2(30);
l_title varchar2(3);
l_mail_stop varchar2(3);
l_area_code varchar2(3);
l_phone varchar2(3);
begin
l_vendor_contact_id := 419926;
l_first_name := 'Anil';
l_last_name := 'Kumar';
AP_PO_VENDORS_APIS_PKG.update_vendor_contact
(
p_vendor_contact_id => l_vendor_contact_id,
p_first_name => l_first_name,
p_last_name => l_last_name,
p_middle_name => l_middle_name,
p_prefix => l_prefix ,
p_title => l_title,
p_mail_stop => l_mail_stop,
p_area_code => l_area_code,
p_phone => l_phone,
p_alt_area_code => NULL,
p_alt_phone => NULL,
p_fax_area_code => NULL,
p_fax => NULL,
p_email_address => 'anilmca02@gmail.com',
p_url => NULL,
x_status => l_status,
x_exception_msg => l_exception_msg
);
dbms_output.PUT_LINE('l_status is '||l_status);
commit;
end;

Using the same API we can update vendors,Sites also.

Unable to Open Webpages Using Some Responsibilities in R12

Please follow the below steps and try again 


1. Login and choose the Functional Administrator responsibility - then choose Home 

2. Choose the Core Services Tab - then the Caching Framework Sub-Menu (In the dark blue region). - Proceed to choose 'Global Configuration' from the left hand side menu that appears - In the far right choose 'Clear all Cache' button. 

3. A screen prompts and confirms that the action will clear all cache on the middle tier server - choose Yes. Essentially, this just forces all user sessions to engage and validate rather than using cached values 

4. A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.

E-text and XSL Outputs in XML Publisher

If the template builder is installed correctly, you should see the menu bar shown below in the toolbar section:

 

From the Data menu select Load XML Data to load a sample XML document that contains the data fields you  may want to insert into your RTF layout template. And then click on Preview,Where we can view the output.

This is for to Genarate the  RTF template and verifying the output in local Desktop and once every thing finish then we will create Template defination and attach the RTF.

The same way we cant view the Output, For E-text & XSL. For E-text we have to use Template Viwer and for XSL we need to use Notepad++.

E-text

Goto All Programs--> Click on Oracle BI/XML Pulisher Desktop--> Select Template Viwer

In the working directory we have to point our local path where XML file and Etext file exists. Then select output format to Etext and click on Start Processing. It will generates the output in that working. directory.


XSL

In notepad ++ got to - Plugins - Plugin Manager -
Show Plugin Manager Under avalable tab select XML Tools and install -
restart notepad ++
Open the XML data file from any of the request in notepad ++ - go to Plugins - XML Tools - XML Transformation - select XSL (will prompt you to chose the xsl file) - click transform This will create a new tab with transformed xml output. :)

AutoInvoice Errors

1. Invalid Salesrep Number (AR_RAXTRX_SALESREP_INACTIVE)
  
    Verify the Batch Source setup, if you want to pass ID, then ensure that the radio button Id is chosen.
    If  instead you want to pass value, then ensure that the radio button Value is chosen.
    If you want to bypass populating the SALES_CREDIT_TYPE_NAME or SALES_CREDIT_TYPE_ID
    then Uncheck the Allow Sales Credit checkbox
 

2. You must supply payment terms for your non-credit transaction
    Payment term cannot be present for a credit memo. TERM_ID and TERM_NAME must be null if you are 
    trying to import a credit transaction.

3. a) Invalid Warehouse ID (WAREHOUSE_ID)
    b) Unable to derive a gl date for your transaction.
 
  Please ensure that your transaction is in a gl period which you have defined A:
  When you get both these errors at the same time,
  please check whether your gl period is open for the date seen in the columns
 GL_DATE, SHIP_DATE_ACTUAL and SALES_ORDER_DATE in the table  RA_INTERFACE_LINES_ALL
 
4. Invalid Warehouse ID (WAREHOUSE_ID)    
       Go to Order Management responsibility...
       Setup>System Parameters>Values
       Select the appropriate Operating Unit
       For Category select Generic Parameters
       Scroll down and verify your Item Validation Organization
   
5. Duplicate Transaction Flexfield
   
     The combination of fields INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1 ……  
      INTERFACE_LINE_ATTRIBUTE15 have duplicate values in the table RA_INTERFACE_LINES_ALL
   
6   Remit-to address defined.
 
      Navigation: Setup-->Print-->Remit-to Addresses