Query to get failed records in Invoices Interface in Oracle APPS R12

SELECT aif.invoice_id

              ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND ailf.invoice_line_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'

   --AND aif.source = 'ERS'

           AND aif.status = 'REJECTED'

        UNION

        SELECT aif.invoice_id

      ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND aif.invoice_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICES_INTERFACE'

           --AND aif.source = 'ERS'

   AND aif.status = 'REJECTED'

Query to get GRN Details in Oracle APPS R12

SELECT    

       DISTINCT rsl.to_subinventory

           ,rts.invoice_status_code

   ,phs.org_id

   ,phs.segment1 po_number

   ,rts.transaction_date

           FROM po_vendor_sites_all pvss

               ,po_vendor_sites_all pvss2

               ,po_vendors pvds

               ,po_headers_all phs

               ,po_lines_all pls

               ,po_line_locations_all plls

               ,rcv_shipment_headers rsh

               ,rcv_shipment_lines rsl

               ,inl_ship_lines_all isl

               ,rcv_transactions rts

               ,mtl_system_items_b msi

          WHERE rts.shipment_header_id = rsh.shipment_header_id

            AND rts.po_header_id = phs.po_header_id

            AND rts.po_line_location_id = plls.line_location_id

            AND rts.po_line_id = pls.po_line_id

            AND rts.shipment_header_id = rsl.shipment_header_id

            AND rts.shipment_line_id = rsl.shipment_line_id

            AND rts.lcm_shipment_line_id = isl.ship_line_id(+)

            AND rts.lcm_adjustment_num = isl.adjustment_num(+)

            AND phs.vendor_id = pvds.vendor_id

            AND phs.vendor_site_id = pvss.vendor_site_id

            AND rsh.receipt_source_code = 'VENDOR'

            AND rts.source_document_code = 'PO' 

            AND phs.segment1 = '1234567890'           --- Purchase Order Number/LPO Number

            --AND NVL (rts.invoice_status_code, 'NA') IN ('PENDING', 'REJECTED')

            AND (   (rts.transaction_type = 'RECEIVE')

                 OR (    'N' = 'Y'

                     AND rts.transaction_type = 'CORRECT'

                     AND rts.timecard_id IS NOT NULL

                     AND EXISTS (

                            SELECT 1

                              FROM apps.rcv_transactions prt

                             WHERE prt.transaction_type = 'RECEIVE'

                               AND prt.source_document_code = 'PO'

                               AND prt.transaction_id =

                                                     rts.parent_transaction_id

                               AND prt.invoice_status_code = 'INVOICED'

                               AND prt.last_update_date <= rts.creation_date)

                    )

                )

            AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')

            AND phs.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')

            AND NVL (plls.consigned_flag, 'N') <> 'Y'

            AND pvss2.vendor_site_id = NVL (pvss.default_pay_site_id, pvss.vendor_site_id)

            AND NVL (rsh.asn_type, ' ') <> 'ASBN'

            AND rts.po_release_id IS NULL

            AND rsl.item_id = msi.inventory_item_id

            AND rts.organization_id = msi.organization_id

            AND rts.transaction_date >= '01-JAN-2024'

Query to get POR is Enabled at Supplier Site and PO Level in Oracle APPS R12

SELECT DISTINCT apss.pay_on_code por_supp_site

               ,pha.pay_on_code por_po

           FROM ap_suppliers aps

       ,ap_supplier_sites_all apss

   ,po_headers_all pha

          WHERE aps.vendor_id = apss.vendor_id

            AND pha.vendor_id = apss.vendor_id

            AND pha.vendor_site_id = apss.vendor_site_id

            AND pha.segment1 = '1234567890'  --- Purchase Order Number/LPO Number

Query to get Inventory Item details in R12

