Thursday, August 20, 2015

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' ));

No comments: