Oracle Apps : Query to get the Position details of an employee in R12.1.3

The below query gives the details of an employee's current position in Oracle Apps R12.1.3.

 SELECT papf.person_id  
       ,papf.full_name  
       ,hapf.name                  position  
       ,papf.effective_start_date  employee_start_date  
       ,papf.effective_end_date    employee_end_date  
       ,asg.effective_start_date   assignment_start_date  
       ,asg.effective_end_date     assignment_end_date  
 FROM  per_all_people_f       papf  
      ,per_all_assignments_f  asg  
      ,hr_all_positions_f     hapf  
 WHERE papf.person_id = asg.person_id(+)  
 AND   SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date  
 AND   SYSDATE BETWEEN asg.effective_start_date  AND asg.effective_end_date  
 AND   SYSDATE BETWEEN hapf.effective_start_date AND hapf.effective_end_date  
 AND   asg.position_id = hapf.position_id  
 AND   UPPER(papf.full_name) LIKE UPPER('%Anoop%')  
 ORDER BY papf.full_name;  

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps : Query to get the Purchase Order (PO) details along with the Requisition details in R12.1.3

 The below gives the details of a Purchase Order (PO) details along with the Requisition details in Oracle Apps R12.1.3.

 SELECT  poh.segment1          po_number  
        ,mp.organization_code  org_code
        ,haou.name             org_name  
        ,porh.segment1         po_requisition_num  
        ,poh.creation_date     po_creation_date  
 FROM  po_headers_all              poh  
      ,po_lines_all                pol  
      ,po_line_locations_all       pll  
      ,po_distributions_all        pda  
      ,po_req_distributions_all    pord  
      ,po_requisition_lines_all    porl  
      ,po_requisition_headers_all  porh  
      ,mtl_parameters              mp  
      ,hr_all_organization_units   haou  
 WHERE 1=1
 AND   poh.po_header_id           = pol.po_header_id  
 AND   pll.po_header_id           = pol.po_header_id  
 AND   pll.po_line_id             = pol.po_line_id  
 AND   pda.po_header_id           = pol.po_header_id  
 AND   pda.po_line_id             = pol.po_line_id  
 AND   pda.line_location_id       = pll.line_location_id  
 AND   pda.req_distribution_id    = pord.distribution_id(+)  
 AND   pda.org_id                 = pord.org_id(+)  
 AND   pord.requisition_line_id   = porl.requisition_line_id(+)  
 AND   porl.requisition_header_id = porh.requisition_header_id(+)  
 AND   mp.organization_id         = poh.org_id  
 AND   haou.organization_id       = poh.org_id
 AND   poh.segment1               = '31200000'  
 AND   poh.org_id                 = 0 -- depending on which operating unit  
;  

Edited: An updated query with some more information fetched from other tables.

 SELECT poh.segment1          po_number  
       ,mp.organization_code  
       ,haou.organization_id  
       ,haou.name             org_name  
       ,porh.segment1         po_requisition_num  
       ,poh.creation_date     po_creation_date  
       ,aps.segment1          supplier_number  
       ,aps.vendor_name       supplier_name  
       ,apss.attribute4       cloud_supplier_site_id  
       ,apt.name terms  
       ,poh.vendor_id  
       ,poh.vendor_site_id  
       ,pol.line_num  
       ,pol.quantity          line_quantity_ordered     
       ,msib.segment1         line_item  
       ,msib.description  
       ,pol.item_description  
       ,pll.shipment_num  
       ,pda.distribution_num  
       ,pda.destination_subinventory  
       ,pda.deliver_to_person_id  
       ,(SELECT ppf.full_name   
         FROM    per_all_people_f ppf  
         WHERE ppf.person_id = pda.deliver_to_person_id  
         AND   sysdate between ppf.effective_start_date AND ppf.effective_end_date) deliver_to_person_name  
       ,pda.quantity_ordered dist_quantity_ordered  
 FROM  po_headers_all poh  
      ,po_lines_all pol  
      ,po_line_locations_all pll  
      ,po_distributions_all pda  
      ,po_req_distributions_all pord  
      ,po_requisition_lines_all porl  
      ,po_requisition_headers_all porh  
      ,mtl_parameters mp  
      ,hr_all_organization_units haou  
      ,ap_suppliers aps  
      ,ap_supplier_sites_all apss  
      ,ap_terms apt  
      ,mtl_system_items_b msib  
 WHERE 1=1  
 AND  poh.po_header_id           = pol.po_header_id  
 AND  pll.po_header_id           = pol.po_header_id  
 AND  pll.po_line_id             = pol.po_line_id  
 AND  pda.po_header_id           = pol.po_header_id  
 AND  pda.po_line_id             = pol.po_line_id  
 AND  pda.line_location_id       = pll.line_location_id  
 AND  pda.req_distribution_id    = pord.distribution_id(+)  
 AND  pda.org_id                 = pord.org_id(+)  
 AND  pord.requisition_line_id   = porl.requisition_line_id(+)  
 AND  porl.requisition_header_id = porh.requisition_header_id(+)  
 AND  mp.organization_id         = poh.org_id  
 AND  haou.organization_id       = poh.org_id  
 AND  aps.vendor_id              = poh.vendor_id  
 AND  apss.vendor_id             = aps.vendor_id   
 AND  apss.vendor_site_id        = poh.vendor_site_id  
 AND  apt.term_id                = poh.terms_id  
 AND  msib.organization_id (+)   = pol.org_id  
 AND  msib.inventory_item_id(+)  = pol.item_id  
 AND  poh.segment1               = '31200000'
 AND  poh.org_id                 = 0 -- depending on which operating unit;  

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps : Query to get the Bill-To, Ship-To and the Location details of a customer in R12