SELECT MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE

      ,MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY

  ,MTL_SYSTEM_ITEMS_B.CREATION_DATE

  ,MTL_SYSTEM_ITEMS_B.CREATED_BY

  ,MTL_SYSTEM_ITEMS_B.DESCRIPTION

  ,MTL_SYSTEM_ITEMS_B.SEGMENT1

  ,MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE

  ,MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT

  ,MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.UNIT_VOLUME

  ,MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID

  ,MTL_SYSTEM_ITEMS_B.ITEM_TYPE

  ,MTL_PARAMETERS.MASTER_ORGANIZATION_ID

  ,MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID

  ,MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE

  ,MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE

  ,MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID

  ,MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE

  ,MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG

  ,MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG

  ,MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG

  ,MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE

  ,MTL_SYSTEM_ITEMS_B.FIXED_ORDER_QUANTITY

  ,MTL_SYSTEM_ITEMS_B.ORDER_COST

  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG

  ,MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG 

  FROM MTL_SYSTEM_ITEMS_B

      ,MTL_PARAMETERS 

 WHERE MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID 

   AND MTL_PARAMETERS.MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID                             

Query to get GL Journal Details in R12

SELECT JEL.JE_HEADER_ID

      ,JEL.JE_LINE_NUM

  ,JEL.LAST_UPDATE_DATE

      ,JEL.LAST_UPDATED_BY

  ,JEL.LEDGER_ID

  ,JEL.CODE_COMBINATION_ID

      ,JEL.PERIOD_NAME

  ,JEL.STATUS

  ,JEL.CREATION_DATE

  ,JEL.CREATED_BY

  ,JEL.ENTERED_DR

  ,JEL.ENTERED_CR

  ,JEL.ACCOUNTED_DR

  ,JEL.ACCOUNTED_CR

  ,JEL.REFERENCE_1

  ,JEL.REFERENCE_2

  ,JEL.REFERENCE_3

  ,JEL.REFERENCE_4

  ,JEL.REFERENCE_5

  ,JEL.REFERENCE_6

  ,JEL.REFERENCE_7

  ,JEL.REFERENCE_8

  ,JEL.REFERENCE_9

  ,JEL.REFERENCE_10

  ,JEL.GL_SL_LINK_ID

  ,JEH.JE_CATEGORY

  ,JEH.JE_SOURCE

  ,JEH.NAME

  ,JEH.CURRENCY_CODE

  ,JEH.POSTED_DATE

  ,JEB.NAME

  ,PRDS.START_DATE

  ,PRDS.END_DATE

  ,GL.LEDGER_CATEGORY_CODE

  ,PRDS.ADJUSTMENT_PERIOD_FLAG 

  FROM GL_JE_LINES JEL

      ,GL_JE_HEADERS JEH

      ,GL_JE_BATCHES JEB

      ,GL_PERIOD_STATUSES PRDS

  ,GL_LEDGERS GL 

 WHERE JEL.JE_HEADER_ID = JEH.JE_HEADER_ID 

   AND JEH.ACTUAL_FLAG = 'A' 

   AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID (+) 

   AND JEL.PERIOD_NAME = PRDS.PERIOD_NAME 

   AND JEL.LEDGER_ID = PRDS.SET_OF_BOOKS_ID 

   AND JEL.LEDGER_ID = GL.LEDGER_ID 

   AND PRDS.APPLICATION_ID = 101 

   AND JEH.CURRENCY_CODE <> 'STAT' 

   AND JEB.STATUS = 'P' 

Query to get Sales Invoice(Receivables) Details in R12

