Oracle EBS R12 Outbound Interface Example

 CREATE OR REPLACE PROCEDURE XX_INV_ITEMS_OUT (Errbuf       OUT varchar2,

                                         Retcode      OUT varchar2,

                                         p_id      IN     number)

AS

   CURSOR c1

   IS

      SELECT   msi.segment1 item,

               msi.inventory_item_id Itemid,

               msi.description itemdesc,

               msi.primary_uom_code Uom,

               ood.organization_name name,

               ood.organization_id id,

               mc.segment1 || ',' || mc.segment2 Category

        FROM   mtl_system_items_b msi,

               org_organization_definitions ood,

               mtl_item_categories mic,

               mtl_categories mc

       WHERE       msi.organization_id = ood.organization_id

               AND msi.inventory_item_id = mic.inventory_item_id

               AND msi.organization_id = mic.organization_id

               AND mic.category_id = mc.category_id

               AND msi.purchasing_item_flag = 'Y'

               AND msi.organization_id = p_id;


   x_id      UTL_FILE.file_type;

   l_count   number (5) DEFAULT 0 ;

BEGIN

   x_id :=

      UTL_FILE.fopen ('/u01/install/APPS/temp/EBSDB', 'InvItem.csv', 'W');


   --select * from v$parameter where name like '%utl_file%'

   FOR x1 IN c1

   LOOP

      l_count := l_count + 1;

      UTL_FILE.put_line (

         x_id,

            x1.item

         || ','

         || x1.itemid

         || ','

         || x1.itemdesc

         || ','

         || x1.uom

         || ','

         || x1.name

         || ','

         || x1.id

         || ','

         || x1.category

      );

   END LOOP;


   UTL_FILE.fclose (x_id);

   Fnd_file.Put_line (

      Fnd_file.output,

      'No of Records transfered to the data file :' || l_count

   );

   Fnd_File.Put_line (fnd_File.Output, ' ');

   Fnd_File.Put_line (

      fnd_File.Output,

      'Submitted User name  ' || Fnd_Profile.VALUE ('USERNAME')

   );

   Fnd_File.Put_line (fnd_File.Output, ' ');

   Fnd_File.Put_line (

      fnd_File.Output,

      'Submitted Responsibility name ' || Fnd_profile.VALUE ('RESP_NAME')

   );

   Fnd_File.Put_line (fnd_File.Output, ' ');

   Fnd_File.Put_line (fnd_File.Output, 'Submission Date :' || SYSDATE);

EXCEPTION

   WHEN UTL_FILE.invalid_operation

   THEN

      fnd_file.put_line (fnd_File.LOG, 'invalid operation');

      UTL_FILE.fclose_all;

   WHEN UTL_FILE.invalid_path

   THEN

      fnd_file.put_line (fnd_File.LOG, 'invalid path');

      UTL_FILE.fclose_all;

   WHEN UTL_FILE.invalid_mode

   THEN

      fnd_file.put_line (fnd_File.LOG, 'invalid mode');

      UTL_FILE.fclose_all;

   WHEN UTL_FILE.invalid_filehandle

   THEN

      fnd_file.put_line (fnd_File.LOG, 'invalid filehandle');

      UTL_FILE.fclose_all;

   WHEN UTL_FILE.read_error

   THEN

      fnd_file.put_line (fnd_File.LOG, 'read error');

      UTL_FILE.fclose_all;

   WHEN UTL_FILE.internal_error

   THEN

      fnd_file.put_line (fnd_File.LOG, 'internal error');

      UTL_FILE.fclose_all;

   WHEN OTHERS

   THEN

      fnd_file.put_line (fnd_File.LOG, 'other error');

      UTL_FILE.fclose_all;

END xx_INV_Out1;

/

Comments

Post a Comment

Popular posts from this blog

Oracle APPS Creating Buy One Get One Free Modifier

Image
1.    Via Oracle Pricing Manager Responsibility 2.    Navigate > Price List > Price List Setup     Verify items (buy item and get item) are on the price list.     3.    Navigate > Modifiers > Modifiers Setup    At the header level create a Modifier Type of Promotion   4.    Modifier line info should be setup as follows:   Level   - Line Modifier Type   - Promotional Goods Automatic   - Checked Pricing Phase   - List Line Adjustment   Product Attribute - Item Number   Product Attribute value   - the item that the customer orders   Volume type   - Item Quantity                       Break type   - Recurring     Operator   - Between UOM   - select the appropriate unit of measure Value from ...
Read more

Oracle R12 Project Interface & APIs

Image
In Oracle Projects (PA) R12 , there are multiple ways to interface project-related data from external systems (like legacy systems, third-party tools, or spreadsheets). The method you choose depends on the type of data , volume , and level of control you need. 1.      Open Interface Tables (Standard Way)            Oracle provides interface tables for different types of project-related data. You insert data into                   these tables, then run Oracle’s concurrent programs to import the data.           Common Interface Tables:                      Best for: Medium to large data loads           Requires: Validation of setup values (status, types, orgs) 2.      Public APIs (PL/SQL-based)           Oracle provi...
Read more

SQL query to find pending PO for approval in Oracle EBS R12

SELECT   fu.user_name po_created_by,pha.org_id ,          (SELECT NAME || ' ( ' || short_code || ' )'             FROM hr_operating_units            WHERE organization_id = pha.org_id) ou_name,          pha.segment1 po_num, pha.revision_num, SUM(pla.quantity) po_qty, SUM(pla.unit_price) po_price,          pha.authorization_status, pha.creation_date po_creation_date,          wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,          TRUNC (SYSDATE - wn.begin_date) no_days_pending,d.name Approval_path          ,wn.from_role forrwad_by,wn.notification_id,pha.po_header_id     FROM wf_notifications wn,          po_headers_all pha,          fnd_user fu,          po_lines_all pla, ...
Read more