OM Useful Queries

Order header info

Select * from oe_order_headers_all ooh

where order_number= :p_order_number

Operating unit info

select * from hr_operating_units

where organization_id=oe_order_headers_all.org_id

Order type info

select * from apps.oe_transaction_types_tl

where transaction_type_id=oe_order_headers_all.order_type_id

Price list info

select * from apps.qp_list_headers_tl

where list_header_id=oe_order_headers_all.price_list_id

select * from apps.qp_list_lines

where list_header_id=oe_order_headers_all.price_list_id

Find customer info

select * from hz_cust_accounts hca

where cust_account_id=oe_order_headers_all.sold_to_org_id

select * from apps.hz_parties

where party_id=hz_cust_accounts.party_id

Find Ship to location info

select * from hz_cust_site_uses_all

where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.hz_cust_acct_sites_all

where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites

where party_site_id=hz_cust_acct_sites_all.party_site_id

Find Bill to location

select * from hz_cust_site_uses_all

where site_use_id=oe_order_headers_all.invoice_to_org_id

         

select * from hz_cust_acct_sites_all

where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

         

select * from hz_party_sites

where party_site_id=hz_cust_acct_sites_all.party_site_id

actual address

select * from hz_locations 

where location_id=hz_party_sites.location_id

Sales rep id

select name from apps.ra_salesreps_all salerep  where

salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

Payment terms

select name from apps.ra_terms_tl

where term_id =oe_order_headers_all.payment_term_id

and language = 'US'

Order source

select name from apps.oe_order_sources

where order_source_id= oe_order_headers_all.order_source_id

and enabled_flag= 'Y'

Order Source Reference

select orig_sys_document_ref from oe_order_headers_all ooh

where order_number='&oracle order number'

FOB Point Code

select lookup_code from ar_lookups

where lookup_type = 'FOB' and enabled_flag = 'Y'

and upper(meaning) = upper(oe_order_headers_all.fob_point_code)

Freight terms

select lookup_code from apps.oe_lookups

where upper (lookup_type) = 'FREIGHT_TERMS'  and enabled_flag = 'Y'

and upper (lookup_code) = upper (oe_order_headers_all.freight_terms_code)

For sales channel code validation

select lookup_code from apps.oe_lookups

where lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y'

upper(lookup_code) = upper(oe_order_headers_all.sales_channel_code)

Ship method

select ship_method_code from wsh.wsh_carrier_services

where ship_method_code = oe_order_headers_all.shipping_method_code

Warehouse Info

select * from org_organization_definitions

where organization_id = oe_order_headers_all.ship_from_org_id

Sales order Lines Details

select * from apps.oe_order_lines_all

where header_id=oe_order_headers_all.header_id

Transactional currency code

select ota.price_list_id, qhb.currency_code

from ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb

where ota.transaction_type_id = oe_order_headers_all.order_type_id

and ota.price_list_id = qhb.list_header_id(+)

and NVL(qhb.list_type_code, 'PRL') = 'PRL'

and qhb.currency_code =oe_order_headers_all.transactional_curr_code

Item info

select * from apps.mtl_system_items_b

where segment1 like oe_order_lines_all.ordered_item

and organization_id=oe_order_lines_all.ship_from_org_id

UOM

select uom_code from inv.mtl_units_of_measure_tl

where upper(uom_code)= upper(oe_order_lines_all.order_quantity_uom)

and language= 'US' and nvl(disable_date, (sysdate + 1)) > sysdate

Item type code validation

select lookup_code from apps.oe_lookups

where upper(lookup_type) = 'ITEM_TYPE'

and enabled_flag = 'Y'

and upper(lookup_code)= oe_order_lines_all.item_type_code

On hand quantities

select * from apps.mtl_onhand_quantities

where inventory_item_id=oe_order_lines_all.inventory_item_id

and organization_id=oe_order_lines_all.ship_from_org_id

Shipping

select * from wsh_delivery_details

where source_header_id=oe_order_headers_all.header_id

select * from wsh_delivery_assignments

where delivery_detail_id=wsh_delivery_details.delivery_detail_id

select * from wsh_new_deliveries

where delivery_id=wsh_delivery_assignments.delivery_id

select * from wsh_delivery_legs

where delivery_id=wsh_new_deliveries.delivery_id

select * from wsh_trip_stops wts

where stop_id=wsh_delivery_legs.pick_up_stop_id

