Meta_Link_Rcv_Transactions

What Are The Different Descriptive Flexfields Used In Receiving And What Is Their Usage?

Doc ID:

Note:312667.1

Applies to:

Oracle Inventory Management - Version: 11.5.2 to 11.5.10
Information in this document applies to any platform.
FORM:RCVRCERC - Enter Receipts GUI
FORM:RCVTXERT - Enter Receiving Transactions GUI
FORM:RCVRCVRC - View Receiving Transactions GUI
FORM:RCVSHESH - Maintain Shipments
FORM:RCVRCMUR - Match Unordered Receipts

Goal

What Are The Different Descriptive Flexfields Used In Receiving And What Is Their Usage?

Solution

There are 4 descriptive flexfields in Receiving Process. These are

• RCV_SHIPMENT_HEADERS
• RCV_SHIPMENT_LINES
• Receiving Transactions
• Receiving Transactions Interface

All of these flexfields are updatable.
USAGE:
1. Enter Receipts Form
In Enter Receipts Form, there are two windows - Header and lines.

In header window the flexfield gets defined on the basis of the profile option "ussgl_option_value".
-  If the value of the profile is "Y", then a descriptive flexfield called PO_REQEXPRESS_LINES will be enabled.
- If the value of the profile is "N", then a descriptive flexfield called RCV_SHIPMENT_HEADERS will be enabled.

In the lines window, Receiving Transactions flexfield is enabled. The data for this flexfield comes from a view called RCV_ENTER_RECEIPTS_V. This view pulls the attributes from the following:
 

Table	Document Type
PO_LINE_LOCATIONS	Purchase Orders
RCV_SHIPMET_LINES	Internal Reqs and Inter-Org Transfers
RCV_SHIPMENT_LINES	ASNs and ASBNs
OE_ORDER_LINES	RMAs

When the receipt is then saved, the Header flexfield goes to RCV_SHIPMENT_HEADERS and line flexfield goes to RCV_TRANSACTIONS_INTERFACE which will eventually land up in RCV_TRANSACTIONS after running Receiving Transaction Processor.
2. Receiving Transactions Form
The flexdfield on Receiving Transactions form is "Receiving Transactions" and the source of this data is from RCV_TRANSACTIONS from the previous RECEIVE or inspection transaction and the final value goes into RCV_TRANSACTIONS_INTERFACE and eventually to RCV_TRANSACTIONS.
3. Manage Shipments Form
There are two flexfields on Manage Shipments Form - Header and Line. The Header comes from
RCV_SHIPMENT_HEADERS and the line comes from RCV_SHIPMENT_LINES (flexfield and
data).

The attributes in RCV_SHIPMENT_LINES get filled from fields SHIP_LINE_ATTRIBUTE1... SHIP_LINE_ATTRIBUTE15 entered in RCV_TRANSACTIONS_INTERFACE while performing Receiving Open Interface(including ASN and ASBN) and from corresponding shipping transactions from Inventory/Order Management for Inter-org and Internal Order Cycles respectively.
4. Match Unordered Receipts Form
The flexfield comes from RCV_TRANSACTIONS and finally goes back to RCV_TRANSACTIONS table.

5. Receiving Transaction Summary Form
The header flexfield comes from RCV_SHIPMENT_HEADERS and the lines flexfield comes from RCV_TRANSACTIONS.

6. Transaction Status Summary Form
The flexfield displayed is from RCV_TRANSACTIONS_INTERFACE (ATTRIBUTE1... ATTRIBUTE15)

trace.sql - Traces a sql statement ensuring that the rows column will be populated
	Doc ID:	Note:246821.1	Type:	DIAGNOSTIC TOOLS
	Last Revision Date:	14-JAN-2004	Status:	PUBLISHED

/*$Header: trace.sql 11.5 246821.1 2003/08/25               rfine aol-bde $*/

SET term off ver off echo off feed off trims on;

