Oracle EBS結帳區間判斷SQL

 From: http://arthur-work.blogspot.com/2020/02/oracle-ebs-invsql.html

Oracle EBS: 用於查詢INV期間是否關閉之SQL

SELECT * FROM all_objects WHERE object_name='ORG_ACCT_PERIODS'

SELECT ood.organization_id "Organization ID" ,

ood.organization_code "Organization Code" ,

ood.organization_name "Organization Name" ,

oap.period_name "Period Name" ,

oap.period_start_date "Start Date" ,

oap.period_close_date "Closed Date" ,

oap.schedule_close_date "Scheduled Close" ,

DECODE(oap.open_flag, 'P','P - Period Close is processing' ,

'N','N - Period Close process is completed' ,

'Y','Y - Period is open if Closed Date is NULL' ,'Unknown') "Period Status"

FROM org_acct_periods oap ,

org_organization_definitions ood

WHERE oap.organization_id = ood.organization_id

AND (TRUNC(SYSDATE) -- Comment line if a a date other than SYSDATE is being tested.

--AND ('01-DEC-2014' -- Uncomment line if a date other than SYSDATE is being tested.

BETWEEN TRUNC(oap.period_start_date) AND TRUNC (oap.schedule_close_date))

ORDER BY ood.organization_id,

oap.period_start_date;

-- If Period Status is 'Y' and Closed Date is not NULL then the closing of the INV period failed.

[EBS]Sales Order Join Delivery

 

SELECT

OOH.ORG_ID,

OOH.ORDER_NUMBER,

OOH.ORDERED_DATE,

OOH.ORDER_TYPE_ID,

OOL.LINE_ID "ORDER_LINE_ID",

OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER "ORDER_LINE",

OOL.INVENTORY_ITEM_ID "ITEM_ID",

OOL.ORDERED_ITEM "ITEM_NUMBER",

OOL.ORDERED_QUANTITY "QUANTITY",

OOL.ORDER_QUANTITY_UOM "UOM",

OOL.UNIT_SELLING_PRICE "UNIT_PRICE",

OOH.TRANSACTIONAL_CURR_CODE "CURRENCY",

trunc(OOL.SCHEDULE_SHIP_DATE) "DEMAND_DATE",

OOH.SOLD_TO_ORG_ID "CUSTOMER_ID",

OOH.SHIP_TO_ORG_ID,

OOH.INVOICE_TO_ORG_ID,

WND.name

FROM

APPS.OE_ORDER_HEADERS_ALL OOH

INNER JOIN APPS.OE_ORDER_LINES_ALL OOL ON

OOL.HEADER_ID = OOH.HEADER_ID

INNER JOIN wsh_delivery_details WDD ON

OOL.header_id=WDD.source_header_id

AND OOL.line_id=WDD.source_line_id

INNER JOIN wsh_delivery_assignments WDA ON

WDD.delivery_detail_id=WDA.delivery_detail_id

INNER JOIN wsh_new_deliveries WND ON

WND.delivery_id = WDA.delivery_id

WHERE

1 = 1

AND OOH.order_number = '12345'

AND WND.name='DD102201130'

;