Oracle Inventory On Hand . Available to Reserve Quantity , Available to Transact

/* Oracle Inventory On Hand . Available to Reserve Quantity , Available to Transact */

/* Formatted on 2/22/2012 11:26:05 AM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE GET_LOT_ITEM_QTY (
   P_LPN_ID                  IN     NUMBER,
   P_ORGANIZATION_ID         IN     NUMBER,
   P_SOURCE_TYPE_ID          IN     NUMBER,
   P_INVENTORY_ITEM_ID       IN     NUMBER,
   P_REVISION                IN     VARCHAR2,
   P_LOCATOR_ID              IN     NUMBER,
   P_SUBINVENTORY_CODE       IN     VARCHAR2,
   P_LOT_NUMBER              IN     VARCHAR2,
   P_IS_REVISION_CONTROL     IN     VARCHAR2,
   P_IS_SERIAL_CONTROL       IN     VARCHAR2,
   P_IS_LOT_CONTROL          IN     VARCHAR2,
   X_LPN_ONHAND                 OUT NUMBER,
   X_RESERVABLE_QUANTITY        OUT NUMBER,
   X_TRANSACTABLE_QUANTITY      OUT NUMBER,
   -- NSRIVAST, INVCONV , START
   P_GRADE_CODE              IN     VARCHAR2,
   X_SQOH                       OUT NUMBER,
   X_SATT                       OUT NUMBER,
   X_SATR                       OUT NUMBER
-- NSRIVAST, INVCONV, END
)
IS
   L_MSG_COUNT             VARCHAR2 (100);
   L_MSG_DATA              VARCHAR2 (1000);
   L_RQOH                  NUMBER;
   L_QR                    NUMBER;
   L_QS                    NUMBER;
   L_ATR                   NUMBER;
   L_ATT                   NUMBER;
   L_QOH                   NUMBER;
   L_LPN_CONTEXT           NUMBER;
   L_RETURN_STATUS         VARCHAR2 (1);
   X_RETURN                VARCHAR2 (1);
   L_IS_REVISION_CONTROL   BOOLEAN := FALSE;
   L_IS_SERIAL_CONTROL     BOOLEAN := FALSE;
   L_IS_LOT_CONTROL        BOOLEAN := FALSE;
   L_LPN_CONTEXT           NUMBER;
   L_TREE_MODE             NUMBER;
   -- NSRIVAST, INVCONV,  START
   X_SRQOH                 NUMBER;
   X_SQR                   NUMBER;
   X_SQS                   NUMBER;
   -- NSRIVAST, INVCONV, END

   QUANTITY_EXCEPTION EXCEPTION;
BEGIN
   -- CLEARING THE QUANTITY CACHE
   INV_QUANTITY_TREE_PUB.CLEAR_QUANTITY_CACHE;

   IF UPPER (P_IS_REVISION_CONTROL) = 'TRUE'
   THEN
      L_IS_REVISION_CONTROL := TRUE;
   ELSE
      L_IS_REVISION_CONTROL := FALSE;
   END IF;

   IF UPPER (P_IS_SERIAL_CONTROL) = 'TRUE'
   THEN
      L_IS_SERIAL_CONTROL := TRUE;
   ELSE
      L_IS_SERIAL_CONTROL := FALSE;
   END IF;

   -- BUG NO 2768731
   IF P_LOT_NUMBER IS NULL
   THEN
      L_IS_LOT_CONTROL := FALSE;
   ELSE
      L_IS_LOT_CONTROL := TRUE;
   END IF;

   IF (P_INVENTORY_ITEM_ID IS NULL)
   THEN
      RAISE QUANTITY_EXCEPTION;
   END IF;

   -- RESERVE MODE
   L_TREE_MODE := 1;                    --TO GET AVAILABLE TO RESERVE QUANTITY

   --CALL PUBLIC API
   INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (
      P_API_VERSION_NUMBER           => 1.0,
      P_INIT_MSG_LST                 => 'F',
      X_RETURN_STATUS                => L_RETURN_STATUS,
      X_MSG_COUNT                    => L_MSG_COUNT,
      X_MSG_DATA                     => L_MSG_DATA,
      P_ORGANIZATION_ID              => P_ORGANIZATION_ID,
      P_INVENTORY_ITEM_ID            => P_INVENTORY_ITEM_ID,
      P_TREE_MODE                    => L_TREE_MODE,
      P_IS_REVISION_CONTROL          => L_IS_REVISION_CONTROL,
      P_IS_LOT_CONTROL               => L_IS_LOT_CONTROL,
      P_IS_SERIAL_CONTROL            => L_IS_SERIAL_CONTROL,
      P_DEMAND_SOURCE_TYPE_ID        => P_SOURCE_TYPE_ID,
      P_REVISION                     => P_REVISION,
      P_LOT_NUMBER                   => P_LOT_NUMBER,
      P_LOT_EXPIRATION_DATE          => NULL,
      P_SUBINVENTORY_CODE            => P_SUBINVENTORY_CODE,
      P_LOCATOR_ID                   => P_LOCATOR_ID,
      P_ONHAND_SOURCE                => 3,
      X_QOH                          => L_QOH,
      X_RQOH                         => L_RQOH,
      X_QR                           => L_QR,
      X_QS                           => L_QS,
      X_ATT                          => L_ATT,
      X_ATR                          => L_ATR,
      P_LPN_ID                       => P_LPN_ID-- NSRIVAST, INVCONV, START
      ,
      P_GRADE_CODE                   => P_GRADE_CODE,
      X_SQOH                         => X_SQOH,
      X_SATT                         => X_SATT,
      X_SATR                         => X_SATR--       , P_TRANSACTION_TYPE      =>   NULL
      ,
      X_SRQOH                        => X_SRQOH,
      X_SQR                          => X_SQR,
      X_SQS                          => X_SQS,
      P_DEMAND_SOURCE_HEADER_ID      => -1,
      P_DEMAND_SOURCE_LINE_ID        => -1,
      P_DEMAND_SOURCE_NAME           => -1,
      P_TRANSFER_SUBINVENTORY_CODE   => NULL,
      P_COST_GROUP_ID                => NULL,
      P_TRANSFER_LOCATOR_ID          => NULL
   -- NSRIVAST, INVCONV, END
   );

   IF (L_RETURN_STATUS = 'S')
   THEN
      X_LPN_ONHAND := L_QOH;
      X_RESERVABLE_QUANTITY := L_ATR;
   ELSE
      X_RETURN := 'F';
      RAISE QUANTITY_EXCEPTION;
   END IF;

   -- TRANSACT MODE
   L_TREE_MODE := 2;                            --TO GET TRANSACTABLE QUANTITY

   --CALL PUBLIC API
   INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (
      P_API_VERSION_NUMBER           => 1.0,
      P_INIT_MSG_LST                 => 'F',
      X_RETURN_STATUS                => L_RETURN_STATUS,
      X_MSG_COUNT                    => L_MSG_COUNT,
      X_MSG_DATA                     => L_MSG_DATA,
      P_ORGANIZATION_ID              => P_ORGANIZATION_ID,
      P_INVENTORY_ITEM_ID            => P_INVENTORY_ITEM_ID,
      P_TREE_MODE                    => L_TREE_MODE,
      P_IS_REVISION_CONTROL          => L_IS_REVISION_CONTROL,
      P_IS_LOT_CONTROL               => L_IS_LOT_CONTROL,
      P_IS_SERIAL_CONTROL            => L_IS_SERIAL_CONTROL,
      P_DEMAND_SOURCE_TYPE_ID        => P_SOURCE_TYPE_ID,
      P_REVISION                     => P_REVISION,
      P_LOT_NUMBER                   => P_LOT_NUMBER,
      P_LOT_EXPIRATION_DATE          => NULL,
      P_SUBINVENTORY_CODE            => P_SUBINVENTORY_CODE,
      P_LOCATOR_ID                   => P_LOCATOR_ID,
      P_ONHAND_SOURCE                => 3,
      X_QOH                          => L_QOH,
      X_RQOH                         => L_RQOH,
      X_QR                           => L_QR,
      X_QS                           => L_QS,
      X_ATT                          => L_ATT,
      X_ATR                          => L_ATR,
      P_LPN_ID                       => P_LPN_ID-- NSRIVAST, INVCONV, START
      ,
      P_GRADE_CODE                   => P_GRADE_CODE,
      X_SQOH                         => X_SQOH,
      X_SATT                         => X_SATT,
      X_SATR                         => X_SATR--       , P_TRANSACTION_TYPE      =>   NULL
      ,
      X_SRQOH                        => X_SRQOH,
      X_SQR                          => X_SQR,
      X_SQS                          => X_SQS,
      P_DEMAND_SOURCE_HEADER_ID      => -1,
      P_DEMAND_SOURCE_LINE_ID        => -1,
      P_DEMAND_SOURCE_NAME           => -1,
      P_TRANSFER_SUBINVENTORY_CODE   => NULL,
      P_COST_GROUP_ID                => NULL,
      P_TRANSFER_LOCATOR_ID          => NULL
   -- NSRIVAST, INVCONV, END
   );

   IF (L_RETURN_STATUS = 'S')
   THEN
      X_LPN_ONHAND := L_QOH;
      X_TRANSACTABLE_QUANTITY := L_ATT;
   ELSE
      X_RETURN := 'F';
      RAISE QUANTITY_EXCEPTION;
   END IF;
EXCEPTION
   WHEN QUANTITY_EXCEPTION
   THEN
      DBMS_OUTPUT.PUT_LINE ('Quanity Exception Raised' || SQLERRM);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END GET_LOT_ITEM_QTY;

-----------------------------------

DECLARE
v_lpn_onhand NUMBER;
v_reservable_quantity NUMBER;
v_transactable_quantity NUMBER;
v_sqoh           NUMBER;    
v_satt           NUMBER;    
v_satr           NUMBER;

BEGIN
 get_lot_item_qty(
                p_lpn_id => NULL,
                p_organization_id => 122, --HG5
                p_source_type_id => 8, --Inventory
                p_inventory_item_id => 85703, --HG_Sample_Item
                p_revision => NULL,
                p_locator_id => NULL,
                p_subinventory_code => 'BULK',
                p_lot_number => NULL ,
                p_is_revision_control => 'FALSE',
                p_is_serial_control => 'FALSE',
                p_is_lot_control => 'TRUE',
                x_lpn_onhand => v_lpn_onhand,
                x_reservable_quantity => v_reservable_quantity,
                x_transactable_quantity => v_transactable_quantity,            
                p_grade_code     => NULL,  
                x_sqoh           => v_sqoh ,    
                x_satt           => v_satt ,    
                x_satr           => v_satr      
              -- NSRIVAST, INVCONV, END
          );
        
dbms_output.put_line('v_lpn_onhand :'||v_lpn_onhand);
dbms_output.put_line('v_reservable_quantity :'||v_reservable_quantity);
dbms_output.put_line('v_transactable_quantity :'||v_transactable_quantity);

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error: '||SQLERRM);         
END;

SQL Queries for checking Profile Option Values

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’

SELECT

substr(pro1.user_profile_option_name,1,35) Profile,

decode(pov.level_id,

10001,'Site',

10002,'Application',

10003,'Resp',

10004,'User') Option_Level,

decode(pov.level_id,

10001,'Site',

10002,appl.application_short_name,

10003,resp.responsibility_name,

10004,u.user_name) Level_Value,

nvl(pov.profile_option_value,'Is Null') Profile_option_Value

FROM 

fnd_profile_option_values pov,

fnd_responsibility_tl resp,

fnd_application appl,

fnd_user u,

fnd_profile_options pro,

fnd_profile_options_tl pro1

WHERE

pro1.user_profile_option_name like ('%Ledger%')

and  pro.profile_option_name = pro1.profile_option_name

and  pro.profile_option_id = pov.profile_option_id

and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */

