per_medical_assessment_api.create_medical_assessment-HRMS-Create Medical Records through API.

CREATE TABLE APPS.XXAYTB_MEDICAL_MASTER_DATA
(
  SR_NO                   VARCHAR2(240 BYTE),
  EMPLOYEE_NUMBER         VARCHAR2(240 BYTE),
  CONSULTATION_DATE       DATE,
  TYPE                    VARCHAR2(240 BYTE),
  RESULT                  VARCHAR2(240 BYTE),
  EXAMINER_NAME           VARCHAR2(240 BYTE),
  NEXT_CONSULTATION_DATE  VARCHAR2(240 BYTE),
  DESCRIPTION             VARCHAR2(240 BYTE),
  TIME_IN                 VARCHAR2(50 BYTE),
  TIME_OUT                VARCHAR2(50 BYTE),
  SICK_LEAVE_FROM         DATE,
  SICK_LEAVE_TO           DATE,
  CASE_A                  VARCHAR2(240 BYTE),
  REFER_TO                VARCHAR2(240 BYTE),
  MEDICINE_COST           VARCHAR2(100 BYTE),
  THIRD_PARTY_COST        VARCHAR2(100 BYTE),
  REMARKS                 VARCHAR2(240 BYTE),
  API_ERROR               VARCHAR2(240 BYTE),
  PROCESS_FLAG            VARCHAR2(30 BYTE),
  ERROR_MESSAGE           VARCHAR2(4000 BYTE),
  PROCESS_DATE            DATE,
  REQUEST_ID              NUMBER,
  CREATED_BY              NUMBER,
  PERSON_ID               NUMBER,
  OBJECT_VERSION_NUMBER   NUMBER
)

--*****************************************************

CREATE OR REPLACE PROCEDURE APPS.XXAYTB_MEDICAL_PROC(
       errbuff      OUT   VARCHAR2,
      retcode       OUT   NUMBER
   )
   IS
