Oracle SCM : RFQ (Request For Quotation) Query

Here we have the query to get the RFQ (Request For Quotation) details, like RFQ No, Supplier details, RFQ date, BID Number, Quote Submission status, Awarded Status..


/* Formatted on 13/12/2015 10:40:26 (QP5 v5.126.903.23003) */
SELECT   assa.VENDOR_ID SUPPLIER_ID,
         PBP.TRADING_PARTNER_NAME SUPPLIER_NAME,
         PAH.DOCUMENT_NUMBER RFQ_NO,
         PADSL.DISPLAY_NAME RFQ_OUTCOME,
         PAH.OPEN_BIDDING_DATE RFQ_DATE,
         PBH.bid_number,
         CASE WHEN PBH.bid_number IS NOT NULL THEN 'Yes' ELSE 'No' END
            Quote_sbumitted,
         DECODE (PBH.AWARD_STATUS, 'AWARDED', 'Yes', 'No') AWARD_STATUS
  FROM   PON_AUCTION_HEADERS_ALL PAH,
         PON_AUC_DOCTYPES PAD,
         PO_ALL_DOC_STYLE_LINES PADSL,
         pon_bidding_parties pbp,
         PON_BID_HEADERS PBH,
         AP_SUPPLIER_SITES_ALL assa
 WHERE       1 = 1
         AND PAD.DOCTYPE_ID = PAH.DOCTYPE_ID
         AND PAD.INTERNAL_NAME = 'REQUEST_FOR_QUOTE'
         AND PAH.AUCTION_STATUS IS NOT NULL
         AND PAH.AUCTION_STATUS <> 'DRAFT'
         AND PAH.STYLE_ID = PADSL.STYLE_ID(+)
         AND PAH.CONTRACT_TYPE = PADSL.DOCUMENT_SUBTYPE(+)
         AND PAH.AUCTION_HEADER_ID = PBP.AUCTION_HEADER_ID
         AND PBP.AUCTION_HEADER_ID = PBH.AUCTION_HEADER_ID(+)
         AND PBP.TRADING_PARTNER_ID = PBH.TRADING_PARTNER_ID(+)
         AND PBP.vendor_site_id = assa.vendor_site_id
     -- AND PAH.DOCUMENT_NUMBER='31001'




Enjoy and Stay Well !! :)

Oracle SCM: Procure to Pay(P2P) Query

Last post we have tried to understand the high level process flow of P2P cycle along with table effected by this process. Today we will try to have a SQL query for the same.

Sometime customer (Specially Contract Holders) wants to a have report to monitor their contract budget and expenses. This report basically should have all details for a blanket release, like

    1. Contract Details ( Contract Start and End date, Contract Agreed Amount.. )
    2. PO Release Number & Amount.
    3. Receipt Amount and Quantity for a Item under a release.
    4. Invoice Amount for a received Item.
    5. Payment Amount. ( Payment is done in Item or Line level, so payment amount always will be in         Invoice Number wise)
    6. Sometime they want to see the Work Order Information as well if any WO attached to it. 

In summary , this report will give PO amount, Receipt Amount , Invoice Amount and Payment Amount for each release. Its a combine report from PO and AP module. Here the query.