/*=============================================================================

trace.sql - Generate a trace file which will then be used as input to the

            tkprof and explain plan. The rows column in the explain plan

            will be populated.

Disclaimer 

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,

PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS

ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT

LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR

PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS

THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR

RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR

EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR

OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE

NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT

RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY

TIME WITHOUT NOTICE.

Limitation of Liability 

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,

SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,

DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN

CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.

ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

 Overview

 --------

    Trace.sql generates a raw trace file which can be used as input to 

    generate a tkprof and explain plan or a Trace Analyzer.

    The sql statement, run from sql*plus, can include bind variables. In

    addition, the rows column in the explain plan will be populated with data. 

 Instructions

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

 1. Copy this whole Note into a text file and name it trace.sql

 2. Replace the default sql statement and enter the statement you want to trace.

 3. You will be prompted for an operating unit. This is relevant only if you have 

    base tables in your sql statement which have an _ALL at the end. If that is not

    the case, comment out the statement in the trace file which refers 

    to the hr_operating_units table.  

 4. If necessary, uncomment the line in order to generate an Event 10046 

    Level 12 trace file, see Metalink Note 171647.1 for details. 

 5. If requested, uncomment out the line in order to generate 

    an Event 10053 trace. 

 6. If requested, uncomment out the line in order to generate 

    an Event 10060 trace.

 7. Modify the trace script in order to declare the bind variables in the sql

    statement. Make sure you define the data type and initial value if known.

    The script comes with 4 default bind variables.

 8. Run trace.sql from apps with no parameters -

    # sqlplus apps/apps

    SQL> START trace.sql;

 9. The trace file will be located in the udump directory.

 Program Notes

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

 1. Always download latest version from Metalink (Note 246821.1).

 2. This script can be used on any Oracle Apps 11i database.

 3. A practical guide in Troubleshooting Oracle ERP Applications Performance

    Issues can be found on Metalink under Note 169935.1

 Parameters

 ----------

    None.

 Caution

 -------

    The sample program in this article is provided for educational purposes

    only and is NOT supported by Oracle Support Services.  It has been tested

    internally, however, and works as documented.  We do not guarantee that it

    will work for you, so be sure to test it in your environment before

    relying on it.

 Portal

 ------

    Abstract: trace.sql - Generates trace file with rows column populated

    Author: Richard Fine

    Date: 25-AUG-03

    Description: Ability to execute a trace file from sql*plus for a 

    sql statement which may include bind variables.

    EMail: richard.fine@oracle.com

    Internal_Only: N

    Keywords: trace script to populate rows column in explain plan

    Metalink_Note: 246821.1

    New_Win: Y

    Product: SQL*Plus script

    Version: 11.5 2003/08/25

=============================================================================*/

SET PAGES 50000 LIN 32000 VER OFF FEED OFF TRIMS ON;

-- Only if multi-org (_ALL) tables are accessed

SELECT organization_id org_id, name

  FROM hr_operating_units;

ACCEPT ORG PROMPT 'enter org_id from list: ';

EXEC FND_CLIENT_INFO.SET_ORG_CONTEXT('&org');

-- Only when tracing with Event 10060

-- drop table kkoipt_table;

-- create table kkoipt_table( c1 number, c2 varchar2(80));

SPOOL trace.txt;

ALTER SESSION SET TIMED_STATISTICS = TRUE;

ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'trace';

ALTER SESSION SET SQL_TRACE = TRUE;

-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') "Begin" FROM DUAL;

SELECT host_name, instance_name FROM v$instance;

SET TERM OFF;

VAR b1 VARCHAR2(30);

VAR b2 NUMBER;

VAR b3 NUMBER;

VAR b4 VARCHAR2(30);

BEGIN

   :b1 := '1234%';

   :b2 := 26161;

   :b3 := NULL;

   :b4 := 'USD';

END;

/

-- ALTER SESSION SET EVENTS '10060 TRACE NAME CONTEXT FOREVER';

select rsh.receipt_num , rsh.shipment_header_id , rsl.to_organization_id,

  od.organization_name, rtxn.currency_code

from

 rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rtxn,

  org_organization_definitions od where (RECEIPT_NUM LIKE :b1) AND (

  rsh.shipment_header_id = rsl.shipment_header_id and RSL.shipment_line_id =

  RTXN.shipment_line_id and RSL.to_organization_id = OD.organization_id and

  RTXN.vendor_id = :b2 and RSL.po_header_id = nvl(:b3, RSL.po_header_id) and

  RTXN.transaction_id = (select min(RTXN1.transaction_id) from

  rcv_transactions RTXN1 where RTXN1.shipment_line_id = RTXN.shipment_line_id)

   and RSL.shipment_line_id = (select min(RSL1.shipment_line_id) from

  rcv_shipment_lines RSL1 where RSL1.shipment_header_id =

  RSL.shipment_header_id) and RTXN.currency_code = :b4);

