MSC_AP_COUNTER_SALES_ORDERS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ITEM_NAME ORIGINAL_ITEM_NAME CONFIGURED_ITEM_NAME ORGANIZATION_CODE RECEIVING_ORG_CODE USING_REQUIREMENT_QUANTITY COMPLETED_QUANTITY SALES_ORDER_NUMBER SO_LINE_NUM SALES_ORDER_HEADER_ID ORCH_ORDER_HEADER_ID SALES_ORDER_LINE_ID ORCH_ORDER_LINE_ID SCHEDULE_SHIP_DATE SCHEDULE_ARRIVAL_DATE REQUEST_DATE ORDER_DATE_TYPE_CODE PROMISE_SHIP_DATE PROMISE_ARRIVAL_DATE LATEST_ACCEPTABLE_SHIP_DATE LATEST_ACCEPTABLE_ARRIVAL_DATE EARLIEST_ACCEPT_SHIP_DATE CARRIER_NAME SERVICE_LEVEL_NAME MODE_OF_TRANSPORT_NAME CUSTOMER_NAME SHIP_TO_SITE_CODE BILL_TO_SITE_CODE SHIP_SET_NAME DEMAND_PRIORITY DEMAND_CLASS DEMAND_CLASS_NAME DEMAND_CLASS_CODE SHIP_TO_SITE_USE_ID SHIP_TO_CUSTOMER_ID SHIPMENT_PRIORITY_CODE ORDERED_UOM SUPPLIER_NAME SUPPLIER_SITE_CODE DEMAND_SOURCE_TYPE PARENT_FULFILLMENT_LINE INCLUDED_ITEMS_FLAG ROOT_FULFILLMENT_LINE SELLING_PRICE CUSTOMER_PO_NUMBER CUSTOMER_PO_LINE_NUMBER CUSTOMER_PO_SCHEDULE_NUMBER INVENTORY_ORGANIZATION_CODE ALLOW_SPLITS ALLOW_SUBSTITUTION FULFILLMENT_COST FULFILLMENT_LINE_NUM DEMAND_SOURCE_SYSTEM ORDERED_DATE ITEM_TYPE_CODE ITEM_SUB_TYPE_CODE ORDER_MARGIN SOURCE_DOCUMENT_NUMBER SOURCE_DOCUMENT_LINE_NUMBER PROMISING_SYSTEM ORCH_ORDER_NUMBER ORCH_ORDER_LINE_NUMBER DISPLAY_LINE_NUMBER SUPPLIER_SITE_SOURCE_SYSTEM SOURCE_SCHEDULE_NUMBER FULFILL_ROW_ID LINES_ROW_ID HEADERS_ROW_ID SELLING_PRICE_CURR_CODE GOP_REFERENCE_ID SECONDARY_UOM PRICE_USING_SEC_UOM_FLAG STATUS_CODE OPEN_FLAG REQUEST_SHIP_DATE PAST_DUE_SO_DAYS PROJECT_ID TASK_ID PO_LINE_LOCATION_ID INVENTORY_INTERFACED_FLAG ORDER_LINE_STATUS

Name

ITEM_NAME

ORIGINAL_ITEM_NAME

CONFIGURED_ITEM_NAME

ORGANIZATION_CODE

RECEIVING_ORG_CODE

USING_REQUIREMENT_QUANTITY

COMPLETED_QUANTITY

SALES_ORDER_NUMBER

SO_LINE_NUM

SALES_ORDER_HEADER_ID

ORCH_ORDER_HEADER_ID

SALES_ORDER_LINE_ID

ORCH_ORDER_LINE_ID

SCHEDULE_SHIP_DATE

SCHEDULE_ARRIVAL_DATE

REQUEST_DATE

ORDER_DATE_TYPE_CODE

PROMISE_SHIP_DATE

PROMISE_ARRIVAL_DATE

LATEST_ACCEPTABLE_SHIP_DATE

LATEST_ACCEPTABLE_ARRIVAL_DATE

EARLIEST_ACCEPT_SHIP_DATE

CARRIER_NAME

SERVICE_LEVEL_NAME

