MSC_AP_SALES_ORDER

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 PO_LINE_LOCATION_ID FULFILL_ROW_ID LINES_ROW_ID HEADERS_ROW_ID DOC_ROW_ID SELLING_PRICE_CURR_CODE PROJECT_ID TASK_ID GOP_REFERENCE_ID SECONDARY_UOM PRICE_USING_SEC_UOM_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

PO_LINE_LOCATION_ID

FULFILL_ROW_ID

LINES_ROW_ID

HEADERS_ROW_ID

DOC_ROW_ID

SELLING_PRICE_CURR_CODE

PROJECT_ID

TASK_ID

GOP_REFERENCE_ID

SECONDARY_UOM

PRICE_USING_SEC_UOM_FLAG

ORDER_LINE_STATUS

Query

SQL_Statement
SELECT (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, FULFILLMENT.ordered_qty using_requirement_quantity, FULFILLMENT.shipped_qty 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, FULFILLMENT.schedule_ship_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, sup.partner_name SUPPLIER_NAME, supsite.tp_site_code 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 , doc.doc_subline_id PO_LINE_LOCATION_ID, FULFILLMENT.rowid fulfill_row_id, lines.rowid lines_row_id, headers.rowid headers_row_id, doc.rowid doc_row_id , HEADERS.TRANSACTIONAL_CURRENCY_CODE SELLING_PRICE_CURR_CODE, 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, FULFILLMENT.GOP_REFERENCE_ID GOP_REFERENCE_ID, FULFILLMENT.secondary_uom, FULFILLMENT.price_using_sec_uom_flag, fulfillment.status_code ORDER_LINE_STATUS FROM doo_fulfill_lines_all FULFILLMENT, DOO_LINES_ALL LINES, doo_headers_all HEADERS, DOO_DOCUMENT_REFERENCES DOC, 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_global_trading_partners sup, msc_apps_instances apps, msc_trading_partner_sites SUPSITE, msc_parameters org, doo_projects prj, (SELECT mai.instance_id FROM msc_apps_instances mai WHERE mai.apps_ver = 2 AND mai.enable_flag = 1 AND ROWNUM = 1) apps_inst, msc_instance_orgs dropshiporg, (SELECT modeled_supplier_id, modeled_supplier_site_id, past_due_so_days FROM msc_parameters mp, msc_instance_orgs mio, msc_apps_instances mai WHERE mio.sr_instance_id = mai.instance_id AND mio.enabled_flag = 1 AND mio.organization_id = mp.organization_id AND mai.apps_ver = 2 AND mp.modeled_supplier_id IS NOT NULL ) dropshiporg_sup WHERE sup.tp_id(+)=FULFILLMENT.SUPPLIER_ID AND DOC.FULFILL_LINE_ID(+)=FULFILLMENT.fulfill_line_id and HEADERS.submitted_flag='Y' and FULFILLMENT.canceled_flag<>'Y' and FULFILLMENT.category_code <> 'RETURN' and doc.header_id(+)=fulfillment.header_id and doc.line_id(+)=fulfillment.line_id and doc.doc_ref_type(+)='DROPSHIP_PO_REFERENCE' and doc.doc_line_context_id(+) <> 'PTOMODEL_ROLL_UP' and SUPSITE.partner_site_id(+)= FULFILLMENT.SUPPLIER_SITE_ID and FULFILLMENT.LINE_ID=LINES.LINE_ID and LINES.HEADER_ID=HEADERS.HEADER_ID and PRJ.parent_entity_id(+) = FULFILLMENT.fulfill_line_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 FULFILLMENT.schedule_ship_date IS NOT NULL and ( ( FULFILLMENT.status_code <> 'CLOSED' and HEADERS.open_flag = 'Y' AND nvl(FULFILLMENT.SHIPPED_QTY,0)=0) OR ( FULFILLMENT.SHIPPED_QTY IS NOT NULL AND FULFILLMENT.schedule_ship_date >= trunc(SYSDATE) - (CASE WHEN shporg.organization_code IS NOT NULL THEN NVL(org.past_due_so_days, 40) ELSE COALESCE(dropshiporg_sup.past_due_so_days, org.past_due_so_days, 40) END) ) ) and (FULFILLMENT.HEADER_ID, FULFILLMENT.LINE_ID) not in (select DOC2.HEADER_ID,DOC2.LINE_ID from DOO_DOCUMENT_REFERENCES DOC2 where DOC2.DOC_REF_TYPE='INTERNAL_ORDER' and DOC2.DOC_CONTEXT_ID='EXPENSE') and org.organization_code (+) = NVL(shporg.organization_code, dropshiporg.organization_code) and nvl(fulfillment.INVENTORY_TRANSACTION_FLAG,'N') ='N' AND dropshiporg.sr_instance_id(+) = apps_inst.instance_id AND dropshiporg.enabled_flag(+) = 1 AND dropshiporg.drop_ship_val_org_flag(+) = 1 AND dropshiporg_sup.modeled_supplier_id(+) = sup.tp_id AND dropshiporg_sup.modeled_supplier_site_id(+) = SUPSITE.partner_site_id

SQL_Statement

SELECT

(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,

FULFILLMENT.ordered_qty using_requirement_quantity,

FULFILLMENT.shipped_qty 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,

FULFILLMENT.schedule_ship_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,

sup.partner_name SUPPLIER_NAME,

supsite.tp_site_code 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 ,

doc.doc_subline_id PO_LINE_LOCATION_ID,

FULFILLMENT.rowid fulfill_row_id,

lines.rowid lines_row_id,

headers.rowid headers_row_id,

doc.rowid doc_row_id ,

HEADERS.TRANSACTIONAL_CURRENCY_CODE SELLING_PRICE_CURR_CODE,

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,

FULFILLMENT.GOP_REFERENCE_ID GOP_REFERENCE_ID,

FULFILLMENT.secondary_uom,

FULFILLMENT.price_using_sec_uom_flag,

fulfillment.status_code ORDER_LINE_STATUS

FROM

doo_fulfill_lines_all FULFILLMENT,

DOO_LINES_ALL LINES,

doo_headers_all HEADERS,

DOO_DOCUMENT_REFERENCES DOC,

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_global_trading_partners sup,

msc_apps_instances apps,

msc_trading_partner_sites SUPSITE,

msc_parameters org,

doo_projects prj,

(SELECT mai.instance_id

FROM msc_apps_instances mai

WHERE mai.apps_ver = 2

AND mai.enable_flag = 1

AND ROWNUM = 1) apps_inst,

msc_instance_orgs dropshiporg,

(SELECT modeled_supplier_id, modeled_supplier_site_id, past_due_so_days

FROM msc_parameters mp, msc_instance_orgs mio, msc_apps_instances mai

WHERE mio.sr_instance_id = mai.instance_id

AND mio.enabled_flag = 1

AND mio.organization_id = mp.organization_id

AND mai.apps_ver = 2

AND mp.modeled_supplier_id IS NOT NULL

) dropshiporg_sup

WHERE

sup.tp_id(+)=FULFILLMENT.SUPPLIER_ID

AND DOC.FULFILL_LINE_ID(+)=FULFILLMENT.fulfill_line_id

and HEADERS.submitted_flag='Y'

and FULFILLMENT.canceled_flag<>'Y'

and FULFILLMENT.category_code <> 'RETURN'

and doc.header_id(+)=fulfillment.header_id

and doc.line_id(+)=fulfillment.line_id

and doc.doc_ref_type(+)='DROPSHIP_PO_REFERENCE'

and doc.doc_line_context_id(+) <> 'PTOMODEL_ROLL_UP'

and SUPSITE.partner_site_id(+)= FULFILLMENT.SUPPLIER_SITE_ID

and FULFILLMENT.LINE_ID=LINES.LINE_ID

and LINES.HEADER_ID=HEADERS.HEADER_ID

and PRJ.parent_entity_id(+) = FULFILLMENT.fulfill_line_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 FULFILLMENT.schedule_ship_date IS NOT NULL

and ( ( FULFILLMENT.status_code <> 'CLOSED' and HEADERS.open_flag = 'Y' AND nvl(FULFILLMENT.SHIPPED_QTY,0)=0)

OR

( FULFILLMENT.SHIPPED_QTY IS NOT NULL

AND FULFILLMENT.schedule_ship_date >= trunc(SYSDATE) - (CASE

WHEN shporg.organization_code IS NOT NULL THEN

NVL(org.past_due_so_days, 40)

ELSE

COALESCE(dropshiporg_sup.past_due_so_days, org.past_due_so_days, 40)

END)

)

and (FULFILLMENT.HEADER_ID, FULFILLMENT.LINE_ID) not in

(select DOC2.HEADER_ID,DOC2.LINE_ID from DOO_DOCUMENT_REFERENCES DOC2

where DOC2.DOC_REF_TYPE='INTERNAL_ORDER' and DOC2.DOC_CONTEXT_ID='EXPENSE')

and org.organization_code (+) = NVL(shporg.organization_code, dropshiporg.organization_code)

and nvl(fulfillment.INVENTORY_TRANSACTION_FLAG,'N') ='N'

AND dropshiporg.sr_instance_id(+) = apps_inst.instance_id

AND dropshiporg.enabled_flag(+) = 1

AND dropshiporg.drop_ship_val_org_flag(+) = 1

AND dropshiporg_sup.modeled_supplier_id(+) = sup.tp_id

AND dropshiporg_sup.modeled_supplier_site_id(+) = SUPSITE.partner_site_id