訂單銷貨收款之報表sql

說明:

透過ar invoice的沖銷關系抓到收款receipt number與receipt date.

主sql:

SELECT ORG_ID,SHIP_HEADER_ID,SHIP_NO,ATTRIBUTE9 SHIP_DATE,aTTRIBUTE12 Cargo_receipt_date,
to_date(gobo_cdate_to_yyyymmdd(ATTRIBUTE9),'yyyymmdd')+61 payment_due_date,
header_id,order_number,to_char(order_number) order_no,
cust_po_number,TRANSACTIONAL_CURR_CODE,
SUM(NVL(SHIPPED_QUANTITY,0)) SHIPPED_QTY,
SUM(NVL(SHIPPED_QUANTITY,0)*NVL(UNIT_SELLING_PRICE,0)
*usd_rate
) SHIPPED_AMOUNT
from (
SELECT distinct GH.ORG_ID,GH.SHIP_HEADER_ID,GH.SHIP_NO,GP.ATTRIBUTE9,GP.ATTRIBUTE12 ,
--GP.ATTRIBUTE9,
oh.header_id,oh.order_number,--oh.order_number,
oh.cust_po_number,oh.TRANSACTIONAL_CURR_CODE,ol.line_id,
OL.SHIPPED_QUANTITY,
OL.UNIT_SELLING_PRICE,
currency_exchange(oh.TRANSACTIONAL_CURR_CODE,'USD',ood.SET_OF_BOOKS_ID,oh.ORDERED_DATE) usd_rate
FROM
ORG_ORGANIZATION_DEFINITIONS ood,
OE_ORDER_HEADERS_ALL OH,
OE_ORDER_LINES_ALL OL,
GOBO_OM_PACKING_ALL GP,
GOBO_OM_SHIP_LINE_ALL GL,
GOBO_OM_SHIP_HEADER_ALL GH,
AR_CUSTOMERS AC
WHERE 1=1
AND OOD.ORGANIZATION_ID=OL.ORG_ID
AND OH.SOLD_TO_ORG_ID=AC.CUSTOMER_ID
AND OH.HEADER_ID=OL.HEADER_ID
AND OH.ORG_ID=OL.ORG_ID
AND OL.LINE_ID=GL.ORDER_LINED_ID
AND OL.ORG_ID=GL.ORG_ID
AND GL.SHIP_HEADER_ID=GH.SHIP_HEADER_ID
AND GL.ORG_ID=GH.ORG_ID
AND gobo_cdate_to_YYYYMM(GP.ATTRIBUTE9(+))>=nvl(:p_yyyymm_FROM,gobo_cdate_to_YYYYMM(GP.ATTRIBUTE9(+)))
AND gobo_cdate_to_YYYYMM(GP.ATTRIBUTE9(+))<=nvl(:p_yyyymm_to,gobo_cdate_to_YYYYMM(GP.ATTRIBUTE9(+)))
and GP.ATTRIBUTE9 is not null
AND GP.SHIP_HEADER_ID=GH.SHIP_HEADER_ID
AND GP.ORG_ID=GH.ORG_ID
AND GH.SHIP_NO >=NVL(:P_SHIPNO_FROM,GH.SHIP_NO)
AND GH.SHIP_NO <=NVL(:P_SHIPNO_TO,GH.SHIP_NO)
AND GH.ORG_ID=:P_ORG_ID
AND AC.CUSTOMER_NUMBER=nvl(:P_CUSTOMER_NUMBER ,AC.CUSTOMER_NUMBER)
)
group by ORG_ID,ATTRIBUTE9 ,SHIP_HEADER_ID,SHIP_NO,ATTRIBUTE12,
header_id,order_number,cust_po_number,TRANSACTIONAL_CURR_CODE

子sql: 用order_no(字元) & org_id 跟主sql關聯

select rac.ORG_ID,rac.interface_header_attribute1 order_no,arc.RECEIPT_NUMBER,RECEIPT_DATE
from ar_receivable_applications_all arr, ar_cash_receipts_all arc,ra_customer_trx_all rac
where arr.CASH_RECEIPT_ID=arc.CASH_RECEIPT_ID
and arr.APPLIED_CUSTOMER_TRX_ID=rac.CUSTOMER_TRX_ID
and rac.interface_header_attribute1=:ORDER_NO and rac.org_id=:org_id