How to join Projects and Customers tables

SELECT proj.segment1 project#, proj.NAME proj_name,
       hc.account_number customer_account_number#
  FROM pa_projects_all proj, pa_project_customers pc, hz_cust_accounts hc
 WHERE 1 = 1
   AND proj.project_id = pc.project_id
   AND pc.customer_id = hc.cust_account_id

Query to check before Disabling any Accounts in oracle

Below Query will help to see if any transactions are in process/pending if we cant to disable any accounts in oracle  

SELECT *
  FROM xla_ae_headers xah, xla_ae_lines xal
 WHERE 1 = 1
   AND xal.ae_header_id = xah.ae_header_id
   AND xah.gl_transfer_status_code = 'N'
   AND xal.code_combination_id IN (
          SELECT code_combination_id
            FROM gl_code_combinations_kfv
           WHERE segment3 IN
                    (500101,
                     500111,
                     800161,
                     800201,
                     850101,
                     850121,
                     850141,
                     850161,
                     850231
                    ))

Why Do Receipts Exist Without Receipt Lines/Transactions?

Why Do Receipts Exist Without Receipt Lines/Transactions?

Issue

Why do Receipt Numbers (rcv_shipment_headers records) exist when there are no Receipt Lines (rcv_shipment_lines) or Transactions (rcv_transactions) associated with the Receipt Number?

Solution

It is correct functionality that Receipt Numbers can be Saved without corresponding Receipt Lines.  The existence of Receipt Numbers (Receipt Headers) without Receipt Lines does not cause any problems so those Receipts Numbers can be ignored or can be utilized for Add To Receipt functionality for adding to the receipt at later time.

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

Records in rcv_shipment_headers may exist without a corresponding rcv_shipment_lines record when transactions were processed using Receiving Open Interface functionality (including iSupplier Portal for creation of ASNs, receiving data populated via EDI and 3rd Party software). Also, when using RCV: Processing Mode=Immediate (or Batch), Receipt Header (rcv_shipment_headers) is saved before Receiving Transaction Processor processes the transaction; so, if the transaction fails (or does not process at all), rcv_shipment_headers record will exist without corresponding rcv_shipment_lines records. By design, Oracle software does not delete Receipt Headers that are created when using RCV: Processing Mode=Immediate (or Batch.)

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

The following steps can be used to created Receipt Headers without Receipt lines using RCV: Processing Mode=On-line:

    Purchasing Responsibility (or equivalent) > Receiving > Receipts

    Query using any criteria

    Select a line then deselect the line (or skip this Step and proceed to Step 4)

    Click on Receipt Header window

    Save

Please note that Receipt Headers (with or without associated Receipt Lines) can be used for subsequent transactions by choosing (Add to Receipt).

If you would like you can use below data fix

Datafix for Orphan Receipt or Shipment Headers (rcv_shipment_headers)

Identification script:

select *

from   rcv_shipment_headers rsh

where  receipt_source_code in ('VENDOR','CUSTOMER')

and trunc(creation_date) like '%AUG-15%'

and    not exists

           (select 1

            from   rcv_shipment_lines rsl

            where  rsl.shipment_header_id = rsh.shipment_header_id)

and    not exists

           (select 1

            from rcv_transactions rt

            where rt.shipment_header_id = rsh.shipment_header_id)

and    not exists

           (select 1

            from rcv_transactions_interface rti

            where  rti.shipment_header_id = rsh.shipment_header_id

            and    (rti.processing_status_code in ('RUNNING','PENDING')

                    or

                    nvl(rti.validation_flag,'N') = 'N' ));

Datafix:

create table orphan_rsh_bk as

select *

from   rcv_shipment_headers rsh

where  receipt_source_code in ('VENDOR','CUSTOMER')

and trunc(creation_date) like '%AUG-15%'

and    not exists

           (select 1

            from   rcv_shipment_lines rsl

            where  rsl.shipment_header_id = rsh.shipment_header_id)

and    not exists

           (select 1

            from rcv_transactions rt

            where rt.shipment_header_id = rsh.shipment_header_id)

and    not exists

           (select 1

            from rcv_transactions_interface rti

            where  rti.shipment_header_id = rsh.shipment_header_id

            and    (rti.processing_status_code in ('RUNNING','PENDING')

                    or

                    nvl(rti.validation_flag,'N') = 'N' ));

delete from  rcv_shipment_headers rsh

where  receipt_source_code in ('VENDOR','CUSTOMER')

and trunc(creation_date) like '%AUG-15%'

and    not exists

           (select 1

            from   rcv_shipment_lines rsl

            where  rsl.shipment_header_id = rsh.shipment_header_id)

and    not exists

           (select 1

            from rcv_transactions rt

            where rt.shipment_header_id = rsh.shipment_header_id)

and    not exists

           (select 1

            from rcv_transactions_interface rti

            where  rti.shipment_header_id = rsh.shipment_header_id

            and    (rti.processing_status_code in ('RUNNING','PENDING')

                    or

                    nvl(rti.validation_flag,'N') = 'N' ));

Order is not eligible for booking. Check workflow status for this order.

If Sales Order is not able to book and if you see below Error

Order is not eligible for booking. Check workflow status for this order.

Then execute below script the order can be booked

DECLARE
   v_headerid   NUMBER;

   CURSOR cr
   IS
      SELECT oeh.header_id
        FROM oe_order_headers_all oeh
       WHERE oeh.order_number = '791137';                        --order in issue

   l_org_id     NUMBER := 204;                                        --OU ID get org id form above
BEGIN
   mo_global.set_policy_context ('S', l_org_id);

   FOR rs IN cr
   LOOP
      v_headerid := rs.header_id;
      apps.wf_engine.startprocess ('OEOH', TO_CHAR (v_headerid));
   END LOOP;
END;