and  pov.level_value = resp.responsibility_id (+)

and  pov.level_value = appl.application_id (+)

and  pov.level_value = u.user_id (+)

order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.

SELECT

substr(pro1.user_profile_option_name,1,35) Profile,

decode(pov.level_id,

10001,'Site',

10002,'Application',

10003,'Resp',

10004,'User') Option_Level,

decode(pov.level_id,

10001,'Site',

10002,appl.application_short_name,

10003,resp.responsibility_name,

10004,u.user_name) Level_Value,

nvl(pov.profile_option_value,'Is Null') Profile_option_Value

FROM 

fnd_profile_option_values pov,

fnd_responsibility_tl resp,

fnd_application appl,

fnd_user u,

fnd_profile_options pro,

fnd_profile_options_tl pro1

WHERE

pro.profile_option_name = pro1.profile_option_name

and  pro.profile_option_id = pov.profile_option_id

and  resp.responsibility_name like '%General%Ledger%'

and  pov.level_value = resp.responsibility_id (+)

and  pov.level_value = appl.application_id (+)

and  pov.level_value = u.user_id (+)

order by 1,2;

 

Drilldown from GL to AR Receiving Transactions

/* Formatted on 2/14/2012 11:48:22 AM (QP5 v5.114.809.3010) */
  SELECT   b.NAME je_batch_name,
           b.description je_batch_description,
           b.running_total_accounted_dr je_batch_total_dr,
           b.running_total_accounted_cr je_batch_total_cr,
           b.status je_batch_status,
           b.default_effective_date je_batch_effective_date,
           b.default_period_name je_batch_period_name,
           b.creation_date je_batch_creation_date,
           u.user_name je_batch_created_by,
           h.je_category je_header_category,
           h.je_source je_header_source,
           h.period_name je_header_period_name,
           h.NAME je_header_journal_name,
           h.status je_header_journal_status,
           h.creation_date je_header_created_date,
           u1.user_name je_header_created_by,
           h.description je_header_description,
           h.running_total_accounted_dr je_header_total_acctd_dr,
           h.running_total_accounted_cr je_header_total_acctd_cr,
           l.je_line_num je_lines_line_number,
           l.ledger_id je_lines_ledger_id,
           glcc.concatenated_segments je_lines_ACCOUNT,
           l.entered_dr je_lines_entered_dr,
           l.entered_cr je_lines_entered_cr,
           l.accounted_dr je_lines_accounted_dr,
           l.accounted_cr je_lines_accounted_cr,
           l.description je_lines_description,
           glcc1.concatenated_segments xla_lines_account,
           xlal.accounting_class_code xla_lines_acct_class_code,
           xlal.accounted_dr xla_lines_accounted_dr,
           xlal.accounted_cr xla_lines_accounted_cr,
           xlal.description xla_lines_description,
           xlal.accounting_date xla_lines_accounting_date,
           xlate.entity_code xla_trx_entity_code,
           xlate.source_id_int_1 xla_trx_source_id_int_1,
           xlate.source_id_int_2 xla_trx_source_id_int_2,
           xlate.source_id_int_3 xla_trx_source_id_int_3,
           xlate.security_id_int_1 xla_trx_security_id_int_1,
           xlate.security_id_int_2 xla_trx_security_id_int_2,
           xlate.transaction_number xla_trx_transaction_number,
           rcvt.transaction_type rcv_trx_transaction_type,
           rcvt.transaction_date rcv_trx_transaction_date,
           rcvt.quantity rcv_trx_quantity,
           rcvt.shipment_header_id rcv_trx_shipment_header_id,
           rcvt.shipment_line_id rcv_trx_shipment_line_id,
           rcvt.destination_type_code rcv_trx_destination_type_code,
           rcvt.po_header_id rcv_trx_po_header_id,
           rcvt.po_line_id rcv_trx_po_line_id,
           rcvt.po_line_location_id rcv_trx_po_line_location_id,
           rcvt.po_distribution_id rcv_trx_po_distribution_id,
           rcvt.vendor_id rcv_trx_vendor_id,
           rcvt.vendor_site_id rcv_trx_vendor_site_id
    FROM   gl_je_batches b,
           gl_je_headers h,
           gl_je_lines l,
           fnd_user u,
           fnd_user u1,
           gl_code_combinations_kfv glcc,
           gl_code_combinations_kfv glcc1,
           gl_import_references gir,
           xla_ae_lines xlal,
           xla_ae_headers xlah,
           xla_events xlae,
           xla.xla_transaction_entities xlate,
           rcv_transactions rcvt
   WHERE       b.created_by = u.user_id
           AND h.created_by = u1.user_id
           AND b.je_batch_id = h.je_batch_id
           AND h.je_header_id = l.je_header_id
           AND l.code_combination_id = glcc.code_combination_id
           AND l.je_header_id = gir.je_header_id
           AND l.je_line_num = gir.je_line_num
           AND gir.gl_sl_link_table = xlal.gl_sl_link_table
           AND gir.gl_sl_link_id = xlal.gl_sl_link_id
           AND xlal.application_id = xlah.application_id
           AND xlal.ae_header_id = xlah.ae_header_id
           AND xlal.code_combination_id = glcc1.code_combination_id
           AND xlah.application_id = xlae.application_id
           AND xlah.event_id = xlae.event_id
           AND xlae.application_id = xlate.application_id
           AND xlae.entity_id = xlate.entity_id
           AND xlate.source_id_int_1 = rcvt.transaction_id
           AND h.je_category = 'Receiving'
           AND b.default_period_name = 'FEB-12'
ORDER BY   h.je_category;

How to complile R12 Form / 10g Form

Telnet

root@ykr12dev # su - appldev
Oracle Corporation      SunOS 5.10      Generic Patch   January 2005
You have new mail.
$ pwd
/export/home/appldev


frmcmp_batch module=XYKAITEMCC.fmb userid=apps/apps output_file=XYKAITEMCC.fmx module_type=form compile_all=special batch=yes;