/* Formatted on 07/12/2015 09:56:30 (QP5 v5.126.903.23003) */
SELECT   PO_HEADERS_ALL.TYPE_LOOKUP_CODE PO_TYPE,
         PO_HEADERS_ALL.SEGMENT1 PO_NUMNER,
         PO_HEADERS_ALL.AGENT_ID BUYER_ID,
         PO_HEADERS_ALL.VENDOR_ID,
         PO_HEADERS_ALL.VENDOR_SITE_ID,
         PO_HEADERS_ALL.START_DATE CONTRACT_START_DATE,
         PO_HEADERS_ALL.END_DATE CONTRACT_END_DATE,
         PO_HEADERS_ALL.AMOUNT_LIMIT CONTRACT_AMOUNT_LIMIT,
         PO_HEADERS_ALL.COMMENTS PO_DESCRIPTION,
         PO_HEADERS_ALL.CREATION_DATE PO_CREATION_DATE,
         PO_HEADERS_ALL.APPROVED_DATE PO_APPROVED_DATE,
         PO_HEADERS_ALL.REVISION_NUM REVISION_NUM,
         DECODE (PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID,
                 NULL,
                 NVL (PO_HEADERS_ALL.AUTHORIZATION_STATUS, 'INCOMPLETE'),
                 NVL (PO_RELEASES_ALL.AUTHORIZATION_STATUS, 'INCOMPLETE'))
            AUTHORIZATION_STATUS,
         PO_LINE_LOCATIONS_ALL.CLOSED_CODE PO_LINE_CLOSED_CODE,
         PO_HEADERS_ALL.ORG_ID,
         PO_LINES_ALL.ITEM_ID PRODUCT_ID,
         PO_LINES_ALL.LINE_NUM PO_LINE_NUM,
         PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM,
         PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID,
         PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,
         PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID,
         PO_DISTRIBUTIONS_ALL.DELIVER_TO_PERSON_ID REQUESTER_ID,
         CASE
            WHEN PO_DISTRIBUTIONS_ALL.REQ_HEADER_REFERENCE_NUM IS NULL
            THEN
               PO_REQUISITION_HEADERS_ALL.SEGMENT1
            ELSE
               PO_DISTRIBUTIONS_ALL.REQ_HEADER_REFERENCE_NUM
         END
            PO_REQUISITION_NUM,
         PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,
         PO_REQUISITION_LINES_ALL.URGENT_FLAG,
         po_requisition_headers_all.CREATION_DATE PR_CREATION_DATE,
         PO_REQUISITION_HEADERS_ALL.APPROVED_DATE PR_APPROVED_DATE,
         (SELECT   APPROVED_DATE
            FROM   PO_HEADERS_ARCHIVE_ALL
           WHERE       REVISION_NUM = 0
                   AND APPROVED_FLAG = 'Y'
                   AND po_header_id = PO_HEADERS_ALL.po_header_id)
            PO_APPROVED_DATE_ORG,
         PO_RELEASES_ALL.RELEASE_NUM PO_RELEASE_NUM,
         PO_RELEASES_ALL.APPROVED_DATE PO_RELEASE_DATE,
         PO_RELEASES_ALL.AUTHORIZATION_STATUS PO_RELEASE_STATUS,
         PO_RELEASES_ALL.APPROVED_DATE PO_RELEASE_APPROVED_DATE,
         RCV_TRANSACTIONS.TRANSACTION_TYPE,
         RCV_TRANSACTIONS.UNIT_OF_MEASURE,
         PO_LINES_ALL.UNIT_PRICE PO_LINE_UNIT_PRICE,
         PO_HEADERS_ALL.CURRENCY_CODE PO_CURRENCY_CODE,
         NVL (PO_LINES_ALL.QUANTITY, PO_LINE_LOCATIONS_ALL.QUANTITY)
            QUANTITY_ORDERED,
         PO_DISTRIBUTIONS_ALL.QUANTITY_CANCELLED,
         RCV_SHIPMENT_HEADERS.RECEIPT_NUM,
         RCV_TRANSACTIONS.TRANSACTION_DATE RECEIPT_DATE,
         NVL (RCV_TRANSACTIONS.QUANTITY, 0) RECEIPT_QUANTITY,
         NVL (RCV_TRANSACTIONS.QUANTITY, 0) * RCV_TRANSACTIONS.PO_UNIT_PRICE
            RECEIPT_DOC_AMOUNT,
         AP_INVOICES_ALL.INVOICE_NUM,
         AP_INVOICES_ALL.INVOICE_CURRENCY_CODE,
         AP_INVOICES_ALL.INVOICE_RECEIVED_DATE,
         AP_INVOICES_ALL.INVOICE_DATE,
         AP_INVOICES_ALL.CREATION_DATE INVOICE_CREATION_DATE,
         NVL (AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED, 0)
            INVOICED_QUANTITY,
         NVL (AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT, 0) INV_DOC_AMOUNT,
         NVL (AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT, 0) INV_AMOUNT,
         AP_INVOICES_ALL.PAYMENT_STATUS_FLAG,
         --AP_INVOICES_ALL.AMOUNT_PAID,
         AP_INVOICE_PAYMENTS_ALL.AMOUNT PAID_AMOUNT,
         AP_CHECKS_ALL.CHECK_NUMBER,
         TO_CHAR (TRUNC (AP_CHECKS_ALL.CHECK_DATE)) PAYMENT_DATE,
         PO_REQUISITION_LINES_ALL.wip_entity_id,
         PO_REQUISITION_LINES_ALL.wip_operation_seq_num OPERATION_SEQ_NUM,
         WIP_DISCRETE_JOBS.OWNING_DEPARTMENT WO_OWNING_DEPARTMENT_ID
  FROM   PO_HEADERS_ALL,
         PO_LINES_ALL,
         PO_DISTRIBUTIONS_ALL,
         PO_LINE_LOCATIONS_ALL,
         PO_RELEASES_ALL,
         PO_REQ_DISTRIBUTIONS_ALL,
         PO_REQUISITION_LINES_ALL,
         PO_REQUISITION_HEADERS_ALL,
         (SELECT   *
            FROM   RCV_TRANSACTIONS
           WHERE   RCV_TRANSACTIONS.TRANSACTION_TYPE IN
                         ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT', 'MATCH'))
         RCV_TRANSACTIONS,
         RCV_SHIPMENT_HEADERS,
         RCV_SHIPMENT_LINES,
         AP_INVOICE_DISTRIBUTIONS_ALL,
         AP_INVOICE_LINES_ALL,
         AP_INVOICES_ALL,
         AP_INVOICE_PAYMENTS_ALL,
         AP_CHECKS_ALL,
         WIP_DISCRETE_JOBS
 WHERE   PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
         AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID
         AND PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID =
               PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
         AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =
               PO_RELEASES_ALL.PO_RELEASE_ID(+)
         AND PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID =
               PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID(+)
         AND PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID =
               PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID(+)
         AND PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID =
               PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
               RCV_TRANSACTIONS.PO_DISTRIBUTION_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
               AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
               AP_INVOICES_ALL.INVOICE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
               AP_INVOICE_LINES_ALL.INVOICE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER =
               AP_INVOICE_LINES_ALL.LINE_NUMBER(+)
         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
               RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID(+)
         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =
               RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID(+)
         AND RCV_TRANSACTIONS.SHIPMENT_LINE_ID =
               RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID(+)
         AND AP_INVOICES_ALL.INVOICE_ID =
               AP_INVOICE_PAYMENTS_ALL.INVOICE_ID(+)
         AND AP_INVOICE_PAYMENTS_ALL.CHECK_ID = AP_CHECKS_ALL.CHECK_ID(+)
         AND PO_REQUISITION_LINES_ALL.wip_entity_id =
               WIP_DISCRETE_JOBS.wip_entity_id(+)


Enjoy and Stay Well !! :)

Oracle EAM: WO Along With PO Details Query

