Monday, 30 September 2019

Query to Get Employee Supervisor Relationships

SELECT
    DISTINCT
      LPAD(' ',5*(LEVEL-1)) || PPF.FULL_NAME,
      PERA.SUPERVISOR_ID,
      PERA.PERSON_ID,
      PER_JOBS.NAME JobName,
      LEVEL,
      SYS_CONNECT_BY_PATH(pera.person_id, '/') Path
FROM PER_ASSIGNMENTS_F PERA,PER_JOBS,PER_ALL_PEOPLE_F PPF
WHERE PER_JOBS.JOB_ID = PERA.JOB_ID
    AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
    AND PPF.PERSON_ID = PERA.PERSON_ID
    AND EXISTS
      (SELECT '1'
        FROM PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1
        WHERE TRUNC(SYSDATE) BETWEEN PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE
        AND PERF.PERSON_ID  = PERA.SUPERVISOR_ID
        AND PERA1.PERSON_ID = PERF.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE
        AND PERA1.PRIMARY_FLAG    = 'Y'
        AND PERA1.ASSIGNMENT_TYPE = 'E'
        AND EXISTS
          (SELECT '1' FROM PER_PERSON_TYPES PPT WHERE PPT.SYSTEM_PERSON_TYPE IN ('EMP','EMP_APL') AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID)
      )
      START WITH PERA.PERSON_ID = 6247
    AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE
    AND PERA.PRIMARY_FLAG                     = 'Y'
    AND PERA.ASSIGNMENT_TYPE                  = 'E'
  CONNECT BY NOCYCLE PRIOR PERA.PERSON_ID = PERA.SUPERVISOR_ID
    AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE
    AND PERA.PRIMARY_FLAG    = 'Y'
    AND PERA.ASSIGNMENT_TYPE = 'E'
ORDER BY PATH

Query to get Detail Customer Information

SELECT DISTINCT hp.party_name "Customer Name",
  hca.account_number,
  hca.status,
  hcsu.location,
  hcsu.site_use_code,
  hcsu.status loc_stat,
  ps.class,
  hcsu.site_use_id,
  hcpc.name profile_name,
  hl.address1,
  hl.address2,
  hl.address3,
  hl.city,
  hl.state,
  hl.postal_code,
  ps.customer_id,
  ps.customer_site_use_id,
  hps.identifying_address_flag,
  ps.trx_date,
  HOU.NAME "Operating Unit"
FROM apps.hz_parties hp,
  apps.hz_party_sites hps,
  apps.hz_locations hl,
  apps.hz_cust_accounts hca,
  apps.hz_cust_acct_sites hcas,
  apps.hz_cust_site_uses hcsu,
  apps.hz_customer_profiles hcp,
  apps.hz_cust_profile_classes hcpc,
  apps.ar_payment_schedules_all ps,
  apps.hr_operating_units hou
WHERE hp.party_id           = hca.party_id(+)
AND hp.party_id             = hcp.party_id
AND hp.party_id             = hps.party_id
AND hps.party_site_id       = hcas.party_site_id
AND hps.location_id         = hl.location_id
AND hca.cust_account_id     = hcas.cust_account_id
AND hcas.cust_acct_site_id  = hcsu.cust_acct_site_id
AND hca.cust_account_id     = hcp.cust_account_id
AND hca.cust_account_id     = ps.customer_id
AND hcp.profile_class_id    = hcpc.profile_class_id
AND ps.customer_site_use_id =hcsu.site_use_id
and hcsu.org_id =hou.organization_id;

Get Onhand Quantity From and API

DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_org_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
   -- Set the variable values
  V_ITEM_ID             := '165792';
  V_ORG_ID              := 1090;
  v_qoh                 := NULL;
  v_rqoh                := NULL;
  v_atr                 := NULL;
  v_lot_control_code    := FALSE;
  V_SERIAL_CONTROL_CODE := false;
-- Set the org context
  FND_CLIENT_INFO.SET_ORG_CONTEXT (1);

-- Call API
   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_org_id,
    p_inventory_item_id        => v_item_id,
    p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode,
    -- or 3
    p_is_revision_control      => FALSE,
    p_is_lot_control           => v_lot_control_code,
    -- is_lot_control,
    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_cost_group_id            => NULL,       -- cg_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;

