How to Get Privelege Leave Initial Balance Using Seeded Oracle Package

How to Get Privelege Leave Initial Balance Using Seeded Oracle Package


SELECT papf.person_id,
         paaf.assignment_id,
         papf.employee_number,
         papf.FULL_NAME,
         'Privelege Leave Initial Balance' element_name,
         per_accrual_calc_functions.Get_Other_Net_Contribution (
            paaf.assignment_id,
            1063,  --ACCRUAL_PLAN_ID from PAY_ACCRUAL_PLANS table
            '01-APR-2014',  --Calculation_Date
            '01-JAN-2014') --Start_Date
            Privelege_Leave_Initial_Bal
    FROM per_all_people_f papf, per_all_assignments_f paaf
   WHERE 1 = 1 AND papf.person_id = paaf.person_id
         AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.EFFECTIVE_START_DATE)
                                 AND TRUNC (papf.EFFECTIVE_END_DATE)
         AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.EFFECTIVE_START_DATE)
                                 AND TRUNC (paaf.EFFECTIVE_END_DATE)
         AND EMPLOYEE_NUMBER IS NOT NULL
--and papf.employee_number='500015'
ORDER BY 1;

PO Approval and Hierarchy Details Script

PO Approval and Hierarchy Details Script

PO Approval and Hierarchy Details Script



PO Hierarchy Details Script

SELECT *
  FROM (SELECT POS_STRUCTURE_VERSION_ID,
               b.name structure_name,
               PARENT_POSITION_ID,
               (SELECT name
                  FROM hr_all_positions_f
                 WHERE position_id = PARENT_POSITION_ID)
                  PARENT_POSITION_NAME,
               SUBORDINATE_POSITION_ID,
               (SELECT name
                  FROM hr_all_positions_f
                 WHERE position_id = SUBORDINATE_POSITION_ID)
                  SUBORDINATE_POSITION_NAME
          --(select full_name from per_all_people_f where person_id in(select person_id from per_all_assignments_f where position_id=SUBORDINATE_POSITION_ID))
          FROM per_pos_structure_elements_v a, PER_POSITION_STRUCTURES b
         WHERE     1 = 1
               AND a.POS_STRUCTURE_VERSION_ID = b.POSITION_STRUCTURE_ID
               AND POS_STRUCTURE_VERSION_ID <> 0)
 WHERE STRUCTURE_NAME = 'XX_STRUTURE_NAME'          




PO Approval Group Details Script

SELECT decode(ppca.org_id,83,'XXORG_NAME',181,'XXORG_NAME') Org_Name,
--       ppca.position_id,
       (select name from per_positions where position_id=ppca.position_id)Position_Name,
--       ppca.job_id,
       (select name from per_jobs where job_id=ppca.job_id) Job_Name,
--       ppca.control_group_id,
--       ppca.control_function_id,
       control_function_name Document_Type,
       control_group_name Approval_Group
  FROM po_position_controls_all ppca,
       po_control_groups_all pcga,
       po_control_functions pcf
 WHERE     1 = 1
       AND ppca.control_group_id = pcga.control_group_id
       AND ppca.control_function_id = pcf.control_function_id;

Inventory Item Details Script

Inventory Item Details Script

SELECT
       a.ORGANIZATION_ID,
       a.ORGANIZATION_CODE,
       a.SUBINVENTORY_CODE,
       a.LOCATOR_ID,
       a.LOCATOR,
       a.INVENTORY_ITEM_ID,
       a.ITEM,
       a.ITEM_DESCRIPTION,
       a.UOM,
       sum(a.ON_HAND) ON_HAND_QTY,
       b.ITEM_COST,
       (sum(a.ON_HAND) *  b.ITEM_COST) Item_Value
  FROM mtl_onhand_total_mwb_v a
  ,cst_item_costs b
 WHERE a.ORGANIZATION_ID = 121
 and a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID(+)
 and a.ORGANIZATION_ID=b.ORGANIZATION_ID(+)
-- and INVENTORY_ITEM_ID=1381681
 group by a.ORGANIZATION_ID,
       a.ORGANIZATION_CODE,
       a.SUBINVENTORY_CODE,
       a.LOCATOR_ID,
       a.LOCATOR,
       a.INVENTORY_ITEM_ID,
       a.ITEM,
       a.ITEM_DESCRIPTION,
       a.UOM,
       b.ITEM_COST order by 6 desc;

Concurrent Program and Executable Details

Concurrent Program and Executable Details

The following SQL query provide you executable file name ,top name , etc...

--Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name

SELECT b.user_concurrent_program_name,
       b.concurrent_program_name,
       a.user_executable_name,
       DECODE (a.execution_method_code,
               'I', 'PL/SQL Stored Procedure',
               'H', 'Host',
               'S', 'Immediate',
               'J', 'Java Stored Procedure',
               'K', 'Java concurrent program',
               'M', 'Multi Language Function',
               'P', 'Oracle reports',
               'B', 'Request Set Stage Function',
               'A', 'Spawned',
               'L', 'SQL*Loader',
               'Q', 'SQL*Plus',
               'E', 'Pearl concurrent Programm',
               'Unkown Type')
          TYPE,
       a.execution_file_name,
       a.execution_file_path,
       a.application_name,
       c.basepath,
       a.executable_id
  FROM fnd_executables_form_v a,
       fnd_concurrent_programs_vl b,
       fnd_application c
 WHERE     a.executable_id = b.executable_id
       AND a.application_id = c.application_id
--       AND EXECUTABLE_NAME = 'XX'
       AND b.user_concurrent_program_name like'XX%';

Execute Immediate in Oracle Reports

Execute Immediate in Oracle Reports

