On-Hand quantity details as per Oracle Form

Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.


Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.


All These details can be fetched using API => inv_quantity_tree_pub.query_quantities



****************************************************************************



DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       segment1 = :item_number
            AND msib.organization_id = mp.organization_id
            AND mp.organization_code = :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number    => 1.0,
      p_init_msg_lst          => 'F',
      x_return_status         => x_return_status,
      x_msg_count             => x_msg_count,
      x_msg_data              => x_msg_data,
      p_organization_id       => v_organization_id,
      p_inventory_item_id     => v_item_id,
      p_tree_mode             => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control   => FALSE,
      p_is_lot_control        => v_lot_control_code,
      p_is_serial_control     => v_serial_control_code,
      p_revision              => NULL,                          -- p_revision,
      p_lot_number            => NULL,                        -- p_lot_number,
      p_lot_expiration_date   => SYSDATE,
      p_subinventory_code     => NULL,                 -- p_subinventory_code,
      p_locator_id            => NULL,                        -- p_locator_id,
      p_onhand_source         => 3,
      x_qoh                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                  => v_rqoh,         --reservable quantity on-hand
      x_qr                    => v_qr,
      x_qs                    => v_qs,
      x_att                   => v_att,               -- available to transact
      x_atr                   => v_atr                 -- available to reserve
   );


   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;


****************************************************************************


Let me know, if any questions. ..


Thanks,
...  ..

Oralce Sales Order Header Details

Below is the Query to get the sales order header details, which includes customer information.


*************************************************************************************


SELECT   ooh.order_number,
         hp.party_name customer_name,
         hca.account_number customer_number,
         hl.address1,
         hl.address2,
         hl.city,
         hl.state,
         hl.postal_code,
         hl.country,
         hl.province,
         hl.county
  FROM   oe_order_headers_all ooh,
         hz_cust_site_uses_all hcsu,
         hz_cust_acct_sites_all hcas,
         hz_party_sites hps,
         hz_locations hl,
         hz_parties hp,
         hz_cust_accounts hca
 WHERE   ooh.invoice_to_org_id = hcsu.site_use_id
         AND  hcsu.cust_acct_site_id = hcas.cust_acct_site_id
         AND hcas.party_site_id = hps.party_site_id
         AND  hps.location_id = hl.location_id
         AND  hps.party_id = hp.party_id
         AND  hp.party_id = hca.party_id
         AND  order_number = :p_order_number;


*************************************************************************************


Let me know, if any questions.


Thanks, ...

Registering the Oracle Concurrent Program from Back-end

**************************************************************************************************************************************************************
BEGIN
   FND_PROGRAM.register (
'Test Concurrent Program Registration from Back-End'       -- program
                                                        ,'XXDO Custom'                                                                     -- application
,'Y' -- enabled
,'XXINV_TEST_REG'                                           -- short_name
,'Test Concurrent Program Registration from Back-End'           -- description
,'XXINV_TEST_REG'                       -- executable_short_name
,'XXDO Custom'                                         -- executable_application
,'' -- execution_options
,'' -- priority
,'Y' -- save_output
,'Y'                                                                                                                                                                                     -                               -- print
,''                                                                                                         -                               -- cols
,''                                                                                              -                               -- rows
,''                                                                                           -                               -- style
,'N' -- style_required
,'' -- printer
,'' -- request_type
,'' -- request_type_application
,'Y' -- use_in_srs
,'N' -- allow_disabled_values
,'N' -- run_alone
,'TEXT'                                                                                                  -- output_type
,'N' -- enable_trace
,'Y' -- restart
,'Y' -- nls_compliant
,'' -- icon_name
,'US'
   );
   


   COMMIT;
END;

**************************************************************************************************************************************************************


Any Questions. ..? Let me know.. .


Thanks.., ..

Registering the Oracle Concurrent Executable from Back End

************************************************************************************************************
BEGIN
   FND_PROGRAM.executable (
'XXINV_TEST_REG'                   -- executable
                                               ,'XXDO Custom'                        -- application
                                               ,'XXINV_TEST_REG'                   -- short_name
                                               ,'Test Exe Registration from Back-End'  -- description
                                               ,'PL/SQL Stored Procedure'       -- execution_method
                                               ,'TEST_PKG.TEST_PROC'          -- execution_file_name
                                               ,''                                            -- subroutine_name
                                               ,''                                            -- Execution File Path
                                               ,'US'                                         -- language_code
                                               ,''
         );




   COMMIT;
END;

************************************************************************************************************

Let me know if any Questions. ..

Thanks... ,

Oracle Trace File Path

If you have enabled the TRACE for a concurrent program, and wants know the file location to check the file.


Here it is.. Below query helps you to get the TRACE file path of your instance.


************************************************************************************************************


SELECT      u_dump.VALUE
         || '/'
         || db_name.VALUE
         || '_ora_'
         || v$process.spid
         || NVL2 (v$process.traceid, '_' || v$process.traceid, NULL)
         || '.trc'
            "Trace File"
  FROM      v$parameter u_dump
            CROSS JOIN
            v$parameter db_name
            CROSS JOIN
            v$process
            JOIN
            v$session
            ON v$process.addr = v$session.paddr
 WHERE       u_dump.name = 'user_dump_dest'
         AND db_name.name = 'db_name'
         AND v$session.audsid = SYS_CONTEXT ('userenv', 'sessionid');
************************************************************************************************************


Let me know if any questions. ..


Thanks.. .

Finding Responsibility of a Form

Below is the Query, which helps you to find all the responsibilities of a Form, to which it has been attached.