select * from wsh_trips wt

where trip_id=wsh_trip_stops.trip_id

select * from org_organization_definitions

where organization_id = wsh_new_deliveries.organization_id

Material transactions

select * from mtl_material_transactions

where inventory_item_id=oe_order_lines_all.inventory_item_id

and organization_id=oe_order_lines_all.ship_from_org_id

select * from mtl_transaction_types

where transaction_type_id = mmt.transaction_type_id

select * from apps.mtl_txn_source_types

where transaction_source_type_id= mmt.transaction_source_type_id

mmt = mtl_material_transactions


Join between OM, WSH, AR Tables
SELECT ooh.order_number

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,rctl.interface_line_attribute1

              ,rctl.interface_line_attribute3

              ,rctl.interface_line_attribute6

              ,rct.org_id

              ,rct.creation_date

              ,trx_number

              ,rctl.quantity_ordered

              ,rct.interface_header_context

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

             ,ra_customer_trx_all rct

             ,ra_customer_trx_lines_all rctl

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      AND rctl.interface_line_attribute1=to_char(ooh.order_number)

      AND rctl.interface_line_attribute6=to_char(ool.line_id)

      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)

      AND rctl.customer_trx_id=rct.customer_trx_id

      AND rct.interface_header_context='ORDER ENTRY'

Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:

SELECT interface_source_code,

           interface_source_line_id,

           quantity,

           destination_type_code,

           transaction_id,

           process_flag,

           request_id,

           TRUNC (creation_date)

  FROM po_requisitions_interface_all

 WHERE interface_source_code = 'ORDER ENTRY'

   AND interface_source_line_id IN (SELECT drop_ship_source_id

                                      FROM oe_drop_ship_sources

                                     WHERE header_id = &order_hdr_id

                                       AND line_id = &order_line_id);

The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)

SELECT porh.segment1,

           porl.line_num,

           pord.distribution_num,

           ooh.order_number

           sales_order,

           ool.line_number so_line_num,

           rsh.receipt_num,

           rcv.transaction_type

  FROM oe_order_headers_all ooh,

          po_requisition_headers_all porh,

          po_requisition_lines_all porl,

          po_req_distributions_all pord,

          oe_order_lines_all ool,

          po_system_parameters_all posp,

          rcv_shipment_headers rsh,

          rcv_transactions rcv

 WHERE ooh.order_source_id = posp.order_source_id

   AND porh.org_id = posp.org_id

   AND porh.requisition_header_id = ool.source_document_id

   AND porl.requisition_line_id = ool.source_document_line_id

   AND porh.requisition_header_id = porl.requisition_header_id

   AND porl.requisition_line_id = pord.requisition_line_id

   AND porl.requisition_line_id = rcv.requisition_line_id

   AND pord.distribution_id = rcv.req_distribution_id

   AND rcv.shipment_header_id = rsh.shipment_header_id

R12 Customer / Resource

select col.name , hcp.CREDIT_CHECKING , hcsu.PRIMARY_SALESREP_ID 
from hz_cust_accounts hca ,
              hz_parties hp,
              hz_party_sites hps,
              hz_cust_acct_sites_all hcas,
              hz_cust_site_uses_all   hcsu,
              hz_customer_profiles hcp,
              ar_collectors           col
where hp.party_id = hca.party_id
and hps.party_id =  hca.party_id
and hps.party_site_id = hcas.party_site_id
and hp.party_id            =  hcp.party_id 
and hca.cust_account_id    =  hcp.cust_account_id
and hcsu.SITE_USE_ID = hcp.SITE_USE_ID
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hcas.ORG_ID = hcsu.org_id
AND col.collector_id       =  hcp.collector_id
--and hca.cust_account_id = :cust_id
and hca.account_number = '200084' 
and SITE_USE_CODE = 'BILL_TO'
and hcas.ORG_ID = nvl(:p_org_id,hcas.ORG_ID)


SELECT   RESOURCE_NAME
  FROM   jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrre
 WHERE   jrs.resource_id = jrre.resource_id
 AND jrs.SALESREP_ID = 100015087

On hand and available to reserve Qty