MODE_OF_TRANSPORT_NAME

CUSTOMER_NAME

SHIP_TO_SITE_CODE

BILL_TO_SITE_CODE

SHIP_SET_NAME

DEMAND_PRIORITY

DEMAND_CLASS

DEMAND_CLASS_NAME

DEMAND_CLASS_CODE

SHIP_TO_SITE_USE_ID

SHIP_TO_CUSTOMER_ID

SHIPMENT_PRIORITY_CODE

ORDERED_UOM

SUPPLIER_NAME

SUPPLIER_SITE_CODE

DEMAND_SOURCE_TYPE

PARENT_FULFILLMENT_LINE

INCLUDED_ITEMS_FLAG

ROOT_FULFILLMENT_LINE

SELLING_PRICE

CUSTOMER_PO_NUMBER

CUSTOMER_PO_LINE_NUMBER

CUSTOMER_PO_SCHEDULE_NUMBER

INVENTORY_ORGANIZATION_CODE

ALLOW_SPLITS

ALLOW_SUBSTITUTION

FULFILLMENT_COST

FULFILLMENT_LINE_NUM

DEMAND_SOURCE_SYSTEM

ORDERED_DATE

ITEM_TYPE_CODE

ITEM_SUB_TYPE_CODE

ORDER_MARGIN

SOURCE_DOCUMENT_NUMBER

SOURCE_DOCUMENT_LINE_NUMBER

PROMISING_SYSTEM

ORCH_ORDER_NUMBER

ORCH_ORDER_LINE_NUMBER

DISPLAY_LINE_NUMBER

SUPPLIER_SITE_SOURCE_SYSTEM

SOURCE_SCHEDULE_NUMBER

FULFILL_ROW_ID

LINES_ROW_ID

HEADERS_ROW_ID

SELLING_PRICE_CURR_CODE

GOP_REFERENCE_ID

SECONDARY_UOM

PRICE_USING_SEC_UOM_FLAG

STATUS_CODE

OPEN_FLAG

REQUEST_SHIP_DATE

PAST_DUE_SO_DAYS

PROJECT_ID

TASK_ID

PO_LINE_LOCATION_ID

INVENTORY_INTERFACED_FLAG

ORDER_LINE_STATUS

Query

