Dear Friends , I have gone through a recent unique requirement to get to display Purchase Category hierarchy. You can get up to level 4 but can be extended up to your business requirement . You can comment or post your experience  following the details . SQL query to display Purchase Category hierarchy SELECT DISTINCT ( SELECT category_name FROM POR_BROWSE_CATEGORIES_TL pbct WHERE pbct.category_id = picpl.level1_cat_id ) Level_1_Name ,( SELECT category_name FROM POR_BROWSE_CATEGORIES_TL pbct WHERE pbct.category_id = picpl.level2_cat_id ) Level_2_Name ,( SELECT category_name FROM POR_BROWSE_CATEGORIES_TL pbct WHERE pbct.category_id = picpl.level3_cat_id ) Level_3_Name ,( SELECT category_name FROM EGP_CATEGORIES_VL ecv WHERE picpl.level4_cat_id = ecv.category_id ) Level_4_Name FROM POR_ITEM_CAT_PARENT_LEVELS picpl WHERE picpl.type = 'SOURCING' ORDER BY Level_1_Name ,Level_2_Name ,Level_3_Name ,Level_4_Name Happy Learnin

Dear Friends ,  Below Query can be useful to find the Unit cost of Item at costing Module . We can have  FOR Unit cost OF Material under specified Val Unit code AND Cost Book SELECT TO_CHAR ( CICT . total_cost , 'fm99999999d00' ) UNIT_COST FROM CST_ITEM_COSTS_V CICT , CST_TRANSACTIONS CT WHERE 1 = 1 AND CICT . ITEM_NUMBER = :P_Item_Number AND CICT . TRANSACTION_ID = CT . TRANSACTION_ID AND TRUNC ( CICT . PREVIOUS_COST , 6 ) = ( SELECT TRUNC ( UNIT_COST_AVERAGE , 6 ) FROM cst_perpavg_cost WHERE PERPAVG_COST_ID = ( SELECT MIN ( PERPAVG_COST_ID ) FROM cst_perpavg_cost WHERE TRANSACTION_ID = CICT . TRANSACTION_ID ) ) AND CICT . VAL_UNIT_CODE LIKE 'XXXXXX' AND TO_CHAR ( CT . TRANSACTION_DATE , 'DD-MM-YYYY' ) BETWEEN TO_CHAR ( :P_START_DATE , 'DD-MM-YYYY' ) AND TO_CHAR ( :P_END_DATE , 'DD-MM-YYYY' ) And If you want to have the Query to fetch Current Item cost

Sometime , We need to know specific relationship between some important tables . Lets come to know one such important SQL Query to know the relationship between Transfer Order, Pick Slip Number, and Supply Orchestration SQL Query can be used to show the link between a Transfer Order, Pick Slip Number, and Supply Orchestration Belo is the screenshot for instance    The Required output We need from the report is below  The Query used for above requirement is below  SELECT  a.pick_slip_number ,   b.source_header_number transfer_order_number,   c.supply_order_number ,   c.supply_order_reference_number FROM   INV_PICK_SLIP_NUMBERS a ,   WSH_DELIVERY_DETAILS b ,   DOS_SUPPLY_HEADERS c ,   INV_TRANSFER_ORDER_HEADERS d,   INV_TRANSFER_ORDER_LINES e WHERE 1 =1   AND a.PICK_SLIP_NUMBER = 11022   AND a.pick_slip_batch_id = b.batch_id   AND b.source_header_number = d.header_number   AND d.header_id = e.header_id   AND e.source_header_id = c.header_id Thank you , Please do comment and share here .

 Dear Friends , There is one small requirement we face mostly while developing the BI Report in fusion cloud Application . How to enable the Multiple choice list of parameter for the BIP Report such as below and How to make necessary change in the Data model of SQL Query . The above Choice List needs to be setup at Parameter Section Like Below  If you have such requirement for your report , Use the COALESCE function for that column in your  SQL Query .  Example : you can write something like below this will help to retrieve all Items as well as Null values at the same times . It will fulfil all such scenarios . AND (    ESI.ITEM_NUMBER=(Coalesce(NULL, :P_ITEM_NUMBER))     OR ESI.ITEM_NUMBER IN (:P_ITEM_NUMBER)   )  You can also use such as below , All scenarios will be handled  (COALESCE(null, : P_ITEM_NUMBER ) is null) OR ( ESI.ITEM_NUMBER  IN (: P_ITEM_NUMBER )) Hope above solution will help to achieve the goal of Multiple choice list of Parameter . You can comment if you have

Dear Friend ,    Lets Understand how to Configure an approval rule to route a notification using a supervisory hierarchy using Business Process Management(BPM)  Lets take an Business use case that The business requirement is that an invoice needs to be approved by managers based on the amount limits set for each approver.  Example  An invoice amount of more than $10,000 dollars needs the approval of two groups. The first group is made up of the Manager, Senior Manager, and Finance Manager. The invoice can be approved by the next group of approvers. That approval group consists of the CFO, COO, and CEO. If the invoice amount is less than $10,000 dollars it only needs to be approved by the Manager, Senior Manager, and Finance Manager depending on the invoice amount.  Lets consider Below Table The first three approvals are done by the managers of the invoice requestor using supervisory hierarchy.  Final approval has to be done by any one of the approver out of 3 approvers from appr