The below gives the details of a customer's Bill-To and Ship-To details along with the location/address details in Oracle Apps R12.1.3

 SELECT hca_bill.cust_account_id   bt_cust_account_id  
       ,hca_bill.account_number    bt_cust_account_num  
       ,hcsu_bill.location         bt_location  
       ,location_bill.address1     bt_address1  
       ,location_bill.city         bt_city  
       ,location_bill.state        bt_state  
       ,location_bill.postal_code  bt_postal_code  
       ,hca_ship.cust_account_id   st_cust_account_id  
       ,hca_ship.account_number    st_cust_account_num  
       ,hcsu_bill.location         st_location  
       ,location_ship.address1     st_address1  
       ,location_ship.city         st_city  
       ,location_ship.state        st_state  
 FROM  apps.hz_cust_accounts_all     hca_ship  
      ,apps.hz_cust_acct_sites_all   hcas_ship  
      ,apps.hz_cust_site_uses_all    hcsu_ship  
      ,apps.hz_party_sites           party_site_ship  
      ,apps.hz_parties               party_ship  
      ,apps.hz_locations             location_ship  
      ,hz_cust_accounts_all       hca_bill  
      ,hz_cust_acct_sites_all     hcas_bill  
      ,hz_cust_site_uses_all      hcsu_bill  
      ,apps.hz_party_sites        party_site_bill  
      ,apps.hz_parties            party_bill  
      ,apps.hz_locations          location_bill  
 WHERE 1=1  
 AND  hcas_bill.cust_account_id     = hca_bill.cust_account_id  
 AND  hcsu_bill.cust_acct_site_id   = hcas_bill.cust_acct_site_id  
 AND  party_site_bill.party_site_id = hcas_bill.party_site_id  
 AND  party_site_bill.party_id      = party_bill.party_id  
 AND  party_site_bill.location_id   = location_bill.location_id  
 AND  hcsu_bill.site_use_code       = 'BILL_TO'  
 AND  hca_ship.cust_account_id      = hcas_ship.cust_account_id  
 AND  hcas_ship.cust_acct_site_id   = hcsu_ship.cust_acct_site_id  
 AND  party_site_ship.party_site_id = hcas_ship.party_site_id  
 AND  party_site_ship.party_id      = party_ship.party_id  
 AND  party_site_ship.location_id   = location_ship.location_id  
 AND  hcsu_ship.site_use_code       = 'SHIP_TO'  
 AND  hcsu_ship.bill_to_site_use_id = hcsu_bill.site_use_id  
 AND  hca_bill.status        = 'A'  
 AND  hcas_bill.status       = 'A'  
 AND  hcsu_bill.status       = 'A'  
 AND  hca_ship.status        = 'A'  
 --AND  hca_ship.account_number = '738673'  
 AND  hca_bill.account_number = '1881880'  
 ;  

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: PO went to Pre-Approved status instead of Approved status

The below is an issue which we encountered in our  R12 12.1.3 TEST instance. I am posting this here, just in case it helps anyone else having the same issue.  

Issue: While testing the PO approval process, we noticed that the PO Approval has gone to Pre-Approved status after the final approver approved the PO. 

