4 Replies Latest reply on Dec 29, 2014 9:47 PM by Srini Chavali-Oracle

    PO query not giving correct values

    edy12

      Hi

       

      I am running the following query in PL?SQL Developer

       

      SELECT hou.name Operating_unit,

             pha.segment1 po_number,

             pha.revision_num,

             ap.vendor_name,

             hla1.location_code,

             papf.full_name Buyer,

             to_char(pha.creation_date, 'DD-MON-RRRR HH24:MI:SS') creation_date,

             pdta.type_name,

             assa.vendor_site_code,

             hla2.location_code,

             initcap(pha.authorization_status) po_status,

             pha.currency_code,

             pla.line_num,

             pltt.line_type,

             msib.segment1 item,

             (mcb.segment1 || '.' || mcb.segment1) category,

             msib.description,

             msib.primary_unit_of_measure UOM,

             pla.quantity,

             pla.unit_price,

             (pla.quantity * pla.unit_price) amount,

             (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||

             gcc.segment4 || '-' || gcc.segment5) charge_account,

             pla.list_price_per_unit,

             at.name,

             pha.freight_terms_lookup_code,

             pha.fob_lookup_code,

             initcap(pha.pay_on_code) pay_on_code,

             pha.acceptance_required_flag,

             ood.organization_code,

             ood.organization_name,

             msib.primary_unit_of_measure UOM,

             pla.quantity,

             plla.country_of_origin_code,

             (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||

             gcc.segment4 || '-' || gcc.segment5) charge_account,

             (pla.quantity * pla.unit_price) amount,

             plla.receive_close_tolerance,

             plla.invoice_close_tolerance,

             plla.quantity,

             plla.quantity_received,

             plla.quantity_cancelled,

             plla.quantity_billed,

             plla.match_option,

             plla.accrue_on_receipt_flag,

             plla.enforce_ship_to_location_code,

             plla.days_early_receipt_allowed,

             plla.days_late_receipt_allowed,

             plla.receipt_days_exception_code,

             plla.qty_rcv_tolerance,

             initcap(plla.qty_rcv_exception_code),

             plla.allow_substitute_receipts_flag,

             plla.receiving_routing_id,

             plla.enforce_ship_to_location_code,

             initcap(pda.destination_type_code),

             papf.full_name requestor,

             hla3.location_code,

             --pda.quantity_ordered,  -- commented because giving wrong value

             (select quantity_ordered

                from po_distributions_all

               where po_header_id IN (select po_header_id

                                        from po_headers_all

                                       where segment1 = pha.segment1)) quantity_ordered, -- Added

             (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||

             gcc.segment4 || '-' || gcc.segment5) po_charge_account,

             haou.name,

             prha.segment1,

             prla.line_num,

             to_char(prla.rate_date, 'DD-MON-RRRR') rate_date,

             (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||

             gcc.segment4 || '-' || gcc.segment5) po_charge_account,

             (SELECT acc.segment1 || '-' || acc.segment2 || '-' || acc.segment3 || '-' ||

                     acc.segment4 || '-' || acc.segment5

                FROM gl_code_combinations acc

               WHERE acc.code_combination_id = pda.accrual_account_id) accrual_account,

             (SELECT acc.segment1 || '-' || acc.segment2 || '-' || acc.segment3 || '-' ||

                     acc.segment4 || '-' || acc.segment5

                FROM gl_code_combinations acc

               WHERE acc.code_combination_id = pda.variance_account_id) variance_account,

             pla.line_num,

             plla.shipment_num,

             ood.organization_code,

             ood.organization_name,

             msib.segment1,

             msib.description,

             pla.quantity

       

        FROM hr_operating_units           hou,

             po_headers_all               pha,

             ap_suppliers                 ap,

             hr_locations_all             hla1,

             per_all_people_f             papf,

             po_document_types_all        pdta,

             po_document_types_all_b      pdtab,

             ap_supplier_sites_all        assa,

             hr_locations_all             hla2,

             po_lines_all                 pla,

             po_line_types_b              pltb,

             po_line_types_tl             pltt,

             mtl_system_items_b           msib,

             po_line_locations_all        plla,

             mtl_categories_b             mcb,

             po_distributions_all         pda,

             gl_code_combinations         gcc,

             ap_terms                     at,

             org_organization_definitions ood,

             hr_locations_all             hla3,

             po_requisition_headers_all   prha,

             po_req_distributions_all     prda,

             po_requisition_lines_all     prla,

             hr_all_organization_units_tl haou

      WHERE 1 = 1

        

         AND pha.segment1 = '6395'        --&po_number

         AND hou.organization_id = 204    --&operating_unit

         AND hou.organization_id = pha.org_id

         AND pha.vendor_id = ap.vendor_id

         AND hla1.location_id = pha.ship_to_location_id

         AND papf.person_id = pha.agent_id

         AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

         AND ((pdtab.document_type_code IN ('PO', 'PA') AND

             pdtab.document_subtype = pha.type_lookup_code))

         AND pdta.document_subtype = pdtab.document_subtype

         AND pdta.document_type_code = pdtab.document_type_code

         AND pdta.org_id = Pdtab.org_id

         AND pha.org_id = pdta.org_id

         AND ap.vendor_id = assa.vendor_id

         AND pha.org_id = assa.org_id

         AND pha.vendor_id = assa.vendor_id

         AND pha.vendor_site_id = assa.vendor_site_id

         AND pha.bill_to_location_id = hla2.ship_to_location_id

         AND pla.po_header_id = pha.po_header_id

         AND pltb.line_type_id = pla.line_type_id

         AND pla.line_type_id = pltt.line_type_id

         AND pltt.language = USERENV('LANG')

         AND msib.inventory_item_id = pla.item_id

         AND plla.po_line_id = pla.po_line_id

         AND msib.organization_id = plla.ship_to_organization_id

         AND mcb.category_id = pla.category_id

         AND pda.po_line_id = pla.po_line_id

         AND pda.po_header_id = pha.po_header_id

         AND pda.code_combination_id = gcc.code_combination_id

         AND at.term_id = pha.terms_id

         AND ood.organization_id = plla.ship_to_organization_id

         AND pda.deliver_to_person_id = papf.person_id

         AND hla3.location_id = pda.deliver_to_location_id

         AND haou.organization_id = prha.org_id

         AND haou.language = USERENV('LANG')

         AND prha.org_id = pha.org_id

         AND prha.requisition_header_id = prla.requisition_header_id

         AND pda.req_distribution_id = prda.distribution_id

         AND prda.requisition_line_id = prla.requisition_line_id

         AND haou.organization_id = hou.organization_id

         AND prla.org_id = pha.org_id

         AND prda.code_combination_id = gcc.code_combination_id    --added

         AND hla3.inventory_organization_id = msib.organization_id   --added

         AND pda.line_location_id = prla.line_location_id  --added

         AND plla.po_header_id = pha.po_header_id  --added

        

      I tested it for 10 POs. for 2 the query is giving right value. but for others it is giving wrong value.

      Mainly the PO Charge Account, accrual and variance account values are not matching. What changes I can make to get the correct data?

      I am using r12 version

       

      Thanks