Search Results get_itemspercategory

Overview

AMV_CATEGORY_PVT_W is the web/rosetta wrapper package for the Oracle E-Business Suite product Information Template (AMV) category private API, AMV_CATEGORY_PVT. It belongs to the APPS schema and is registered under the API classification OTHER within the ETRM repository. Its principal architectural role is to bridge the strongly typed PL/SQL varray structures used internally by the category API and the generic JTF indexed table types (JTF_NUMBER_TABLE, JTF_VARCHAR2_TABLE_100, JTF_VARCHAR2_TABLE_300, JTF_VARCHAR2_TABLE_2000, and JTF_VARCHAR2_TABLE_4000) that are exchanged across Java, JSP, and XML/HTTP boundaries. In the context of Oracle EBS 12.1.1 and 12.2.2, this package exposes category maintenance and inquiry operations — such as reordering, finding, and counting items, channels, and category hierarchy members — in a form usable from the HTML-based and OAF interfaces that replaced earlier client/server Forms logic.

Key Procedures and Functions

The package contains sixteen documented entry points, combining conversion utilities with functional wrappers.

• ROSETTA_TABLE_COPY_IN_P2 / ROSETTA_TABLE_COPY_OUT_P2 — Convert between AMV_CHAR_VARRAY_TYPE and JTF_VARCHAR2_TABLE_4000, supporting category description or name character arrays.
• ROSETTA_TABLE_COPY_IN_P3 / ROSETTA_TABLE_COPY_OUT_P3 — Convert between AMV_NUMBER_VARRAY_TYPE and JTF_NUMBER_TABLE, used for numeric category identifiers.
• ROSETTA_TABLE_COPY_IN_P7 / ROSETTA_TABLE_COPY_OUT_P7 — Populate and unpack the AMV_CATEGORY_VARRAY_TYPE record type, mapping five numeric attributes plus a 100-character, a 2000-character, and an eighth numeric attribute to parallel JTF tables.
• ROSETTA_TABLE_COPY_IN_P10 / ROSETTA_TABLE_COPY_OUT_P10 — Handle the AMV_CAT_HIERARCHY_VARRAY_TYPE, which carries two numeric identifier columns and a 300-character descriptive column for hierarchy rows.
• REORDER_CATEGORY — The primary update API. It accepts the standard EBS API versioning and message-list parameters (API version, initialization flag, commit flag, validation level, plus return status, message count, and message data outputs), together with a login-user check flag and application context, allowing categories to be re-sequenced while honoring the caller's security context.
• FIND_CATEGORIES — Query counterpart that retrieves category records matching supplied criteria.
• GET_CHANNELSPERCATEGORY — Returns the channels associated with a given category.
• GET_ITEMSPERCATEGORY — Returns the items assigned to a category. This is the procedure most commonly located through the search term "get_itemspercategory"; it is the API used when an interface must enumerate the item population of a category rather than merely its parent-child structure.
• GET_CATPARENTSHIERARCHY / GET_CATCHILDRENHIERARCHY — Return ancestor and descendant category hierarchy sets respectively, enabling navigation up or down the category tree.
• GET_CHNCATEGORYHIERARCHY — Returns the channel-to-category hierarchy view, used when channel-driven merchandising requires the category path per sales channel.

Tables Accessed

The documented database object referenced by this package through APPS synonyms is PLITBLM, a temporary/interim work table used in category processing. The functional procedures delegate persistence to AMV_CATEGORY_PVT, which manages the underlying AMV category and hierarchy tables; direct DML from the wrapper is therefore minimal. The rosetta conversion routines perform no table I/O at all — they exist purely to marshal collection data types.

Usage Notes

AMV_CATEGORY_PVT_W is not intended for direct invocation by end users. It is called by one other package within the AMV module, which supplies the JTF table bindings that the rosetta routines translate. Typical call paths include OAF and JSP pages for category maintenance, concurrent programs that rebuild or re-sequence category hierarchies, and custom integration code that retrieves items-per-category or channel-category relationships for external systems. When extending code, developers should call AMV_CATEGORY_PVT directly for pure PL/SQL work and reserve the _W wrapper for interfaces that must pass or receive JTF_NUMBER_TABLE and JTF_VARCHAR2_TABLE collections. Standard EBS API conventions apply: pass p_init_msg_list and p_commit correctly, check x_return_status after every call, and surface messages through FND_MSG_PUB. Behavior is consistent across 12.1.1 and 12.2.2, since the wrapper predates and survives the Online Patching model without signature change.