DOO_FULFILL_LINES_RM_V

Details

• Schema: FUSION

• Object owner: DOO

• Object type: VIEW

Columns

Name

ACTUAL_SHIPMENT_DATE BILL_TO_CUSTOMER_SITE_ID BILL_TO_CUSTOMER_ID UOM_CODE QUANTITY ITEM_ID DOC_ID_INT_1 LINE_NUM DOC_LINE_ID_INT_1 INVENTORY_ORG_ID REFERENCE_DOC_LINE_ID_INT_1 UNIT_SELLING_PRICE UNIT_LIST_PRICE DISCOUNT_PERCENTAGE SHIP_TO_CUSTOMER_SITE_ID SHIP_TO_CUSTOMER_ID PAYMENT_TERM_ID ACCOUNTING_RULE_ID LINE_AMOUNT CUST_PO_NUMBER DELIVERY_DATE DELIVERY_STATUS RULE_START_DATE RULE_END_DATE ACCOUNTING_RULE_DURATION REVENUE_REVERSAL_METHOD DOCUMENT_TYPE_ID SOURCE_SYSTEM REFERENCE_DOCUMENT_TYPE_ID REFERENCE_SOURCE_SYSTEM MEMO_LINE_SEQ_ID VERSION_FLAG VERSION_NUMBER ACCOUNTING_EFFECT_FLAG LINE_CREATION_DATE LINE_LAST_UPDATE_DATE RETURN_REASON_CODE SHIP_TO_COUNTRY BILL_TO_COUNTRY BILL_TO_CUSTOMER_STATE BILL_TO_CUSTOMER_COUNTY BILL_TO_CUSTOMER_CITY BILL_TO_CUSTOMER_POSTAL_CODE BILL_TO_CUST_CLASSIFICATION

Query

SQL_Statement

