Create Group using the TCA API - HZ_PARTY_V2PUB

Running The Create Group API

Name of the API: CREATE GROUP API

Base Tables Affected: HZ_PARTIES

PL/SQL Procedure used : CREATE_GROUP

Package Used: HZ_PARTY_V2PUB

Create Person using the TCA API - HZ_PARTY_V2PUB

Running the Create Person API

Name of the API: CREATE PERSON API

Base Tables Affected: HZ_PARTIES

HZ_PERSON_PROFILES

PL/SQL Procedure used: CREATE_PERSON

Package Used: HZ_PARTY_V2PUB

HZ_CUST_ACCOUNT_V2PUB

Create an Organization using the TCA API - HZ_PARTY_V2PUB

Running the Create Organization API

Name of the API: CREATE ORGANIZATION API

Base Tables Affected: HZ_PARTIES

HZ_ORGANIZATION_PROFILES

PL/SQL Procedure used: CREATE_ORGANIZATION

Package Used: HZ_PARTY_V2PUB

How to identify the package name and version of an API

To identify the package name of an API as compiled into the Oracle database, in SQL*Plus enter the following select statement (logged in as APPS)

SQL> SELECT text FROM all_source

WHERE name like '%user_api_name%'

  AND text like '%Header%';

(eg:HZ_CUSTOMER_ACCOUNT_PUB API)

How to Debug TCA APIs

Debug Messages (PL/SQL API)

Use the extensive debug messages to troubleshoot in case of unexpected problems. These debugging messages are useful because an API would be difficult to debug otherwise. User can turn on debug messages with a certain profile option. These messages can be written to a log file as well.

Introduction to Trading Community Architecture (TCA) APIs

The TCA API is an integrated set of code designed in a highly modular fashion, resulting in codes that are easy to understand, maintain and extend.

The modular approach defaults and validates users who enter information; default information not provided by the user and calls the appropriate entity handler to perform the business related tasks.

Query to get the list of Item Orderability Rules defined in Order Management

SELECT oio.item_level,

       'Inventory Item' item_level_dsp,

       b.concatenated_segments item,

       t.description item_description,

       oio.item_category_id,

       oio.generally_available,

       oio.enable_flag,

       oior.rule_level,

       oior.enable_flag rule_enable_flag,

       DECODE (rule_level,

Query to get the Defaulting rules defined in Order Managment

SELECT d.database_object_display_name,

       d.attribute_display_name,

       d.precedence,

       d.display_name,

       d.system_flag,

       d.enabled_flag,

       k.sequence_no,

       l.meaning,

       DECODE (

          src_type,

          'API', src_api_pkg || '.' || src_api_fn,

          'CONSTANT', src_constant_value,

Query to get the OM Sales Order summary details

  SELECT h.transactional_curr_code Currency,

         h.conversion_rate Conversion_rate,

         h.conversion_type_code Conversion_Type_Code,

         h.transactional_curr_code use_currency,

         DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'S', sr.name, NULL)

            Sales_Person,

         DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'O', h.ordered_date, NULL)

            dummy_order_date,

         DECODE (SUBSTR (UPPER (:p_order_by), 1, 1), 'A', agree.name, NULL)

            dummy_agreement,

Query to get the list of Order management Processing Constraints

  SELECT c.constraint_id,

         e.entity_display_name entity,

         c.column_display_name attribute,

         l1.meaning opeartion,

         l2.meaning user_action,

         l4.meaning seeded,

         cc.group_number,

Retroactive billing report in oracle order management