SQL_Statement
with MTRX AS (SELECT trx_source_line_id , transaction_Date , row_number() over (partition by trx_source_line_id , transaction_Date order by trx_source_line_id ) latest from inv_material_txns where transaction_source_type_id = 2 AND transaction_action_id = 69 AND transaction_type_id = 6), INV AS (SELECT trx_source_line_id , transaction_Date , row_number() over (partition by trx_source_line_id , transaction_Date order by trx_source_line_id ) latest from inv_transactions_interface where transaction_source_type_id = 2 AND transaction_action_id = 69 AND transaction_type_id = 6) SELECT /+ leading(FULFILLMENT) / (SELECT item.ITEM_NUMBER FROM MSC_PLANNED_EGP_SYSTEM_ITEMS item WHERE ITEM.INVENTORY_ITEM_ID = FULFILLMENT.INVENTORY_ITEM_ID and (FULFILLMENT.FULFILL_ORG_ID = ITEM.ORGANIZATION_ID OR FULFILLMENT.FULFILL_ORG_ID IS NULL ) AND rownum =1 ) ITEM_NAME, (SELECT item.ITEM_NUMBER FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V item WHERE ITEM.INVENTORY_ITEM_ID = FULFILLMENT.original_inventory_item_id AND rownum =1 ) ORIGINAL_ITEM_NAME, (SELECT item.ITEM_NUMBER FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V item WHERE ITEM.INVENTORY_ITEM_ID = FULFILLMENT.config_inventory_item_id AND rownum =1 ) configured_item_name, shporg.organization_code ORGANIZATION_CODE , rcvorg.organization_code receiving_org_code, decode(nvl(FULFILLMENT.inventory_interfaced_flag,'N'),'Y',0,fulfillment.ordered_qty) using_requirement_quantity, decode(nvl(FULFILLMENT.inventory_interfaced_flag,'N'),'Y',fulfillment.ordered_qty,0) Completed_quantity, DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',null,FULFILLMENT.SOURCE_ORDER_NUMBER) sales_order_number, DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',null,FULFILLMENT.SOURCE_LINE_NUMBER) so_line_num, FULFILLMENT.source_order_id Sales_order_header_id, FULFILLMENT.header_id orch_order_header_id , FULFILLMENT.fulfill_line_id Sales_order_line_id , FULFILLMENT.line_id orch_order_line_id, nvl(MTRX.TRANSACTION_dATE , INV.TRANSACTION_dATE) schedule_ship_date , FULFILLMENT.schedule_arrival_date schedule_arrival_date, decode(FULFILLMENT.request_arrival_date,null,FULFILLMENT.request_ship_date,FULFILLMENT.request_arrival_date) request_date , decode(FULFILLMENT.request_arrival_date,null,1,2) order_date_type_code , FULFILLMENT.promise_ship_date promise_ship_date, FULFILLMENT.promise_arrival_date promise_arrival_date , FULFILLMENT.latest_acceptable_ship_date latest_acceptable_ship_date, FULFILLMENT.latest_acceptable_arrival_date latest_acceptable_arrival_date, FULFILLMENT.earliest_acceptable_ship_date earliest_accept_ship_date, HZP.PARTNER_NAME CARRIER_NAME, FNLSRL.meaning SERVICE_LEVEL_NAME, FNLMOD.meaning MODE_OF_TRANSPORT_NAME, custHZP.PARTY_NUMBER customer_name, custsitesHPS.PARTY_SITE_NUMBER ship_to_site_code, null bill_to_site_code, FULFILLMENT.ship_set_name ship_set_name, NULL demand_priority, FULFILLMENT.DEMAND_CLASS_CODE demand_class, dc.meaning demand_class_name, FULFILLMENT.DEMAND_CLASS_CODE, FULFILLMENT.SHIP_TO_SITE_USE_ID, FULFILLMENT.SHIP_TO_CUSTOMER_ID, HEADERS.SHIPMENT_PRIORITY_CODE, FULFILLMENT.Ordered_uom Ordered_uom, null SUPPLIER_NAME, null SUPPLIER_SITE_CODE, DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'ISO',8,'TO',1) DEMAND_SOURCE_TYPE, FULFILLMENT.PARENT_FULFILL_LINE_ID Parent_Fulfillment_Line, decode(FULFILLMENT.ITEM_SUB_TYPE_CODE,'ATO',2,1) included_items_flag , FULFILLMENT.ROOT_PARENT_FULFILL_LINE_ID root_Fulfillment_Line , nvl(FULFILLMENT.UNIT_SELLING_PRICE,0) SELLING_PRICE, FULFILLMENT.CUSTOMER_PO_NUMBER CUSTOMER_PO_NUMBER, FULFILLMENT.CUSTOMER_PO_LINE_NUMBER CUSTOMER_PO_LINE_NUMBER, FULFILLMENT.customer_po_schedule_number CUSTOMER_PO_SCHEDULE_NUMBER, invorg.organization_code inventory_organization_code, DECODE(FULFILLMENT.PARTIAL_SHIP_ALLOWED_FLAG,'N',2,1) ALLOW_SPLITS, DECODE(FULFILLMENT.SUBSTITUTE_ALLOWED_FLAG,'N',2,1) ALLOW_SUBSTITUTION, FULFILLMENT.ESTIMATE_FULFILLMENT_COST FULFILLMENT_COST, FULFILLMENT.FULFILL_LINE_NUMBER FULFILLMENT_LINE_NUM, FULFILLMENT.SOURCE_ORDER_SYSTEM DEMAND_SOURCE_SYSTEM, headers.ORDERED_DATE ORDERED_DATE, decode(FULFILLMENT.ITEM_TYPE_CODE,'1','MODEL','2','OPTION_CLASS','4','STANDARD') ITEM_TYPE_CODE, FULFILLMENT.ITEM_SUB_TYPE_CODE ITEM_SUB_TYPE_CODE, FULFILLMENT.ESTIMATE_MARGIN ORDER_MARGIN, DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',FULFILLMENT.SOURCE_ORDER_NUMBER,NULL) SOURCE_DOCUMENT_NUMBER, DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',FULFILLMENT.SOURCE_LINE_NUMBER,NULL) SOURCE_DOCUMENT_LINE_NUMBER, 'GOP' PROMISING_SYSTEM, HEADERS.ORDER_NUMBER ORCH_ORDER_NUMBER, LINES.LINE_NUMBER ORCH_ORDER_LINE_NUMBER, LINES.DISPLAY_LINE_NUMBER DISPLAY_LINE_NUMBER, apps.instance_code SUPPLIER_SITE_SOURCE_SYSTEM , FULFILLMENT.SOURCE_SCHEDULE_NUMBER SOURCE_SCHEDULE_NUMBER , FULFILLMENT.rowid fulfill_row_id, lines.rowid lines_row_id, headers.rowid headers_row_id, HEADERS.TRANSACTIONAL_CURRENCY_CODE SELLING_PRICE_CURR_CODE, FULFILLMENT.GOP_REFERENCE_ID GOP_REFERENCE_ID, FULFILLMENT.secondary_uom, FULFILLMENT.price_using_sec_uom_flag, FULFILLMENT.status_code, HEADERS.open_flag, fulfillment.request_ship_date , org.past_due_so_days , decode(NVL(FULFILLMENT.FULFILL_ORG_ID,-99) ,-99,NULL, prj.pjc_project_id) project_id, decode(NVL(FULFILLMENT.FULFILL_ORG_ID,-99) ,-99,NULL,prj.pjc_task_id) task_id, null PO_LINE_LOCATION_ID , inventory_interfaced_flag , fulfillment.status_code ORDER_LINE_STATUS FROM doo_fulfill_lines_all FULFILLMENT, DOO_LINES_ALL LINES, doo_headers_all HEADERS, inv_org_parameters shporg, inv_org_parameters rcvorg, inv_org_parameters invorg, MSC_GLOBAL_TRADING_PARTNERS HZP, HZ_PARTIES custHZP, HZ_PARTY_SITES custsitesHPS, mSC_SR_LOOKUP_VALUES_VL FNLMOD, MSC_SR_LOOKUP_VALUES_VL FNLSRL, MSC_SR_LOOKUP_VALUES_VL DC, msc_apps_instances apps, msc_parameters org, doo_projects prj, MTRX,INV WHERE fulfillment.INVENTORY_TRANSACTION_FLAG ='Y' AND MTRX.trx_source_line_id(+) = FULFILLMENT.FULFILL_LINE_ID AND inv.trx_source_line_id(+) = FULFILLMENT.FULFILL_LINE_ID and MTRX.latest(+)=1 and INV.latest(+)= 1 and HEADERS.submitted_flag='Y' and FULFILLMENT.canceled_flag <>'Y' and FULFILLMENT.category_code <> 'RETURN' and FULFILLMENT.LINE_ID=LINES.LINE_ID and LINES.HEADER_ID=HEADERS.HEADER_ID and shporg.organization_id (+) = FULFILLMENT.fulfill_org_id and rcvorg.organization_id (+) = FULFILLMENT.DESTINATION_ORG_ID and invorg.organization_id (+) = FULFILLMENT.INVENTORY_ORGANIZATION_ID and HZP.TP_ID(+) = FULFILLMENT.CARRIER_ID and custHZP.party_id(+) = FULFILLMENT.ship_to_party_id AND custsitesHPS.PARTY_SITE_ID(+)=FULFILLMENT.ship_to_party_site_id and FNLMOD.LOOKUP_CODE(+) = decode(FULFILLMENT.ship_mode_of_transport , NULL, NULL, decode(upper(FULFILLMENT.ship_mode_of_transport), lower(FULFILLMENT.ship_mode_of_transport), to_number(FULFILLMENT.ship_mode_of_transport),NULL)) and FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT' and FNLSRL.LOOKUP_CODE(+) = decode(FULFILLMENT.ship_class_of_service , NULL, NULL, decode(upper(FULFILLMENT.ship_class_of_service), lower(FULFILLMENT.ship_class_of_service), to_number(FULFILLMENT.ship_class_of_service),NULL)) and FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS' and dc.LOOKUP_CODE(+)= decode(FULFILLMENT.DEMAND_CLASS_CODE , NULL, NULL, decode(upper(FULFILLMENT.DEMAND_CLASS_CODE), lower(FULFILLMENT.DEMAND_CLASS_CODE), to_number(FULFILLMENT.DEMAND_CLASS_CODE),NULL)) and dc.LOOKUP_TYPE(+) ='DEMAND_CLASS' and apps.instance_id(+)=FULFILLMENT.FULFILL_INSTANCE_ID and org.organization_code (+) = shporg.organization_code and PRJ.parent_entity_id(+) = FULFILLMENT.fulfill_line_id

