Customer Name: OE_ORDER_LINES_ALL.SOLD_TO_ORG_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
Party Name: HZ_CUST_ACCOUNTS.PARTY_ID = HZ_PARTIES.PARTY_ID
Ship to address: OE_ORDER_LINES_ALL.SHIP_TO_ORG_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID
Bill to address: OE_ORDER_LINES_ALL.INVOICE_TO_ORG_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID
Location: HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID
HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID
HZ_PARTY_SITES.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID
Price List: OE_ORDER_HEADER_ALL.PRICE_LIST_ID = QP_LIST_HEADERS_TL.LIST_HEADER_ID
Order Type: OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID = OE_TRANSACTION_TYPES_TL.TRANSACTION_TYPE_ID
Payment Terms: OE_ORDER_HEADER_ALL.PAYMENT_TERM_ID = RA_TERMS.TERM_ID
Currency: OE_ORDER_HEADERS_ALL.TRANSACTIONAL_CURR_CODE = FND_CURRENCIES.CURRENCY_CODE
AR Transaction: OE_ORDER_HEADERS_ALL.ORDER_NUMBER = RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR(Order_number)
OE_ORDER_LINES_ALL.LINE_ID = RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(Line_id)
Shipping details:OE_ORDER_HEADERS_ALL.HEADER_ID = WSH_DELIVARY_DETAILS.SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL.LINE_ID = WSH_DELIVARY_DETAILS.SOURCE_LINE_ID
WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID
WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID = WSH_NEW_DELIVERIES.DELIVERY_ID
WSH_NEW_DELIVERIES.DELIVERY_ID = WSH_DELIVERY_LEGS.DELIVERY_ID
WSH_TRIP_STOPS.TRIP_ID = WSH_TRIPS.TRIP_ID
WSH_PICKING_BATCHES.TRIP_ID = WSH_TRIPS.TRIP_ID
Operating unit: OE_ORDER_HEADERS_ALL.ORG_ID = HR_OPERATING_UNITS.ORGANIZATION_ID
Salesrep Info: OE_ORDER_HEADERS_ALL.SALESREP_ID = RA_SALESREPS_ALL.SALESREP_ID
Order Source: OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID = OE_ORDER_SOURCES.ORDER_SOURCE_ID
Invnetory Item: OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID = MTL_SYSTEMS_ITEM_B.INVENTORY_ITEM_ID
OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID = MTL_SYSTEMS_ITEM_B.ORGANIZATION_ID
Usefull queries:
FOB Point Code
SELECT LOOKUP_CODE FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'FOB' AND ENABLED_FLAG = 'Y'
AND UPPER(MEANING) = UPPER(OE_ORDER_HEADERS_ALL.FOB_POINT_CODE)
Freight terms
SELECT LOOKUP_CODE FROM APPS.OE_LOOKUPS
WHERE UPPER (LOOKUP_TYPE) = 'FREIGHT_TERMS' AND ENABLED_FLAG = 'Y'
AND UPPER (LOOKUP_CODE) = UPPER (OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE)
For sales channel code validation
SELECT LOOKUP_CODE FROM APPS.OE_LOOKUPS
WHERE LOOKUP_TYPE = 'SALES_CHANNEL' AND ENABLED_FLAG = 'Y'
UPPER(LOOKUP_CODE) = UPPER(OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE)
Ship method:
SELECT SHIP_METHOD_CODE FROM WSH.WSH_CARRIER_SERVICES
WHERE SHIP_METHOD_CODE = OE_ORDER_HEADERS_ALL.SHIPPING_METHOD_CODE
UOM:
SELECT UOM_CODE FROM INV.MTL_UNITS_OF_MEASURE_TL
WHERE UPPER(UOM_CODE)= UPPER(OE_ORDER_LINES_ALL.ORDER_QUANTITY_UOM)
AND LANGUAGE= 'US' AND NVL(DISABLE_DATE, (SYSDATE + 1)) > SYSDATE
Item type code validation
SELECT LOOKUP_CODE FROM APPS.OE_LOOKUPS
WHERE UPPER(LOOKUP_TYPE) = 'ITEM_TYPE'
AND ENABLED_FLAG = 'Y'
AND UPPER(LOOKUP_CODE)= OE_ORDER_LINES_ALL.ITEM_TYPE_CODE
On hand quantities
SELECT * FROM APPS.MTL_ONHAND_QUANTITIES
WHERE INVENTORY_ITEM_ID=OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID
AND ORGANIZATION_ID=OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID
Shipping:
SELECT * FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID=OE_ORDER_HEADERS_ALL.HEADER_ID
SELECT * FROM WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID=WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID
SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID=WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID
SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID=WSH_NEW_DELIVERIES.DELIVERY_ID
SELECT * FROM WSH_TRIP_STOPS WTS
WHERE STOP_ID=WSH_DELIVERY_LEGS.PICK_UP_STOP_ID
SELECT * FROM WSH_TRIPS WT
WHERE TRIP_ID=WSH_TRIP_STOPS.TRIP_ID
SELECT * FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = WSH_NEW_DELIVERIES.ORGANIZATION_ID
Material transactions
SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE INVENTORY_ITEM_ID=OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID
AND ORGANIZATION_ID=OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID
SELECT * FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
SELECT * FROM APPS.MTL_TXN_SOURCE_TYPES
WHERE TRANSACTION_SOURCE_TYPE_ID= MMT.TRANSACTION_SOURCE_TYPE_ID
MMT = MTL_MATERIAL_TRANSACTIONS
OM, WSH and AR Joins:
SELECT OOH.ORDER_NUMBER
,OOL.LINE_ID
,OOL.ORDERED_QUANTITY
,OOL.SHIPPED_QUANTITY
,OOL.INVOICED_QUANTITY
,WDD.DELIVERY_DETAIL_ID
,WND.DELIVERY_ID
,RCTL.INTERFACE_LINE_ATTRIBUTE1
,RCTL.INTERFACE_LINE_ATTRIBUTE3
,RCTL.INTERFACE_LINE_ATTRIBUTE6
,RCT.ORG_ID
,RCT.CREATION_DATE
,TRX_NUMBER
,RCTL.QUANTITY_ORDERED
,RCT.INTERFACE_HEADER_CONTEXT
FROM OE_ORDER_HEADERS_ALL OOH
,OE_ORDER_LINES_ALL OOL
,WSH_DELIVERY_DETAILS WDD
,WSH_NEW_DELIVERIES WND
,WSH_DELIVERY_ASSIGNMENTS WDA
,RA_CUSTOMER_TRX_ALL RCT
,RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE OOH.HEADER_ID=OOL.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND RCTL.INTERFACE_LINE_ATTRIBUTE1=TO_CHAR(OOH.ORDER_NUMBER)
AND RCTL.INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(OOL.LINE_ID)
AND RCTL.INTERFACE_LINE_ATTRIBUTE3=TO_CHAR(WND.DELIVERY_ID)
AND RCTL.CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID
AND RCT.INTERFACE_HEADER_CONTEXT='ORDER ENTRY'
The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)
SELECT PORH.SEGMENT1,
PORL.LINE_NUM,
PORD.DISTRIBUTION_NUM,
OOH.ORDER_NUMBER
SALES_ORDER,
OOL.LINE_NUMBER SO_LINE_NUM,
RSH.RECEIPT_NUM,
RCV.TRANSACTION_TYPE
FROM OE_ORDER_HEADERS_ALL OOH,
PO_REQUISITION_HEADERS_ALL PORH,
PO_REQUISITION_LINES_ALL PORL,
PO_REQ_DISTRIBUTIONS_ALL PORD,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS_ALL POSP,
RCV_SHIPMENT_HEADERS RSH,
RCV_TRANSACTIONS RCV
WHERE OOH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID
AND PORH.ORG_ID = POSP.ORG_ID
AND PORH.REQUISITION_HEADER_ID = OOL.SOURCE_DOCUMENT_ID
AND PORL.REQUISITION_LINE_ID = OOL.SOURCE_DOCUMENT_LINE_ID
AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID
AND PORL.REQUISITION_LINE_ID = PORD.REQUISITION_LINE_ID
AND PORL.REQUISITION_LINE_ID = RCV.REQUISITION_LINE_ID
AND PORD.DISTRIBUTION_ID = RCV.REQ_DISTRIBUTION_ID
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:
SELECT INTERFACE_SOURCE_CODE,
INTERFACE_SOURCE_LINE_ID,
QUANTITY,
DESTINATION_TYPE_CODE,
TRANSACTION_ID,
PROCESS_FLAG,
REQUEST_ID,
TRUNC (CREATION_DATE)
FROM PO_REQUISITIONS_INTERFACE_ALL
WHERE INTERFACE_SOURCE_CODE = 'ORDER ENTRY'
AND INTERFACE_SOURCE_LINE_ID IN (SELECT DROP_SHIP_SOURCE_ID
FROM OE_DROP_SHIP_SOURCES
WHERE HEADER_ID = &ORDER_HDR_ID
AND LINE_ID = &ORDER_LINE_ID);
SELECT ORG_ID, ORDER_TYPE_ID, ORDER_NUMBER,ORDER_SOURCE_ID,ORDERED_DATE,PRICE_LIST_ID,CUST_PO_NUMBER,
PAYMENT_TERM_ID, SHIP_FROM_ORG_ID, SOLD_TO_ORG_ID,INVOICE_TO_ORG_ID, FLOW_STATUS_CODE, BOOKED_FLAG,
OPEN_FLAG, SALESREP_ID
FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = '1000018000000047';
Ship to:
OE_ORDER_LINES_ALL.ship_to_org_id =
(SELECT site_use_id
FROM hz_cust_site_uses_all hczu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
AND hczu.site_use_code = 'SHIP_TO');
Bill to:
OE_ORDER_LINES_ALL.invoice_to_org_id =
(SELECT site_use_id
FROM hz_cust_site_uses_all hczu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
AND hczu.site_use_code = 'BILL_TO');
Party Name: HZ_CUST_ACCOUNTS.PARTY_ID = HZ_PARTIES.PARTY_ID
Ship to address: OE_ORDER_LINES_ALL.SHIP_TO_ORG_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID
Bill to address: OE_ORDER_LINES_ALL.INVOICE_TO_ORG_ID = HZ_CUST_SITE_USES_ALL.SITE_USE_ID
Location: HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID
HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID
HZ_PARTY_SITES.LOCATION_ID = HZ_LOCATIONS.LOCATION_ID
Price List: OE_ORDER_HEADER_ALL.PRICE_LIST_ID = QP_LIST_HEADERS_TL.LIST_HEADER_ID
Order Type: OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID = OE_TRANSACTION_TYPES_TL.TRANSACTION_TYPE_ID
Payment Terms: OE_ORDER_HEADER_ALL.PAYMENT_TERM_ID = RA_TERMS.TERM_ID
Currency: OE_ORDER_HEADERS_ALL.TRANSACTIONAL_CURR_CODE = FND_CURRENCIES.CURRENCY_CODE
AR Transaction: OE_ORDER_HEADERS_ALL.ORDER_NUMBER = RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR(Order_number)
OE_ORDER_LINES_ALL.LINE_ID = RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(Line_id)
Shipping details:OE_ORDER_HEADERS_ALL.HEADER_ID = WSH_DELIVARY_DETAILS.SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL.LINE_ID = WSH_DELIVARY_DETAILS.SOURCE_LINE_ID
WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID
WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID = WSH_NEW_DELIVERIES.DELIVERY_ID
WSH_NEW_DELIVERIES.DELIVERY_ID = WSH_DELIVERY_LEGS.DELIVERY_ID
WSH_TRIP_STOPS.TRIP_ID = WSH_TRIPS.TRIP_ID
WSH_PICKING_BATCHES.TRIP_ID = WSH_TRIPS.TRIP_ID
Operating unit: OE_ORDER_HEADERS_ALL.ORG_ID = HR_OPERATING_UNITS.ORGANIZATION_ID
Salesrep Info: OE_ORDER_HEADERS_ALL.SALESREP_ID = RA_SALESREPS_ALL.SALESREP_ID
Order Source: OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID = OE_ORDER_SOURCES.ORDER_SOURCE_ID
Invnetory Item: OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID = MTL_SYSTEMS_ITEM_B.INVENTORY_ITEM_ID
OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID = MTL_SYSTEMS_ITEM_B.ORGANIZATION_ID
Usefull queries:
FOB Point Code
SELECT LOOKUP_CODE FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'FOB' AND ENABLED_FLAG = 'Y'
AND UPPER(MEANING) = UPPER(OE_ORDER_HEADERS_ALL.FOB_POINT_CODE)
Freight terms
SELECT LOOKUP_CODE FROM APPS.OE_LOOKUPS
WHERE UPPER (LOOKUP_TYPE) = 'FREIGHT_TERMS' AND ENABLED_FLAG = 'Y'
AND UPPER (LOOKUP_CODE) = UPPER (OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE)
For sales channel code validation
SELECT LOOKUP_CODE FROM APPS.OE_LOOKUPS
WHERE LOOKUP_TYPE = 'SALES_CHANNEL' AND ENABLED_FLAG = 'Y'
UPPER(LOOKUP_CODE) = UPPER(OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE)
Ship method:
SELECT SHIP_METHOD_CODE FROM WSH.WSH_CARRIER_SERVICES
WHERE SHIP_METHOD_CODE = OE_ORDER_HEADERS_ALL.SHIPPING_METHOD_CODE
UOM:
SELECT UOM_CODE FROM INV.MTL_UNITS_OF_MEASURE_TL
WHERE UPPER(UOM_CODE)= UPPER(OE_ORDER_LINES_ALL.ORDER_QUANTITY_UOM)
AND LANGUAGE= 'US' AND NVL(DISABLE_DATE, (SYSDATE + 1)) > SYSDATE
Item type code validation
SELECT LOOKUP_CODE FROM APPS.OE_LOOKUPS
WHERE UPPER(LOOKUP_TYPE) = 'ITEM_TYPE'
AND ENABLED_FLAG = 'Y'
AND UPPER(LOOKUP_CODE)= OE_ORDER_LINES_ALL.ITEM_TYPE_CODE
On hand quantities
SELECT * FROM APPS.MTL_ONHAND_QUANTITIES
WHERE INVENTORY_ITEM_ID=OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID
AND ORGANIZATION_ID=OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID
Shipping:
SELECT * FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID=OE_ORDER_HEADERS_ALL.HEADER_ID
SELECT * FROM WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID=WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID
SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID=WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID
SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID=WSH_NEW_DELIVERIES.DELIVERY_ID
SELECT * FROM WSH_TRIP_STOPS WTS
WHERE STOP_ID=WSH_DELIVERY_LEGS.PICK_UP_STOP_ID
SELECT * FROM WSH_TRIPS WT
WHERE TRIP_ID=WSH_TRIP_STOPS.TRIP_ID
SELECT * FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = WSH_NEW_DELIVERIES.ORGANIZATION_ID
Material transactions
SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE INVENTORY_ITEM_ID=OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID
AND ORGANIZATION_ID=OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID
SELECT * FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
SELECT * FROM APPS.MTL_TXN_SOURCE_TYPES
WHERE TRANSACTION_SOURCE_TYPE_ID= MMT.TRANSACTION_SOURCE_TYPE_ID
MMT = MTL_MATERIAL_TRANSACTIONS
OM, WSH and AR Joins:
SELECT OOH.ORDER_NUMBER
,OOL.LINE_ID
,OOL.ORDERED_QUANTITY
,OOL.SHIPPED_QUANTITY
,OOL.INVOICED_QUANTITY
,WDD.DELIVERY_DETAIL_ID
,WND.DELIVERY_ID
,RCTL.INTERFACE_LINE_ATTRIBUTE1
,RCTL.INTERFACE_LINE_ATTRIBUTE3
,RCTL.INTERFACE_LINE_ATTRIBUTE6
,RCT.ORG_ID
,RCT.CREATION_DATE
,TRX_NUMBER
,RCTL.QUANTITY_ORDERED
,RCT.INTERFACE_HEADER_CONTEXT
FROM OE_ORDER_HEADERS_ALL OOH
,OE_ORDER_LINES_ALL OOL
,WSH_DELIVERY_DETAILS WDD
,WSH_NEW_DELIVERIES WND
,WSH_DELIVERY_ASSIGNMENTS WDA
,RA_CUSTOMER_TRX_ALL RCT
,RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE OOH.HEADER_ID=OOL.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND RCTL.INTERFACE_LINE_ATTRIBUTE1=TO_CHAR(OOH.ORDER_NUMBER)
AND RCTL.INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(OOL.LINE_ID)
AND RCTL.INTERFACE_LINE_ATTRIBUTE3=TO_CHAR(WND.DELIVERY_ID)
AND RCTL.CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID
AND RCT.INTERFACE_HEADER_CONTEXT='ORDER ENTRY'
The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)
SELECT PORH.SEGMENT1,
PORL.LINE_NUM,
PORD.DISTRIBUTION_NUM,
OOH.ORDER_NUMBER
SALES_ORDER,
OOL.LINE_NUMBER SO_LINE_NUM,
RSH.RECEIPT_NUM,
RCV.TRANSACTION_TYPE
FROM OE_ORDER_HEADERS_ALL OOH,
PO_REQUISITION_HEADERS_ALL PORH,
PO_REQUISITION_LINES_ALL PORL,
PO_REQ_DISTRIBUTIONS_ALL PORD,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS_ALL POSP,
RCV_SHIPMENT_HEADERS RSH,
RCV_TRANSACTIONS RCV
WHERE OOH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID
AND PORH.ORG_ID = POSP.ORG_ID
AND PORH.REQUISITION_HEADER_ID = OOL.SOURCE_DOCUMENT_ID
AND PORL.REQUISITION_LINE_ID = OOL.SOURCE_DOCUMENT_LINE_ID
AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID
AND PORL.REQUISITION_LINE_ID = PORD.REQUISITION_LINE_ID
AND PORL.REQUISITION_LINE_ID = RCV.REQUISITION_LINE_ID
AND PORD.DISTRIBUTION_ID = RCV.REQ_DISTRIBUTION_ID
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:
SELECT INTERFACE_SOURCE_CODE,
INTERFACE_SOURCE_LINE_ID,
QUANTITY,
DESTINATION_TYPE_CODE,
TRANSACTION_ID,
PROCESS_FLAG,
REQUEST_ID,
TRUNC (CREATION_DATE)
FROM PO_REQUISITIONS_INTERFACE_ALL
WHERE INTERFACE_SOURCE_CODE = 'ORDER ENTRY'
AND INTERFACE_SOURCE_LINE_ID IN (SELECT DROP_SHIP_SOURCE_ID
FROM OE_DROP_SHIP_SOURCES
WHERE HEADER_ID = &ORDER_HDR_ID
AND LINE_ID = &ORDER_LINE_ID);
SELECT ORG_ID, ORDER_TYPE_ID, ORDER_NUMBER,ORDER_SOURCE_ID,ORDERED_DATE,PRICE_LIST_ID,CUST_PO_NUMBER,
PAYMENT_TERM_ID, SHIP_FROM_ORG_ID, SOLD_TO_ORG_ID,INVOICE_TO_ORG_ID, FLOW_STATUS_CODE, BOOKED_FLAG,
OPEN_FLAG, SALESREP_ID
FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = '1000018000000047';
Ship to:
OE_ORDER_LINES_ALL.ship_to_org_id =
(SELECT site_use_id
FROM hz_cust_site_uses_all hczu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
AND hczu.site_use_code = 'SHIP_TO');
Bill to:
OE_ORDER_LINES_ALL.invoice_to_org_id =
(SELECT site_use_id
FROM hz_cust_site_uses_all hczu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE hczu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = OE_ORDER_LINES_ALL.sold_to_org_id
AND hczu.site_use_code = 'BILL_TO');
No comments:
Post a Comment