/* Formatted on 06-Aug-14 9:41:45 AM (QP5 v5.114.809.3010) */
DECLARE
   CURSOR bmd_onhand
   IS
     -- SELECT  * FROM XYKA_BMD_AGE_JUN;
      SELECT   segment1
        FROM   mtl_system_items_b
         WHERE   segment1 IN (SELECT item_code FROM XYKA_BMD_AGE_JUN)
            AND organization_id = 798;

   l_inv_id          number;
   l_org_id          number;
   l_onhand          number;
   l_reserved        number;
   x_return_status   VARCHAR2 (1);
   x_msg_data        VARCHAR2 (4000);
   x_msg_count       NUMBER;
   x_qoh             NUMBER;
   x_rqoh            NUMBER;
   x_qr              NUMBER;
   x_qs              NUMBER;
   x_att             NUMBER;
   x_atr             NUMBER;
   x_sqoh            NUMBER;
   x_srqoh           NUMBER;
   x_sqr             NUMBER;
   x_sqs             NUMBER;
   x_satt            NUMBER;
   x_sqtr            NUMBER;
BEGIN
   FOR c1_rec IN bmd_onhand
   LOOP
      SELECT   INVENTORY_ITEM_ID
        INTO   l_inv_id
        FROM   mtl_system_items_b
       WHERE   segment1 = c1_rec.segment1 AND organization_id = 798;
     

      inv_globals.set_org_id (798);
      inv_quantity_tree_pub.clear_quantity_cache;
      inv_quantity_tree_pub.query_quantities (
         p_api_version_number    => 1.0,
         x_return_status         => x_return_status,
         x_msg_count             => x_msg_count,
         x_msg_data              => x_msg_data,
         p_organization_id       => 798,
         p_inventory_item_id     => l_inv_id,
         p_tree_mode             => 1,
         p_is_revision_control   => FALSE,
         p_is_lot_control        => FALSE,
         p_is_serial_control     => FALSE,
         p_grade_code            => NULL,
         p_revision              => NULL,
         p_lot_number            => NULL,
         p_subinventory_code     => NULL,
         p_locator_id            => NULL,
         x_qoh                   => x_qoh,
         x_rqoh                  => x_rqoh,
         x_qr                    => x_qr,
         x_qs                    => x_qs,
         x_att                   => x_att,
         x_atr                   => x_atr,
         x_sqoh                  => x_sqoh,
         x_srqoh                 => x_srqoh,
         x_sqr                   => x_sqr,
         x_sqs                   => x_sqs,
         x_satt                  => x_satt,
         x_satr                  => x_sqtr
      );

      UPDATE   XYKA_BMD_AGE_JUN
         SET   ONHAND_QTY = x_qoh, RESERVED_QTY = x_rqoh
       WHERE   ITEM_CODE = c1_rec.segment1;

      DBMS_OUTPUT.put_line (c1_rec.segment1 ||' - '|| x_qoh ||' - '|| x_rqoh);
   END LOOP;
END;

Help--Diagnostics--Examine gives function not available error

 Help--Diagnostics--Examine gives function not available error

- Log in as System Administrator
- Choose Profile/System
- Find profile "Utilities:Diagnostics" and change it's value to "Yes".


How to Password the Examine Function [ID 1041652.6]
How to Make Help Diagnostics Examine be Password Protected OR Not? [ID 160151.1] 

cst_match_date_period Period Close Error

Symptoms

When attempting to cost Sales Order Pick and Internal Order Pick the following error occurs:

ERROR



Error Code =CST_MATCH_DATE_PERIOD

Error Explanation = The transaction date is not within the accounting period specified.





The issue can be reproduced at will with the following steps:

1. Oracle cost Management
2. Inventory
3. Transactions
4. View Transactions
5. select costed_flag option error

Cause

The cause of the issue is invalid / incorrect data in MTL_MATERIAL_TRANSACTIONS, the acct_period_id is getting stamped with the accounting is of the previous period.

Solution

To identify the issue, please obtain the following scripts output:

1)
SELECT mmt.transaction_id txn_id,
mmt.transaction_date txn_date,
mmt.acct_period_id txn_period_id,
o.acct_period_id org_period_id,
o.period_start_date st_date,
o.schedule_close_date cl_date
FROM mtl_material_transactions mmt,
org_acct_periods o
WHERE mmt.organization_id = o.organization_id
AND TRUNC(o.schedule_close_date) >= TRUNC(mmt.transaction_date)
AND TRUNC(o.period_start_date) <= TRUNC(mmt.transaction_date)
and mmt.transaction_date is not NULL
and mmt.acct_period_id <> o.acct_period_id ;