SELECT DECODE (:p_sort_by, 'O', orig_head.order_number) sort_by_order_number,

       DECODE (

          :p_sort_by,

          'T', oe_retrobill_pvt.invoice_number (orig_head.order_number,

                                                orig_lin.line_id,

                                                orig_head.order_type_id),

          NULL)

          sort_by_invoice_no,

       DECODE (

          SUBSTR (UPPER (:p_item_display), 1, 1),

Query to get the workflow status of a OM sales order

SELECT 1 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       soh.ordered_date,

       mtlv.segment1,

       mtlv.description,

Query to get the order hold source activity

  SELECT ht.meaning hold_type,

         hs.creation_date apply_date,

         hr.creation_date release_date,

         h.name hold_name,

         hs.hold_entity_code object_type_code,

         ot.meaning object_type,

         hs.hold_entity_code2 object_type_code2,

         ot2.meaning object_type2,

         hs.hold_entity_id object_id,

Query to get the outstanding holds on an order (Open holds)

  SELECT org.name customer_name,

         ho.name hold_name,

         hs.hold_until_date,

         hs.hold_comment,

         h.order_number,

         h.ordered_date order_date,

         h.transactional_curr_code currency_code,

         NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,

         oh.header_id,

         oh.line_id,

         oh.order_hold_id,

         l.item_identifier_type,

         l.inventory_item_id,

         l.ordered_item_id,

         l.ordered_item

    FROM oe_sold_to_orgs_v org,

         mtl_system_items_vl si,

         oe_order_holds_all oh,

         oe_order_lines_all l,

         oe_order_headers h,

         oe_hold_definitions ho,

         oe_hold_sources_all hs

   WHERE     oh.header_id = h.header_id

         AND h.sold_to_org_id = org.organization_id

         AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

         AND h.open_flag = 'Y'

         AND oh.hold_source_id = hs.hold_source_id

         AND hs.hold_id = ho.hold_id

         AND h.header_id = l.header_id(+)

         AND l.open_flag = 'Y'

         AND l.line_id = NVL (oh.line_id, l.line_id)

         AND l.inventory_item_id = si.inventory_item_id

         AND oh.hold_release_id IS NULL

         AND NVL (si.organization_id, 0) = :c_master_org

         AND NVL (h.org_id, 0) = NVL (:p_org_id, 0)

         AND NVL (l.org_id, 0) = NVL (:p_org_id, 0)

         AND h.order_number = '123456789'

ORDER BY org.name,

         ho.name,

         h.order_number,

         si.description

Query to get the discrepancy between the sales orders and purchase orders in Drop ship orders

  SELECT osto.name oe_customer_name,

         osto.customer_number oe_customer_number,

         oeh.header_id oe_header_id,

         oeh.order_number oe_number,

         OTYPE.name oe_type,

         oeh.ordered_date oe_order_date,

         oel.line_id oe_line_id,

         oel.line_number

         || DECODE (oel.shipment_number,

                    NULL, NULL,

Query to get the details of discounts on the orders in Order Management

SELECT   h.transactional_curr_code           Currency2,

      ot.name                       Order_Type,

      DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL) CUSTOMER_ID,

      DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL) CUSTOMER_NAME_2,

      h.order_number                Order_Number,

      PARTY.PARTY_NAME  CUSTOMER_NAME_1, 

      CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,

            h.ordered_date                Order_Date,

Code to write in before report and after report trigger - Oracle Reports

Piece of code to write in before report trigger

function BeforeReport return boolean is

begin

  BEGIN

  SRW.USER_EXIT('FND SRWINIT');

Query to check the line workflows assigned to an order type

exec MO_GLOBAL.SET_POLICY_CONTEXT('S',102);

SELECT OWA.order_type_id,

       ol.name,

       oe.meaning item_type_code,

       wf.display_name line_flow

  FROM wf_activities_vl wf,

       oe_workflow_assignments OWA,

       oe_line_types_v ol,

       oe_lookups oe

 WHERE     OWA.line_type_id = ol.line_type_id

       AND oe.lookup_type(+) = 'WF_ASSIGN_ITEM_TYPES'

       AND oe.lookup_code(+) = OWA.item_type_code

       AND wf.name = OWA.process_name

       AND wf.item_type = 'OEOL'

       AND wf.version =

              (SELECT MAX (version)

                 FROM wf_activities_vl wf1

                WHERE wf1.name = wf.name AND wf1.item_type = 'OEOL');

Query to check the workflow assigned to an order type

Query to check the workflow assigned to an order type

  SELECT ot.order_type_id,

         ot.name order_type_name,

         ot.description,

         ot.start_date_active,