SELECT RA_CUST_TRX_TYPES_ALL.TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CREATION_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.CREATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER,

       RA_CUSTOMER_TRX_LINES_ALL.SET_OF_BOOKS_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_CREDITED,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_STANDARD_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_SELLING_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,

       RA_CUSTOMER_TRX_LINES_ALL.TAX_RATE,

       RA_CUSTOMER_TRX_LINES_ALL.UOM_CODE,

       RA_CUSTOMER_TRX_LINES_ALL.ORG_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_ALL.TRX_NUMBER,

       RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID,

       RA_CUSTOMER_TRX_ALL.TRX_DATE,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID,

       OE_ORDER_HEADERS_ALL.SOLD_TO_CONTACT_ID,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.TERM_ID,

       RA_CUSTOMER_TRX_ALL.TERM_DUE_DATE,

       RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID,

       RA_CUSTOMER_TRX_ALL.PRINTING_ORIGINAL_DATE,

       RA_CUSTOMER_TRX_ALL.PURCHASE_ORDER,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_DATE,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE,

       RA_CUSTOMER_TRX_ALL.TERRITORY_ID,

       RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,

       RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG,

       RA_CUSTOMER_TRX_ALL.PAYING_SITE_USE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL1.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.STATUS_TRX,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE_TYPE,

       RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE,

       RA_CUST_TRX_TYPES_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID,

       OE_ORDER_HEADERS_ALL.PAYMENT_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE,

       OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE,

       OE_ORDER_LINES_ALL.ITEM_TYPE_CODE,

       OE_ORDER_LINES_ALL.SHIPMENT_NUMBER,

       OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID,

       OE_ORDER_LINES_ALL.PROJECT_ID,

       OE_ORDER_LINES_ALL.TASK_ID,

       OE_ORDER_LINES_ALL.OPTION_NUMBER,

       OE_ORDER_LINES_ALL.COMPONENT_NUMBER,

       OE_ORDER_LINES_ALL.SOURCE_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID,

       RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE11

  FROM RA_CUSTOMER_TRX_LINES_ALL  RA_CUSTOMER_TRX_LINES_ALL1,

       RA_CUSTOMER_TRX_LINES_ALL,

       RA_CUSTOMER_TRX_ALL,

       RA_CUST_TRX_TYPES_ALL,

       OE_ORDER_HEADERS_ALL,

       OE_ORDER_LINES_ALL

 WHERE     RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID =

           RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID

       AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = 'Y'

       AND RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID =

           RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID

       AND RA_CUSTOMER_TRX_ALL.ORG_ID = RA_CUST_TRX_TYPES_ALL.ORG_ID

       AND RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID =

           RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID(+)

       AND RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT IN

               ('ORDER ENTRY', 'INTERCOMPANY')

       AND TO_NUMBER (RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6) =

           OE_ORDER_LINES_ALL.LINE_ID(+)

       AND OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID(+)

       AND RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE >=

           TO_DATE (:P_LAST_EXTRACT_DATE, 'MM/D D/YYYY HH24:MI:SS')

Query to get AP Invoice details in R12

SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.INVOICE_ID

  ,AP_INVOICES_ALL.INVOICE_NUM

  ,AP_INVOICES_ALL.INVOICE_CURRENCY_CODE

  ,AP_INVOICES_ALL.VENDOR_ID

  ,AP_INVOICES_ALL.VENDOR_SITE_ID

  ,AP_INVOICES_ALL.INVOICE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.INVOICE_RECEIVED_DATE

  ,AP_INVOICES_ALL.CREATION_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID

  ,PO_HEADERS_ALL.SEGMENT1

  ,PO_LINES_ALL.LINE_NUM

  ,PO_HEADERS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.LAST_UPDATE_DATE

  ,PO_HEADERS_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.ITEM_DESCRIPTION

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.WFAPPROVAL_STATUS

  ,AP_INVOICES_ALL.PAYMENT_STATUS_FLAG

  ,AP_INVOICES_ALL.CANCELLED_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE

  ,PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID

  ,AP_INVOICES_ALL.TERMS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE

  ,AP_INVOICES_ALL.EXCHANGE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICES_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE

  ,AP_INVOICES_ALL.EXCHANGE_RATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICES_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED

  ,AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT

  ,AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT

  ,CASE WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL 

        THEN PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