Some users have a requirement to have the data for WO and PO together.

1. WO Information.
2. WO Operation
3. Project
4. Resource
5. GL Account
6. Asset
7. PTW Information
8. PO - If the WO is having PO.


Here is the query for that.

/* Formatted on 22/11/2015 11:59:30 (QP5 v5.126.903.23003) */
SELECT   a.ORGANIZATION_ID,
         a.WIP_ENTITY_ID,
         b.OPERATION_SEQ_NUM,
         b.DESCRIPTION OP_DESC,
         b.FIRST_UNIT_START_DATE OP_SCHEDULE_START_DATE,
         b.LAST_UNIT_COMPLETION_DATE OP_SCHEDULE_END_DATE,
         (b.LAST_UNIT_COMPLETION_DATE - b.FIRST_UNIT_START_DATE) * 24
            WO_OP_Duration,
         f.ACTUAL_START_DATE OP_ACTUAL_START_DATE,
         f.ACTUAL_END_DATE OP_ACTUAL_END_DATE,
         (a.SCHEDULED_COMPLETION_DATE - a.SCHEDULED_START_DATE) * 24
            WO_Duration,
         a.PROJECT_ID,
         a.TASK_ID,
         a.PM_SCHEDULE_ID,
         a.STATUS_TYPE WO_STATUS_ID,
         h.USER_DEFINED_STATUS_ID WO_USER_DEFINED_STATUS_ID,
         a.CLASS_CODE WIP_Account,
         a.MATERIAL_ACCOUNT MATERIAL_ACCOUNT_ID,
         a.NOTIFICATION_REQUIRED Scaffolding_Required_Flag,
         a.TAGOUT_REQUIRED Insulation_Required_Flag,
         b.ATTRIBUTE2 Permit_Number,
         b.ATTRIBUTE3 Permit_Type,
         b.ATTRIBUTE4 PTW_Status,
         b.ATTRIBUTE6 PTW_DATE,
         c.RESOURCE_ID,
         c.USAGE_RATE_OR_AMOUNT Quantity_Required,
         g.EMPLOYEE_NUMBER,
         g.FULL_NAME EMPLOYEE_NAME,
         PO.PR_NUMBER,
         PO.PR_LINE,
         PO.PR_REQUEST_DATE,
         PO.REQUESTER_NUMNER,
         PO.REQUESTER_NAME,
         PO.PR_STATUS,
         PO.PO_NUMBER,
         PO.PO_LINE,
         PO.RELEASE_NUM,
         PO.PO_CREATION_DATE,
         PO.PO_STATUS,
         PO.ITEM_ID,
         PO.QUANTITY_REQUESTED,
         PO.UNIT_PRICE,
         PO.QUANTITY_ONHAND,
         PO.QUANTITY_RECEIVED,
         PO.DELIVERY_TO,
         PO.DELIVERY_DATE,
         PO.TOTAL_COST,
         a.CREATION_DATE WO_CREATION_DATE,
         b.CREATED_BY OP_CREATED_BY,
         b.CREATION_DATE OP_CREATION_DATE,
         b.DEPARTMENT_ID OP_DEPARTMENT_ID,
         a.OWNING_DEPARTMENT WO_OWNING_DEPARTMENT,
         cii.INSTANCE_ID ASSET_ID,
         wr.WORK_REQUEST_ID WORK_REQUEST_ID,
         PO.PO_RELEASE_STATUS
  FROM   WIP_DISCRETE_JOBS a,
         WIP_OPERATIONS b,
         WIP_OPERATION_RESOURCES c,
         WIP_OP_RESOURCE_INSTANCES d,
         BOM_RESOURCE_EMPLOYEES e,
         CSI_ITEM_INSTANCES CII,
         WIP_EAM_WORK_REQUESTS WR,
         (SELECT   person_id, full_name, EMPLOYEE_NUMBER
            FROM   PER_ALL_PEOPLE_F
           WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                       AND  EFFECTIVE_END_DATE) g,
         eam_work_order_details h,
         (SELECT   ACTUAL_START_DATE,
                   ACTUAL_END_DATE,
                   WIP_ENTITY_ID,
                   OPERATION_SEQ_NUM,
                   LAST_UPDATE_DATE,
                   TRANSACTION_TYPE,
                   CREATION_DATE
            FROM   EAM_OP_COMPLETION_TXNS
           WHERE   (TRANSACTION_ID, WIP_ENTITY_ID, OPERATION_SEQ_NUM) IN
                         (  SELECT   MAX (TRANSACTION_ID) MAX_TRANSACTION,
                                     WIP_ENTITY_ID,
                                     OPERATION_SEQ_NUM
                              FROM   EAM_OP_COMPLETION_TXNS
                             WHERE   EAM_OP_COMPLETION_TXNS.TRANSACTION_TYPE =
                                        1
                          GROUP BY   WIP_ENTITY_ID, OPERATION_SEQ_NUM)) f,
         (SELECT   porl.wip_entity_id,
                   porl.wip_operation_seq_num OPERATION_SEQ_NUM,
                   porh.segment1 PR_NUMBER,
                   porl.line_num PR_LINE,
                   porh.creation_date PR_Request_date,
                   g.EMPLOYEE_NUMBER Requester_Numner,
                   g.FULL_NAME Requester_NAME,
                   porh.authorization_status PR_STATUS,
                   poh.segment1 PO_NUMBER,
                   pol.line_num PO_LINE,
                   por.RELEASE_NUM,
                   por.authorization_status PO_RELEASE_STATUS,
                   --                   (SELECT   RELEASE_NUM
                   --                      FROM   po_releases_all
                   --                     WHERE   po_release_id = poll.po_release_id)
                   --                      release_num,
                   poh.CREATION_DATE PO_CREATION_DATE,
                   poh.authorization_status PO_STATUS,
                   pol.item_id,
                   NVL (pol.quantity, poll.quantity) Quantity_requested,
                   pol.unit_price,
                   (SELECT   SUM (mohq.TRANSACTION_QUANTITY)
                      FROM   MTL_ONHAND_QUANTITIES mohq
                     WHERE   pol.item_id = mohq.inventory_item_id
                             AND poll.SHIP_TO_ORGANIZATION_ID =
                                   mohq.ORGANIZATION_ID)
                      QUANTITY_ONHAND,
                   poll.QUANTITY_RECEIVED,
                   --poll.SHIP_TO_LOCATION_ID,
                   (SELECT   DESCRIPTION
                      FROM   hr_locations
                     WHERE   LOCATION_ID = poll.SHIP_TO_LOCATION_ID)
                      Delivery_To,
                   rsh.creation_date Delivery_Date,
                   (NVL (pol.unit_price, 0) * NVL (poll.QUANTITY_RECEIVED, 0))
                      TOTAL_COST
            FROM   po_requisition_headers_all porh,
                   PO_REQUISITION_LINES_ALL porl,
                   po_req_distributions_all pord,
                   po_distributions_all pod,
                   po_lines_all pol,
                   po_headers_all poh,
                   po_line_locations_all poll,
                   rcv_shipment_lines rsl,
                   rcv_shipment_headers rsh,
                   po_releases_all por,
                   (SELECT   person_id, full_name, EMPLOYEE_NUMBER
                      FROM   PER_ALL_PEOPLE_F
                     WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                                 AND  EFFECTIVE_END_DATE) g
           WHERE   porh.requisition_header_id = porl.requisition_header_id(+)
                   AND porl.requisition_line_id = pord.requisition_line_id(+)
                   AND pord.distribution_id = pod.req_distribution_id(+)
                   AND pod.po_header_id = pol.po_header_id(+)
                   AND pod.po_line_id = pol.po_line_id(+)
                   AND pol.po_header_id = poh.po_header_id(+)
                   AND pod.po_header_id = poll.po_header_id(+)
                   AND pod.po_line_id = poll.po_line_id(+)
                   AND pod.line_location_id = poll.line_location_id(+)
                   AND pod.po_header_id = rsl.po_header_id(+)
                   AND pod.po_line_id = rsl.po_line_id(+)
                   AND pod.line_location_id = rsl.po_line_location_id(+)
                   AND rsl.shipment_header_id = rsh.shipment_header_id(+)
                   AND porl.to_person_id = g.person_id(+)
                   AND poll.po_release_id = por.po_release_id(+)
                   AND porl.wip_entity_id IS NOT NULL
          UNION ALL
          SELECT   a.WIP_ENTITY_ID,
                   a.OPERATION_SEQ_NUM,
                   c.REQUEST_NUMBER PR_MO_NUMBER,
                   b.line_number LINE_NUMBER,
                   c.CREATION_DATE REQUEST_DATE,
                   -- c.CREATED_BY,
                   g.EMPLOYEE_NUMBER,
                   g.FULL_NAME EMPLOYEE_NAME,
                   (SELECT   MEANING
                      FROM   FND_LOOKUP_VALUES_VL
                     WHERE   LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
                             AND LOOKUP_CODE = c.HEADER_STATUS)
                      STATUS,
                   NULL PO_NUMBER,
                   NULL PO_LINE,
                   NULL RELEASE_NUM,
                   NULL PO_RELEASE_STATUS,
                   NULL PO_CREATION_DATE,
                   NULL PO_STATUS,
                   b.inventory_item_id ITEM_ID,
                   b.quantity QUANTITY_REQUESTED,
                   d.ITEM_COST UNIT_PRICE,
                   (SELECT   SUM (mohq.TRANSACTION_QUANTITY)
                      FROM   MTL_ONHAND_QUANTITIES mohq
                     WHERE   b.INVENTORY_ITEM_ID = mohq.inventory_item_id
                             AND b.ORGANIZATION_ID = mohq.ORGANIZATION_ID)
                      QUANTITY_ONHAND,
                   b.QUANTITY_DELIVERED QUANTITY_RECEIVED,
                   NULL DELIVERY_TO,
                   NULL DELIVERY_DATE,
                   NVL ( (b.quantity * d.ITEM_COST), 0) TOTAL_COST
            FROM   WIP_REQUIREMENT_OPERATIONS a,
                   mtl_txn_request_lines b,
                   mtl_txn_request_headers c,
                   cst_item_costs d,
                   (SELECT   person_id, full_name, EMPLOYEE_NUMBER
                      FROM   PER_ALL_PEOPLE_F
                     WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                                 AND  EFFECTIVE_END_DATE) g
           WHERE       a.WIP_ENTITY_ID = b.TXN_SOURCE_ID(+)
                   AND a.inventory_item_id = b.inventory_item_id(+)
                   AND a.operation_seq_num = b.TXN_SOURCE_LINE_ID(+)
                   AND b.header_id = c.header_id
                   AND b.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID(+)
                   AND b.ORGANIZATION_ID = d.ORGANIZATION_ID(+)
                   AND c.CREATED_BY = g.person_id(+)) PO
 WHERE       a.WIP_ENTITY_ID = b.WIP_ENTITY_ID(+)
         AND b.WIP_ENTITY_ID = c.WIP_ENTITY_ID(+)
         AND b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM(+)
         AND c.WIP_ENTITY_ID = d.WIP_ENTITY_ID(+)
         AND c.OPERATION_SEQ_NUM = d.OPERATION_SEQ_NUM(+)
         AND c.RESOURCE_SEQ_NUM = d.RESOURCE_SEQ_NUM(+)
         AND d.instance_id = e.instance_id(+)
         AND c.WIP_ENTITY_ID = f.WIP_ENTITY_ID(+)
         AND c.OPERATION_SEQ_NUM = f.OPERATION_SEQ_NUM(+)
         AND a.WIP_ENTITY_ID = h.WIP_ENTITY_ID(+)
         AND e.PERSON_ID = g.PERSON_ID(+)
         AND b.WIP_ENTITY_ID = po.WIP_ENTITY_ID(+)
         AND b.OPERATION_SEQ_NUM = po.OPERATION_SEQ_NUM(+)
         AND a.MAINTENANCE_OBJECT_ID = cii.INSTANCE_ID(+)
         AND a.WIP_ENTITY_ID = wr.wip_entity_id(+)
-- AND b.DEPARTMENT_ID=63234
--AND a.WIP_ENTITY_ID=63234

Oracle EAM: WO Operation Details Query

Here is the oracle query to get the EAM work order operation level details.
This query will get you below details in WO Operation level.

1. Work Order Details.
2. WO Operation Details.
3. Asset Details- for which WO has been raised.
4. Project details-  if WO is related to project.
5. Resource details - To whom this WO operation is assigned.
6. PTW Details - Permit information

Here is the query.


/* Formatted on 07/12/2015 15:26:07 (QP5 v5.126.903.23003) */
SELECT   b.WIP_ENTITY_ID,
         b.OPERATION_SEQ_NUM OPERATION_SEQ_NUM,
         b.DESCRIPTION OP_DESC,
         --c.RESOURCE_SEQ_NUM,
         b.FIRST_UNIT_START_DATE OP_SCHEDULE_START_DATE,
         b.LAST_UNIT_COMPLETION_DATE Op_SCHEDULE_END_DATE,
         f.ACTUAL_START_DATE OP_ACTUAL_START_DATE,
         f.ACTUAL_END_DATE OP_ACTUAL_END_DATE,
         b.OPERATION_COMPLETED OPERATION_COMPLETED_FLG,
         c.RESOURCE_ID,
         NVL (i.PROJECT_ID, 0) AS PROJECT_ID,
         NVL (j.INSTANCE_ID, 0) AS ASSET_NUMBER_ID,
         NVL (i.OWNING_DEPARTMENT, 0) AS WO_OWNING_DEPARTMENT_ID,
         NVL (i.STATUS_TYPE, 0) AS WO_STATUS_ID,
         g.employee_number,
         g.full_name EMPLOYEE_NAME,
         d.START_DATE INS_START_DATE,
         d.COMPLETION_DATE INS_COMPLETION_DATE,
         (d.COMPLETION_DATE - d.START_DATE) * 24 Duration,
         b.ATTRIBUTE2 PTW_Number,
         b.ATTRIBUTE3 PTW_Type,
         b.ATTRIBUTE4 PTW_Status,
         TO_DATE (b.ATTRIBUTE6, 'yyyy/mm/dd hh24:mi:ss') PTW_DATE,
         h.USER_DEFINED_STATUS_ID WO_USER_DEFINED_STATUS_ID,
         b.ORGANIZATION_ID,
         b.creation_date WO_OP_CREATION_DATE,
         TO_DATE (b.ATTRIBUTE5, 'yyyy/mm/dd hh24:mi:ss') PTW_STATUS_DATE -- New Column,
  FROM   WIP_OPERATIONS b,
         WIP_OPERATION_RESOURCES c,
         WIP_OP_RESOURCE_INSTANCES d,
         BOM_RESOURCE_EMPLOYEES e,
         eam_work_order_details h,
         WIP_DISCRETE_JOBS I,
         CSI_ITEM_INSTANCES J,
         (SELECT   person_id, full_name, EMPLOYEE_NUMBER
            FROM   PER_ALL_PEOPLE_F
           WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                       AND  EFFECTIVE_END_DATE) g,
         (SELECT   ACTUAL_START_DATE,
                   ACTUAL_END_DATE,
                   WIP_ENTITY_ID,
                   OPERATION_SEQ_NUM,
                   LAST_UPDATE_DATE,
                   TRANSACTION_TYPE,
                   CREATION_DATE
            FROM   EAM_OP_COMPLETION_TXNS
           WHERE   (TRANSACTION_ID, WIP_ENTITY_ID, OPERATION_SEQ_NUM) IN
                         (  SELECT   MAX (TRANSACTION_ID) MAX_TRANSACTION,
                                     WIP_ENTITY_ID,
                                     OPERATION_SEQ_NUM
                              FROM   EAM_OP_COMPLETION_TXNS
                             WHERE   EAM_OP_COMPLETION_TXNS.TRANSACTION_TYPE =
                                        1
                          GROUP BY   WIP_ENTITY_ID, OPERATION_SEQ_NUM)) f
 WHERE       b.WIP_ENTITY_ID = c.WIP_ENTITY_ID(+)
         AND b.OPERATION_SEQ_NUM = c.OPERATION_SEQ_NUM(+)
         AND c.WIP_ENTITY_ID = d.WIP_ENTITY_ID(+)
         AND c.OPERATION_SEQ_NUM = d.OPERATION_SEQ_NUM(+)
         AND c.RESOURCE_SEQ_NUM = d.RESOURCE_SEQ_NUM(+)
         AND d.instance_id = e.instance_id(+)
         AND c.WIP_ENTITY_ID = f.WIP_ENTITY_ID(+)
         AND c.OPERATION_SEQ_NUM = f.OPERATION_SEQ_NUM(+)
         AND e.PERSON_ID = g.PERSON_ID(+)
         AND b.wip_entity_id = i.wip_entity_id
         AND b.WIP_ENTITY_ID = h.WIP_ENTITY_ID(+)
         AND I.ASSET_NUMBER = j.SERIAL_NUMBER(+)
       --AND b.WIP_ENTITY_ID     = 26006