-- ALTER SESSION SET EVENTS '10060 TRACE NAME CONTEXT OFF';

SET TERM ON;

SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') "End" FROM DUAL;

ALTER SESSION SET SQL_TRACE FALSE;

-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

-- Only when tracing with Event 10060

-- select * from kkoipt_table;

SELECT (SELECT value FROM v$parameter WHERE name = 'user_dump_dest')||'/'||

       (SELECT instance_name FROM v$instance)||

       '_ora_'||

       (SELECT spid FROM v$process WHERE addr = paddr)||

       (SELECT DECODE(value,NULL,NULL,'_'||value) FROM v$parameter WHERE name = 'tracefile_identifier')||

       '.trc' raw_sql_trace

  FROM v$session

 WHERE audsid = USERENV('SESSIONID');

PROMPT

PROMPT TKPROF with Explain Plan and send SPOOL and TKPROF files to Support

-- PROMPT

-- PROMPT Execute Trace Analyzer on same Raw SQL Trace and send output

SPOOL OFF;

SET PAGES 24 LIN 80 VER ON FEED ON TRIMS OFF;

Displayed below are the messages of the selected thread.
Thread Status: Closed
From: Ned Epstein 30-Dec-05 16:38
Subject: MAX(transaction_date) in rc_transactions too general.


MAX(transaction_date) in rc_transactions too general.

I wrote a Purchasing module sql for Oracle apps 11.5.9 below. It is meant to pull off requisition lines for a delivery date. I am basing my delivery information on rows in the rcv_transactions table which I join to po_requisition_lines_all through the line_location_id column. For multiple receivals I need only the oldest one. I placed logic to find the rcv_transactions row with the max transaction_date. However, for some multiple receivals this date and time are the same, hence it pulls in all of the rows because they all equal MAX(transaction_date)! ( see /* Receival Joins and selection criteria */ logic). How can I filter out the all but one of these? Should I base it on max transaction_id instead? Can I base is on both transaction_id and transaction_date?
(please disreagrd tables not invoked as this is a code snippet.)

Regards, Ned

/* Drop Ship: Purchase Requisitions Vendor Sourced */
SELECT *
FROM apps.po_requisition_headers_all prha
, apps.po_requisition_lines_all prla
, apps.mtl_system_items_b msi
, apps.mtl_categories_b mc
, apps.org_organization_definitions ood
, apps.po_action_history pah
, apps.rcv_transactions rt
, apps.hr_locations_all hla
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prha.type_lookup_code = 'PURCHASE'
AND prla.SOURCE_TYPE_CODE = 'VENDOR'
AND prha.CREATION_DATE BETWEEN to_date('01-APR-2005') and to_date('30-JUN-2005')
/* Receival Joins and selection criteria */
AND prla.LINE_LOCATION_ID = rt.PO_LINE_LOCATION_ID
AND rt.transaction_type = 'RECEIVE'
AND rt.transaction_date = (SELECT MAX(rt.transaction_date)
FROM apps.rcv_transactions rt_lookup
WHERE rt_lookup.po_line_location_id = prla.LINE_LOCATION_ID
AND rt_lookup.transaction_type = 'RECEIVE')
/* Action History joins */
AND pah.object_id = prha.requisition_header_id
AND pah.object_type_code = 'REQUISITION'
AND pah.action_code = 'APPROVE'
AND pah.sequence_num = (SELECT MAX(pah_look_up.sequence_num)
FROM apps.po_action_history pah_look_up
WHERE pah_look_up.object_id = prha.requisition_header_id
AND pah_look_up.object_type_code = 'REQUISITION'
AND pah_look_up.action_code = 'APPROVE')

---

From: Oracle, Savitha Gowda 02-Jan-06 02:35
Subject: Re : MAX(transaction_date) in rc_transactions too general.

Hello Ned,

Please post queries related to the Oracle Applications in the Oracle Application Library forum.

Regards,
Savitha

---

From: Frank Kulash 02-Jan-06 19:41
Subject: Re : MAX(transaction_date) in rc_transactions too general.