ELSE AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

   END

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE

  ,PO_VENDORS.RECEIPT_REQUIRED_FLAG

  ,PO_VENDORS.INSPECTION_REQUIRED_FLAG

  ,PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG

  ,PO_VENDORS.LAST_UPDATE_DATE

  ,AP_INVOICES_ALL.SOURCE

  ,AP_INVOICES_ALL.PROJECT_ID

  ,AP_INVOICES_ALL.TASK_ID

  ,AP_INVOICES_ALL.EXPENDITURE_TYPE

  ,AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID

  ,AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE

  ,AP_INVOICES_ALL.PAYMENT_CROSS_RATE

  ,AP_INVOICES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.LINE_NUMBER

  ,AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICE_LINES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.LEGAL_ENTITY_ID

  FROM AP_INVOICE_DISTRIBUTIONS_ALL

      ,AP_INVOICE_LINES_ALL

  ,AP_INVOICES_ALL

  ,PO_LINES_ALL

  ,PO_HEADERS_ALL

  ,PO_VENDORS

  ,PO_VENDOR_SITES_ALL

  ,PO_DISTRIBUTIONS_ALL

  ,(SELECT AP_INVOICES_ALL.INVOICE_ID 

      FROM AP_INVOICES_ALL 

WHERE AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/Y YYY HH24:MI:SS') 

UNION 

SELECT AP_INVOICE_LINES_ALL.INVOICE_ID 

  FROM AP_INVOICE_LINES_ALL 

WHERE AP_INVOICE_LINES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM /DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_HOLDS_ALL.INVOICE_ID 

  FROM AP_HOLDS_ALL 

WHERE AP_HOLDS_ALL.RELEASE_LOOKUP_CODE IS NULL 

   AND AP_HOLDS_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/D D/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL

      ,AP_INVOICE_DISTRIBUTIONS_ALL

  ,PO_HEADERS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID

UNION 

    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, PO_LINES_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID 

   AND PO_LINES_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/DD/YYYY H H24:MI:SS') 

   ) TEMP 

 WHERE AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID 

   AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+) 

   AND AP_INVOICES_ALL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = TEMP.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 

Query to get Oracle Workflow attribute values

SELECT name attribute_name

      ,NVL(text_value, NVL(TO_CHAR(number_value), TO_CHAR(date_value))) VALUE

  FROM wf_item_attribute_values

 WHERE item_type = 'XXITEMTP'

   AND item_key = 'XXITEMTP001'

Query to get Errored Activities in Oracle Workflow

SELECT wav.display_name            activity

      ,wias.activity_result_code activity_result

  ,wias.error_name            error_name

  ,wias.error_message         error_message

  ,wias.error_stack           error_stack

   FROM wf_item_activity_statuses wias

       ,wf_process_activities      wpa

   ,wf_activities_vl           wav

   ,wf_activities_vl           wav1

   ,wf_items wi

  WHERE wias.item_type = wi.item_type

    AND wias.item_key = wi.item_key

    AND wias.activity_status = 'ERROR'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wav.name

    AND wpa.activity_item_type = wav.item_type

    AND wpa.process_name = wav1.name

    AND wpa.process_item_type = wav1.item_type

    AND wpa.process_version = wav1.version

    AND wi.parent_item_type = 'XXITEMTP'

    AND wi.parent_item_key = 'XXITEMTP001'

    AND wi.begin_date >= wav.begin_date

    AND wi.begin_date < NVL(wav.end_date, wi.begin_date + 1)

  ORDER BY wias.execution_time

Query to get Oracle Workflow Deferred Activities

SELECT COUNT(1)

      ,wias.item_type

  FROM apps.wf_items wi

      ,apps.wf_item_activity_statuses wias

  ,apps.wf_process_activities wpa

 WHERE wi.item_type = wias.item_type

   AND wi.item_key = wias.item_key

   AND wi.end_date IS NULL

   AND wias.end_date IS NULL

   AND wias.activity_status = 'DEFERRED'

   --AND wias.item_type = 'REQAPPRV'

   AND wias.item_type = wi.item_type

   AND wpa.instance_id(+) = wias.process_activity

GROUP BY wias.item_type

Query to check the Workflow Agent Listeners and their Statuses

SELECT fsc.component_name

      ,dq.owner

      ,dq.queue_table

      ,fsc.correlation_id

  FROM applsys.fnd_svc_components fsc

      ,applsys.wf_agents          wa

  ,dba_queues dq

 WHERE fsc.inbound_agent_name || fsc.outbound_agent_name = wa.name

   AND dq.owner || '.' || dq.name = wa.queue_name

   AND fsc.component_type LIKE 'WF_%AGENT%'