Enjoy and Stay Well !! :)

BI Agent Export Limit

Default number of rows to include in deliveries sent via BI Delivery Agents is 75. Which can be changed Enterprise Manager(EM) page or in instanceconfig.xml file.

1. Change in EM page. 

2. Change in instanceconfig.xml file. 

Need to add this line under <Pivot> and <Table> tag. 

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>


Enjoy and Stay well !! :)

Oracle SCM: Important Tables of P2P Cycle

Here all important tables of Oracle SCM module for P2P (Procure to Pay) Cycle.

Process flow on very high level.

Create ITEM ->  Create PR - > Create Suppliers -> Create PO -> Create Receipts -> Create INVOICE -> Payments -> Create Accounting -> Post to GL.

ITEM/INVENTORY

---------------------------------------------------------------------------------------------------------

Once we create an Item in Oracle, below are the tables/view where data will be populated.

SELECT * FROM MTL_SYSTEM_ITEM_B WHERE SEGMENT1=’ITEM1’;

SELECT * FROM MTL_SYSTEM_ITEM WHERE SEGMENT1=’ITEM1’;

SELECT * FROM MTL_ONHAND_QUANTITIES_DETAILS --- To get the ON HAND quantity of an Item.

PURCHASE REQUISITION

---------------------------------------------------------------------------------------------------------

