SELECT OOH.ORDER_NUMBER ORDER_NUMBER,
OTT.NAME ORDER_TYPE,
OOH.ORDERED_DATE DATE_ORDERED,
QLH.NAME PRICE_LIST,
OOH.CUST_PO_NUMBER CUSTOMER_PO,
OOH.FLOW_STATUS_CODE STATUS,
OOH.TRANSACTIONAL_CURR_CODE CURRENCY,
OOL.LINE_NUMBER LINE,
OOL.ORDERED_ITEM ORDERED_ITEM,
MSIB.DESCRIPTION ITEM_DESC,
OOL.ORDERED_QUANTITY QTY,
OOL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE,
(OOL.ORDERED_QUANTITY*OOL.UNIT_SELLING_PRICE) SUBTOTAL,
OOL.TAX_VALUE TAX,
OOL.ORDER_QUANTITY_UOM UOM,
OOL.REQUEST_DATE REQUEST_DATE,
OOL.SCHEDULE_SHIP_DATE SCHEDULE_SHIP_DATE,
OOL.SCHEDULE_ARRIVAL_DATE SCHEDULE_ARRIVAL_DATE,
OOL.PROMISE_DATE PROMISE_DATE,
OOL.FLOW_STATUS_CODE LINE_STATUS,
OTT1.NAME LINE_TYPE,
OOL.CANCELLED_QUANTITY QUANTITY_CANCELLED,
OOL.SHIPPED_QUANTITY QUANTITY_SHIPPED,
OOL.RETURN_REASON_CODE REASON,
OOL.SERVICE_TXN_COMMENTS COMMENTS,
OOL.SERVICE_REFERENCE_TYPE_CODE SERVICE_REFERENCE_TYPE,
NULL PROJECT_NUMBER,
NULL TASK_NUMBER,
OOL.END_ITEM_UNIT_NUMBER UNIT_NUMBER,
OOL.CUSTOMER_JOB CUSTOMER_JOB,
OOL.CUSTOMER_PRODUCTION_LINE PRODUCTION_LINE,
OOL.CUST_MODEL_SERIAL_NUMBER MODEL_SERIAL_NUMBER,
OOL.CUSTOMER_DOCK_CODE CUSTOMER_DOCK,
OOL.CUST_PRODUCTION_SEQ_NUM CUST_PRODUCTION_SEQUENCE,
OOL.RLA_SCHEDULE_TYPE_CODE RLM_SCHEDULE_TYPE,
OOS.NAME ORDER_SOURCE,
OOH.ORIG_SYS_DOCUMENT_REF ORDER_SOURCE_REFERENCE,
OOL.ORIG_SYS_LINE_REF ORDER_SOURCE_LINE_REFERENCE,
OOL.TAX_CODE TAX_CLASSIFICATION_CODE,
OOL.PRICING_QUANTITY_UOM PRICING_UOM,
OOL.PRICING_DATE PRICING_DATE,
MP.ORGANIZATION_CODE WAREHOUSE,
OOL.SOURCE_TYPE_CODE SOURCE_TYPE,
OOL.SHIPPING_METHOD_CODE SHIPPING_METHOD,
RCT.TRX_NUMBER INVOICE_NUMBER,
ACR.RECEIPT_NUMBER RECEIPT_NUMBER,
OOD.ORGANIZATION_NAME LEGAL_ENTITY,
HL.ADDRESS1||HL.ADDRESS2||HL.ADDRESS3||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.COUNTY SHIP_TO_LOC,
HL1.ADDRESS1||HL1.ADDRESS2||HL1.ADDRESS3||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.COUNTY BILL_TO_LOC,
JRS.NAME SALES_PERSON
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
OE_TRANSACTION_TYPES_TL OTT,
OE_TRANSACTION_TYPES_TL OTT1,
QP_LIST_HEADERS_TL QLH,
OE_ORDER_SOURCES OOS,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_PARAMETERS MP,
ORG_ORGANIZATION_DEFINITIONS OOD,
AR_RECEIVABLE_APPLICATIONS_ALL ARA,
AR_CASH_RECEIPTS_ALL ACR,
RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCTL,
HZ_CUST_ACCOUNTS HCA,
JTF_RS_SALESREPS JRS,
-- JTF_RS_DEFRESOURCES_V JRD,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS_ALL HCAA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_LOCATIONS HL,
HZ_PARTY_SITES HPS1,
HZ_CUST_ACCOUNTS_ALL HCAA1,
HZ_CUST_SITE_USES_ALL HCSUA1,
HZ_CUST_ACCT_SITES_ALL HCASA1,
HZ_LOCATIONS HL1
WHERE OOH.HEADER_ID = OOL.HEADER_ID
AND OOH.ORG_ID = OOL.ORG_ID
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OOL.LINE_TYPE_ID = OTT1.TRANSACTION_TYPE_ID
AND QLH.LIST_HEADER_ID = OOH.PRICE_LIST_ID
AND OOH.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID
AND OOL.ORG_ID = OOD.OPERATING_UNIT
AND OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND OOL.ORDERED_ITEM = MSIB.SEGMENT1
AND OOL.ORG_ID = MP.ORGANIZATION_ID
AND OOD.ORGANIZATION_ID = RCT.LEGAL_ENTITY_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND OOL.LINE_ID = RCTL.INTERFACE_LINE_ATTRIBUTE6
AND ARA.STATUS='APP'
AND ARA.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID
AND ARA.APPLIED_CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID
AND OOH.ORDER_NUMBER = RCT.INTERFACE_HEADER_ATTRIBUTE1
AND OOH.SOLD_TO_ORG_ID = HCAA.CUST_ACCOUNT_ID
AND JRS.SALESREP_ID = RCT.PRIMARY_SALESREP_ID
AND HPS.PARTY_ID = HP.PARTY_ID
AND HCASA.CUST_ACCOUNT_ID = HCAA.CUST_ACCOUNT_ID
AND RCT.SHIP_TO_SITE_USE_ID = HCSUA.SITE_USE_ID
AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID
AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND RCT.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID
AND HCASA1.CUST_ACCOUNT_ID = HCAA1.CUST_ACCOUNT_ID
AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID
AND HCASA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID
AND HPS1.LOCATION_ID = HL1.LOCATION_ID
AND OOH.ORDER_NUMBER = '90291' --<Your_order_number>
AND ACR.RECEIPT_NUMBER = 'RECEIPT_90291'--<Your_receipt_ number>
GROUP BY OOH.ORDER_NUMBER,
OTT.NAME,
OOH.ORDERED_DATE,
QLH.NAME,
OOH.CUST_PO_NUMBER,
OOH.FLOW_STATUS_CODE,
OOH.TRANSACTIONAL_CURR_CODE,
OOL.LINE_NUMBER,
OOL.ORDERED_ITEM,
MSIB.DESCRIPTION,
OOL.ORDERED_QUANTITY,
OOL.UNIT_SELLING_PRICE,
OOL.TAX_VALUE,
OOL.ORDER_QUANTITY_UOM,
OOL.REQUEST_DATE,
OOL.SCHEDULE_SHIP_DATE,
OOL.SCHEDULE_ARRIVAL_DATE,
OOL.PROMISE_DATE,
OOL.FLOW_STATUS_CODE,
OTT1.NAME,
OOL.CANCELLED_QUANTITY,
OOL.SHIPPED_QUANTITY,
OOL.RETURN_REASON_CODE,
OOL.SERVICE_TXN_COMMENTS,
OOL.SERVICE_REFERENCE_TYPE_CODE,
OOL.END_ITEM_UNIT_NUMBER,
OOL.CUSTOMER_JOB,
OOL.CUSTOMER_PRODUCTION_LINE,
OOL.CUST_MODEL_SERIAL_NUMBER,
OOL.CUSTOMER_DOCK_CODE,
CUST_PRODUCTION_SEQ_NUM,
OOL.RLA_SCHEDULE_TYPE_CODE,
OOS.NAME,
OOH.ORIG_SYS_DOCUMENT_REF,
OOL.ORIG_SYS_LINE_REF,
OOL.TAX_CODE,
OOL.PRICING_QUANTITY_UOM,
OOL.PRICING_DATE,
MP.ORGANIZATION_CODE,
OOL.SOURCE_TYPE_CODE,
OOL.SHIPPING_METHOD_CODE,
RCT.TRX_NUMBER,
ACR.RECEIPT_NUMBER,
OOD.ORGANIZATION_NAME, HL.ADDRESS1||HL.ADDRESS2||HL.ADDRESS3||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.COUNTY,
HL1.ADDRESS1||HL1.ADDRESS2||HL1.ADDRESS3||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.COUNTY,
JRS.NAME;
Happy Learning!!
No comments:
Post a Comment