API to update customer Profile in Oracle Applications

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);
   g_org_id                                      NUMBER :=Fnd_Profile.Value('ORG_ID');
   G_User_Name                             Varchar2(200):=Fnd_Profile.Value('USERNAME');
   g_resp_name                                varchar2(200):=fnd_profile.value('RESP_NAME');

CURSOR c_data_rtvl --------+=data retrival cursor
Is
  Select Hcp.Object_Version_Number Obj_Ver_No,
    Hcp.Cust_Account_Profile_Id Account_Profile
  FROM Hz_Customer_Profiles Hcp,
    Hz_Parties Hp,
    Hz_Cust_Accounts Hca
  WHERE Hcp.Attribute5   IS NULL
  AND Hca.Party_Id        = Hp.Party_Id
  AND Hca.Cust_Account_Id = Hcp.Cust_Account_Id
  And Hcp.Attribute_Category Like 'ALL_CAT'
  And Hca.Account_Number Like 'C0001%';

CURSOR c_cntx_vlu --------+=context value cursor
Is
  Select Afu.User_Id User_Id,
    Aft.Application_Id Application_Id,
    Aft.Responsibility_Id resp_id
  FROM Applsys.Fnd_User Afu,
    applsys.fnd_responsibility_tl aft
  WHERE Afu.User_Name LIKE g_user_name
  AND aft.responsibility_name LIKE g_resp_name;
Cdr_Rec C_Data_Rtvl%Rowtype;
ccv_rec c_cntx_vlu%ROWTYPE;

BEGIN
-- Setting the Context --
  Open C_Cntx_Vlu;
  Fetch C_Cntx_Vlu Into ccv_rec;
 
   Mo_Global.Init ('AR');
   Fnd_Global.Apps_Initialize (User_Id           => Ccv_Rec.User_Id,
                               Resp_Id           => Ccv_Rec.Application_Id,
                               resp_appl_id      => ccv_rec.resp_id
                              );
   mo_global.set_policy_context ('S', g_org_id);
   fnd_global.set_nls_context ('AMERICAN');
                                                                          -- Initializing the Mandatory API parameters
  For Cdr_Rec In  C_Data_Rtvl
   Loop
   p_customer_profile_rec_type.cust_account_profile_id := Cdr_Rec.Account_Profile; --first value
   P_Customer_Profile_Rec_Type.Credit_Rating :=            Null;
   P_Customer_Profile_Rec_Type.Attribute5 :=                 'S';
   p_object_version_number :=                                         Cdr_Rec.Obj_Ver_No;          --always change this also
   DBMS_OUTPUT.put_line
          ('Calling the API hz_customer_profile_v2pub.update_customer_profile');
   hz_customer_profile_v2pub.update_customer_profile
                (p_init_msg_list                         => fnd_api.g_true,
                 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
                );

   IF x_return_status = fnd_api.g_ret_sts_success
   Then
      --COMMIT;
      DBMS_OUTPUT.put_line ('Updation of Customer Profile is Successful ');
      DBMS_OUTPUT.put_line ('Output information ....');
      DBMS_OUTPUT.put_line (   'Object Version Number = '
                            || TO_CHAR (p_object_version_number)
                           );
      DBMS_OUTPUT.put_line (   'CDE STMT DIST = '
                            || p_customer_profile_rec_type.attribute5
                           );

   ELSE
      DBMS_OUTPUT.put_line (   'Updation of Customer Profile got failed:'
                            || x_msg_data
                           );
      ROLLBACK;

      FOR i IN 1 .. x_msg_count
      LOOP
         x_msg_data := fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F');
         DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
      END LOOP;
   End If;
   EXIT WHEN c_data_rtvl%NOTFOUND;-----------look up
end loop;
   DBMS_OUTPUT.put_line ('Completion of API');
END;

API to Update a Customer Account TCA R12

DECLARE
p_cust_account_rec      HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_object_version_number NUMBER;
x_return_status         VARCHAR2(2000);
x_msg_count             NUMBER;
x_msg_data              VARCHAR2(2000);

BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 1318
                            ,resp_id      => 50559
                            ,resp_appl_id => 222);
mo_global.set_policy_context('S',204);
fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters
p_cust_account_rec.cust_account_id := 150734;
p_cust_account_rec.customer_type   := 'R';  -- Should be available under the lookup_type "CUSTOMER_TYPE"
p_cust_account_rec.account_name    := 'TCA - Account';
p_object_version_number            := 1;

