Dear Friends

Here are a few important Fusion Quries for BI Reports in Fusion .

The Below query will fetch the Purchase order Extracts with Business Units

SELECT distinct  haot.name "Business Unit"

   , pha.SEGMENT1 "Po Number"

   , PRHA.REQUISITION_NUMBER "Requisition Number"

   , '' "BPA Num"

   , pha.DOCUMENT_STATUS "Po HDR Status"

   , pla.LINE_STATUS "Po LN Status"

   , pda.DESTINATION_TYPE_CODE "PO Destination Type"

       , pha.TYPE_LOOKUP_CODE "Po  Type"

   , pla.LINE_NUM "Line Number"

   , plla.Shipment_num "PO Schedule Num"

   , plla.INSPECTION_REQUIRED_FLAG

   , plla.SHIP_TO_ORGANIZATION_ID

   , pda.distribution_num "PO Distrubution Num"

   , MSIB.ITEM_NUMBER "Item"

   ,pla.ITEM_DESCRIPTION 

   ,pla.QUANTITY "PO Quantity"

   ,rsl.QUANTITY_RECEIVED

   ,rsl.QUANTITY_ACCEPTED

   ,rsl.QUANTITY_DELIVERED

   ,rsl.QUANTITY_SHIPPED

   ,rsl.QUANTITY_RETURNED

   ,rsl.QUANTITY_REJECTED

   ,pda.QUANTITY_BILLED

   ,rsl.SHIPMENT_LINE_STATUS_CODE

      ,rsh.receipt_num PO_receipt_number

  ,mp.ORGANIZATION_CODE

  ,mp.ORGANIZATION_ID

  ,pda.DESTINATION_ORGANIZATION_ID

,aia.invoice_num invoice_number

,aila.LINE_NUMBER "Invoice Line Num"

,aid.MATCH_STATUS_FLAG  "Invoice Header Status"

  FROM po_headers_all pha

      ,po_lines_all pla

  ,PO_LINE_LOCATIONS_ALL plla

      ,EGP_SYSTEM_ITEMS_B MSIB

      ,ap_invoice_distributions_all aid

      ,ap_invoices_all aia

  ,ap_invoice_lines_all aila

      ,rcv_shipment_headers rsh

  ,RCV_SHIPMENT_LINES   rsl

  ,PO_DISTRIBUTIONS_ALL PDA 

  ,POR_REQ_DISTRIBUTIONS_ALL PRDA 

  ,POR_REQUISITION_LINES_ALL PRLA 

  ,POR_REQUISITION_HEADERS_ALL PRHA

      ,HR_ALL_ORGANIZATION_UNITS_F hao

   ,HR_ORGANIZATION_UNITS_F_TL haot

   ,INV_ORG_PARAMETERS MP

 WHERE 1=1

    AND pha.po_header_id=pda.po_header_id

   AND pha.PO_HEADER_id=pla.PO_header_id

   AND pha.PO_HEADER_ID=plla.PO_HEADER_ID

   AND pla.PO_LINE_ID=plla.PO_LINE_ID

   AND Pla.ITEM_ID = MSIB.INVENTORY_ITEM_ID

   AND (pha.PRC_BU_ID=HAO.ORGANIZATION_ID OR pha.REQ_BU_ID=HAO.ORGANIZATION_ID)

   AND aid.po_distribution_id(+)=pda.po_distribution_id

   AND aia.invoice_id(+)=aid.invoice_id

   AND aia.invoice_id=aila.invoice_id(+)

   AND rsl.shipment_Header_id=rsh.shipment_Header_id(+)

     AND pha.PO_HEADER_id=rsl.PO_header_id(+)

   AND pla.PO_LINE_ID=pda.PO_LINE_ID

     AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID(+)

AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID(+)

AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID(+)

AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)

     AND hao.ORGANIZATION_ID = haot.ORGANIZATION_ID

AND hao.EFFECTIVE_START_DATE = haot.EFFECTIVE_START_DATE

AND hao.EFFECTIVE_END_DATE = haot.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN hao.EFFECTIVE_START_DATE AND hao.EFFECTIVE_END_DATE

AND haot.LANGUAGE='US'

   -- AND pha.SEGMENT1='568'

    AND mp.ORGANIZATION_ID=pda.DESTINATION_ORGANIZATION_ID

  -- AND PRHA.REQUISITION_NUMBER='RSE-PR-601'

  AND pha.DOCUMENT_STATUS=:P_PO_Status

  AND haot.ORGANIZATION_ID=:P_BU_ID

  AND  pha.DOCUMENT_STATUS NOT IN ('CANCELLED')

ORDER BY pha.SEGMENT1,pla.LINE_NUM,aila.LINE_NUMBER


Comments

Post a Comment

Popular posts from this blog

How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM)

Image
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
Read more
Image
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 .
Read more
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
Read more