SQL_Statement

with

MTRX AS

(SELECT trx_source_line_id , transaction_Date , row_number() over

(partition by trx_source_line_id , transaction_Date order by trx_source_line_id ) latest

from inv_material_txns where

transaction_source_type_id = 2

AND transaction_action_id = 69

AND transaction_type_id = 6),

INV AS

(SELECT trx_source_line_id , transaction_Date , row_number() over

(partition by trx_source_line_id , transaction_Date order by trx_source_line_id ) latest

from inv_transactions_interface where

transaction_source_type_id = 2

AND transaction_action_id = 69

AND transaction_type_id = 6)

SELECT /*+ leading(FULFILLMENT) */

(SELECT item.ITEM_NUMBER

FROM MSC_PLANNED_EGP_SYSTEM_ITEMS item

WHERE ITEM.INVENTORY_ITEM_ID = FULFILLMENT.INVENTORY_ITEM_ID

and (FULFILLMENT.FULFILL_ORG_ID = ITEM.ORGANIZATION_ID OR FULFILLMENT.FULFILL_ORG_ID IS NULL )

AND rownum =1

) ITEM_NAME,

(SELECT item.ITEM_NUMBER

FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V item

WHERE ITEM.INVENTORY_ITEM_ID = FULFILLMENT.original_inventory_item_id

AND rownum =1

) ORIGINAL_ITEM_NAME,

