BIP report in Oracle fusion

BIP report in Oracle fusion

BIP is the new reporting tool provided by Oracle in its Fusion Version. We can develop the Custom Reports in Oracle Fusion with the Help of this BIP tool. BIP is the only reporting tool in Oracle fusion to create the Customize reports in Oracle Fusion. BIP report in oracle fusion , gives the flexibility to develop the custom reports as per the Business requirements and its needs.

 

BIP is the customize reporting tool. Oracle Fusion has provided BIP to create custom reports or to meet the need of the business which is not achievable through OTBI reporting tool.

BIP is most similar like Oracle RDF report in which we creates Data Models , Different -2 groups then Link groups with Data Links. We can create Before and After Report triggers in form of event triggers. We can Create Parameters and List of Values also. We can also use custom Package also in the BIP.

But in Oracle R12 , we have very important useful tool set  that Formula Columns , Placeholder and summary columns but these tool set is not available in BIP reports. We can do some calculation in BIP on columns but we cannot write separate code in BIP like Formula column in Oracle RDF. This is a Big limitation in BIP reports because Formula Columns and Placeholder and Summary Columns quite useful in designing Complex reports.

We can also create Lexical Parameters in Oracle Fusion BIP reports so that we can change the report query dynamically based on conditions. We can creates Lexical parameters with the help of Event trigger but this is also possible if you using Oracle Fusion PAAS service if you are using Oracle Fusion SAAS then you cannot use Event Triggers.

 

Steps to create the BIP Report in Oracle Fusion

 

 

Step 1:- Login to Oracle Fusion and go to Menu Navigator as below and Click on Reports and Analysis option.

Step2:-

Step3:- Click on + sign and Data set will be open as below
Give the name of Data Set:= XX_INVOICE_DETAILS
Data Source:= ApplicaionDB_FSCM

 

Step3:- Again Click on + sign and Data set will be open as below
Give the name of Data Set:= XX_SUPPLIER_SITES
Data Source:= ApplicaionDB_FSCM

 

 

 

 



Step 5:- Now these two data sets has been created and now we will join these two group by data link so Click on Vendor_id column and select Create Link

Step 5:- we will join these two group by data link so Click on Vendor_id column

 

 

Step 7:- Now Save the Data model as below in right folder under Invoices Folder.

 

 

 

 

Step 8:- Now we will Create Parameters. I am going to create Vendor Name parameter for this  as below.

 

 

Step 10:-

 

Step 11:-

 

Now we will create Value set and attach this Value set to Parameters.

 

 

 

Step 12:- LOV Creation as below.

 

 

 

Hi Friends due to the length of this topic I will split these topic three parts , I have split this topic in three parts. Please follow the next post for rest of the steps.

Part 2 Url:-

http://rpforacle.blogspot.com/2018/01/oracle-fusion-bip-reports-step-by-step-process-to-create-bip-report-and-register-in-oracle-fusion.html

 

Memo lines in oracle receivables

Memo lines in oracle receivables

In this post , we will discuss about Memo lines in Oracle receivables.We define standard Memo lines for AR Invoices , Debit Memos , Charge Backs. Oracle Receivables Display these Standard Memo Lines as a List of Value in AR Invoices , At Debit Memos Lines.When you create chargebacks and debit memo reversals, you can either use the standard line that Receivables provides or enter your own. You can create an unlimited number of standard memo lines.

 

Receivables lets you enter tax code, unit list price, and unit of measure information for each standard memo line in oracle receivables. You can also specify a standard invoicing and accounting rule for each standard memo line.

 

If Auto Accounting depends on standard line items, Receivables uses the revenue account that you enter here along with your Auto Accounting setup to determine the default revenue, freight, AutoInvoice Clearing, Tax, Unbilled Receivable, Unearned Revenue, and Receivable accounts for invoices with this line item.

 

Steps to Create Memo lines in Oracle Receivables

 

Enter the Name and a Description of this memo line. Receivables prints this description on your debit memo, on-account credit, debit memo reversal, chargeback or invoice.

 

Choose a line type of Line, Freight, Tax, or Charges.

Enter the Unit List Price for this memo line (optional). Receivables displays this price on the debit memos, on-account credits, chargebacks, and invoices you create using this standard line.

 