Once we create a new REQUISITION , below tables will be populated.

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1=’PR Number’

SELECT * FROM PO_ REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=1234

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=3214

SELECT * FROM GL_CODE_COMBINATION WHERE CODE_COMBINATION_ID=12313

SUPPLIERS

---------------------------------------------------------------------------------------------------------

Once we create an Suppliers in Oracle, below are the tables/view where data will be populated.

SELECT * FROM AP_SUPPLIERS WHERE SEGMENT1=’Suppliers1’

SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_ID=1212

SELECT * FROM AP_SUPPLIER_CONTACTS

Below tables also have the same information , these are views in R12 but base table in below versions.

SEELCT * FROM PO_VENDORS WHERE SEGMENT1=’Suppliers1’

SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID=1212

SELECT * FROM PO_VENDOR_CONTACTS

Below tables are TCA(Trading Community Architecture) tables.

SELECT * FROM HZ_PARTIES WHERE PARTY_NAME=’Supplier Name’

SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID=1223

PURCHASE ORDER

---------------------------------------------------------------------------------------------------------

Once we create a new PO , below tables will be populated.

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1=’PO Number’;

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=1212;

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=2123;

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_LINE_LOCATION_ID=343;

RECEIPTS

---------------------------------------------------------------------------------------------------------

SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=’121’

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPENT_HEADER_ID=’233’; ---- To get the how much quantity has been received.

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPENT_HEADER_ID=’233’ --- To get the received and delivered quantity.

INVOICE

---------------------------------------------------------------------------------------------------------

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM=’Invoice Num’;

SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=1212;

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=1212; ---- ACCOUNTING_EVET_ID will be populated once Invoice is validated.

PAYMENTS

---------------------------------------------------------------------------------------------------------

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOIVE_ID=1233;

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOIVE_ID=1233;

SELECT * FROM AP_CHECKS_ALL;

ACCOUNTING

---------------------------------------------------------------------------------------------------------

SELECT * FROM XLA_EVENTS WHERE EVENT_ID=1212;  -- All accounting related events (LIKE VALIDATION) will be stored in this table.

Once we run an accounting event, oracle runs multiple programs behind the screen.

1.       Create Accounting : This program will transfer the data from Sub-ledger to GL interface tables.

2.       Journal Import: This one will move the data from GL interface table to GL  base tables.

SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=1223;

SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=121;

SELECT * FROM GL_INTERFACE WHERE REFERENECE26=’Event Id’;  ----This is GL interface table. Once Journal Import program runs data will move from this table to GL base table. You won’t find any record after that.

GL LEDGERS

---------------------------------------------------------------------------------------------------------

SELECT * FROM GL_JE_BATCHES WHERE NAME =’Batch Name’;  --- We will get this Batch Name from Journal Import program output.

SELECT * FROM GL_JE_HEADERS  WHERE JE_BATCH_ID=123;

SELECT * FROM GL_JE_LINES  WHERE JE_HEADER_ID=434;

GL POSTING

---------------------------------------------------------------------------------------------------------

SELECT * FROM GL_BALANCES;

Enjoy & Stay Well !! :)

  

EBS: Employee Performance Rank

To get the Employee Performance Rank.