(SELECT item.ITEM_NUMBER

FROM MSC_PLANNED_EGP_SYSTEM_ITEMS_V item

WHERE ITEM.INVENTORY_ITEM_ID = FULFILLMENT.config_inventory_item_id

AND rownum =1

) configured_item_name,

shporg.organization_code ORGANIZATION_CODE ,

rcvorg.organization_code receiving_org_code,

decode(nvl(FULFILLMENT.inventory_interfaced_flag,'N'),'Y',0,fulfillment.ordered_qty) using_requirement_quantity,

decode(nvl(FULFILLMENT.inventory_interfaced_flag,'N'),'Y',fulfillment.ordered_qty,0) Completed_quantity,

DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',null,FULFILLMENT.SOURCE_ORDER_NUMBER) sales_order_number,

DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',null,FULFILLMENT.SOURCE_LINE_NUMBER) so_line_num,

FULFILLMENT.source_order_id Sales_order_header_id,

FULFILLMENT.header_id orch_order_header_id ,

FULFILLMENT.fulfill_line_id Sales_order_line_id ,

FULFILLMENT.line_id orch_order_line_id,

nvl(MTRX.TRANSACTION_dATE , INV.TRANSACTION_dATE) schedule_ship_date ,

FULFILLMENT.schedule_arrival_date schedule_arrival_date,

decode(FULFILLMENT.request_arrival_date,null,FULFILLMENT.request_ship_date,FULFILLMENT.request_arrival_date) request_date ,

decode(FULFILLMENT.request_arrival_date,null,1,2) order_date_type_code ,

FULFILLMENT.promise_ship_date promise_ship_date,

FULFILLMENT.promise_arrival_date promise_arrival_date ,

FULFILLMENT.latest_acceptable_ship_date latest_acceptable_ship_date,

FULFILLMENT.latest_acceptable_arrival_date latest_acceptable_arrival_date,