If the type of this memo line is 'Line,' enter a Unit of Measure. Receivables defaults the unit of measure to this value when you choose this standard line item during invoice or memo entry.

 

Enter the Revenue Account for this memo line (optional). When you create a debit memo or on-account credit, this revenue account will be the default for each standard memo line that you select. When you create debit memo reversals or chargebacks, Receivables uses the Revenue Flexfield from the original receivable item as the credit account. 

 

Enter the Invoicing Rule to use with this standard line (optional). Receivables does not currently use this information when you select a standard line (in the Lines window during invoice entry) to which you have assigned a standard invoicing rule. Oracle Order Entry assigns standard invoicing rules to standard lines for orders that you import through AutoInvoice.

 

Standard Memo Lines in Oracle Receivables. How to Define Standard Memo Lines

Item import interface in oracle apps r12

Item import interface in oracle apps r12

Oracle apps has provided the Item Interface to create the Inventory Items form the External system to the Oracle application. This Item interface helps to create the Inventory Items directly from the Interface without creating manually one by one from the Front end Application. In this Post , I will show the Item Import Interface in oracle apps r12 and the Complete procedure which helps to import item from Item interface to the Oracle Inventory base tables.

 

Steps for Item Import in Oracle apps r12

 

1.Prepare the Items data in the Form of Item Import Interface tables.

2.Insert Item Information in the Item Interface Tables.