--Created by Senthil Kalvarayapillai wipro 10-Mar-2013
/* Out variables */
   p_validate                       boolean    default false;
   p_medical_assessment_id          number;
   p_object_version_number          number;
  
  
   /* In variables*/
  
   p_person_id                      number;
   p_reference                      varchar2(240);
   p_type                           varchar2(240);
   p_status                         varchar2(240);
   p_status_reason                  varchar2(240)  default null;
   p_organization_id                NUMBER   DEFAULT NULL;
   p_next_consultation_date         DATE     DEFAULT NULL;
   l_medical_assessment_id          number;
   l_object_version_number          number;
  
  
   /* Error Handling Variables */
   v_error_message            varchar2(240);
   v_err_count                number := 0;
   v_cnt                      NUMBER          := 0;
   v_err_code                 VARCHAR2 (50);
   v_err_msg                  VARCHAR2 (2000);
   ln_business_group_id       number;
   ln_user_id                 number;
   ln_org_id                  number;
   ln_resp_id                 number;  
   ln_error                   NUMBER := 0;
   lv_user_msg_err            VARCHAR2(10000) := NULL;
   v_effective_start_date     date;
   v_effective_date           date;
   v_bg_id                    NUMBER;
   v_status                   VARCHAR2 (100);
   l_error_message            VARCHAR2 (2000);
   l_error_code               VARCHAR2 (2000);
  
   CURSOR C1 IS
  
           SELECT distinct ppf.person_id ppf_person_id,
           decode(to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'),'//','',to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'))sick_leave_from_aa,
           decode(to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'),'//','',to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'))sick_leave_to_aa,
           xmmd.*
        FROM
        APPS.XXAYTB_MEDICAL_MASTER_DATA xmmd
        ,per_all_people_f ppf
        where substr(xmmd.EMPLOYEE_NUMBER,3,15)=ppf.EMPLOYEE_NUMBER
        --and xmmd.SR_NO='823'
        --AND XMMD.EMPLOYEE_NUMBER='BC23481'
        AND XMMD.CONSULTATION_DATE IS NOT NULL
        and xmmd.CREATED_BY=666
        --AND XMMD.PROCESS_FLAG IS NULL --29693
--AND XMMD.PROCESS_FLAG ='E' --29693
           ;
  
BEGIN
            ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
            ln_org_id :=  fnd_profile.VALUE ('ORG_ID');  -- Get the Org_id
            ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
            FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
            FND_FILE.put_line(FND_FILE.output,'org id '||ln_org_id);
            FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
            --Initializing the variables
            fnd_global.apps_initialize (user_id           => ln_user_id,
                                        resp_id           => 50639,
                                        resp_appl_id      => 800
                                             );
            --mo_global.init ('PER');
            mo_global.set_policy_context ('S', ln_org_id);
           
            For REC IN C1
    Loop
    begin      
            per_medical_assessment_api.create_medical_assessment
        ( p_validate => FALSE
        ,p_effective_date => TRUNC(SYSDATE)
        ,p_person_id => rec.ppf_person_id
        ,p_consultation_date => rec.consultation_date
        ,p_consultation_type => 'RMC' --nvl(rec.type,'AM')
        ,p_consultation_result => '' --rec.result
        ,p_examiner_name => rec.examiner_name
        ,p_organization_id => p_organization_id -- med_ass_det_rec.service_provider_id
        ,p_next_consultation_date => p_next_consultation_date
        ,p_description => rec.description
        ,p_attribute1   =>rec.time_in
        ,p_attribute2   =>rec.time_out
        ,p_attribute3   =>rec.sick_leave_from_aa
        ,p_attribute4   =>rec.sick_leave_to_aa
        ,p_attribute5   =>rec.refer_to
        ,p_attribute6   =>rec.medicine_cost
        ,p_attribute7   =>rec.third_party_cost
        ,p_attribute8   =>rec.remarks
        ,p_attribute9   =>rec.CASE_A
        ,p_medical_assessment_id => l_medical_assessment_id
        ,p_object_version_number => l_object_version_number
        );       
       
        dbms_output.put_line('Medical loaded for the employee '||rec.employee_number);
      FND_FILE.put_line(FND_FILE.output,'Medical loaded for the employee '||rec.employee_number);
     
    UPDATE APPS.XXAYTB_MEDICAL_MASTER_DATA
            SET PROCESS_FLAG = 'S',
                error_message = 'SUCCESS--Medical ID IS '||p_medical_assessment_id,
                REQUEST_ID=p_medical_assessment_id               
             WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
             and SR_NO=rec.SR_NO;
         DBMS_OUTPUT.put_line (   'Medical Created: '
                               || REC.EMPLOYEE_NUMBER
                               || ' '
                               || '-'
                               || ' '
                               || p_medical_assessment_id
                              );
         p_medical_assessment_id := NULL;
         p_object_version_number := NULL;
        
      EXCEPTION
         WHEN OTHERS
         THEN
            v_err_msg := SUBSTR (SQLERRM, 1, 100);
            v_err_code := SQLCODE;
            DBMS_OUTPUT.put_line (   'Medical NOT Created:Error in API.'
                                  || '-'
                                  || REC.EMPLOYEE_NUMBER
                                 );
            FND_FILE.put_line(FND_FILE.output,'Medical NOT loaded for the employee: '||rec.employee_number);                                
            UPDATE XXAYTB_MEDICAL_MASTER_DATA
               SET process_flag = 'E',
                   error_message = v_err_msg
             WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
             and SR_NO=rec.SR_NO
             ;
            p_medical_assessment_id := NULL;
            p_object_version_number := NULL;
           
      END;
   END LOOP;
   end;
/

Delete concurrent program,executable from Back end

Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;

query for item onhand quantity & Lot wise query

SELECT  msi.segment1 item_name,mq.subinventory_code, b.SEGMENT1||'.'||b.SEGMENT2||'.'||b.SEGMENT3||'.'||b.SEGMENT4 locator,
SUM (mq.transaction_quantity) on_hand,msi.INVENTORY_ITEM_ID,msi.organization_id
    FROM apps.org_organization_definitions ood,
         apps.mtl_onhand_quantities mq,
         apps.mtl_system_items_b msi,
         mtl_item_locations b
   WHERE 1 = 1
     AND mq.organization_id = msi.organization_id
     AND ood.organization_id(+) = msi.organization_id
     AND mq.inventory_item_id = msi.inventory_item_id
     AND mq.locator_ID = b.Inventory_location_id
     --AND mq.inventory_item_id = b.Inventory_item_id
     --and mq.organization_id = b.Organization_id
     AND msi.segment1 = nvl(:P_ITEM_CODE,msi.segment1)
     and msi.organization_id=nvl(:p_organization_id,msi.organization_id)  
     and mq.subinventory_code=nvl(:p_subinvcode,mq.subinventory_code)
GROUP BY msi.segment1, ood.organization_name, mq.subinventory_code,msi.organization_id,msi.INVENTORY_ITEM_ID,b.SEGMENT1,b.SEGMENT2,b.SEGMENT3,b.SEGMENT4

============================Lotwise Query===============================

select --*
 moqd.ORGANIZATION_ID
,OOD.ORGANIZATION_NAME
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1 item
,SUM (moqd.primary_transaction_quantity) on_hand
,SUM (moqd.secondary_transaction_quantity) secondary_onhand
,msib.PRIMARY_UOM_CODE
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,(SELECT EXPIRATION_DATE FROM MTL_LOT_NUMBERS WHERE LOT_NUMBER=moqd.LOT_NUMBER
AND inventory_item_id =MOQD.inventory_item_id AND organization_id =MOQD.organization_id )LOT_EXPIRY_DATE
FROM
 mtl_onhand_quantities_detail moqd
,mtl_system_items_b msib
,ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE 1 = 1
     --AND moqd.organization_id = 143
     /*AND moqd.subinventory_code = 'R DRY HALA'
     AND moqd.inventory_item_id = 9095
     AND moqd.owning_organization_id = 143
     AND moqd.planning_organization_id = 143*/
     AND moqd.INVENTORY_ITEM_ID=msib.INVENTORY_ITEM_ID
     AND moqd.organization_id=msib.organization_id
     AND OOD.organization_id=msib.organization_id
group by
moqd.ORGANIZATION_ID
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1  
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,msib.PRIMARY_UOM_CODE
,OOD.ORGANIZATION_NAME
,MOQD.inventory_item_id

=================Using API=====================================

create or replace function XXMADN_GET_OHQTY(
p_inv_item_id in varchar2,
p_org_id number,
p_qty_type          IN VARCHAR2
)
return number is

   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;
   L_QTY NUMBER;
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       msib.inventory_item_id = p_inv_item_id
            AND msib.organization_id = mp.organization_id
            AND msib.organization_id =p_org_id; -- :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
   );

 IF
p_qty_type='OHQ' THEN --On Hand qty
L_QTY :=v_qoh; --v_QuantityOnhand;
ELSE IF
p_qty_type='ATR' THEN --Available to Reserve
L_QTY :=v_atr;
ELSE IF
p_qty_type='ATT' THEN --Available to Transact
L_QTY :=v_att;
END IF;
END IF;
END IF;

 return L_QTY;
 --return v_atr;

   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 XXMADN_GET_OHQTY;

select XXMADN_GET_OHQTY(183645,1404,'OHQ')from dual