**********************************************************************************************
SELECT   FORMS.form_name,
         FORMSTL.user_form_name,
         RESTL.responsibility_name,
         FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions FUNC,
         fnd_menu_entries MENU,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE       FORMSTL.user_form_name LIKE 'CUM Workbench%'
         AND FORMS.form_id = FORMSTL.form_id
         AND FUNC.form_id = FORMS.form_id
         AND MENU.function_id = FUNC.function_id
         AND RES.menu_id(+) = MENU.menu_id
         AND RES.responsibility_id = RESTL.responsibility_id(+)
**********************************************************************************************


Let me know if any questions. ..


Thanks.. .

Order to Cash Cycle - Tables get Affected @ Each Step

1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'


oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'


2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'


oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'


wsh_delivery_details.released_status = 'R' (ready to release)


wsh_delivery_assignments.delivery_id = BLANK


3) Reservation
------------------------------------
mtl_demand


mtl_reservations


4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'


wsh_delivery_assignments.delivery_id gets assigned


wsh_delivery_details.released_status = 'S' (submitted for release)


mtl_txn_request_headers


mtl_txn_request_lines


mtl_material_transactions_temp


5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'


mtl_material_transactions


wsh_delivery_details.released_status = 'Y' (Released)


mtl_onhand_quantities


6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries


oe_order_lines_all.flow_status_code = 'SHIPPED'


wsh_delivery_details.released_status = 'C' (Shipped)


wsh_serial_numbers


data will be deleted from mtl_demand and mtl_reservations


item qty gets deducted from mtl_onhand_quantities


7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to


ra_interface_lines_all


Auto invoice program picks up records from interface table and insert them into


ra_customer_trx_all (trx_number is invoice number)


ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)


8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'


9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'


oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'


10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL


11) Transfer to General Ledger
------------------------------------
GL_INTERFACE


12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES


13) Posting
------------------------------------
GL_BALANCES

Creating Requisitions

Below is the example to create a requisition.


We just need to insert data into Interface table and then we need to call standard oracle import program.


In below example, i have created a Internal Requisition.


************************************************************************************************



DECLARE
   l_request_id   NUMBER;
BEGIN
   INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (interface_source_code,
                                              source_type_code,
                                              requisition_type,
                                              destination_type_code,
                                              item_id,
                                              item_description,
                                              quantity,
                                              authorization_status,
                                              preparer_id,
                                              autosource_flag,
                                              uom_code,
                                              destination_organization_id,
                                              deliver_to_location_id,
                                              deliver_to_requestor_id,
                                              need_by_date,
                                              gl_date,
                                              charge_account_id,
                                              org_id,
                                              suggested_vendor_id,
                                              suggested_vendor_site_id,
                                              unit_price,
                                              creation_date,
                                              created_by,
                                              last_update_date,
                                              last_updated_by,
                                              batch_id
                                              )
     VALUES   ('INV',
               'INVENTORY',
               'INTERNAL',
               'INVENTORY',
               1831768,
               'leadsets',
               50,
               'APPROVED',
               48880,
               'P',
               'EA',
               2609,
               75018,
               48880,
               SYSDATE,
               SYSDATE,
               237125,
               2592,
               1058,
               607,
               150,
               SYSDATE,
               59944,
               SYSDATE,
               59944,
               454
               );


   COMMIT;


   l_request_id :=
      fnd_request.submit_request (application   => 'PO',
                                  program       => 'REQIMPORT',
                                  argument1     => 'INV',
                                  argument2     => '454',
                                  argument3     => 'ALL',
                                  argument4     => '',
                                  argument5     => '',
                                  argument6     => 'N');
   COMMIT;
   DBMS_OUTPUT.put_line ('request_id - ' || l_request_id);
END;
/


************************************************************************************************


Let me know if any questions.. .




Thanks. ..

Generating XML Tags Using SQL Query

**********************************************************************************************************


We have a standard oracle package to generate the XML Tags.
We just need to pass the SQL statement as parameter to the package.


Below is an example, which prints Todays date as XML Tag


SELECT   DBMS_XMLGEN.getxml ('select sysdate today_date from dual') xml
  FROM   DUAL;


Below is the output, which you can see by executing above SQL


<?xml version="1.0"?>
<ROWSET>
<ROW>
<TODAY_DATE>21-NOV-11</TODAY_DATE>
</ROW>
</ROWSET>


ROWSET and ROW are the default tags, which gets printed for any sql using this package.
TODAY_DATE is the main tag printed by SQL statement, which we have passed to the package.


**********************************************************************************************************


There is another way to get the same output using some other functions.


Let me start off with a simple query to print TODAY_DATE tag only


SELECT   XMLELEMENT ("TODAY_DATE", SYSDATE) FROM DUAL;


Below is the output for the above statement


<TODAY_DATE>2011-11-21</TODAY_DATE>


Now, Let me change the date format as DD-MON-YY


SELECT   XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY')) 
FROM DUAL;


Below is the output for the above statement


<TODAY_DATE>21-NOV-11</TODAY_DATE>


Now, i will modify the query to get same output as we got using standard package


SELECT   '<?xml version="1.0"?>'
         || XMLELEMENT (
               "ROWSET",
               XMLELEMENT (
                  "ROW",
                  XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY'))
               )
            )
  FROM   DUAL;


Below is the output for the above statement, which is same as what we got using standard package


<?xml version="1.0"?>
<ROWSET>
<ROW>
<TODAY_DATE>21-NOV-11</TODAY_DATE>
</ROW>
</ROWSET>


**********************************************************************************************************


Let me know if you have any questions. ..


Thanks.. .