Query to get List of all Errored Oracle Workflow Activities for a given Item Type/Item Key

SELECT wac.display_name          activity

      ,wias.activity_result_code activity_result

  ,wias.error_name           error_name

  ,wias.error_message        error_message

  ,wias.error_stack          error_stack

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.activity_status = 'ERROR'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

 ORDER BY wias.execution_time

SQL Query to get Activity Statuses for all Workflow Activities of a given Item Type and Item Key in Oracle APPS

SELECT *

  FROM(

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items         wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

UNION ALL

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses_h wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

  WHERE wias.item_type = 'XXITEMTP'

    AND wias.item_key = 'XXITEMTP001'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wac.name

    AND wpa.activity_item_type = wac.item_type

    AND wpa.process_name = wap.name

    AND wpa.process_item_type = wap.item_type

    AND wpa.process_version = wap.version

    AND wi.item_type = 'XXITEMTP'

    AND wi.item_key = wias.item_key

    AND wi.begin_date >= wac.begin_date

    AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

)

  ORDER BY begin_date

          ,execution_time

Query to get Notifications sent by a given Oracle Workflow

SELECT wn.notification_id nid

      ,wn.context

      ,wn.group_id

      ,wn.status

      ,wn.mail_status

      ,wn.message_type

      ,wn.message_name

      ,wn.access_key

      ,wn.priority

      ,wn.begin_date

      ,wn.end_date

      ,wn.due_date

      ,wn.callback

      ,wn.recipient_role

      ,wn.responder

      ,wn.original_recipient

      ,wn.from_user

      ,wn.to_user

      ,wn.subject

  FROM wf_notifications wn

      ,wf_item_activity_statuses wias

 WHERE wn.group_id = wias.notification_id

   AND wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

Query to Select all Oracle Workflow Items for a given Item Type

