DOO_ACCRUAL_CHARGE_COMP_V

Details

• Schema: FUSION

• Object owner: DOO

• Object type: VIEW

Columns

Name

FULFILL_LINE_ID SOURCE_ORG_ID BILLING_TRANSACTION_NUMBER BILLING_TRANSACTION_DATE CUSTOMER_TRX_LINE_ID WAYBILL_NUMBER BILL_OF_LADING_NUMBER TRACKING_NUMBER DELIVERY_NAME BILLING_PLANS_EXIST RMA_RECEIPT_DATE PRICING_SOURCE_ID PRICING_SOURCE_TYPE_CODE PRICE_ELEMENT_CODE CHARGE_CURRENCY_CODE CHARGE_CURRENCY_EXT_AMOUNT CHARGE_CURRENCY_UNIT_PRICE HEADER_CURRENCY_CODE HEADER_CURRENCY_EXT_AMOUNT HEADER_CURRENCY_UNIT_PRICE PERCENT_OF_COMPARISON_ELEMENT PRICE_ELEMENT_USAGE_CODE ORDER_CHARGE_ID CHARGE_DEFINITION_CODE PRICE_PERIODICITY_CODE HEADER_ID BILL_TO_CUSTOMER_ID BILL_TO_SITE_USE_ID SHIP_TO_PARTY_SITE_ID SHIP_TO_PARTY_ID ACTUAL_SHIP_DATE FULFILLED_QTY SHIPPED_QTY ORDERED_QTY ORDERED_UOM UNIT_LIST_PRICE UNIT_SELLING_PRICE STATUS_CODE CATEGORY_CODE FULFILLMENT_DATE CREATION_DATE LAST_UPDATE_DATE INVOICE_INTERFACED_FLAG INVENTORY_ITEM_ID PRICED_ON

Query

SQL_Statement

select FLINES.FULFILL_LINE_ID ,FLINES.SOURCE_ORG_ID ,FLINE_DETAILS.BILLING_TRANSACTION_NUMBER ,FLINE_DETAILS.BILLING_TRANSACTION_DATE ,FLINE_DETAILS.CUSTOMER_TRX_LINE_ID ,(case when (FLINES.shipped_qty > 0 ) then (select WAYBILL_NUMBER from DOO_FULFILL_LINE_DETAILS where TASK_TYPE = 'Shipment' and fulfill_line_id=FLINES.fulfill_line_id) else null end ) WAYBILL_NUMBER ,(case when (FLINES.shipped_qty > 0 ) then (select BILL_OF_LADING_NUMBER from DOO_FULFILL_LINE_DETAILS where TASK_TYPE = 'Shipment' and fulfill_line_id=FLINES.fulfill_line_id) else null end ) BILL_OF_LADING_NUMBER ,(case when (FLINES.shipped_qty > 0 ) then (select TRACKING_NUMBER from DOO_FULFILL_LINE_DETAILS where TASK_TYPE = 'Shipment' and fulfill_line_id=FLINES.fulfill_line_id) else null end ) TRACKING_NUMBER ,(case when (FLINES.shipped_qty > 0 ) then (select DELIVERY_NAME from DOO_FULFILL_LINE_DETAILS where TASK_TYPE = 'Shipment' and fulfill_line_id=FLINES.fulfill_line_id) else null end ) DELIVERY_NAME ,decode((SELECT count(*) FROM DOO_BILLING_PLANS WHERE FULFILL_LINE_ID=FLINES.FULFILL_LINE_ID),0,'N','Y') BILLING_PLANS_EXIST , (case when (FLINES.rma_delivered_qty > 0 ) then (select rma_receipt_date from DOO_FULFILL_LINE_DETAILS where TASK_TYPE = 'Return' and fulfill_line_id=FLINES.fulfill_line_id) else null end ) RMA_RECEIPT_DATE ,COMPONENTS.PRICING_SOURCE_ID ,COMPONENTS.PRICING_SOURCE_TYPE_CODE ,COMPONENTS.PRICE_ELEMENT_CODE ,COMPONENTS.CHARGE_CURRENCY_CODE ,COMPONENTS.CHARGE_CURRENCY_EXT_AMOUNT ,COMPONENTS.CHARGE_CURRENCY_UNIT_PRICE ,COMPONENTS.HEADER_CURRENCY_CODE ,COMPONENTS.HEADER_CURRENCY_EXT_AMOUNT ,COMPONENTS.HEADER_CURRENCY_UNIT_PRICE ,COMPONENTS.PERCENT_OF_COMPARISON_ELEMENT ,COMPONENTS.PRICE_ELEMENT_USAGE_CODE ,CHARGES.ORDER_CHARGE_ID ,CHARGES.CHARGE_DEFINITION_CODE ,CHARGES.PRICE_PERIODICITY_CODE ,FLINES.HEADER_ID ,FLINES.BILL_TO_CUSTOMER_ID ,FLINES.BILL_TO_SITE_USE_ID ,FLINES.SHIP_TO_PARTY_SITE_ID ,FLINES.SHIP_TO_PARTY_ID ,FLINES.ACTUAL_SHIP_DATE ,FLINES.FULFILLED_QTY ,FLINES.SHIPPED_QTY ,FLINES.ORDERED_QTY ,FLINES.ORDERED_UOM ,FLINES.UNIT_LIST_PRICE ,FLINES.UNIT_SELLING_PRICE ,FLINES.STATUS_CODE ,FLINES.CATEGORY_CODE ,FLINES.FULFILLMENT_DATE ,FLINES.CREATION_DATE ,FLINES.LAST_UPDATE_DATE ,FLINES.INVOICE_INTERFACED_FLAG ,FLINES.INVENTORY_ITEM_ID ,FLINES.PRICED_ON from DOO_FULFILL_LINE_DETAILS FLINE_DETAILS ,DOO_FULFILL_LINES_ALL FLINES ,DOO_ORDER_CHARGES CHARGES ,DOO_ORDER_CHARGE_COMPONENTS COMPONENTS where exists (select header_id from doo_headers_all where header_id=FLINES.HEADER_ID and submitted_flag='Y') and FLINE_DETAILS.TASK_TYPE = 'Invoice' and FLINE_DETAILS.FULFILL_LINE_ID = FLINES.FULFILL_LINE_ID and CHARGES.PARENT_ENTITY_ID = FLINES.FULFILL_LINE_ID and CHARGES.ORDER_CHARGE_ID = COMPONENTS.ORDER_CHARGE_ID and CHARGES.PARENT_ENTITY_CODE = 'LINE' and CHARGES.ROLLUP_FLAG = 'N' and PRICE_ELEMENT_CODE = 'QP_ACCRUAL' and PRICING_SOURCE_TYPE_CODE = 'PRICING_TERM'