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;

Thursday, 19 September 2019

Receivables to GL Link in 11i

SELECT je_header_id, line_number, trx_number, myrank, item_number,
       customer_number, customer_name, SOURCE, CATEGORY, batch_name,
       transaction_date, posted_date, period_name, currency_code,
       account_number, description,
       CASE
          WHEN myrank = 1
             THEN debits
          ELSE NULL
       END debits, CASE
          WHEN myrank = 1
             THEN credits
          ELSE NULL
       END credits
  FROM (SELECT   rcta.trx_number, rcta.customer_trx_id, gh.je_header_id,
                 ac.customer_number, ac.customer_name, gh.je_source SOURCE,
                 gh.je_category CATEGORY, gh.NAME batch_name,
                 TO_CHAR (gh.default_effective_date,
                          'DD-MON-YYYY'
                         ) transaction_date,
                 TO_CHAR (gh.posted_date, 'DD-MON-YYYY') posted_date,
                 gh.period_name period_name, gh.currency_code currency_code,
                 gl.je_line_num line_number,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5
                 || '.'
                 || gcc.segment6 account_number,
                 gh.description description, gl.accounted_dr debits,
                 gl.accounted_cr credits, rctla.inventory_item_id,
                 (SELECT segment1
                    FROM mtl_system_items_b msib
                   WHERE msib.inventory_item_id =
                                          rctla.inventory_item_id
                     AND msib.organization_id = rctla.warehouse_id)
                                                                  item_number,
                 DENSE_RANK () OVER (PARTITION BY gh.je_header_id, gl.je_line_num ORDER BY rctlg.cust_trx_line_gl_dist_id)
                                                                    AS myrank
            FROM gl_je_headers gh,
                 gl_je_lines gl,
                 gl_code_combinations gcc,
                 fnd_user fu,
                 gl_sets_of_books gsob,
                 gl_import_references gir,
                 ra_customer_trx_all rcta,
                 ra_cust_trx_line_gl_dist_all rctlg,
                 ra_customer_trx_lines_all rctla,
                 ar_customers ac
           WHERE gh.je_header_id = gl.je_header_id
             AND gl.code_combination_id = gcc.code_combination_id
             AND gh.last_updated_by = fu.user_id
             AND gh.period_name = :p_period
             AND gh.status = 'P'
             AND gh.set_of_books_id = gsob.set_of_books_id
             AND gsob.set_of_books_id = 1
             AND gh.je_source = 'Receivables'
             AND gir.je_header_id = gh.je_header_id
             AND gir.je_line_num = gl.je_line_num
             AND gir.je_batch_id = gh.je_batch_id
             AND TO_CHAR (gir.reference_2) = TO_CHAR (rcta.customer_trx_id)
             AND TO_CHAR (gir.reference_3) =
                                      TO_CHAR (rctlg.cust_trx_line_gl_dist_id)
             AND rcta.customer_trx_id = rctlg.customer_trx_id
             AND rctla.customer_trx_line_id = rctlg.customer_trx_line_id
             AND rctlg.customer_trx_id = rctla.customer_trx_id
             AND rctla.customer_trx_id = rcta.customer_trx_id
             AND rcta.bill_to_customer_id = ac.customer_id(+)
        ORDER BY gh.je_header_id, gl.je_line_num)

Script for getting Sales Tax Transactions

SELECT DISTINCT aia.invoice_date transaction_date,
                aia.invoice_num transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = UPPER (zl.event_class_code)
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                aia.gl_date accounting_date,
                psv.vendor_name third_party, pss.vendor_site_code site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ap_invoices_all aia,
                ap_invoice_lines_all ail,
                poz_suppliers_v psv,
                poz_supplier_sites_v pss,
                zx_lines zl,
                xle_entity_profiles hou,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE aia.invoice_id = ail.invoice_id
            AND aia.vendor_id = psv.vendor_id
            AND aia.vendor_site_id = pss.vendor_site_id
            AND ail.invoice_id = zl.trx_id(+)
            AND ail.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = aia.legal_entity_id           
            AND zl.cancel_flag <> 'Y'
            AND aia.cancelled_date IS NULL