FULFILLMENT.earliest_acceptable_ship_date earliest_accept_ship_date,

HZP.PARTNER_NAME CARRIER_NAME,

FNLSRL.meaning SERVICE_LEVEL_NAME,

FNLMOD.meaning MODE_OF_TRANSPORT_NAME,

custHZP.PARTY_NUMBER customer_name,

custsitesHPS.PARTY_SITE_NUMBER ship_to_site_code,

null bill_to_site_code,

FULFILLMENT.ship_set_name ship_set_name,

NULL demand_priority,

FULFILLMENT.DEMAND_CLASS_CODE demand_class,

dc.meaning demand_class_name,

FULFILLMENT.DEMAND_CLASS_CODE,

FULFILLMENT.SHIP_TO_SITE_USE_ID,

FULFILLMENT.SHIP_TO_CUSTOMER_ID,

HEADERS.SHIPMENT_PRIORITY_CODE,

FULFILLMENT.Ordered_uom Ordered_uom,

null SUPPLIER_NAME,

null SUPPLIER_SITE_CODE,

DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'ISO',8,'TO',1) DEMAND_SOURCE_TYPE,

FULFILLMENT.PARENT_FULFILL_LINE_ID Parent_Fulfillment_Line,

decode(FULFILLMENT.ITEM_SUB_TYPE_CODE,'ATO',2,1) included_items_flag ,

FULFILLMENT.ROOT_PARENT_FULFILL_LINE_ID root_Fulfillment_Line ,

nvl(FULFILLMENT.UNIT_SELLING_PRICE,0) SELLING_PRICE,

FULFILLMENT.CUSTOMER_PO_NUMBER CUSTOMER_PO_NUMBER,

FULFILLMENT.CUSTOMER_PO_LINE_NUMBER CUSTOMER_PO_LINE_NUMBER,

FULFILLMENT.customer_po_schedule_number CUSTOMER_PO_SCHEDULE_NUMBER,

invorg.organization_code inventory_organization_code,

DECODE(FULFILLMENT.PARTIAL_SHIP_ALLOWED_FLAG,'N',2,1) ALLOW_SPLITS,

DECODE(FULFILLMENT.SUBSTITUTE_ALLOWED_FLAG,'N',2,1) ALLOW_SUBSTITUTION,

FULFILLMENT.ESTIMATE_FULFILLMENT_COST FULFILLMENT_COST,

FULFILLMENT.FULFILL_LINE_NUMBER FULFILLMENT_LINE_NUM,

FULFILLMENT.SOURCE_ORDER_SYSTEM DEMAND_SOURCE_SYSTEM,

headers.ORDERED_DATE ORDERED_DATE,

decode(FULFILLMENT.ITEM_TYPE_CODE,'1','MODEL','2','OPTION_CLASS','4','STANDARD') ITEM_TYPE_CODE,

FULFILLMENT.ITEM_SUB_TYPE_CODE ITEM_SUB_TYPE_CODE,

FULFILLMENT.ESTIMATE_MARGIN ORDER_MARGIN,

DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',FULFILLMENT.SOURCE_ORDER_NUMBER,NULL) SOURCE_DOCUMENT_NUMBER,

DECODE(HEADERS.SOURCE_DOCUMENT_TYPE_CODE,'TO',FULFILLMENT.SOURCE_LINE_NUMBER,NULL) SOURCE_DOCUMENT_LINE_NUMBER,

'GOP' PROMISING_SYSTEM,

HEADERS.ORDER_NUMBER ORCH_ORDER_NUMBER,

LINES.LINE_NUMBER ORCH_ORDER_LINE_NUMBER,

LINES.DISPLAY_LINE_NUMBER DISPLAY_LINE_NUMBER,

apps.instance_code SUPPLIER_SITE_SOURCE_SYSTEM ,

FULFILLMENT.SOURCE_SCHEDULE_NUMBER SOURCE_SCHEDULE_NUMBER ,

FULFILLMENT.rowid fulfill_row_id,

lines.rowid lines_row_id,

headers.rowid headers_row_id,

