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.