UNION
SELECT DISTINCT rct.trx_date transaction_date,
                rct.trx_number transaction_number, hou.NAME company_name,               
                zl.tax_rate_code tax_code, zl.tax_rate tax_rate,
                (SELECT meaning
                   FROM fnd_lookup_values_vl
                  WHERE lookup_code = zl.event_class_code
                    AND lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
                    AND enabled_flag = 'Y') transaction_class,               
                gl_date accounting_date,
                hca.account_name third_party,
                NVL (hps.party_site_name, hps.party_site_number) site,
                zl.tax_amt tax_amount, zl.trx_currency_code entered_curr,
                zl.taxable_amt_funcl_curr tax_base_amount,
                NVL (zl.tax_currency_code, zl.trx_currency_code ) functional_currency,
                tax_amt_funcl_curr tax_amount_func,
                xle.accounting_status, xle.tgl GL_Transfer_Status
           FROM ra_customer_trx_all rct,
                ra_customer_trx_lines_all rctl,
                zx_lines zl,
                xle_entity_profiles hou,
                hz_cust_site_uses_all hcsua,
                hz_cust_acct_sites_all hcasa,
                hz_cust_accounts hca,
                hz_party_sites hps,
                (SELECT DISTINCT customer_trx_line_id, gl_date,
                                 customer_trx_id
                            FROM ra_cust_trx_line_gl_dist_all) dist_tab,
                (SELECT CASE
                           WHEN xae.gl_transfer_date IS NULL
                              THEN 'Not Transferred'
                           ELSE 'Transferred'
                        END tgl,
                        xte.source_id_int_1,
                        CASE
                           WHEN xae.accounting_date IS NULL
                              THEN 'Draft'
                           ELSE 'Accounted'
                        END accounting_status
                   FROM xla_transaction_entities xte, xla_ae_headers xae
                  WHERE xte.entity_id = xae.entity_id) xle
          WHERE rct.customer_trx_id = rctl.customer_trx_id
            AND rct.customer_trx_id = zl.trx_id(+)
            AND rctl.line_number = zl.trx_line_number(+)
            AND hou.legal_entity_id(+) = rct.legal_entity_id
            AND zl.entity_code = 'TRANSACTIONS'
            AND zl.cancel_flag <> 'Y'
            AND rctl.line_type = 'LINE'
            AND hcsua.site_use_id = rct.bill_to_site_use_id
            AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
            AND hcasa.cust_account_id = hca.cust_account_id
            AND hps.party_site_id = hcasa.party_site_id
            AND dist_tab.customer_trx_id = rct.customer_trx_id
            AND dist_tab.customer_trx_line_id = rctl.customer_trx_line_id
            

Script for Extracting InterCompany batch details

SELECT DISTINCT xep.NAME provider, ftb.status batch_status,
                        ftb.batch_number batch_number,
                        ftb.description batch_description,
                        ftb.batch_date batch_date,
                        ftb.gl_date accounting_date,
                        fttb.trx_type_code transaction_type,
                        (SELECT xep2.NAME
                           FROM xle_entity_profiles xep2
                          WHERE 1 = 1
                            AND xep2.legal_entity_id = fth.to_le_id) receiver,
                        fth.trx_number transaction_number,                       
                        DECODE (party_type_flag,
                                'I', 'Provider',
                                'R', 'Receiver'
                               ) distribution,
                        fdl.dist_number distribution_number,
                        ftb.currency_code currency,
                        NVL (fdl.amount_dr,
                             -1 * fdl.amount_cr) entered_amount,
                        (SELECT gll.currency_code
                           FROM gl_ledgers gll,
                                gl_je_batches gjb,
                                gl_je_headers gjh,
                                gl_je_lines gjl,
                                gl_import_references gir
                          WHERE gll.ledger_id = gjh.ledger_id
                            AND gjb.je_batch_id = gjh.je_batch_id
                            AND gjh.je_header_id = gjl.je_header_id
                            AND gir.reference_2 = ftb.batch_id
                            AND gir.reference_3 = fth.trx_id
                            AND gir.reference_4 = ftl.line_id
                            AND gir.reference_5 = fdl.dist_id
                            AND gir.je_batch_id = gjb.je_batch_id
                            AND gir.je_header_id = gjh.je_header_id
                            AND gir.je_line_num = gjl.je_line_num
                            AND (   gjh.ledger_id = fth.to_ledger_id
                                 OR gjh.ledger_id = ftb.from_ledger_id
                                )
                            AND ROWNUM = 1) primary_currency,
                        (SELECT NVL (gjl.accounted_dr,
                                     -1 * gjl.accounted_cr
                                    )
                           FROM gl_je_batches gjb,
                                gl_je_headers gjh,
                                gl_je_lines gjl,
                                gl_import_references gir
                          WHERE gjb.je_batch_id = gjh.je_batch_id
                            AND gjh.je_header_id = gjl.je_header_id
                            AND gir.reference_2 = ftb.batch_id
                            AND gir.reference_3 = fth.trx_id
                            AND gir.reference_4 = ftl.line_id
                            AND gir.reference_5 = fdl.dist_id
                            AND gir.je_batch_id = gjb.je_batch_id
                            AND gir.je_header_id = gjh.je_header_id
                            AND gir.je_line_num = gjl.je_line_num
                            AND (   gjh.ledger_id = fth.to_ledger_id
                                 OR gjh.ledger_id = ftb.from_ledger_id
                                )
                            AND ROWNUM = 1) primary_amount,
                        fdl.description
                   FROM xle_entity_profiles xep,
                        fun_trx_batches ftb,
                        fun_trx_types_b fttb,
                        fun_trx_headers fth,
                        fun_trx_lines ftl,
                        fun_dist_lines fdl
                  WHERE 1 = 1
                    AND xep.legal_entity_id = ftb.from_le_id
                    AND fttb.trx_type_id = ftb.trx_type_id
                    AND ftb.batch_id = fth.batch_id
                    AND fdl.trx_id = fth.trx_id
                    AND ftl.trx_id = fth.trx_id
                    AND ftl.line_id = fdl.line_id
                    AND ftb.trx_type_code = 'IC_GL'