2) select *
   from org_acct_periods
    where organization_id = '&org_id_where_issue_exists';

-- To implement the solution, please execute the following steps:

1) Make a backup of MTL_MATERIAL_TRANSACTIONS before running the update script.
2) Shut down the cost manager before the update.
3) Run the following update to correct the acct_period_id:

Update mtl_material_transactions
set acct_period_id =
,   costed_flag = 'N'
,   transaction_group_id = NULL
,   transaction_set_id = NULL
,   error_code = NULL
,   error_explanation = NULL
where costed_flag = 'E'
and acct_period_id =  
and error_code = 'CST_MATCH_DATE_PERIOD'
and organization_id = ''

Note: The transaction_id could also be used as the basis for the update, but in this case the number of records made that difficult and uniqueness could be achieved with the four where clauses parameters above.

3) Restart the Cost Manager.

Customer Profile Credit Amount Update API

/* Formatted on 25-May-14 5:56:03 PM (QP5 v5.114.809.3010) */
DECLARE
   p_customer_profile_rec_type   HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   p_cust_account_profile_id     NUMBER;
   p_object_version_number       NUMBER;
   x_return_status               VARCHAR2 (2000);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (2000);
   l_user_id                     number;
   l_resp_id                     number;
   l_respid_id                   number;
   in_out_version_no             number;
   in_out_version_no_hcpa        number;
   v_customer_profile_amt        HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
   v_cust_act_prof_amt_id        number;
   v_cust_account_profile_id     NUMBER;
   v_return_status               VARCHAR2 (2000);
   v_msg_count                   NUMBER;
   v_msg_data                    VARCHAR2 (2000);
   p_create_profile_amt          VARCHAR2 (2000);
   v_msg_dummy                   VARCHAR2 (5000);
   t_output                      VARCHAR2 (5000);
   mTrx                          number;
   mOverall                      number;
BEGIN
   FOR i
   IN (SELECT   hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
                hca.cust_account_id,
                hcp.site_use_id,
                account_number,
                hcas.ORG_ID org_id,
                CUST_ACCT_PROFILE_AMT_ID
         FROM   hz_cust_accounts hca,
                hz_parties hp,
                hz_party_sites hps,
                hz_cust_acct_sites_all hcas,
                hz_cust_site_uses_all hcua,
                Hz_Customer_Profiles hcp,
                HZ_CUST_PROFILE_AMTS hcpa
        WHERE       hca.party_id = hp.party_id
                AND hps.party_id = hp.party_id
                AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
                AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID
                AND hcua.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
                AND hcp.cust_account_id = hca.cust_account_id
                AND hcp.site_use_id = hcua.site_use_id
                AND HCP.CUST_ACCOUNT_PROFILE_ID =
                      HCPA.CUST_ACCOUNT_PROFILE_ID
                AND account_number IN
                         (SELECT   DISTINCT CUSTOMER_NUMBER
                            FROM   ncc_po_credit)
                AND hcua.SITE_USE_CODE = 'BILL_TO'
                AND hcas.ORG_ID IN (612, 613, 614, 615, 616, 617))
   LOOP
      p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;

      DBMS_OUTPUT.put_line ('profile_id = ' || i.profile_id);

      MO_GLOBAL.INIT ('AR');
      mo_global.set_policy_context ('S', '612');

      l_user_id := 1295;
      l_resp_id := 52514;
      l_respid_id := 222;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_respid_id);


      SELECT   CREDIT_LIMIT_AMOUNT, CREDIT_LIMIT_AMOUNT
        INTO   mTrx, mOverall
        FROM   ncc_po_credit
       WHERE   CUSTOMER_NUMBER = i.account_number AND ORG_ID = i.org_id;

      DBMS_OUTPUT.put_line ('Credit Limit = ' || mTrx || ' ' || mOverall);
      DBMS_OUTPUT.put_line (
         'CUST_ACCT_PROFILE_AMT_ID = ' || i.CUST_ACCT_PROFILE_AMT_ID
      );

      v_customer_profile_amt.cust_account_profile_id := i.profile_id;
      v_customer_profile_amt.cust_acct_profile_amt_id :=
         i.CUST_ACCT_PROFILE_AMT_ID;
      v_customer_profile_amt.cust_account_id := i.cust_account_id;
      v_customer_profile_amt.SITE_USE_id := i.site_use_id;
      v_customer_profile_amt.currency_code := 'BHD';
      v_customer_profile_amt.trx_credit_limit := mTrx;
      v_customer_profile_amt.overall_credit_limit := mOverall;

      -- v_customer_profile_amt.created_by_module := 'HZ_CPUI';   -- commented by Rushi

      SELECT   HCPA.object_version_number
        INTO   in_out_version_no_hcpa
        FROM   hz_customer_profiles hcp, HZ_CUST_PROFILE_AMTS HCPA
       WHERE   hcp.cust_account_profile_id = i.profile_id
               AND HCP.CUST_ACCOUNT_PROFILE_ID = HCPA.CUST_ACCOUNT_PROFILE_ID;

      DBMS_OUTPUT.put_line (
         'Profile amt version = ' || in_out_version_no_hcpa
      );

      p_object_version_number := in_out_version_no_hcpa;

      hz_customer_profile_v2pub.update_cust_profile_amt (
         p_init_msg_list           => 'T',
         p_cust_profile_amt_rec    => v_customer_profile_amt,
         p_object_version_number   => p_object_version_number,
         x_return_status           => x_return_status,
         x_msg_count               => x_msg_count,
         x_msg_data                => x_msg_data
      );

      DBMS_OUTPUT.put_line (
         'x_return_status = ' || SUBSTR (x_return_status, 1, 255)
      );
      DBMS_OUTPUT.put_line (
         'Object Version Number = ' || TO_CHAR (p_object_version_number)
      );
      DBMS_OUTPUT.put_line (
         'Credit Rating = ' || p_customer_profile_rec_type.credit_rating
      );
      DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
      DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));

      IF x_msg_count > 1
      THEN
         FOR I IN 1 .. x_msg_count
         LOOP
            DBMS_OUTPUT.put_line(I || '.'
                                 || SUBSTR (
                                       FND_MSG_PUB.Get (
                                          p_encoded   => FND_API.G_FALSE
                                       ),
                                       1,
                                       255
                                    ));
         END LOOP;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;