SELECT DFLA.ACTUAL_SHIP_DATE ACTUAL_SHIPMENT_DATE, BILL_CAS.CUST_ACCT_SITE_ID BILL_TO_CUSTOMER_SITE_ID, BILL_TO_CUSTOMER_ID, DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('UOM_CODE', 'UOM_CODES', DFLA.ORDERED_UOM) UOM_CODE, DECODE(DFLA.CATEGORY_CODE, 'ORDER', NVL(DFLA.TOTAL_CONTRACT_QUANTITY, DFLA.ORDERED_QTY), DECODE(DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'RETURN_REASON', DFLA.CANCEL_REASON_CODE), 'ORA_PRICE_CHANGE', NVL(DFLA.TOTAL_CONTRACT_QUANTITY, DFLA.ORDERED_QTY), -1 * NVL(DFLA.TOTAL_CONTRACT_QUANTITY, DFLA.ORDERED_QTY))) QUANTITY, DFLA.INVENTORY_ITEM_ID ITEM_ID, DFLA.HEADER_ID DOC_ID_INT_1, DLA.LINE_NUMBER LINE_NUM, DFLA.FULFILL_LINE_ID DOC_LINE_ID_INT_1, DFLA.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID, DDR.DOC_SUBLINE_ID REFERENCE_DOC_LINE_ID_INT_1, DECODE(DFLA.CATEGORY_CODE, 'ORDER', DOC.AVG_UNIT_SELLING_PRICE, DECODE(DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'RETURN_REASON', DFLA.CANCEL_REASON_CODE), 'ORA_PRICE_CHANGE', DOC.AVG_UNIT_SELLING_PRICE, -1 * DOC.AVG_UNIT_SELLING_PRICE)) UNIT_SELLING_PRICE, DFLA.UNIT_LIST_PRICE, DECODE(DFLA.UNIT_LIST_PRICE, 0, 0, ((((NVL(DOC.AVG_UNIT_SELLING_PRICE,0) / NVL(DFLA.TOTAL_CONTRACT_QUANTITY,1)) - DFLA.UNIT_LIST_PRICE) / DFLA.UNIT_LIST_PRICE)) * 100) DISCOUNT_PERCENTAGE, SHIP_TO_PARTY_SITE_ID SHIP_TO_CUSTOMER_SITE_ID, SHIP_TO_PARTY_ID SHIP_TO_CUSTOMER_ID, DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('TERM_ID', 'PAYMENT_TERMS', DFLA.PAYMENT_TERM_ID) PAYMENT_TERM_ID, NVL(DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('RULE_ID', 'INVOICING_ACCT_RULES', DFLA.ACCOUNTING_RULE_ID), -106) ACCOUNTING_RULE_ID, TOTAL_CONTRACT_AMOUNT LINE_AMOUNT, DFLA.CUSTOMER_PO_NUMBER CUST_PO_NUMBER, DECODE(DFLA.CATEGORY_CODE, 'ORDER', NVL(DFLA.FULFILLMENT_DATE, NVL(DFLA.ACTUAL_SHIP_DATE,DHA.ORDERED_DATE)), NVL(DFLA.FULFILLMENT_DATE, NVL(DFLD.RMA_RECEIPT_DATE,DHA.ORDERED_DATE))) DELIVERY_DATE, 'FULFILLED' DELIVERY_STATUS, NVL(DFLA.CONTRACT_START_DATE, NVL(DFLA.ACTUAL_SHIP_DATE,NVL(DHA.ORDERED_DATE,trunc(sysdate)))) RULE_START_DATE, CONTRACT_END_DATE AS "RULE_END_DATE", NULL ACCOUNTING_RULE_DURATION, DECODE(DFLA.CATEGORY_CODE, 'RETURN', DECODE(CONTRACT_START_DATE, NVL( DBP.CANCELLATION_EFFECTIVE_DATE, DHA.ORDERED_DATE), 'PRORATE', 'LIFO'), NULL) REVENUE_REVERSAL_METHOD, 5 DOCUMENT_TYPE_ID, 'FUSION' SOURCE_SYSTEM, DECODE(DFLA.CATEGORY_CODE, 'ORDER', NULL, 5) REFERENCE_DOCUMENT_TYPE_ID, DECODE(DFLA.CATEGORY_CODE, 'ORDER', NULL, 'FUSION') REFERENCE_SOURCE_SYSTEM, NULL MEMO_LINE_SEQ_ID, 'N' VERSION_FLAG, 1 VERSION_NUMBER, 'N' ACCOUNTING_EFFECT_FLAG, DFLA.CREATION_DATE LINE_CREATION_DATE, DFLA.LAST_UPDATE_DATE LINE_LAST_UPDATE_DATE, DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('LOOKUP_CODE', 'RETURN_REASON', DFLA.RETURN_REASON_CODE) RETURN_REASON_CODE, SHIP_LOC.COUNTRY SHIP_TO_COUNTRY, BILL_LOC.COUNTRY BILL_TO_COUNTRY, BILL_LOC.STATE BILL_TO_CUSTOMER_STATE, BILL_LOC.COUNTY BILL_TO_CUSTOMER_COUNTY, BILL_LOC.CITY BILL_TO_CUSTOMER_CITY, BILL_LOC.POSTAL_CODE BILL_TO_CUSTOMER_POSTAL_CODE, CUST_ACCT.CUSTOMER_CLASS_CODE BILL_TO_CUST_CLASSIFICATION FROM DOO_FULFILL_LINES_ALL DFLA , DOO_FULFILL_LINE_DETAILS DFLD, DOO_HEADERS_ALL DHA, DOO_LINES_ALL DLA, DOO_ORDER_CHARGES DOC, DOO_DOCUMENT_REFERENCES DDR , HZ_PARTY_SITES SHIP_PS , HZ_LOCATIONS SHIP_LOC, HZ_CUST_SITE_USES_ALL BILL_SU , HZ_PARTY_SITES BILL_PS , HZ_LOCATIONS BILL_LOC , HZ_CUST_ACCT_SITES_ALL BILL_CAS, HZ_CUST_ACCOUNTS CUST_ACCT, (SELECT FULFILL_LINE_ID, MAX(CANCELLATION_EFFECTIVE_DATE) CANCELLATION_EFFECTIVE_DATE FROM DOO_BILLING_PLANS GROUP BY FULFILL_LINE_ID) DBP WHERE DFLA.FULFILL_LINE_ID = DOC.PARENT_ENTITY_ID(+) AND (DOC.CHARGE_APPLIES_TO(+) = 'PRICE' AND DOC.PRIMARY_FLAG(+) = 'Y') AND DFLA.FULFILL_LINE_ID = DDR.FULFILL_LINE_ID(+) AND DDR.DOC_REF_TYPE(+) = 'ORIGINAL_ORCHESTRATION_ORDER' AND DFLA.FULFILL_LINE_ID = DBP.FULFILL_LINE_ID(+) AND SHIP_LOC.LOCATION_ID(+) = SHIP_PS.LOCATION_ID AND DFLA.SHIP_TO_PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID (+) AND BILL_SU.CUST_ACCT_SITE_ID= BILL_CAS.CUST_ACCT_SITE_ID(+) AND BILL_CAS.PARTY_SITE_ID = BILL_PS.PARTY_SITE_ID(+) AND BILL_LOC.LOCATION_ID(+) = BILL_PS.LOCATION_ID AND DFLA.BILL_TO_SITE_USE_ID = BILL_SU.SITE_USE_ID (+) AND DFLA.BILL_TO_CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+) AND DFLA.HEADER_ID = DHA.HEADER_ID AND DFLA.HEADER_ID = DLA.HEADER_ID AND DFLA.FULFILL_LINE_ID = DFLD.FULFILL_LINE_ID(+) AND DFLA.INVOICE_INTERFACED_FLAG = 'Y'