/*
Hi, Ned,

I'm not familiar with Oracle Apps, but I think I see the problem. The code below uses two tables:
(1) prla, the parent table which may have any number of children in
(2) rt.

For this query, you're not interested in the full rt table, but only in those rows of rt that represent the most recent 'RECEIVE' event for each po_line_location_id. The problem is, when there's a tie for the most recent row, you only want one of the contenders. The code below uses transaction_id as a tie-breaker: if there are two or more rows with the exact same date, only the one with the lowest transaction_id will be considered.

The strategy is this: the main query will use an in-line view that contains only the relevant rows from rt. I have called this in-line view rt_relevant, but you can call it rt (that is, the same name as the actual table) if you prefer. The task of producing rt_relevant from rt is a "top-N query", which can be done using a counter generated either by an analytic function (like ROW_NUMBER, as shown below) or by the ROWNUM pseudo-column. Either way, you can't use the counter in the same sub-query in which it is generated, so you have to nest it in a sub-sub-query.

********** Beginning of Code **********

PROMPT ========== Creating prla ==========

DROP TABLE prla;

CREATE TABLE prla
( line_location_id NUMBER
);

INSERT INTO prla (line_location_id) VALUES (10);
INSERT INTO prla (line_location_id) VALUES (20);
INSERT INTO prla (line_location_id) VALUES (30);
INSERT INTO prla (line_location_id) VALUES (40);
INSERT INTO prla (line_location_id) VALUES (50);
INSERT INTO prla (line_location_id) VALUES (60);

PROMPT ========== Creating rt ==========

DROP TABLE rt;

CREATE TABLE rt
( transaction_id NUMBER
, po_line_location_id NUMBER
, transaction_type VARCHAR2 (10)
, transaction_date DATE
);

INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (11, 10,
'RECEIVE', SYSDATE);
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (21, 20,
'RECEIVE', SYSDATE);
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (22, 20,
'RECEIVE', SYSDATE - 1);
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (31, 30,
'CANCEL', SYSDATE);
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (32, 30,
'RECEIVE', SYSDATE - 1);
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (41, 40,
'RECEIVE', TRUNC (SYSDATE));
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (42, 40,
'RECEIVE', TRUNC (SYSDATE));
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (43, 40,
'RECEIVE', SYSDATE - 1);
INSERT INTO rt (transaction_id, po_line_location_id, transaction_type, transaction_date)
VALUES (51, 50,
'CANCEL', SYSDATE - 1);

SELECT *
FROM rt
ORDER BY
po_line_location_id
, transaction_date
, transaction_id;

-- Continued in next message

---

From: Frank Kulash 02-Jan-06 19:42
Subject: Re : Re : MAX(transaction_date) in rc_transactions too general.

-- code continued from previous message

PROMPT ========== Bad Query ==========
PROMPT Duplicate rows for line_location_id = 40

SELECT *
FROM prla
, rt
WHERE prla.line_location_id = rt.po_line_location_id
AND rt.transaction_type = 'RECEIVE'
AND rt.transaction_date =
( -- Begin subquery to get last date
SELECT MAX (transaction_date)
FROM rt rt_lookup
WHERE rt_lookup.po_line_location_id = prla.line_location_id
AND rt_lookup.transaction_type = 'RECEIVE'
) -- End subquery to get last date
ORDER BY
prla.line_location_id
;

PROMPT ========== Good Query ==========

SELECT *
FROM prla
, ( -- Begin subquery to return relevant rows from rt
SELECT * -- or list of columns, excluding row_num
FROM ( -- Begin subquery to compute row_num
SELECT rt.*
, ROW_NUMBER () OVER
( PARTITION BY po_line_location_id
ORDER BY transaction_date DESC
, transaction_id -- Optional tie-breaker
) AS row_num
FROM rt
WHERE transaction_type = 'RECEIVE'
) rt_ordered -- End subquery to compute row_num
WHERE row_num = 1
) rt_relevant -- End subquery to return relevant rows from rt
WHERE prla.line_location_id = rt_relevant.po_line_location_id
ORDER BY
prla.line_location_id
;

********** End of Code **********

The code above includes the counter (rt_relevant.row_num) in the result set. If you don't want that, change the "SELECT *" to "SELECT (list)" (where "(list)" is a list of all the columns in the table rt) on the fourth line.

Notice that the WHERE clause of the main query does not have to test for the proper ransaction_type or transaction_date from rt_relevant. That's because rt_relevant, by definition, contains only the rows that pass those tests.

---

From: Oracle, Savitha Gowda 03-Jan-06 04:58
Subject: Re : MAX(transaction_date) in rc_transactions too general.

Hello Frank,

Thanks for your input.

Regards,
Savitha