Customer Profile Update API

/* Formatted on 25-May-14 5:44:31 PM (QP5 v5.114.809.3010) */
DECLARE
   p_customer_profile_rec_type   HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   p_cust_account_profile_id     NUMBER;
   p_object_version_number       NUMBER;
   x_return_status               VARCHAR2 (2000);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (2000);
   l_user_id                     number;
   l_resp_id                     number;
   l_respid_id                   number;
   in_out_version_no             number;
   in_out_version_no_hcpa        number;
   v_customer_profile_amt        HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
   v_cust_act_prof_amt_id        number;
   v_cust_account_profile_id     NUMBER;
   v_return_status               VARCHAR2 (2000);
   v_msg_count                   NUMBER;
   v_msg_data                    VARCHAR2 (2000);
   p_create_profile_amt          VARCHAR2 (2000);
   v_msg_dummy                   VARCHAR2 (5000);
   t_output                      VARCHAR2 (5000);
   mTrx                          number;
   mOverall                      number;
BEGIN
   FOR i
   IN (SELECT   hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
                hca.cust_account_id,
                hcp.site_use_id,
                account_number,
                hcas.ORG_ID org_id,
                CUST_ACCT_PROFILE_AMT_ID
         FROM   hz_cust_accounts hca,
                hz_parties hp,
                hz_party_sites hps,
                hz_cust_acct_sites_all hcas,
                hz_cust_site_uses_all hcua,
                Hz_Customer_Profiles hcp,
                HZ_CUST_PROFILE_AMTS hcpa
        WHERE       hca.party_id = hp.party_id
                AND hps.party_id = hp.party_id
                AND hcas.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
                AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID
                AND hcua.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID
                AND hcp.cust_account_id = hca.cust_account_id
                AND hcp.site_use_id = hcua.site_use_id
                AND HCP.CUST_ACCOUNT_PROFILE_ID =
                      HCPA.CUST_ACCOUNT_PROFILE_ID
                AND account_number IN
                         (SELECT   DISTINCT CUSTOMER_NUMBER
                            FROM   ncc_po_credit )
                AND hcua.SITE_USE_CODE = 'BILL_TO'
                AND hcas.ORG_ID IN (612, 613, 614, 615, 616, 617))
   LOOP
      p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;
      p_customer_profile_rec_type.credit_checking := 'Y';

      -- p_customer_profile_rec_type.credit_hold := 'Y';

      SELECT   object_version_number
        INTO   in_out_version_no
        FROM   hz_customer_profiles
       WHERE   cust_account_profile_id = i.profile_id;

      p_object_version_number := in_out_version_no;

      MO_GLOBAL.INIT ('AR');
      mo_global.set_policy_context ('S', '612');

      l_user_id := 1295;
      l_resp_id := 52514;
      l_respid_id := 222;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_respid_id);

      hz_customer_profile_v2pub.update_customer_profile (
         p_init_msg_list           => 'T',
         p_customer_profile_rec    => p_customer_profile_rec_type,
         p_object_version_number   => p_object_version_number,
         x_return_status           => x_return_status,
         x_msg_count               => x_msg_count,
         x_msg_data                => x_msg_data
      );


      SELECT   CREDIT_LIMIT_AMOUNT, CREDIT_LIMIT_AMOUNT
        INTO   mTrx, mOverall
        FROM   ncc_po_credit
       WHERE   CUSTOMER_NUMBER = i.account_number AND ORG_ID = i.org_id;


      v_customer_profile_amt.cust_account_profile_id := i.profile_id;
      v_customer_profile_amt.cust_acct_profile_amt_id :=
         i.CUST_ACCT_PROFILE_AMT_ID;
      v_customer_profile_amt.cust_account_id := i.cust_account_id;
      v_customer_profile_amt.SITE_USE_id := i.site_use_id;
      v_customer_profile_amt.currency_code := 'BHD';
      v_customer_profile_amt.trx_credit_limit := mTrx;
      v_customer_profile_amt.overall_credit_limit := mOverall;
      v_customer_profile_amt.created_by_module := 'HZ_CPUI';


      DBMS_OUTPUT.put_line (
         'x_return_status = ' || SUBSTR (x_return_status, 1, 255)
      );
      DBMS_OUTPUT.put_line (
         'Object Version Number = ' || TO_CHAR (p_object_version_number)
      );
      DBMS_OUTPUT.put_line (
         'Credit Rating = ' || p_customer_profile_rec_type.credit_rating
      );
      DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
      DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));

      IF x_msg_count > 1
      THEN
         FOR I IN 1 .. x_msg_count
         LOOP
            DBMS_OUTPUT.put_line(I || '.'
                                 || SUBSTR (
                                       FND_MSG_PUB.Get (
                                          p_encoded   => FND_API.G_FALSE
                                       ),
                                       1,
                                       255
                                    ));
         END LOOP;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;