HEADERS.TRANSACTIONAL_CURRENCY_CODE SELLING_PRICE_CURR_CODE,

FULFILLMENT.GOP_REFERENCE_ID GOP_REFERENCE_ID,

FULFILLMENT.secondary_uom,

FULFILLMENT.price_using_sec_uom_flag,

FULFILLMENT.status_code,

HEADERS.open_flag,

fulfillment.request_ship_date ,

org.past_due_so_days ,

decode(NVL(FULFILLMENT.FULFILL_ORG_ID,-99) ,-99,NULL, prj.pjc_project_id) project_id,

decode(NVL(FULFILLMENT.FULFILL_ORG_ID,-99) ,-99,NULL,prj.pjc_task_id) task_id,

null PO_LINE_LOCATION_ID ,

inventory_interfaced_flag ,

fulfillment.status_code ORDER_LINE_STATUS

FROM

doo_fulfill_lines_all FULFILLMENT,

DOO_LINES_ALL LINES,

doo_headers_all HEADERS,

inv_org_parameters shporg,

inv_org_parameters rcvorg,

inv_org_parameters invorg,

MSC_GLOBAL_TRADING_PARTNERS HZP,

HZ_PARTIES custHZP,

HZ_PARTY_SITES custsitesHPS,

mSC_SR_LOOKUP_VALUES_VL FNLMOD,

MSC_SR_LOOKUP_VALUES_VL FNLSRL,

MSC_SR_LOOKUP_VALUES_VL DC,

msc_apps_instances apps,

msc_parameters org,

doo_projects prj, MTRX,INV

WHERE

fulfillment.INVENTORY_TRANSACTION_FLAG ='Y'

AND MTRX.trx_source_line_id(+) = FULFILLMENT.FULFILL_LINE_ID

AND inv.trx_source_line_id(+) = FULFILLMENT.FULFILL_LINE_ID

and MTRX.latest(+)=1

and INV.latest(+)= 1

and HEADERS.submitted_flag='Y'

and FULFILLMENT.canceled_flag <>'Y'

and FULFILLMENT.category_code <> 'RETURN'

and FULFILLMENT.LINE_ID=LINES.LINE_ID

and LINES.HEADER_ID=HEADERS.HEADER_ID

and shporg.organization_id (+) = FULFILLMENT.fulfill_org_id

and rcvorg.organization_id (+) = FULFILLMENT.DESTINATION_ORG_ID

and invorg.organization_id (+) = FULFILLMENT.INVENTORY_ORGANIZATION_ID

and HZP.TP_ID(+) = FULFILLMENT.CARRIER_ID

and custHZP.party_id(+) = FULFILLMENT.ship_to_party_id

AND custsitesHPS.PARTY_SITE_ID(+)=FULFILLMENT.ship_to_party_site_id

and FNLMOD.LOOKUP_CODE(+) = decode(FULFILLMENT.ship_mode_of_transport , NULL, NULL,

decode(upper(FULFILLMENT.ship_mode_of_transport),

lower(FULFILLMENT.ship_mode_of_transport),

to_number(FULFILLMENT.ship_mode_of_transport),NULL))

and FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT'

and FNLSRL.LOOKUP_CODE(+) = decode(FULFILLMENT.ship_class_of_service , NULL, NULL,

decode(upper(FULFILLMENT.ship_class_of_service),

lower(FULFILLMENT.ship_class_of_service),

to_number(FULFILLMENT.ship_class_of_service),NULL))

and FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS'

and dc.LOOKUP_CODE(+)= decode(FULFILLMENT.DEMAND_CLASS_CODE , NULL, NULL,

decode(upper(FULFILLMENT.DEMAND_CLASS_CODE),

lower(FULFILLMENT.DEMAND_CLASS_CODE),

to_number(FULFILLMENT.DEMAND_CLASS_CODE),NULL))

and dc.LOOKUP_TYPE(+) ='DEMAND_CLASS'

and apps.instance_id(+)=FULFILLMENT.FULFILL_INSTANCE_ID

and org.organization_code (+) = shporg.organization_code

and PRJ.parent_entity_id(+) = FULFILLMENT.fulfill_line_id