Links between HZ module and other ERP Modules (CSI,IB &OM )

Links between csi and hz

1. In Order Management table, customer account, bill to and ship to are stored as

OE_ORDER_LINES_ALL.sold_to_org_id = HZ_CUST_ACCOUNTS.cust_account_id

OE_ORDER_LINES_ALL.ship_to_org_id = HZ_CUST_SITE_USES_ALL.site_use_id

OE_ORDER_LINES_ALL.invoice_to_org_id = HZ_CUST_SITE_USES_ALL.site_use_id

2. In Installed Base table, party, customer account, bill to and ship to are stored as

CSI_ITEM_INSTANCES.owner_party_id = HZ_PARTIES.party_id

CSI_ITEM_INSTANCES.location_id = HZ_PARTY_SITES.party_site_id

CSI_I_PARTIES.party_id = HZ_PARTIES.party_id

CSI_IP_ACCOUNTS.party_account_id = HZ_CUST_ACCOUNTS.cust_account_id

CSI_IP_ACCOUNTS.ship_to_address = HZ_CUST_SITE_USES_ALL.site_use_id

CSI_IP_ACCOUNTS.bill_to_address = HZ_CUST_SITE_USES_ALL.site_use_id

3. The mapping between TCA, OM and IB tables are

CSI_ITEM_INSTANCES.owner_party_id =

                                         (SELECT party_id
                                             FROM hz_cust_accounts
                                           WHERE cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id) ;



CSI_I_PARTIES.party_id =

                                        (SELECT party_id
                                            FROM hz_cust_accounts
                                          WHERE cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id)

CSI_IP_ACCOUNTS.party_account_id = OE_ORDER_LINES_ALL.sold_to_org_id


CSI_IP_ACCOUNTS.ship_to_address = OE_ORDER_LINES_ALL.ship_to_org_id

CSI_IP_ACCOUNTS.bill_to_address = OE_ORDER_LINES_ALL.invoice_to_org_id

OE_ORDER_LINES_ALL.ship_to_org_id =

                                       (SELECT site_use_id
                                          FROM hz_cust_site_uses_all hczu,
                                                      hz_cust_acct_sites_all hcas,
                                                      hz_cust_accounts hca
                                        WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
                                             AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
                                             AND hczu.site_use_code = 'SHIP_TO');


OE_ORDER_LINES_ALL.invoice_to_org_id =

                                      (SELECT site_use_id
                                          FROM hz_cust_site_uses_all hczu,
                                                      hz_cust_acct_sites_all hcas,
                                                      hz_cust_accounts hca
                                       WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
                                            AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
                                            AND hczu.site_use_code = 'BILL_TO');  

***Please leave a comment***

Concatenated Sales Order Line Number - Oracle Applications OrderManagement

Concatenated Sales Order Line Number in Oracle Applications Order Management

Wondering there should be an easy way to get the concatenated sales order line number in Oracle Applications Order Management? Tired of writing a concatenation logic with NVL/DECODE conditions? Here is the amazing solution.

Syntax:

SELECT oe_order_misc_pub.get_concat_line_number (<line_id>) so_line_number

  FROM DUAL

Example:

SELECT oe_order_misc_pub.get_concat_line_number (22366203) so_line_number

  FROM DUAL 

If you follow the traditional way here how it looks like

SELECT oola.line_number

             || '.'

             || oola.shipment_number

             || DECODE (oola.option_number, NULL, NULL, '.' || oola.option_number)

             || DECODE (oola.component_number,

                   NULL, NULL,

                   DECODE (oola.option_number, NULL, '.') || '.' || oola.component_number)

             || DECODE (oola.service_number,

                        NULL,

                        NULL,

                           DECODE (oola.option_number, NULL, '.')

                        || DECODE (oola.component_number, NULL, '.')

                        || '.'

                        || oola.service_number

                ) so_line_number

FROM oe_order_lines_all oola

WHERE oola.line_id = 22366203;

***Please leave a comment***