Order Hold

SELECT   ooha.header_id,
         order_number,
         hold_lines.line_id,
         oe_order_misc_pub.get_concat_line_number (hold_lines.line_id) so_line_number, 
      /* Uncomment below to get the concatenated line number if you are running from non apps user */

        /*hold_lines.line_number || '.' || hold_lines.shipment_number

         || DECODE (hold_lines.option_number,

                    NULL, NULL,

                    '.' || hold_lines.option_number)

         || DECODE (

               hold_lines.component_number,

               NULL,

               NULL,

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

               || '.'

               || hold_lines.component_number

            )

         || DECODE (

               hold_lines.service_number,

               NULL,

               NULL,

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

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

               || '.'

               || hold_lines.service_number

            )

            so_line_number, */
         ohd.name "Hold Name",

         holds.released_flag,
         holds.creation_date "Hold Applied Date",
         ohr.creation_date "Hold Released date",
         ohr.release_reason_code,
         ohr.release_comment
  FROM   oe_order_lines_all hold_lines,
         oe_order_headers_all ooha,
         oe_order_holds_all holds,
         oe_hold_sources_all ohsa,
         oe_hold_releases ohr,
         oe_hold_definitions ohd
 WHERE       holds.line_id = hold_lines.line_id(+)
         AND holds.header_id = hold_lines.header_id(+)
         AND holds.hold_release_id = ohr.hold_release_id(+)
         AND holds.hold_source_id = ohsa.hold_source_id
         AND ohsa.hold_id = ohd.hold_id
         AND holds.header_id = ooha.header_id
         AND ooha.order_number = :P_Order_Number

ORDER BY NVL(hold_lines.line_id,0), ohsa.hold_source_id

AR Customer Master Query

