oracle-EBS-PL/sql 采购单检验匹配入库匹配明细
oracle-EBS-PL/sql 采购单检验匹配入库匹配明细,可以检查一个po单是否做了入库,财务是否做了匹配
SELECT hou.short_code,
TO_CHAR(ph.CREATION_DATE,'yyyy-mm-dd') CREATION_DATE,
TO_CHAR(rt.TRANSACTION_DATE,'yyyy-mm-dd') receive_date,
TO_CHAR(AIL.INVOICE_DATE,'yyyy-mm-dd') INVOICE_DATE,
TO_CHAR(AIL.GL_DATE,'yyyy-mm-dd') AP_GL_DATE,
as1.segment1 vendor_number,
as1.vendor_name,
NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBER,
SH.RECEIPT_NUM RECEIPT_NUMBER,
RT.LAST_UPDATE_DATE,
RT_DE_DATE,
NVL(RT.QUANTITY,0) - NVL(RT_RE_RT.RETURN_TO_VENDOR_QUANTITY,0) RCV_TRANSACTION_QUANTITY,
--sl.QUANTITY_RECEIVED,
NVL(RT_AC.ACCEPT_QUANTITY,0) ACCEPT_QUANTITY,
NVL(RT_de.DELIVER_QUANTITY,0) - NVL(RT_RE_DE.RETURN_DELIVER_QUANTITY,0) DELIVER_QUANTITY,
NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) PO_UNIT_PRICE,
nvl(PL.attribute1,PL.Unit_Price) PO_Unit_Price_TAX,
--RT.QUANTITY_BILLED QUANTITY_BILLED,
--RT.AMOUNT_BILLED AMOUNT_BILLED,
AIL.MATCH_STATUS_FLAG,
NVL(AIL.QUANTITY_INVOICED,0) AP_QUANTITY_INVOICED,
ROUND(NVL(AIL.UNIT_PRICE,0),4) AP_UNIT_PRICE,
NVL(round ((AIL.AMOUNT + AIL.AMT_TAX )/AIL.QUANTITY_INVOICED,4),0) AP_UNIT_PRICE_TAX,
pl.attribute3 PO_TAX_RATE,
NVL(round(AIL.AMOUNT + AIL.AMT_TAX,2),0) AP_TOTAL_AMOUNT_TAX ,
NVL(AIL.AMOUNT,0) AMOUNT,
--NVL(AIL.AMOUNT_TAX,0) AMOUNT_TAX,
NVL(round(AIL.AMT_TAX,2),0) AMT_TAX ,
AIL.TAX_RATE_CODE,
AIL.TAX_RATE,
AIL.BATCH_NAME,
AIL.INVOICE_NUM ,
PL.UNIT_MEAS_LOOKUP_CODE PO_UOM_LOOKUP_CODE,
(SELECT msi.SEGMENT1
FROM mtl_system_items msi
WHERE SL.item_id =msi.inventory_item_id
AND sl.TO_ORGANIZATION_ID=msi.organization_id
) 物料编码,
SL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
LO.LOCATION_CODE SHIP_TO_LOCATION,
PH.FREIGHT_TERMS_LOOKUP_CODE PO_FREIGHT_TERMS,
AT.NAME PO_PAYMENT_TERMS,
RT.CURRENCY_CODE CURRENCY_CODE,
OD.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE
FROM RCV_TRANSACTIONS RT,
(SELECT SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID,
SUM(QUANTITY) ACCEPT_QUANTITY
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'ACCEPT'
GROUP BY SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID
) RT_AC,
(SELECT SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID, TRANSACTION_DATE RT_DE_DATE,
SUM(QUANTITY) DELIVER_QUANTITY
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'DELIVER'
GROUP BY SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID,TRANSACTION_DATE
) RT_DE,
(SELECT SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID,
SUM(QUANTITY) RETURN_DELIVER_QUANTITY
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'RETURN TO RECEIVING'
GROUP BY SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID
) RT_RE_DE, --退回至接收,影响库存
(SELECT SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID ,
SUM(QUANTITY) RETURN_TO_VENDOR_QUANTITY
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'RETURN TO VENDOR'
GROUP BY SHIPMENT_HEADER_ID ,
SHIPMENT_LINE_ID
) RT_RE_RT, --退回至接收,影响接收
(
select AID.MATCH_STATUS_FLAG ,apba.BATCH_NAME,ai.INVOICE_NUM ,ail1.ORG_ID,ail1.INVOICE_ID,AIL1.LINE_NUMBER,AIL1.RCV_TRANSACTION_ID,ail1.PO_HEADER_ID,ail1.PO_LINE_ID,ail1.PO_LINE_LOCATION_ID
,ail1.ITEM_DESCRIPTION,ail1.AMOUNT,ail1.QUANTITY_INVOICED,ail1.UNIT_PRICE,
ail2.AMOUNT AMOUNT_TAX, round(ail1.AMOUNT/(ai.INVOICE_AMOUNT-ai.TOTAL_TAX_AMOUNT)*ai.TOTAL_TAX_AMOUNT,2) AMT_TAX ,ail2.TAX_RATE_CODE,ail2.TAX_RATE, ai.INVOICE_DATE,ai.GL_DATE
from AP_INVOICE_LINES_ALL ail1, AP_INVOICE_LINES_ALL ail2 ,AP_INVOICES_ALL ai,AP_BATCHES_ALL apba,
(select distinct MATCH_STATUS_FLAG, INVOICE_ID from AP_INVOICE_DISTRIBUTIONS_ALL AID where AID.RCV_TRANSACTION_ID IS NOT NULL
AND AID.PO_DISTRIBUTION_ID IS NOT NULL
AND AID.BASE_AMOUNT IS NOT NULL
AND NVL(REVERSAL_FLAG,'-') <>'Y' ) AID
where ail1.AMOUNT <> 0 and nvl(ail2.DISCARDED_FLAG,'N') <> 'Y' -- and ail2.AMOUNT <> 0
and ail1.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND ail2.LINE_TYPE_LOOKUP_CODE = 'TAX'
AND ail1.INVOICE_ID = ail2.INVOICE_ID
and ail1.INVOICE_ID = ai.INVOICE_ID
and ai.BATCH_ID =apba.BATCH_ID (+) and AID.INVOICE_ID(+) = ai.INVOICE_ID
) AIL,
PO_HEADERS_all PH,
PO_LINES_ALL PL,
PO_LINE_LOCATIONS_ALL PS,
PO_RELEASES_ALL PR,
RCV_SHIPMENT_HEADERS SH,
RCV_SHIPMENT_LINES SL,
ap_suppliers as1,
AP_TERMS AT,
/* PO_VENDORS VE,
PO_VENDOR_SITES_ALL VS, Commented for bug#10072369 */
ORG_ORGANIZATION_DEFINITIONS OD,
hr_operating_units hou,
HR_LOCATIONS_ALL_TL LO,
AP_TAX_CODES_ALL TX
/* Amount Based Matching */
/* Contract Payments: Progress Payments*/
WHERE rt.vendor_id = as1.vendor_id
AND RT.SHIPMENT_HEADER_ID = RT_ac.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID = RT_ac.SHIPMENT_LINE_ID(+)
AND RT.SHIPMENT_HEADER_ID = RT_de.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID = RT_de.SHIPMENT_LINE_ID(+)
AND RT.SHIPMENT_HEADER_ID = RT_RE_DE.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID = RT_RE_DE.SHIPMENT_LINE_ID(+)
AND RT.SHIPMENT_HEADER_ID = RT_RE_RT.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID = RT_RE_RT.SHIPMENT_LINE_ID(+)
AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
AND RT.PO_LINE_ID = PL.PO_LINE_ID
AND RT.PO_LINE_LOCATION_ID = PS.LINE_LOCATION_ID
AND RT.PO_RELEASE_ID = PR.PO_RELEASE_ID(+)
AND RT.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID
AND PH.TERMS_ID = AT.TERM_ID(+)
AND
/* RT.VENDOR_ID = VE.VENDOR_ID(+) AND
RT.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+) AND Commented for bug#10072369 */
SL.TO_ORGANIZATION_ID = OD.ORGANIZATION_ID(+)
AND od.OPERATING_UNIT =hou.ORGANIZATION_ID
AND SL.SHIP_TO_LOCATION_ID = LO.LOCATION_ID(+)
AND SH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND LO.LANGUAGE(+) = USERENV('LANG')
AND PS.TAX_CODE_ID = TX.TAX_ID(+)
AND RT.TRANSACTION_TYPE IN ('RECEIVE', 'MATCH')
AND ((PS.PO_RELEASE_ID IS NOT NULL
AND PR.PCARD_ID IS NULL)
OR (PS.PO_RELEASE_ID IS NULL
AND PH.PCARD_ID IS NULL ))
AND RT.transaction_id = AIL.rcv_transaction_id(+)
and hou.short_code =3455
--and trunc(rt.TRANSACTION_DATE,'dd') between to_date('20160601', 'RRRRMMDD') and to_date('20160630', 'RRRRMMDD')
and ph.segment1 = '20000262' ---- 20000169
order by vendor_number,PO_NUMBER,RECEIPT_NUMBER
作者: 轻烟随风
当前文章地址: https://www.zyxpp.com/oracle-ebs-pl-sql-poreceiptmatch/
来源: 轻烟随风的博客
文章版权归作者所有,欢迎转载
当前文章地址: https://www.zyxpp.com/oracle-ebs-pl-sql-poreceiptmatch/
来源: 轻烟随风的博客
文章版权归作者所有,欢迎转载
THE END
二维码
文章目录
关闭
共有 0 条评论