Using this EXECUTE IMMEDIATE command directy in reports is not possible, if you tried to use, i.e. EXECUTE IMMEDIATE ‘drop TABLE TEST’,
this error message will occured,  “this feature is not supported in client-side programs”.
EXECUTE IMMEDIATE is only supported on the server side.,but you can
work around this by creating astored procedure in the database and then using this
procedure in reports.

The database stored procedure can be created as follows:

CREATE OR REPLACE PROCEDURE DYNAMIC_SQL(STMNT CHAR) IS
BEGIN
   BEGIN
     EXECUTE IMMEDIATE STMNT;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error while executing dynamic sql');
   END;
END;

Now, this procedure can be called in Reports.
For Example:
In the BeforeParam Trigger,the procedure can be called as:
DYNAMIC_SQL('DROP TABLE TEST');
This will drop the table 'TEST' from the database.

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts Details

R12 Supplier Bank Accounts
In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. The supplier (or External) bank account information are stored in the table called IBY_EXT_BANK_ACCOUNTS. The bank and bank branches information are stored in the table HZ_PARTIES. They are linked together through Relationships (in HZ_RELATIONSHIP).

There is a separate link for both Bank to Branch and also from Branch to Bank. Bank sites and Location information are stored in tables: HZ_PARTY_SITES and HZ_LOCATIONS. The bank_id and branch_id fields of IBY_EXT_BANK_ACCOUNTS table link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table (IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id).

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.

When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees.

Queries:


SELECT  aps.vendor_name "VERDOR NAME",
        apss.vendor_site_code "VENDOR SITE CODE",
        ieb.bank_name "BANK NAME",
        iebb.bank_branch_name "BANK BRANCH NAME",
        iebb.branch_number "BRANCH NUMBER",
        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM    ap.ap_suppliers aps,
        ap.ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts ieba,
        apps.iby_account_owners iao,
        apps.iby_ext_banks_v ieb,
        apps.iby_ext_bank_branches_v iebb
WHERE   aps.vendor_id = apss.vendor_id
        and iao.account_owner_party_id = aps.party_id
        and ieba.ext_bank_account_id = iao.ext_bank_account_id
        and ieb.bank_party_id = iebb.bank_party_id
        and ieba.branch_id = iebb.branch_party_id
        and ieba.bank_id = ieb.bank_party_id;
Oracle PL/SQL


SELECT party_supp.party_name supplier_name
,      aps.segment1          supplier_number
,      ass.vendor_site_code  supplier_site
,      ieb.bank_account_num
,      ieb.bank_account_name
,      party_bank.party_name bank_name
,      branch_prof.bank_or_branch_number bank_number
,      party_branch.party_name branch_name
,      branch_prof.bank_or_branch_number branch_number
FROM   hz_parties party_supp
,      ap_suppliers aps
,      hz_party_sites site_supp
,      ap_supplier_sites_all ass
,      iby_external_payees_all iep
,      iby_pmt_instr_uses_all ipi
,      iby_ext_bank_accounts ieb
,      hz_parties party_bank
,      hz_parties party_branch
,      hz_organization_profiles bank_prof
,      hz_organization_profiles branch_prof
WHERE  party_supp.party_id = aps.party_id
AND    party_supp.party_id = site_supp.party_id
AND    site_supp.party_site_id = ass.party_site_id
AND    ass.vendor_id = aps.vendor_id
AND    iep.payee_party_id = party_supp.party_id
AND    iep.party_site_id = site_supp.party_site_id
AND    iep.supplier_site_id = ass.vendor_site_id
AND    iep.ext_payee_id = ipi.ext_pmt_party_id
AND    ipi.instrument_id = ieb.ext_bank_account_id
AND    ieb.bank_id = party_bank.party_id
AND    ieb.bank_id = party_branch.party_id
AND    party_branch.party_id = branch_prof.party_id
AND    party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
,        ass.vendor_site_code;

Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program

Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program

Here we can segregate this issue into 2 Parts.

1. Invoices in status of Selected for validation when cancelled Invoice Validation Program it will be stamped with Validation_request_id.
2. Invoice Validation program keep on running from hours together and want to cancel the Request

When ever we cancel the invoice validation program when its running, it should roolback all the invoices back to its normal status but some/many times it will not rollback the invoices and it will stamp the cancelled request id on all the invoices under column VALIDATION_REQUEST_ID of AP_INVOICE_HEADERS_ALL.

What ever the invoices stamped with request_id can be get with this query to know the count or list of invoices.
SQL:
select * from ap_invoices_all where validation_request_id = 'XXXXX';-- or where validation_request_id is not null;

Once we extract these invoices we need to fix the invoices with the help of data-fix to rollback stamped invoices.

Solution: (Reference Patch#17428522)

1. Cancel the  request (If the request is not yet cancel the request)

A.  Cancel present child request #46981209

B.  Wait for few minutes to let parent request complete, it might initiate other new child requests in couple of minutes.

C.  If parent request do not initiate other child requests and parent request not completes in few minutes, cancel parent request as well.

2. Apply patch#17428522 and Run script: ap_inv_val_prb_sel.sql

Path: $AP_TOP/patch/115/sql/ ap_inv_val_prb_sel.sql

Temp Driver Table:
AP_TEMP_DATA_DRIVER_9327208

If you want to extract the invoices from the driver table use this query

select * from AP_TEMP_DATA_DRIVER_9327208

3. Run update statement:

update AP_TEMP_DATA_DRIVER_9327208
set process_flag=’N’
where validation_request_id is null;

commit;
/
4. Run fix script: ap_inv_val_prb_fix.sql

Path:$AP_TOP/patch/115/sql/ ap_inv_val_prb_fix.sql

Once the fix completed successfully, run the below again here you should get '0' records

Query:
select count(invoice_id) from ap_invoices_all where validation_request_id is not null;