3.Run the 'Item Import' program to import items from Item Interface tables to the Oracle item Base tables.

 

 

 DECLARE
      CURSOR cursor_item_stg
      IS
        SELECT   * FROM ITEM_MASTER_STAG
                         
      v_child_org_id      VARCHAR2 (10);
      v_child_org_code    VARCHAR2 (10);
      v_master_org_id     VARCHAR2 (10);
      v_master_org_code   VARCHAR2 (10);
      l_template_id       VARCHAR2 (20);
      l_inv_id            VARCHAR2 (20);
      l_error             VARCHAR2 (1000);
      l_struct            VARCHAR2 (20);
      l_cat               VARCHAR2 (20);
      l_category          NUMBER;
      exist_num11         NUMBER;
      master_exist_num    NUMBER;
      alert_number        NUMBER;
      v_template_name     VARCHAR2 (20000);
      item_code           VARCHAR2 (2000);
   BEGIN
      FOR i IN cursor_item_stg
      LOOP
         item_code := (i.new_item_code);
         BEGIN
            ---------------------Fetech master and Child Oragnisation Code------------------------
            BEGIN
               SELECT a.organization_id, a.organization_code,
                      a.master_organization_id, b.organization_code
                 INTO v_child_org_id, v_child_org_code,
                      v_master_org_id, v_master_org_code
                 FROM mtl_parameters a, org_organization_definitions b
                WHERE a.master_organization_id = b.organization_id
                  AND a.organization_code = i.org_code;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  v_child_org_id := NULL;
                  v_child_org_code := NULL;
                  v_master_org_id := NULL;
                  v_master_org_code := NULL;
               WHEN OTHERS
               THEN
                  v_child_org_id := NULL;
                  v_child_org_code := NULL;
                  v_master_org_id := NULL;
                  v_master_org_code := NULL;
            END;
            ----------------------------Fetch Template Code -----------------------
            BEGIN
               SELECT template_id, template_name
                 INTO l_template_id, v_template_name
                 FROM apps.mtl_item_templates
                WHERE UPPER (template_name) = UPPER (i.template_name);
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  l_template_id := NULL;
               WHEN OTHERS
               THEN
                  l_template_id := NULL;
            END;
            /* Insert Record Into Interface data */
            BEGIN
               --------FOR MASTER organisation Item insertion ----------------------
               IF v_child_org_id = v_master_org_id
               THEN
                  INSERT INTO mtl_system_items_interface
                              (organization_id, description,
                               long_description, segment1,
                               segment2, segment3, segment4,
                               segment5, segment6, segment7,
                               segment8, segment9, segment10,
                               segment11, segment12, segment13,
                               segment14, segment15, segment16,
                               segment17, segment18, segment19,
                               segment20, primary_uom_code,
                               secondary_uom_code, process_flag,
                               template_id, transaction_type,
                               set_process_id, shelf_life_days,
                               location_control_code,
                               ----INVENTORY_ITEM_FLAG,
                               min_minmax_quantity, max_minmax_quantity,
                               expense_account,
                               must_use_approved_vendor_flag,
                               days_late_receipt_allowed,
                               list_price_per_unit, attribute1,
                               attribute2, attribute3,attribute4,attribute5,
                               attribute6,attribute7,attribute8,attribute9,
                               attribute10,attribute11,attribute12,attribute13,
                               attribute14,attribute15
                              )
                       VALUES (v_child_org_id, i.item_description,
                               i.item_long_desc, i.new_item_code,
                               i.segment2, i.segment3, i.segment4,
                               i.segment5, i.segment6, i.segment7,
                               i.segment8, i.segment9, i.segment10,
                               i.segment11, i.segment12, i.segment13,
                               i.segment14, i.segment15, i.segment16,
                               i.segment17, i.segment18, i.segment19,
                               i.segment20, i.stocking_uom,
                               i.procurement_uom, 1,
                               l_template_id, 'CREATE',
                               1, i.shelf_period,
                               DECODE (i.stock_locator_flag, 'N', 1, 1),
                               -- NVL(R (L (I.Inventory_Planning_Method)),'N'),
                               i.inv_min, i.inv_max,
                               DECODE (NVL (i.expense_item_control, 'N'),
                                       'N', NULL,
                                       i.expense_account
                                      ),
                               i.approved_supplier_flag,
                               i.purchasing_lead_days,
                               i.purchased_list_price, i.attribute1,
                               i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                              );
                  COMMIT;
               ELSIF v_child_org_id <> v_master_org_id
               THEN
                  /*If Item Code New In Child Organization
                  but  Exist in Master Organizatio Then Create only One Record For
                  Child organization  */
                  BEGIN
                     SELECT COUNT (*)
                       INTO master_exist_num
                       FROM mtl_system_items_b
                      WHERE (segment1) = ((i.new_item_code))
                        AND organization_id = v_master_org_id;
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        master_exist_num := 0;
                     WHEN OTHERS
                     THEN
                        master_exist_num := 0;
                  END;
                  IF master_exist_num <> 0
                  THEN
                     INSERT INTO mtl_system_items_interface
                                 (organization_id, description,
                                  long_description, segment1,
                                  segment2, segment3,
                                  segment4, segment5,
                                  segment6, segment7,
                                  segment8, segment9,
                                  segment10, segment11,
                                  segment12, segment13,
                                  segment14, segment15,
                                  segment16, segment17,
                                  segment18, segment19,
                                  segment20, primary_uom_code,
                                  secondary_uom_code, process_flag,
                                  template_id, transaction_type,
                                  set_process_id, shelf_life_days,
                                  location_control_code,
                                  ----INVENTORY_ITEM_FLAG,
                                  min_minmax_quantity, max_minmax_quantity,
                                  expense_account,
                                  must_use_approved_vendor_flag,
                                  days_late_receipt_allowed,
                                  list_price_per_unit, attribute1,
                                  attribute2, attribute3,attribute4,attribute5,
                                  attribute6,attribute7,attribute8,attribute9,
                                  attribute10,attribute11,attribute12,attribute13,
                                  attribute14,attribute15
                                 )
                          VALUES (v_child_org_id, i.item_description,
                                  i.item_long_desc, (i.new_item_code),
                                  (i.segment2), (i.segment3),
                                  (i.segment4), (i.segment5),
                                  (i.segment6), (i.segment7),
                                  (i.segment8), (i.segment9),
                                  (i.segment10), (i.segment11),
                                  (i.segment12), (i.segment13),
                                  (i.segment14), (i.segment15),
                                  (i.segment16), (i.segment17),
                                  (i.segment18), (i.segment19),
                                  (i.segment20), i.stocking_uom,
                                  i.procurement_uom, 1,
                                  l_template_id, 'CREATE',
                                  1, i.shelf_period,
                                  DECODE (i.stock_locator_flag, 'N', 1, 1),
                                  -------------NVL(R (L (I.Inventory_Planning_Method)),'N'),
                                  i.inv_min, i.inv_max,
                                  DECODE (NVL (i.expense_item_control, 'N'),
                                          'N', NULL,
                                          i.expense_account
                                         ),
                                  i.approved_supplier_flag,
                                  i.purchasing_lead_days,
                                  i.purchased_list_price, i.attribute1,
                                  i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                 );
                  /*If Item Code New In Child Organization
                  but Not Exist in Master Organizatio Then Create One Record For
                  Master Organization and careate reocrd for Child organization  */
                  ELSE
                     ---------Master Organization -----------
                     /*  Now Check Same New  Item Code is existed with master organization            */
                     BEGIN
                        SELECT COUNT (*)
                          INTO exist_num11
                          FROM mtl_system_items_interface
                         WHERE inventory_item_id IS NULL
                           AND organization_id = v_master_org_id
                           AND (segment1) = ((i.new_item_code));
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           exist_num11 := 0;
                        WHEN OTHERS
                        THEN
                           exist_num11 := 0;
                     END;
                     IF exist_num11 = 0
                     THEN
                        INSERT INTO mtl_system_items_interface
                                    (organization_id, description,
                                     long_description, segment1,
                                     segment2, segment3,
                                     segment4, segment5,
                                     segment6, segment7,
                                     segment8, segment9,
                                     segment10, segment11,
                                     segment12, segment13,
                                     segment14, segment15,
                                     segment16, segment17,
                                     segment18, segment19,
                                     segment20, primary_uom_code,
                                     secondary_uom_code, process_flag,
                                     template_id, transaction_type,
                                     set_process_id, shelf_life_days,
                                     location_control_code,
                                     ----INVENTORY_ITEM_FLAG,
                                     min_minmax_quantity,
                                     max_minmax_quantity,
                                     expense_account,
                                     must_use_approved_vendor_flag,
                                     days_late_receipt_allowed,
                                     list_price_per_unit, attribute1,
                                     attribute2, attribute3,attribute4,attribute5,
                                     attribute6, attribute7, attribute8, attribute9,
                                     attribute10, attribute11, attribute12,attribute13,
                                     attribute14,attribute15
                                    )
                             VALUES (v_master_org_id, i.item_description,
                                     i.item_long_desc, (i.new_item_code),
                                     (i.segment2
                                     ), (i.segment3),
                                     (i.segment4), (i.segment5),
                                     (i.segment6), (i.segment7),
                                     (i.segment8), (i.segment9),
                                     (i.segment10
                                     ), (i.segment11),
                                     (i.segment12), (i.segment13),
                                     (i.segment14
                                     ), (i.segment15),
                                     (i.segment16), (i.segment17),
                                     (i.segment18
                                     ), (i.segment19),
                                     (i.segment20), i.stocking_uom,
                                     i.procurement_uom, 1,
                                     l_template_id, 'CREATE',
                                     1, i.shelf_period,
                                     DECODE (i.stock_locator_flag, 'N', 1, 1),
                                     -------------NVL(R (L (I.Inventory_Planning_Method)),'N'),
                                     i.inv_min,
                                     i.inv_max,
                                     DECODE (NVL (i.expense_item_control, 'N'),
                                             'N', NULL,
                                             i.expense_account
                                            ),
                                     i.approved_supplier_flag,
                                     i.purchasing_lead_days,
                                     i.purchased_list_price, i.attribute1,
                                     i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                    );
                        --------------------------Child Organization ------------------
                        INSERT INTO mtl_system_items_interface
                                    (organization_id, description,
                                     long_description, segment1,
                                     segment2, segment3,
                                     segment4, segment5,
                                     segment6, segment7,
                                     segment8, segment9,
                                     segment10, segment11,
                                     segment12, segment13,
                                     segment14, segment15,
                                     segment16, segment17,
                                     segment18, segment19,
                                     segment20, primary_uom_code,
                                     secondary_uom_code, process_flag,
                                     template_id, transaction_type,
                                     set_process_id, shelf_life_days,
                                     location_control_code,
                                     ----INVENTORY_ITEM_FLAG,
                                     min_minmax_quantity,
                                     max_minmax_quantity,
                                     expense_account,
                                     must_use_approved_vendor_flag,
                                     days_late_receipt_allowed,
                                     list_price_per_unit, attribute1,
                                     attribute2, attribute3,attribute4,attribute5,
                                     attribute6, attribute7, attribute8, attribute9,
                                     attribute10, attribute11, attribute12,attribute13,
                                     attribute14,attribute15
                                    )
                             VALUES (v_child_org_id, i.item_description,
                                     i.item_long_desc, (i.new_item_code),
                                     (i.segment2
                                     ), (i.segment3),
                                     (i.segment4), (i.segment5),
                                     (i.segment6), (i.segment7),
                                     (i.segment8), (i.segment9),
                                     (i.segment10
                                     ), (i.segment11),
                                     (i.segment12), (i.segment13),
                                     (i.segment14
                                     ), (i.segment15),
                                     (i.segment16), (i.segment17),
                                     (i.segment18
                                     ), (i.segment19),
                                     (i.segment20), i.stocking_uom,
                                     i.procurement_uom, 1,
                                     l_template_id, 'CREATE',
                                     1, i.shelf_period,
                                     DECODE (i.stock_locator_flag, 'N', 1, 1),
                                     -------------NVL(R (L (I.Inventory_Planning_Method)),'N'),
                                     i.inv_min,
                                     i.inv_max,
                                     DECODE (NVL (i.expense_item_control, 'N'),
                                             'N', NULL,
                                             i.expense_account
                                            ),
                                     i.approved_supplier_flag,
                                     i.purchasing_lead_days,
                                     i.purchased_list_price, i.attribute1,
                                     i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                    );
                     ELSE
                        --------------------------Child Organization ------------------
                        INSERT INTO mtl_system_items_interface
                                    (organization_id, description,
                                     long_description, segment1,
                                     segment2, segment3,
                                     segment4, segment5,
                                     segment6, segment7,
                                     segment8, segment9,
                                     segment10, segment11,
                                     segment12, segment13,
                                     segment14, segment15,
                                     segment16, segment17,
                                     segment18, segment19,
                                     segment20, primary_uom_code,
                                     secondary_uom_code, process_flag,
                                     template_id, transaction_type,
                                     set_process_id, shelf_life_days,
                                     location_control_code,
                                     min_minmax_quantity,
                                     max_minmax_quantity,
                                     expense_account,
                                     must_use_approved_vendor_flag,
                                     days_late_receipt_allowed,
                                     list_price_per_unit, attribute1,
                                     attribute2, attribute3,attribute4,attribute5,
                                     attribute6, attribute7, attribute8, attribute9,
                                     attribute10, attribute11, attribute12,attribute13,
                                     attribute14,attribute15
                                    )
                             VALUES (v_child_org_id, i.item_description,
                                     i.item_long_desc, (i.new_item_code),
                                     (i.segment2
                                     ), (i.segment3),
                                     (i.segment4), (i.segment5),
                                     (i.segment6), (i.segment7),
                                     (i.segment8), (i.segment9),
                                     (i.segment10
                                     ), (i.segment11),
                                     (i.segment12), (i.segment13),
                                     (i.segment14
                                     ), (i.segment15),
                                     (i.segment16), (i.segment17),
                                     (i.segment18
                                     ), (i.segment19),
                                     (i.segment20), i.stocking_uom,
                                     i.procurement_uom, 1,
                                     l_template_id, 'CREATE',
                                     1, i.shelf_period,
                                     DECODE (i.stock_locator_flag, 'N', 1, 1),
                                     i.inv_min,
                                     i.inv_max,
                                     DECODE (NVL (i.expense_item_control, 'N'),
                                             'N', NULL,
                                             i.expense_account
                                            ),
                                     i.approved_supplier_flag,
                                     i.purchasing_lead_days,
                                     i.purchased_list_price, i.attribute1,
                                     i.attribute2, i.attribute3,i.attribute4,i.attribute5,
                               i.attribute6,i.attribute7,i.attribute8,i.attribute9,
                               i.attribute10,i.attribute11,i.attribute12,i.attribute13,
                               i.attribute14,i.attribute15
                                    );
                     END IF;
                  END IF;
               END IF;
            END;
            UPDATE item_master_stag
               SET flag_process = 'P'
             WHERE entry_id = i.entry_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error := SQLERRM ();
               UPDATE item_master_stag
                  SET flag_process = 'E',
                      error_message = l_error
                WHERE entry_id = i.entry_id;
         END;
      END LOOP;
      COMMIT;

END;

 

 

Import Items:-

This Program helps to Create the Inventory Items from External Application to the Oracle application. This Item Import programs helps to move the records from the Item interface table to the Oracle Base Tables.