Thursday, 16 July 2015

How to Find PO (Purchase Order) number with Receipt With PO TAX Query in Oracle apps R12

SELECT   pha.segment1 po_order_no, jpt.tax_rate tax_rate, jpt.tax_amount,
         RTRIM (NVL (jct.tax_descr, NULL)) tax_name, jpt.tax_line_no,
         pha.po_header_id, pha.segment1 po_num, jpt.line_location_id,
         (SELECT msib.segment1
            FROM mtl_system_items_b msib
           WHERE msib.inventory_item_id = pla.item_id
             AND msib.organization_id = plla.ship_to_organization_id)
                                                                    item_code,
         pla.po_line_id, pla.item_id, pla.item_description,
         pla.unit_meas_lookup_code, pla.unit_price, pla.quantity,
         pda.po_distribution_id, pda.quantity_ordered, plla.line_location_id,
         plla.ship_to_location_id, plla.ship_to_organization_id,
         rsh.vendor_id, rsh.receipt_num, rsl.shipment_line_id,
         rsl.shipment_header_id, rsl.quantity_shipped, rsl.quantity_received
    FROM po_headers_all pha,
         po_lines_all pla,
         po_distributions_all pda,
         po_line_locations_all plla,
         rcv_transactions rt,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         jai_po_taxes jpt,
         jai_cmn_taxes_all jct
   WHERE pha.po_header_id = pla.po_header_id
     AND pha.po_header_id = pda.po_header_id
     AND pla.po_line_id = pda.po_line_id
     AND plla.po_header_id = pla.po_header_id
     AND plla.po_line_id = pla.po_line_id
     AND rt.po_header_id = pha.po_header_id
     AND rt.po_line_id = pla.po_line_id
     AND rt.shipment_header_id = rsh.shipment_header_id
     AND rt.shipment_line_id = rsl.shipment_line_id
     AND rsh.shipment_header_id = rsl.shipment_header_id
     AND jpt.tax_id = jct.tax_id(+)
     AND jct.end_date IS NULL
     AND pla.po_line_id = jpt.po_line_id(+)
     AND pla.po_header_id = jpt.po_header_id(+)
     AND pha.po_header_id = pla.po_header_id(+)
     AND jpt.tax_amount < > 0
     AND pha.type_lookup_code = 'STANDARD'
     AND TRUNC (pha.creation_date) > '01-APR-2015'
     AND pha.segment1 = NVL (:po_no, pha.segment1)
     AND rt.organization_id = 87
GROUP BY pha.po_header_id,
         pha.segment1,
         pha.vendor_id,
         pla.po_line_id,
         pla.item_id,
         pla.item_description,
         pla.unit_meas_lookup_code,
         pla.unit_price,
         pla.quantity,
         pda.po_distribution_id,
         pda.quantity_ordered,
         plla.line_location_id,
         plla.ship_to_location_id,
         plla.ship_to_organization_id,
         rsh.vendor_id,
         rsh.receipt_num,
         rsl.shipment_line_id,
         rsl.shipment_header_id,
         rsl.quantity_shipped,
         rsl.quantity_received,
         jpt.line_location_id,
         jpt.tax_rate,
         jpt.tax_amount,
         jpt.tax_line_no,
         jct.tax_descr
ORDER BY jpt.tax_line_no,pha.segment1

2 comments:

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...