Cause: Workflow Background Process of PO Approval was not running for the Deferred option. 

Solution : Submit the Workflow Background Process for "PO Approval" Item type with Deferred option set to Yes..

After submitting the workflow background job, the PO went to Approved status.

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps : Approval List could not be generated

The below is an issue which we encountered in our  R12 12.1.3 TEST instance. I am posting this here, just in case it helps anyone else having the same issue.  This below mentioned is not the only cause for this issue.

Issue: While creating requisition the approval list was not getting generated and it was coming up with the message  "Approval List could not be generated".

Cause: In our case, the issue was the FND user account associated with the third level approver was inactive. In our non-prod environments, we normally end-date the user who don't use the system as part of the clone process. 

Solution : We enabled the FND user account associated with the third level approver and it started working. FND user accounts associated with the approvers need to be active for the approval hierarchy to be generated.

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps : Global Descriptive Flexfield not enabling for Malaysia on AR Transaction Form

For one of our customers, we enabled the Global Descriptive Flexfield (GDFF) for India and Malaysia.  The GDFF appeared successfully for India responsibility, but was not enabled for Malaysia responsibility. All the needed configurations were correctly done for both countries.

Configurations at a high level.

• Added 2 contexts (and the needed segments) for the DFF JG_RA_CUSTOMER_TRX as below 
• JA.IN.ARXTWMAI.RA_CUSTOMER_TRX
• JA.MY.ARXTWMAI.RA_CUSTOMER_TRX
• Set the following profiles under Profile Options:
• JG: Applications
• JG: Product 
• JG: Territory
• Added the function JG_ENABLE_GLOBAL_FLEX to the root menu of both country responsibilities

Upon conducting extensive investigation, we discovered that the 'JA.pll' was not getting called when the form was opened from the Malaysia responsibility.  This pll is supposed to be opened for the Asia Pacific countries, which includes both India and Malaysia.

Further investigation revealed that 'GLOBE.pll' was being called, but there was a code in that PLL which was missing Malaysia (MY) in the list within the procedure 'GET_PRODUCT_FROM_COUNTRY'.

So, the solution was to modify 'GLOBE.pll' and add the country code 'MY' to the list.

The modified code looks like below:

After compiling the modified PLL in the server, the GDFF started appearing in the AR Transaction Form.

Note: If your version of PLL is a higher version, then this may not be an issue for you.

Feel free to point out if anything is missing/wrong in this blog.

PL/SQL: Sample code for submitting a DBMS_SCHEDULER jobs

 In this post I will provide a sample simple code to submit a dbms_scheduler job.

• Create a procedure. You can create a procedure inside a package also if needed.

 --create table   
 CREATE TABLE xxaj_debug (  
              ,id         NUMBER  
              ,msg        VARCHAR2(4000)  
              ,call_stack VARCHAR2(4000)  
              ,date_stamp DATE  
             );  
 --create sequence   
 CREATE SEQUENCE xxaj_seq  
  START WITH 1  
  INCREMENT BY 1; 
  
 --create procedure   
 CREATE OR REPLACE PROCEDURE xxaj_proc (msg VARCHAR2)  
 IS  
  PRAGMA autonomous_transaction;  
 BEGIN  
   INSERT INTO xxaj_debug  
   VALUES ( xxaj_seq.NEXTVAL  
           ,msg  
           ,dbms_utility.format_call_stack  
           ,SYSDATE);  
   COMMIT;  
 END xxaj_proc;   

You can execute the below code to submit the above procedure as a dbms job.

            Parameters

                job_name      : you can give any name. Give something meaningful so that you can filter with 

                                        the  name when trouble shooting

                job_type        : in this case it is a stored procedure

                job_action     : in this case we are executing a stored procedure, so specify the procedure                                                  name

                enabled         : we want the job to run straight away, so set to true  

 BEGIN  
   dbms_scheduler.create_job (job_name   => 'XXAJ_JOB_'||TO_CHAR(SYSDATE, 'DDMMHH24MI')  
                             ,job_type   => 'STORED_PROCEDURE'  
                             ,job_action => 'xxaj_proc'  
                             ,enabled    => TRUE);  
 END;   

There are lot more options on the job submission. I will create a separate blog with different variances. 

You can also refer the below oracle site for the details.

Refer:Scheduler (DBMS_SCHEDULER) in Oracle Database 10g Onward

Feel free to point out if anything is missing/wrong in this blog.