SELECT item_type

      ,item_key

  ,TO_CHAR(begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,TO_CHAR(end_date, 'DD-MON-RR HH24:MI:SS') end_date

  ,root_activity activity

  FROM apps.wf_items

 WHERE item_type = 'XXITEMTP'

   AND end_date IS NULL

 ORDER BY to_date(begin_date, 'DD-MON-YYYY hh24:mi:ss')

 ASC

How to add LOV to the Manual Adjustment reasons in Comp off Plan Type ?(Doc ID 2483891.1)

 Use ORA_ANC_COMP_ADJRSN from Manage Common Lookup 

Query to get Employee Absence details in Oracle APPS

SELECT /*+ rule*/

       paaf.person_id

  ,paaf.assignment_id

  ,flv.meaning absence_category

  ,c.name absence_type

  ,flv1.meaning absence_reason

  ,abs.date_notification

  ,abs.date_projected_start

  ,abs.date_projected_end

  ,abs.date_start

  ,abs.date_end

  ,abs.absence_days

  ,abs.attribute1

  ,abs.attribute2

  ,abs.attribute3

  ,abs.attribute4

  ,abs.attribute5

  ,abs.attribute6

  ,abs.attribute7

  ,abs.attribute8

  ,abs.attribute9

  ,abs.attribute10

  ,abs.attribute11

  ,abs.attribute12

  ,abs.attribute13

  ,abs.attribute14

  ,abs.attribute15

  ,abs.attribute16

  ,abs.attribute17

  ,abs.attribute18

  ,abs.attribute19

  ,abs.attribute20

  ,b.admission_code

  ,b.admission_date

  ,b.amendment_date

      ,b.amendment_reason

  ,b.concatenated_segments

  ,b.contact_grade

      ,b.contact_type

  ,b.discharge_date

  ,b.disease_name

      ,b.hospital_name

  ,b.leave_amended

  ,b.leave_salary_paid

      ,b.physician_approved_accident

  ,b.physician_name

      ,b.resumption_date

  ,b.context

  FROM per_absence_attendances abs

      ,per_absence_attendance_types c

      ,per_abs_attendance_reasons d

      ,per_absence_attendances_dfv b

      ,fnd_lookup_values flv1

      ,fnd_lookup_values flv

      ,per_all_assignments_f paaf          

 WHERE abs.person_id = paaf.person_id

   AND abs.ROWID = b.row_id

   AND abs.absence_attendance_type_id = c.absence_attendance_type_id(+)

   AND abs.abs_attendance_reason_id = d.abs_attendance_reason_id(+)

   AND d.name = flv1.lookup_code(+)

   AND flv1.lookup_type(+) = 'ABSENCE_REASON'

   AND flv.lookup_code(+) = c.absence_category

   AND flv.lookup_type(+) = 'ABSENCE_CATEGORY'

ORDER BY abs.person_id

        ,absence_type

,date_start 

DESC

Oracle HRMS, Absence Management & PMS Interview Questions

• What are the tables impacted while creating a new Employee?
• What are the mandatory fields require to create an employee In Employee Screen?
• What are the base tables of HRMS?
• What is the impact on base tables while Rehiring an Employee?
• What is the impact on base tables while Terminating an Employee?
• What is the process to create Business Group?
• What are the mandatory KFFs & DFFs for Creating Business Group?
• What are EIT & SITs and difference between them ?
• What is bursting file do in XML publisher Report?
• What are the ways to create an XML Publisher Report?
• Absence Management Setup Steps and what are the Fast Formulas used in Absence Management?
• What are the main Absence Management Tables?
• What are the Performance Management setup steps?
• What are the Appraisal Document statuses?
• What are the APIs used in HRMS and what are mandatory parameters for API?
• What is object version number, p_validate and what is the use?
• Payroll basic tables and what is costing and payroll setup.
• What is Payroll run process and what is the use of element link.
• How to attach Element to all employees?

Query to get Internal Bank Details in R12

 SELECT items.item_number

      ,itemcats.category_id

  ,itemcats.sequence_number

  ,itemcats.alt_item_cat_code

  ,ect.description

  ,ect.category_name

  FROM egp_item_cat_assignments itemcats

      ,egp_category_sets_b catsets

  ,egp_system_items_b items

  ,inv_org_parameters org

  ,egp_categories_tl ect

 WHERE catsets.category_set_id = itemcats.category_set_id

   AND items.inventory_item_id = itemcats.inventory_item_id

   AND org.organization_id = items.organization_id

   AND itemcats.category_id=ect.category_id

   AND ( ( catsets.control_level = 1

AND itemcats.organization_id = org.master_organization_id 

     )

  OR ( catsets.control_level = 2

AND itemcats.organization_id = org.organization_id

    ) 

)

Query to get Vendor Bank Detail in Oracle Fusion

SELECT DISTINCT psv.vendor_id

      ,psv.vendor_name

  ,pssa.vendor_site_id

      ,accts.ext_bank_account_id

      ,bank.party_name bank_name

      ,branch.bank_branch_name

      ,accts.bank_account_type

      ,accts.country_code

      ,accts.bank_account_name

      ,accts.attribute1 ifsc_code

      ,accts.bank_account_num

      ,accts.currency_code

  FROM poz_suppliers_v psv

      ,poz_supplier_sites_all_m pssa

      ,iby_pmt_instr_uses_all uses

      ,iby_external_payees_all payee

      ,iby_ext_bank_accounts accts

      ,hz_parties bank

      ,ce_bank_branches_v branch

 WHERE psv.vendor_id      = pssa.vendor_id

   AND psv.party_id       = payee.payee_party_id

   AND payee.ext_payee_id = uses.ext_pmt_party_id

   AND uses.instrument_id = accts.ext_bank_account_id

   AND accts.bank_id      = bank.party_id

   AND accts.branch_id    = branch.branch_party_id

   --AND payee.payment_function = 'PAYABLES_DISB'

Query to get Asset Details in Oracle Fusion

SELECT asset_number

      ,fab.asset_id

      ,fat.description

      ,fab.asset_type

      ,fcb.segment1||'-'||fcb.segment2 category

      ,fab.tag_number

      ,fb.book_type_code

      ,fabc.book_class

      ,fb.cost

      ,fb.recoverable_cost

      ,(SELECT SUM(deprn_reserve)

  FROM fa_deprn_detail

         WHERE asset_id = fab.asset_id

   AND deprn_run_date = (SELECT MAX(deprn_run_date)

   FROM fa_deprn_detail

  WHERE asset_id = fab.asset_id

) depriciation_cost

  FROM fa_additions_b fab

      ,fa_additions_tl fat

      ,fa_categories_b fcb

      ,fa_books fb

      ,fa_book_controls fabc

 WHERE 1=1

   AND fab.asset_id = fat.asset_id 

   AND fat.language = USERENV('LANG')

   AND fcb.category_id=fab.asset_category_id

   AND fb.asset_id=fab.asset_id

   AND fb.depreciate_flag = 'NO'

   AND fab.asset_number = 123456789

SQL Query to get Item Details in Oracle Fusion

SELECT esi.inventory_item_id

      ,esi.item_number

      ,esi.organization_id

      ,iop.organization_code  

      ,esi.description

      ,uomt.unit_of_measure

      ,esi.item_type

      ,esi.enabled_flag

      ,esi.planner_code planner

      ,esi.safety_stock_planning_method

  FROM inv_org_parameters iop

      ,egp_system_items  esi

      ,inv_units_of_measure_tl uomt

      ,inv_units_of_measure_b uomb

 WHERE 1=1

   AND iop.organization_id = esi.organization_id

   AND uomb.uom_code = esi.primary_uom_code

   AND uomt.unit_of_measure_id (+)= uomb.unit_of_measure_id

   AND uomt.language = 'US'

   AND esi.item_number = 'ITEM_NUM_001'

SQL Query to get the Contexts for Formula Type in Oracle Fusion

SELECT ft.base_formula_type_name

      ,base_context_name 

  FROM ff_contexts_vl con

      ,ff_ftype_context_usages fcu

  ,ff_formula_types_vl ft

 WHERE ft.formula_type_id = ft.formula_type_id

   AND fcu.formula_type_id = ft.formula_type_id

   AND con.context_id = fcu.context_id

   AND ft.base_formula_type_name = :p_formula_type_name

SQL Query to get the contexts of a DBI in Oracle Fusion

SELECT dbi.user_name

      ,base_context_name

  FROM ff_database_items_vl dbi

      ,ff_user_entities_vl userent

      ,ff_routes_vl routes

      ,ff_route_context_usages rcu

      ,ff_contexts_vl con

 WHERE dbi.user_entity_id = userent.user_entity_id

   AND userent.route_id = routes.route_id

   AND routes.route_id = rcu.route_id

   AND con.context_id = rcu.context_id

   AND dbi.base_user_name = :p_dbi_name

SQL Query to find all DBI's based on ANC Tables in Oracle Fusion

SELECT d.base_user_name dbi_name

      ,d.data_type dbi_data_type

      ,(SELECT listagg ('<' || rcu.sequence_no || ',' || c.base_context_name || '>', ',')

   within GROUP (ORDER BY rcu.sequence_no)

          FROM ff_route_context_usages rcu

              ,ff_contexts_b c

         WHERE rcu.route_id = r.route_id

           AND rcu.context_id = c.context_id

   ) route_context_usages

  FROM ff_database_items_b d

      ,ff_user_entities_b u

      ,ff_routes_b r

 WHERE UPPER(d.base_user_name) LIKE 'ANC%'

   AND d.user_entity_id = u.user_entity_id

   AND r.route_id = u.route_id

CLOB column splitting(one empty row and other actual data) data into multiple rows in Oracle Fusion

When working with CLOB columns in Fusion Reports we may face challenges like data splitting into multiple rows for the CLOB Column.

We faced challenges when generating report in Excel Output. The Description column showing one empty row followed by actual data as shown below.

Data Model Query:

When pasted in Excel File:

To overcome this issue convert clob value into Character using TO_CHAR function.

Ex: TO_CHAR(DESCRIPTION) 
here DESCRIPTION is a CLOB Column 

Thousand Separator((,)comma) in e-Text Report

AMOUNT column want to print thousand separator in Oracle Fusion e-Text Report use  Number, ###,###.00

Number, ###,###.00

R, ' '

AMOUNT