/* Formatted on 01/12/2015 11:52:31 (QP5 v5.126.903.23003) */
SELECT   papf.employee_number,
         papf.full_name,
         DECODE (papf.sex,
                 'M',
                 'Male',
                 'F',
                 'Female')
            Gender,
         (SELECT   MAX (ppos.DATE_START)
            FROM   per_periods_of_service ppos
           WHERE   ppos.person_id = paaf.person_id)
            start_date,
         (SELECT   haou.name
            FROM   hr_all_organization_units haou
           WHERE   haou.organization_id = paaf.organization_id)
            Department,
         (SELECT   ppd.segment3 Ref_Ind
            FROM   apps.per_positions pp, apps.per_position_definitions ppd
           WHERE   ppd.position_definition_id = pp.position_definition_id
                   AND pp.position_id = paaf.position_id)
            Ref_Ind,
         (SELECT   ppg.group_name
            FROM   pay_people_groups ppg
           WHERE   ppg.people_group_id = paaf.people_group_id)
            group_name,
         (hr_general.DECODE_GRADE (paaf.grade_id)) SG,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2011'), 'YYYY'))
            Ranking_2011,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2012'), 'YYYY'))
            Ranking_2012,
         (SELECT   hl.meaning rating
            FROM   per_performance_reviews prr, hr_lookups hl
           WHERE       hl.lookup_type = 'PERFORMANCE_RATING'
                   AND prr.performance_rating = hl.lookup_code
                   AND PRR.PERSON_ID = papf.person_id
                   AND TO_CHAR (REVIEW_DATE, 'YYYY') =
                         TO_CHAR (TO_DATE ('01-JAN-2013'), 'YYYY'))
            Ranking_2013
  FROM   apps.per_all_people_f papf, apps.per_all_assignments_f paaf
 WHERE       papf.person_id = paaf.person_id
         AND paaf.primary_flag = 'Y'
         AND papf.current_employee_flag = 'Y'
         AND papf.business_group_id = :business_group_id
         AND SYSDATE BETWEEN paaf.effective_start_date
                         AND  paaf.effective_end_date
         AND SYSDATE BETWEEN papf.effective_start_date
                         AND  papf.effective_end_date;



Enjoy and Stay Well !! :)

EBS: HRMS Pay Element Details Query

To get the Pay Element details.

