Report:一个时间段内有做Receipt动作的PO,以及具体数据

Doc ID: AG0991

Subject: Request from KS FIN to customize a report listing PO and relevant receipt information

Type: Request

Creation Date: 17-Sep-08

Last Revision Date: 22-Sep-08

Status: Open

Owner: Kevin

 

Request

KS FIN needs to customize a report listing PO and relevant receipt information. (Refer to AG0991_01)

Purpose

Purpose is help for budgeting accurately.

Current Practice

 

Solutions

1. [17-Sep-08] Kevin developed the report and named it ‘XX_PO_RECEIPT_REPORT’. (Refer to XX_PO_RECEIPT_REPORT.sql)(Need end of Thomas’s approval)

2. [17-Sep-08] Kevin applied the report on TEST Server (ERPDB) and named it ‘AG PO Receipt Report’. (Refer to AG0991_02) (Need end of Thomas’s approval)

3. [22-Sep-08] KS FIN Zhangling asked us to add a column ‘PO Total Amount’ in the report.

 

 

 

---------------------------------------------------------
-- DEVELOPED by   : Kevin
-- Updated Date : 2008-09-18
---------------------------------------------------------

set feedback off;
set heading on;
set pagesize 30000;
set lines 750;
set verify off;
set echo off;
set feedb off;

col PO_NUM format a20 head 'PO_NUM';
col SUPPLIER format a35 head 'SUPPLIER';
col TERMS format a20 head 'TERMS';
col CURRENCY format a20 head 'CURRENCY';
col ITEM_CODE format a25 head 'ITEM_CODE';
col DESCRIPTION format a80 head 'DESCRIPTION';
col QUANTITY format 999999999999.999 head 'QUANTITY';
col UNIT_PRICE format 999999999999.999 head 'UNIT_PRICE';
col AMOUNT format 999999999999.999 head 'AMOUNT';
col RECEIPT_NUM format a20 head 'RECEIPT_NUM';
col QUANTITY_RECEIVED format 999999999999.999 head 'QUANTITY_RECEIVED';
col AMOUNT_RECEIVED format 999999999999.99999999 head 'AMOUNT_RECEIVED';
col RECEIVED_DATE format a50 head 'RECEIVED_DATE';
select pha.segment1 po_num,
PV.VENDOR_NAME supplier,
at.NAME terms,
pha.currency_code currency,
msb.segment1 item_code,
msb.description description,
pla.quantity quantity,
pla.unit_price unit_price,
pla.quantity*pla.unit_price amount,
rsh.receipt_num receipt_num,
plla.quantity_received quantity_received,
plla.quantity_received*unit_price amount_received,
plla.closed_for_receiving_date received_date
from po_headers_all pha,
     PO_VENDORS PV,
     AP_TERMS AT,
     po_lines_all pla,
     mtl_system_items_b msb,
     po_line_locations_all plla,
     RCV_SHIPMENT_HEADERS rsh,
     RCV_SHIPMENT_LINES rsl
where pha.vendor_id=pv.vendor_id

and pha.org_id=102
and pha.terms_id=at.TERM_ID
and pha.po_header_id=pla.po_header_id
and pla.item_id=msb.inventory_item_id
and msb.organization_id=111
and plla.po_header_id=pha.po_header_id
and plla.po_line_id=pla.po_line_id
and plla.closed_for_receiving_date is not null
and rsh.shipment_header_id=rsl.shipment_header_id
and pla.po_line_id=rsl.po_line_id
and pla.po_header_id=rsl.po_header_id
and pla.item_id=rsl.item_id
and plla.line_location_id=rsl.po_line_location_id
and plla.closed_for_receiving_date >= to_date('&1','dd/mm/yyyy')
and plla.closed_for_receiving_date <= to_date('&2','dd/mm/yyyy')
order by plla.closed_for_receiving_date;

NOTE:Ctrl+A 光标在最后一行的下面,最后一行的最后面以分号结束.