Skip to main content

Uninvoiced Receipts Query Oracle r12


Uninvoiced Receipts:

SELECT   pha.segment1 po_number,
         TO_CHAR (pha.creation_date, 'DD-MON-RRRR') po_date,
         (SELECT   vendor_name
            FROM   ap_suppliers ap
           WHERE   ap.vendor_id = pha.vendor_id)
            supplier_name,
         pla.quantity,
         pla.unit_price,
         pla.quantity * pla.unit_price AS line_amount,
         (SELECT   concatenated_segments
            FROM   gl_code_combinations_kfv gcc
           WHERE   gcc.code_combination_id = pda.code_combination_id)
            charge_account,
         rsh.receipt_num grn_no,
         TO_CHAR (rsh.creation_date, 'DD-MON-RRRR') grn_date,
         rt.quantity grn_quantity,
         rt.quantity_billed
  FROM   po_distributions_all pda,
         po_lines_all pla,
         po_headers_all pha,
         rcv_transactions rt,
         rcv_shipment_headers rsh
 WHERE       pda.po_line_id = pla.po_line_id
         AND pla.po_header_id = pha.po_header_id
         AND pha.po_header_id = rt.po_header_id
         AND pla.po_line_id = rt.po_line_id
         AND rt.transaction_type = 'RECEIVE'
         AND rt.shipment_header_id = rsh.shipment_header_id
         AND pda.po_distribution_id NOT IN
                  (SELECT   po_distribution_id
                     FROM   po_distributions_all pda
                    WHERE   po_distribution_id IN
                                  (SELECT   DISTINCT po_distribution_id
                                     FROM   ap_invoice_distributions_all))

Comments

Popular posts from this blog

API to Create & Update Price Adjustment and Order Lines

Important Tables: select header_id from oe_order_headers_all; select line_id from oe_order_lines_all; select list_header_id from qp_list_headers_all; select list_line_id from qp_list_lines; CREATE OR REPLACE PROCEDURE apps.xxapply_discount (p_header_id number) IS    v_api_version_number           number := 1;    v_return_status                varchar2 (2000);    v_msg_count                    number;    v_msg_data                     varchar2 (2000);    -- in variables --    v_header_rec                   oe_order_pub.header_rec_type;    v_line_tbl                     oe_order_pub.line_tbl_type;    v_action_request_tbl   ...