Oracle Fusion : Inventory Item Manufacturer Name & Manufacturer Part Number SQL Query

Important Note: It is possible that Inventory Item has multiple Manufacturer and Part Number, With respect to your business need you can fetch any No. of Manufacturer details from below query using rec_order value.

select 

(SELECT manufacturer_name

          FROM (  SELECT hp.party_name manufacturer_name,

                         a.tp_item_number manfr_part_num,

                         a.tp_item_desc description,

                         eirb.inventory_item_id,

                         eirb.creation_date,

                         ROWNUM rec_order

                    FROM egp_trading_partner_items a,

                         hz_parties hp,

                         egp_item_relationships_b eirb

                   WHERE     a.tp_type = 'MANUFACTURER'

                         AND hp.party_id = a.trading_partner_id

                         AND a.tp_item_id = eirb.tp_item_id

                         AND eirb.item_relationship_type = 'MFG_PART_NUM'

                         AND eirb.inventory_item_id = esib.inventory_item_id ---300000001940364

                ORDER BY eirb.creation_date ASC)

         WHERE rec_order = 1)

          MANUFACTURER_NAME,

       (SELECT manfr_part_num

          FROM (  SELECT hp.party_name manufacturer_name,

                         a.tp_item_number manfr_part_num,

                         a.tp_item_desc description,

                         eirb.inventory_item_id,

                         eirb.creation_date,

                         ROWNUM rec_order

                    FROM egp_trading_partner_items a,

                         hz_parties hp,

                         egp_item_relationships_b eirb

                   WHERE     a.tp_type = 'MANUFACTURER'

                         AND hp.party_id = a.trading_partner_id

                         AND a.tp_item_id = eirb.tp_item_id

                         AND eirb.item_relationship_type = 'MFG_PART_NUM'

                         AND eirb.inventory_item_id = esib.inventory_item_id ---300000001940364

                ORDER BY eirb.creation_date ASC)

         WHERE rec_order = 1)

          MANUFACTURER_PART_NUM

from FROM egp_system_items_b esib

  

Oracle Fusion BIP Report - How to register custom BIP report as ESS Job

 

Purpose: In this session, we learn how to register custom BIP report as ESS Schedule Process.

Step1: Navigate to Fusion Application > Go to Setting and Actions > Setup and Maintenance

Step2: Select Task > Click on Search

Step3: Search panel enter Manage%Enter%Schedual%Job  > Search > Select "Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications"

Step4: Click on "+" button

Step5: Select Manage Job Definitions tab > 

- Enter Display Name = 'AP Invoice Report'

- Name = 'AP Invoice Report'

- Path = /oracle/apps/ess/custom/

- Description = 'AP Invoice Report'

- Job Application Name = FscmEss

- Job Type = 'BIPJobType'

- Report Id= /Custom/Demo/APInvoiceReport.xdo 

- Allow Multiple Pending Submissions = False

Step6: Navigate to Manage List of Values Sources tab > Click on '+' button

- Application = Payable

- User List of value source name = Invoice_Num

- Description = Invoice_Num

- LOV Type = User Defined

- SQL Query = Select your LOV query which you defined in Data Model LOV

- Click on Save and Close.

Step7: LOV created 

Step8: Navigate to Manage Job Definitions tab > Select bottom side 'AP Invoice Report: Parameter tab' > Click on "+" button

Step9: Enter Parameter details and assign LOV as per attached screenshots > Click on Save and Close

Step10: Now Close Save and Close Job Definition.

Step11: You can see now the ESS Job Created Successfully.

Step12: Navigate to Tools > Click on Schedule Process.

Step13: Search AP%Invoice%Report 

- Now we can able to see the registered report.

Step14: Select Parameter & Submit

Step15: You can see the ESS job Status is Succeeded.

Step16: You can download Report output from the output tab.

Oracle Fusion: How to design BIP Report using Data Model and RTF Template & Register ESS Job

 In this session, we learn to step by step process of designing oracle BIP Report using the RTF template.

Step1: How to Create Data Model - LOV - Parameter - Assign LOV to Parameter

https://chetanrajputoracleapps.blogspot.com/2020/11/oracle-fusion-bip-report-how-to-create.html

Step2: How to design RTF Template & Assign Template to Data Model & Design a Report

https://chetanrajputoracleapps.blogspot.com/2020/11/oracle-fusion-bip-report-how-to-design.html

Step3: Oracle Fusion BIP Report - How to register custom BIP report as ESS Job

https://chetanrajputoracleapps.blogspot.com/2020/11/oracle-fusion-bip-report-how-to.html