/* Formatted on 01/12/2015 11:45:57 (QP5 v5.126.903.23003) */
  SELECT   petf.ELEMENT_TYPE_ID "Element_ID",
           petf.element_name "Element Name",
           petf.reporting_name "Reporting Name",
           petf.description "Description",
           pec.classification_name "Classification",
           DECODE (petf.ADDITIONAL_ENTRY_ALLOWED_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Additional Entry Allowed",
           DECODE (petf.ADJUSTMENT_ONLY_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Adjustment Only",
           DECODE (petf.CLOSED_FOR_ENTRY_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Closed For Entry",
           DECODE (petf.MULTIPLE_ENTRIES_ALLOWED_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Multiple Entries",
           DECODE (petf.process_in_run_flag,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Process In Run",
           DECODE (petf.STANDARD_LINK_FLAG,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              "Standard Link",
           DECODE (petf.processing_type,
                   'N',
                   'Non-Recurring',
                   'R',
                   'Recurring')
              "Rec Or Non Rec",
           DECODE (petf.post_termination_rule,
                   'A',
                   'Actual Termination',
                   'F',
                   'Final Close',
                   'L',
                   'Last Standard Process')
              "Termination Rule",
           petf.effective_start_date "Effective Start Date",
           petf.effective_end_date "Effective End Date",
           petf.processing_priority,
           TO_CHAR (petf.creation_date, 'MM/DD/YYYY') creation_date
    FROM   pay_element_types_f petf, pay_element_classifications pec
   WHERE   petf.classification_id = pec.classification_id
ORDER BY   1;



Enjoy and Stay Well !! :)

EBS: Payroll Query

To get the Payroll details .

/* Formatted on 01/12/2015 11:57:50 (QP5 v5.126.903.23003) */
  SELECT   employee_number,
           full_name,
           ptp.period_name,
           pec.classification_name,
           petf.element_name,
           petf.reporting_name,
           DECODE (petf.processing_type,
                   'N',
                   'Non-Recurring',
                   'R',
                   'Recurring')
              processing_type,
           pivf.NAME,
           prrv.result_value pay_value
    FROM   pay_run_result_values prrv,
           pay_run_results prr,
           pay_input_values_f pivf,
           pay_element_classifications pec,
           pay_element_types_f petf,
           per_all_people_f papf,
           per_all_assignments_f paaf,
           pay_assignment_actions paa,
           pay_payroll_actions ppa,
           pay_all_payrolls_f papfp,
           per_time_periods ptp
   WHERE       prrv.input_value_id = pivf.input_value_id
           AND prrv.run_result_id = prr.run_result_id
           AND pivf.NAME = 'Pay Value'
           AND ptp.end_date BETWEEN pivf.effective_start_date
                                AND  pivf.effective_end_date
           AND pivf.element_type_id = petf.element_type_id
           AND pec.classification_name =
                 NVL (:p_classification_name, pec.classification_name)
           AND pec.classification_id = petf.classification_id
           AND petf.element_name = NVL (:p_element_name, petf.element_name)
           AND ptp.end_date BETWEEN petf.effective_start_date
                                AND  petf.effective_end_date
           AND prr.element_type_id = petf.element_type_id
           AND prr.assignment_action_id = paa.assignment_action_id
           AND papf.employee_number =
                 NVL (:p_employee_number, papf.employee_number)
           AND ptp.end_date BETWEEN papf.effective_start_date
                                AND  papf.effective_end_date
           AND papf.person_id = paaf.person_id
           AND paaf.payroll_id = ppa.payroll_id
           AND ppa.effective_date BETWEEN paaf.effective_start_date
                                      AND  paaf.effective_end_date
           AND paaf.primary_flag = 'Y'
           AND paaf.business_group_id = ppa.business_group_id
           AND paaf.business_group_id = ppa.business_group_id
           AND paaf.assignment_id = paa.assignment_id
           AND paa.payroll_action_id = ppa.payroll_action_id
           AND ( (paa.source_action_id IS NULL AND ppa.action_type = 'R')
                OR (paa.source_action_id IS NOT NULL AND ppa.action_type = 'Q'))
           AND ppa.action_status = 'C'
           AND ppa.action_type IN ('Q', 'R')
           AND ppa.business_group_id = :p_business_group_id
           AND ppa.payroll_id = ptp.payroll_id
           AND ppa.time_period_id = ptp.time_period_id
           AND ptp.end_date BETWEEN papfp.effective_start_date
                                AND  papfp.effective_end_date
           AND papfp.payroll_name = NVL (:p_payroll_name, papfp.payroll_name)
           AND papfp.payroll_id = ptp.payroll_id
           AND ptp.period_name = NVL (:p_period_name, ptp.period_name)
           AND pec.classification_name IN ('Earnings', 'Voluntary Deductions')
ORDER BY   1,
           2,
           3,
           4,
           5;




Enjoy and Stay Well !! :)

EBS: Employee Assignment Information Query

To get the Employee Assignment Information.


/* Formatted on 01/12/2015 11:44:04 (QP5 v5.126.903.23003) */
  SELECT   papf.employee_number,
           paaf.assignment_number,
           paaf.effective_start_date,
           paaf.effective_end_date,
           hr_general.decode_organization (paaf.organization_id)
              organization_name,
           hr_general.decode_job (paaf.job_id) job_name,
           hr_general.decode_position_current_name (paaf.position_id)
              position_name,
           hr_general.decode_grade (paaf.grade_id) grade_name,
           hr_general.decode_location (paaf.location_id) location_name,
           hr_general.decode_lookup ('YES_NO', ppg.segment3) Shift_Based,
           ppg.segment7 Work_Type,
           (SELECT   paat.user_status
              FROM   per_assignment_status_types paat
             WHERE   paat.assignment_status_type_id =
                        paaf.assignment_status_type_id)
              assignment_status,
           hr_general.decode_lookup ('EMP_CAT', paaf.employment_category)
              Assignment_Category,
           hr_general.decode_lookup ('EMPLOYEE_CATG', paaf.employment_category)
              Employee_category,
           hr_general.decode_pay_basis (paaf.pay_basis_id) salary_basis_name,
           DECODE (
              paaf.effective_end_date - TO_DATE ('31-DEC-4712'),
              0,
              (SELECT   papfs.employee_number
                 FROM   per_all_people_f papfs
                WHERE   TRUNC (:p_date) BETWEEN papfs.effective_start_date
                                            AND  papfs.effective_end_date
                        AND papfs.current_employee_flag = 'Y'
                        AND papfs.person_id = paaf.supervisor_id
                        AND papfs.business_group_id = paaf.business_group_id),
              NULL
           )
              Supervisor_Number,
           paaf.probation_period,
           hr_general.decode_lookup ('QUALIFYING_UNITS', paaf.probation_unit)
              probation_units,
           paaf.date_probation_end,
           paaf.notice_period,
           hr_general.decode_lookup ('QUALIFYING_UNITS',
                                     paaf.notice_period_uom)
              notice_period_units,
           paaf.normal_hours working_hours,
           paaf.frequency working_freq,
           paaf.time_normal_start,
           paaf.time_normal_finish,
           paaf.internal_address_line,
           hr_general.decode_lookup ('EMP_ASSIGN_REASON', paaf.change_reason)
              Assignment_Change_Reason,
           paaf.primary_flag,
           paaf.manager_flag,
           paaf.ass_attribute1 Group_Basic_Index,
           paaf.ass_attribute2 Base_Country,
           paaf.ass_attribute3 Colaf,
           paaf.ass_attribute4 Company,
           paaf.ass_attribute5 PBS,
           paaf.ass_attribute6 PBS_PIR,
           paaf.ass_attribute16 EBAS_Reduction,
           paaf.ass_attribute12 IBAS_Base_Country,
           paaf.ass_attribute13 IBAS_Host_Colaf,
           paaf.ass_attribute14 Work_Cycle,
           paaf.ass_attribute15 IBAS_Base_Colaf,
           paaf.ass_attribute7 Part_time_Percentage,
           paaf.ass_attribute9 Individual_Car_Ded_Percentage,
           paaf.ass_attribute10 Utilities_Allowance_Override,
           paaf.ass_attribute11 Fixed_Term_Contract_Staff_Ind,
           paaf.business_group_id,
           paaf.assignment_status_type_id,
           paaf.people_group_id,
           paaf.person_id,
           paaf.assignment_id
    FROM   pay_people_groups ppg,
           pay_all_payrolls_f papfp,
           per_all_assignments_f paaf,
           per_all_people_f papf
   WHERE   ppg.people_group_id(+) = paaf.people_group_id
           AND TRUNC (:p_date) BETWEEN papfp.effective_start_date(+)
                                   AND  papfp.effective_end_date(+)
           AND papfp.payroll_id(+) = paaf.payroll_id
           AND paaf.business_group_id = papf.business_group_id
           AND paaf.primary_flag = 'Y'
           AND paaf.person_id = papf.person_id
           AND TRUNC (:p_date) BETWEEN papf.effective_start_date
                                   AND  papf.effective_end_date
           AND papf.current_employee_flag = 'Y'
           AND papf.business_group_id = :p_business_group_id
ORDER BY   papf.employee_number, paaf.effective_start_date;



Enjoy and Stay Well !! :)