SELECT   hca.account_number   --,cust.party_id
       --  (select ORGANIZATION_NAME from org_organization_definitions where OPERATING_UNIT = hcsu.org_id) org
        ,customer_name,
         (   hl.address1
          || '  '
          || hl.address2
          || ' '
          || hl.address3
          || ' '
          || hl.address4)
            ship_to,
         hl.city,
         hl.country,
         (SELECT   hcp.raw_phone_number
            FROM   hz_contact_points hcp, hz_relationships hr, hz_parties hzp
           WHERE       hcp.owner_table_id = hr.party_id
                   AND hr.object_id = NVL (cust.party_id, hr.object_id)
                   AND hr.status = 'A'
                   AND hcp.status = 'A'
                   AND hcp.primary_flag = 'Y'
                   AND hzp.party_id = hr.party_id
                   AND hcp.phone_line_type = 'EMAIL'
                   AND hcp.owner_table_name = 'HZ_PARTIES')
            email,
         (SELECT   description
            FROM   ar_customer_profiles_v a, ra_terms b
           WHERE       customer_id = NVL (f.customer_id, customer_id)
                   AND status = 'A'
                   AND site_use_id IS NULL
                   AND a.standard_terms = b.term_id)
            cust_profile,
        -- hcsu.org_id,
         xyka_custom_yka.get_ar_balance_yka (customer_id) balance,
         xyka_custom_yka.get_ar_overall_cr_limit (customer_id) cr_limit,
         xyka_custom_yka.get_ar_customer_category_name (customer_id)
            customer_category,
         xyka_custom_yka.get_ar_credit_check (customer_id, 126) credit_check, -- org_id
         xyka_custom_yka.get_ar_collector_code (customer_id, 126) collector,
         (SELECT   MAX (name) collector_id
            --into l_collector_code
            FROM   ar_customers a, hz_customer_profiles b, ar_collectors c
           WHERE       a.customer_id = b.cust_account_id
                   AND a.customer_id = b.cust_account_id
                   AND b.site_use_id IS NULL
                   AND b.collector_id = c.collector_id
                   AND a.customer_id = f.customer_id)
            collector_name,
         (SELECT   MAX (credit_checking)
            FROM   hz_customer_profiles hcp
           WHERE   hcp.cust_account_id = f.customer_id)            cr_chk ,
           (select segment5 from gl_code_combinations where code_combination_id = hcsu.GL_ID_REC ) gcc
  FROM   hz_parties cust,
         hz_cust_accounts hca,
         hz_cust_acct_sites_all hcas,
         hz_cust_site_uses_all hcsu,
         hz_party_sites hps,
         hz_locations hl,
         ar_customers f
 WHERE       1 = 1
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hps.location_id(+) = hl.location_id
         AND hps.party_site_id = hcas.party_site_id
         AND cust.party_id = hca.party_id
         AND f.customer_id = hca.cust_account_id
         --and hcsu.primary_flag = 'y'
         AND hcsu.status = 'A'
         AND hcsu.site_use_code = 'BILL_TO'
        -- AND hca.cust_account_id = 7168
         AND hcsu.org_id = NVL (:org_id, hcsu.org_id)
         and hca.account_number like 'S%'
         order by 1

AP Trial Balance Report Redinition

The R12 Accounts Payable Trial Balance has 4 templates available that control the output that is
displayed. The four templates available are:

Accounts Payable Trial Balance - Group by Account, Summary
Accounts Payable Trial Balance - Group by Account, Detail
Accounts Payable Trial Balance - Group by Third Party, Summary
Accounts Payable Trial Balance - Group by Third Party, Detail

Do the following to change the template that is used when the report is submitted:

1. Navigate: Payables Responsibility>Other>Request>Run
2. Select the Accounts Payable Trial Balance
3. Enter all of the parameters for the report. DO NOT SUBMIT THE REPORT.
4. Click on the Options button in the Upon Completion section of the Submit Request screen.
5. Select the desired template name.
6. Submit the Trial balance.

Do the following to change the default template used when the report is submitted:

1. Navigate: System Administration Responsibility>Concurrent>Programs
2. Search for:

Application = Payables
Program = Accounts Payable Trial Balance

Click on Go

3. Click on Update for the Accounts Payable Trial Balance program

4. Select the Onsite Setting tab

5. Select the default template from the Template dropdown in the General section.

6. Click on Apply to save the changes.
--------------------------- IMPORTANT --------------------------------

Navigate to AP->Setup->Accounting Setups->Subledger Accounting Setup->Open Account Balances Listing Definitions

Click on Create and give in the details.

Save and try to run the Report. It should show LOV now.