ORA-00257: ARCHIVER ERROR. CONNECT INTERNAL ONLY, UNTIL FREED

Sometime we used to get an error like as below. Due to that we wont be able to login to any BI applications whose metadata schema is connected to database.

Error:

ORA-00257: ARCHIVER ERROR. CONNECT INTERNAL ONLY, UNTIL FREED

1. DAC login error.

2. OBIEE agent wont work. 

3. Wont be able to login DB.

Cause :

Database archive mount point got filled up.

Solution:

Release the space for archive mount point.

Enjoy and Stay Well !! :)

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 !! :)