How to Customize Configure an approval rule to route a notification to a group of approvers using Business Process Management(BPM) The business requirement is that all invoices related to the operating expenditures incurred needs to be reviewed by two approvers. The invoice first needs to be reviewed and approved by the Payables Department head and then needs to be approved by the Finance Manager. The nature of the expenditure is based on the natural account entered on the invoice. Example: An invoice is received for the expenditure incurred related to a marketing event: If the account code combination entered on the invoice contains the natural accounts ranges from 512001 to 512099 then invoice requires the approval from the Payables Department head and the Finance Manager. If the account code combination entered on the invoice does not contain the natural accounts ranges from 512001 to 512099 then invoice requires the approval from the Payables Department head. Solution:

 Dear Friends , Here is some important query to Extract all BIP reports which are currently scheduled in ESS jobs in Oracle Application Cloud . You can create a BIP Report and get the details . SELECT r.requestid ,r.REQUESTEDSTART ,r.processstart ,r.processend ,r.executable_status ,v.name property_name ,vtwo.value report_params ,vthree.value report_url --,extractValue(xmlparse(content v.value), '/PARAMETER1') as param1 ,regexp_replace(regexp_substr(v.value, '<PARAMETER1>.*</PARAMETER1>'), '(<PARAMETER1>)(.*)(</PARAMETER1>)', '\2') to_email ,regexp_replace(regexp_substr(v.value, '<PARAMETER2>.*</PARAMETER2>'), '(<PARAMETER2>)(.*)(</PARAMETER2>)', '\2') cc ,regexp_replace(regexp_substr(v.value, '<PARAMETER5>.*</PARAMETER5>'), '(<PARAMETER5>)(.*)(</PARAMETER5>)', '\2') message ,regexp_replace(regexp_substr(v.value,

Dear Viewers ,   Lets Understand with different case studies How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM) .   Case Study 1 #Configure an approval rule to route a notification to a requestor  It covers the most common business use cases of invoice approval in a Payables Department.   Each business use case provides how to configure the invoice approval rules, using various participants, invoice attributes, and list builders such as:   • Single Approver   • Approval Group   • Supervisor Hierarchy   • Job level Hierarchy   • Position Hierarchy   The information presented in this document is intended to help various users (Payables business users, Approval workflow administrators and Consultants) who are involved in the configuration of invoice approval policy to meet the various business use cases of invoice approvals in a Payables Department.  Note: 1) Refer to the Appendix section for navigation details for configuring in

In this video ,you'll be learning about creating receipts in Oracle FUsion Cloud Procurement Work Area. Brief about self-service receiving process. The requester enters a requisition online, which is then approved. The goods should be delivered to the delivered-to location as set on the requisition preferences at the time the requisition was raised. These details are transferred to the purchase order when the requisition is approved. Once the delivery has been made by the supplier, creating the receipt assists in the process of the suppliers receiving payment.

 Dear friends , Lets understand the Important Oracle Cloud Procurement Tables and its feature in details . PO_ACTION_HISTORY PO_AGENTS PO_AGENT_ACCESSES PO_AGENT_ASSIGNMENTS PO_APPROVED_SUPPLIER_LIST PO_ASL_ATTRIBUTES PO_ASL_DOCUMENTS PO_ASL_STATUSES PO_ATTRIBUTE_VALUES PO_ATTRIBUTE_VALUES_DRAFT PO_ATTRIBUTE_VALUES_TLP PO_ATTRIBUTE_VALUES_TLP_DRAFT PO_ATTR_VALUES_ARCHIVE PO_ATTR_VALUES_INTERFACE PO_ATTR_VALUES_TLP_ARCHIVE PO_ATTR_VALUES_TLP_INTERFACE PO_BCEA_DATA PO_BUYER_ASSIGNMENT_RULES PO_COMMUNICATION_GT PO_CONFIG_COMPONENTS PO_CONFIG_COMPONENTS_ARCHIVE PO_CONFIG_COMPONENTS_DRAFT PO_CO_TEMPLATES_B PO_CO_TEMPLATES_TL PO_CO_TEMPLATE_ATTR_VALUES PO_DISTRIBUTIONS_ALL PO_DISTRIBUTIONS_ARCHIVE_ALL PO_DISTRIBUTIONS_DRAFT_ALL PO_DISTRIBUTIONS_GT PO_DISTRIBUTIONS_INTERFACE PO_DOCUMENT_TOTALS_GT PO_DOCUMENT_TYPES_ALL_B PO_DOCUMENT_TYPES_ALL_TL PO_DOC_STYLE_HEADERS PO_DOC_STYLE_LINES_B PO_DOC_STYLE_LINES_TL PO_DOC_STYLE_VALUES PO_EVENT_AUDIT PO_GA_ORG_ASSIGNMENTS PO_GA_ORG_ASSIGNMENTS_ARCHIVE