DBMS_OUTPUT.PUT_LINE('Calling the API hz_cust_account_v2pub.update_cust_account');

HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT
                  (
                    p_init_msg_list         => FND_API.G_TRUE,
                    p_cust_account_rec      => p_cust_account_rec,
                    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
                          );

IF  x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Updation of Customer Account is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....'); 
    dbms_output.put_line('Object Version Number ='||p_object_version_number);
   
ELSE
    DBMS_OUTPUT.put_line ('Updation of Customer Account got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;

API for Bank Account Creation in Oracle Apps

DECLARE
   p_init_msg_list   VARCHAR2 (200);
   p_acct_rec        apps.ce_bank_pub.bankacct_rec_type;
   x_acct_id         NUMBER;
   x_return_status   VARCHAR2 (200);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (4000);
   p_count           NUMBER;

BEGIN
   p_init_msg_list := NULL;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
   p_acct_rec.account_owner_party_id := 2041;

   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 14256789;
   p_acct_rec.currency := 'INR';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;

   CE_BANK_PUB.CREATE_BANK_ACCT
                  (p_init_msg_list      =>p_init_msg_list,
                   p_acct_rec           => p_acct_rec,
                   x_acct_id            => x_acct_id,
                   x_return_status      =>x_return_status,
                   x_msg_count          => x_msg_count,
                   x_msg_data           => x_msg_data
                  );
                     
   DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_id);
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

   IF x_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
   ELSIF x_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF x_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
      END LOOP;
   END IF;
END;

Friday, 20 September 2019

Function to calculate Item Cost

CREATE OR REPLACE FUNCTION XXTTK_ITEM_COST (P_INV_ITEM_ID IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
                                           P_INV_ORG_ID    IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE )
RETURN NUMBER

AS

L_PERIOD  gmf_period_statuses.PERIOD_CODE%TYPE;
L_COST  NUMBER;


BEGIN

BEGIN

select max(c.period_code)INTO L_PERIOD  FROM cm_cmpt_dtl_vw a,
                                  mtl_system_items b,
                                  gmf_period_statuses c,mtl_lot_numbers mln
                            WHERE 1 = 1          --a.organization_id = :org_id
                           -- AND b.organization_id >= in ( 250,178)
                              AND a.inventory_item_id = b.inventory_item_id
                              AND a.period_id = c.period_id
                              AND a.inventory_item_id = mln.inventory_item_id
                              AND a.organization_id = mln.organization_id
                              AND a.inventory_item_id=P_INV_ITEM_ID;
                             
                              dbms_output.put_line('L_PERIOD '||L_PERIOD);
                             
                              EXCEPTION
                             
                              WHEN OTHERS THEN
                              FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found While Capturing the item Cost Period');
                           dbms_output.put_line('No Data Found While Capturing the item Cost Period');
                             
                              end;
                             
                             
                             
                             
                             
                             IF L_PERIOD IS NOT NULL THEN
                           
                             BEGIN
                           
                             SELECT   SUM (cmpnt_cost) INTO L_COST
                             FROM cm_cmpt_dtl_vw a,
                                  mtl_system_items b,
                                  gmf_period_statuses c
                                   --mtl_lot_numbers mln
                            WHERE 1 = 1          --a.organization_id = :org_id
                             AND b.organization_id = P_INV_ORG_ID    --1238
                              AND a.inventory_item_id = b.inventory_item_id
                              AND a.period_id = c.period_id
                             -- AND a.inventory_item_id = mln.inventory_item_id
                              --AND a.organization_id = mln.organization_id
                              AND c.period_code =L_PERIOD
                              AND b.inventory_item_id=P_INV_ITEM_ID
                         GROUP BY a.inventory_item_id,
                                  segment1,
                                  a.period_id,
                                  b.description,
                                  primary_uom_code;
                                 
                                dbms_output.put_line('L_COST '||L_COST);
                             
                              EXCEPTION
                             
                              WHEN OTHERS THEN
                            FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found While Capturing the item Cost Period');
                           dbms_output.put_line('No Data Found While Capturing the item Cost Period');
                             
                              end;
                                 
                                  end if;
                                 
                                  Return L_COST;
                                 
                                  EXCEPTION WHEN OTHERS THEN
                                 
                                 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error IN Main XXTTK_ITEM_COST function');
                             
                               dbms_output.put_line('Error IN Main XXTTK_ITEM_COST function');
                                 
                                  Return NULL;
                                 
                                  end;