Script to get Project Listing

SELECT   pt.project_id, ppa.NAME project_name,
         ppa.description project_description, ppa.segment1 project_number,
         pt.task_number,
         ppa.project_status_code project_status,
         (SELECT DISTINCT (SELECT person_number
                             FROM per_people_x ppx
                            WHERE ppx.person_id =
                                                ppn.person_id)
                     FROM pjf_project_parties proj_dir,
                          pjf_proj_role_types_tl prt,
                          per_all_assignments_f paf,
                          per_person_names_f ppn
                    WHERE proj_dir.project_id = ppa.project_id
                      AND proj_dir.project_role_id = prt.project_role_id
                      AND prt.project_role_name = 'Project Manager'
                      AND proj_dir.resource_source_id = paf.person_id
                      AND paf.assignment_status_type = 'ACTIVE'
                      AND paf.person_id = ppn.person_id
                      AND ppn.name_type = 'GLOBAL'
                      AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                              AND NVL (paf.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                      AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active
                                              AND NVL
                                                    (proj_dir.end_date_active,
                                                     SYSDATE + 1
                                                    )
                      AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date
                                              AND NVL (ppn.effective_end_date,
                                                       SYSDATE + 1
                                                      )
                      AND paf.primary_flag = 'Y'
                      AND ROWNUM < 2) project_manager
    FROM pjf_tasks_v pt ,
         pjf_projects_all_vl ppa
   WHERE 1 = 1 AND ppa.project_id = pt.project_id(+)         

Wednesday, 18 September 2019

Fusion HCM - Payroll Setup Sequence

Below are the Setup Sequence for Fusion Payroll. It differs as per the Client requirement.



1. Manage Payroll Calculation Card
2. Manage Payment Methods
3. Manage Salary Basis
4. Manage Consolidation Groups
5. Manage Payroll Definitions
6. Manage Payroll Balances
7. Manage Object Groups
8. Manage Payroll Event Groups
9. Manage Elements

Fusion HCM - Absence Management Setup Sequence


Below are the Setup Sequence for Fusion Absence Management. It differs as per the Client requirement.


1. Manage Work Shifts
2. Manage Work Workday Patterns
3. Manage Work Schedules
4. Manage Work Schedule Assignment Administration
5. Manage Geography Trees
6. Manage Calendar Events
7. Manage Eligibility Profiles
8. Manage Derived Factors
9. Manage Absence Reasons
10. Manage Absence Certifications
11. Manage Repeating Time Periods
12. Manage Absence Plans
13. Manage AbsenceTypes

Approval Management in Fusion


Approval Management in Fusion: Points to Ponder:

Oracle Business Process Management (BPM):

The BPM Worklist Application is a web-based application that lets users access tasks assigned to them and perform actions based on their roles in the approval process. Administrators can set up approval groups and task-level configuration via an administration portal or through the Oracle BPM Worklist.
As in EBS suite, we have different types of transactions (“Purchase Requisition Approval”) we have tasks defined for each Transaction in Fusion for example for I-Expenses the task is “Manage Expense Approvals and for Procurement the task is “Manage Requisition Approvals.
How to configure or edit Manage Requisition Approval process:
Navigate to: Setup and Maintenance > Select: Procurement > Setup button > Functional Areas: procurement> Show: All Tasks > Manage Requisition Approvals > Oracle BPM Worklist as shown below



Click on the Manage Requisition Approvals which will take you to Mange Requisition Approval Set up page as shown below:


You can create new rules by clicking Edit Rules button as shown in above screen shot or by selecting create option from Actions Drop Down.

An approval rule is composed of the following:
1.       Rule Name
2.       Condition
3.       Action

Rule Name:
The Rule Name is used to identify the approval rule.
• In cases where there are large numbers of rules, users can filter search results in the Query by Example fields.



Condition:
The Condition indicates when the approval rule will be applied.
A rule can contain multiple conditions, and you can select the “and” or “or” operators to indicate if all conditions in the approval rule must be true or if only one condition must be true for the approval rule to apply.
For example, if the requisition amount is less than 10,000 and requisitioning BU is US Business Unit. A condition can be defined using attributes seeded in a dimension or user-defined attributes. Please look into the section on how to create User Defined Attributes.
You can create the condition by using predefined Approval Task attributes or by using User Defined Attributes.

You can serch for any standard attributes and can create the condition as shown below.


Action:
An action defines what needs to be done if the conditions of a rule is met.
It identifies if approvals or FYI notifications are required and the type of users needed to approve or receive notification for a document.


The supported action types are: -
·         Approval required
·         Approval actions required from the recipients of the approval tasks - Automatic
·         Automatically approve or reject the approval task - Information only
·         FYI notifications sent to recipients


In the below screenshot you can see we created Action based on Condition

The Supported Routing Methods are:
·         Approval Group
·         Job-Level
·         Position Hierarchy
·         Single Approver
·         Supervisory Hierarchy

User-Defined Attributes
Organizations have different requirements for document approvals. Some of these requirements may include the need to perform currency conversions for amount based attributes or derive approvers based on a roll-up amounts across lines within a document with common attributes. These user-defined attributes are managed and used within a specific approval task.
There are two types of user-defined attributes they are:
·         Currency Based
·         Summation.


Currency Based on User-Defined Attributes:
You can define currency based attributes such that amounts indifferent currencies are converted to a specific currency for the rule evaluation.
For example, Acme Corp. creates an attribute “USD Requisition Amount” and uses the attribute for rule conditions, such as if USD Requisition Amount is less than 500, approvals from the preparer’s manager is required and so forth. If a requisition created in Mexico is submitted, where the functional currency is pesos, the requisition amount will first be converted to USD before rules are evaluated.


Summation User-Defined Attributes:
Customers who need to perform approval routings based on data aggregated across one or more attributes when a document contains more then one line, schedule or distribution can create summation user-defined attributes.
For example, Acme Corp’s approval policy requires the number of IT approvers to be based on the total amount of IT requests within a requisition. If the total IT amount is less than 1200 USD, then the IT manager needs to approve. If the IT amount exceeds 1200 USD, then the IT manager, director, and VP need to approve the document.

Deploying Approval Rules:
Any changes made to the rules or any rules that have been newly added have to be deployed for them to be effective.
·         Deploying approval rules will require users to Click on the Deploy button on the Edit Rules and on the Manage Approval Rules page.
·         If users do not intend to deploy the changes that were made to the rules, they can choose to click on the
·         Discard button to revert the rules to the last deployed state.

Save: Save or Save and Close will save the updated rules to the database without deploying them. Users can edit and save rules across multiple participants before deploying them.

Deploy: Deploy updates the rules engine and makes all the rule changes across all participants effective. It is displayed on both the Manage Approval Rules page and the Edit Rules page. The Deploy button is disabled by default and is enabled when there are rules that have been setting up Document Approvals updated and saved.
Discard: Discard is displayed on both the Manage Approval Rules page and the Edit Rules page. It is disabled by default and is enabled when there are rules that have been updated and saved. Discard removes any rule changes that were saved and the rules will go back to the last deployed state.


Once You Click on Deploy The below warning Message will Appear.



Click on the Confirmation Dialog Message.

Once the changes are deployed, the rule will be effective once by enabling (Enable) button adjacent to “Edit” button where you created or edited the rule as shown below.