MSC_AP_SHIPMENTS_HISTORY

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
FULFILL_LINE_ID FROM_CURRENCY TO_CURRENCY PERSON_NAME SR_INSTANCE_ID SUPPLIER_SITE_SOURCE_SYSTEM ORDER_TYPE_CODE DEMAND_CLASS_CODE ORIGINAL_ITEM_NAME ITEM_NAME ORGANIZATION_CODE CUS_LVL_MEMBER_NAME CUS_SITE_LVL_MEMBER_NAME ORDER_TYPE_FLAG SHIPPED_DATE REQUESTED_DATE SCHEDULED_DATE VALUE_NUMBER_QUANTITY VALUE_NUMBER_AMOUNT IS_INCLUDED DF_ROW_ID DH_ROW_ID

Name

FULFILL_LINE_ID

FROM_CURRENCY

TO_CURRENCY

PERSON_NAME

SR_INSTANCE_ID

SUPPLIER_SITE_SOURCE_SYSTEM

ORDER_TYPE_CODE

DEMAND_CLASS_CODE

ORIGINAL_ITEM_NAME

ITEM_NAME

ORGANIZATION_CODE

CUS_LVL_MEMBER_NAME

CUS_SITE_LVL_MEMBER_NAME

ORDER_TYPE_FLAG

SHIPPED_DATE

REQUESTED_DATE

SCHEDULED_DATE

VALUE_NUMBER_QUANTITY

VALUE_NUMBER_AMOUNT

IS_INCLUDED

DF_ROW_ID

DH_ROW_ID

Query

SQL_Statement
SELECT distinct DF.FULFILL_LINE_ID, DH.TRANSACTIONAL_CURRENCY_CODE FROM_CURRENCY, MPARA.CURRENCY_CODE TO_CURRENCY, rsp.person_name, apps.INSTANCE_ID SR_INSTANCE_ID, apps.instance_code SUPPLIER_SITE_SOURCE_SYSTEM , DH.ORDER_TYPE_CODE ORDER_TYPE_CODE, DF.DEMAND_CLASS_CODE DEMAND_CLASS_CODE, ORGITEMSV.ITEM_NUMBER ORIGINAL_ITEM_NAME, ITEMSV.ITEM_NUMBER ITEM_NAME, ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE, HZ.PARTY_NUMBER CUS_LVL_MEMBER_NAME, HPS.PARTY_SITE_NUMBER CUS_SITE_LVL_MEMBER_NAME, (CASE WHEN (DH.SOURCE_DOCUMENT_TYPE_CODE = 'ISO') THEN 'Internal' ELSE 'External' END) ORDER_TYPE_FLAG, CAST(FROM_TZ(CAST( DF.ACTUAL_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) SHIPPED_DATE, CAST(FROM_TZ(CAST(DF.REQUEST_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) REQUESTED_DATE, CAST(FROM_TZ(CAST(DF.SCHEDULE_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) SCHEDULED_DATE, DF.SHIPPED_QTY * (nvl(SC.PERCENT,100)/100 )* decode (ITEMSV.primary_uom_code , DF.ordered_uom, 1,inv_convert.inv_um_convert(DF.INVENTORY_ITEM_ID,0,null,DF.ordered_uom,ITEMSV.primary_uom_code,null,null ) ) VALUE_NUMBER_QUANTITY, (( DF.SHIPPED_QTY * DF.UNIT_SELLING_PRICE) * nvl(SC.PERCENT,100)/100 ) VALUE_NUMBER_AMOUNT, DECODE(DF.ITEM_SUB_TYPE_CODE, 'INCLUDED', 1, 2 ) IS_INCLUDED, DF.rowid DF_ROW_ID, DH.rowid DH_ROW_ID FROM DOO_FULFILL_LINES_ALL DF, DOO_HEADERS_ALL DH, doo_sales_credits sc, jtf_rs_resource_profiles rsp, EGP_SYSTEM_ITEMS_VL ITEMSV, EGP_SYSTEM_ITEMS_VL ORGITEMSV, MSC_INSTANCE_ORGS ORG, MSC_PARAMETERS MPARA, HZ_PARTIES HZ, HZ_PARTY_SITES HPS, msc_apps_instances apps WHERE 1=1 /* AND DF.FULFILL_LINE_ID IN (SELECT DSID.FULFILL_LINE_ID FROM DOO_STEP_INSTANCE_DETAILS DSID , DOO_PROCESS_STEP_INSTANCES DSI , DOO_TASK_INSTANCES DTI , DOO_TASK_DEFINITIONS_VL DTD , DOO_TASK_TYPES_VL DTT WHERE DSID.STEP_INSTANCE_ID = DSI.STEP_INSTANCE_ID AND DTI.TASK_ID = DTD.TASK_ID AND DTD.TASK_TYPE_ID = DTT.TASK_TYPE_ID AND DTT.TASK_TYPE = 'Shipment' ) */ AND DH.HEADER_ID = DF.HEADER_ID AND DH. SUBMITTED_FLAG='Y' and (DH.SOURCE_DOCUMENT_TYPE_CODE is null or DH.SOURCE_DOCUMENT_TYPE_CODE <> 'TO') AND DF.SHIPPED_QTY > 0 and DF.ITEM_SUB_TYPE_CODE NOT IN ('ATO-OPTION-CLASS','ATO-OPTION','OPTION-CLASS','OPTION') and sc.salesperson_id = rsp.party_id (+) AND DF.HEADER_ID = sc.HEADER_ID (+) AND ITEMSV.INVENTORY_ITEM_ID = DF.INVENTORY_ITEM_ID AND ITEMSV.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID AND ORGITEMSV.INVENTORY_ITEM_ID(+) = DF.ORIGINAL_INVENTORY_ITEM_ID AND ORGITEMSV.ORGANIZATION_ID(+) = ORG.SR_ORGANIZATION_ID and MPARA.organization_code=ORG.sr_Organization_code and MPARA.organization_id=org.organization_id AND HPS.PARTY_SITE_ID = DF.SHIP_TO_PARTY_SITE_ID AND HZ.PARTY_ID = HPS.PARTY_ID and apps.instance_id (+)=DF.FULFILL_INSTANCE_ID AND ( ( ORG.sr_ORGANIZATION_ID = DF.FULFILL_ORG_ID AND ORG.FULFILLMENT_INSTANCE_FLAG = 1 ) OR ( DF.FULFILL_ORG_ID is null and (MPARA.modeled_supplier_id =df.supplier_id AND MPARA.MODELED_SUPPLIER_SITE_ID = DF.SUPPLIER_SITE_ID and ORG.drop_ship_val_org_flag is null) or ( ORG.drop_ship_val_org_flag= 1 and DF.SUPPLIER_SITE_ID is null) ) )

SQL_Statement

SELECT distinct

DF.FULFILL_LINE_ID,

DH.TRANSACTIONAL_CURRENCY_CODE FROM_CURRENCY,

MPARA.CURRENCY_CODE TO_CURRENCY,

rsp.person_name,

apps.INSTANCE_ID SR_INSTANCE_ID,

apps.instance_code SUPPLIER_SITE_SOURCE_SYSTEM ,

DH.ORDER_TYPE_CODE ORDER_TYPE_CODE,

DF.DEMAND_CLASS_CODE DEMAND_CLASS_CODE,

ORGITEMSV.ITEM_NUMBER ORIGINAL_ITEM_NAME,

ITEMSV.ITEM_NUMBER ITEM_NAME,

ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE,

HZ.PARTY_NUMBER CUS_LVL_MEMBER_NAME,

HPS.PARTY_SITE_NUMBER CUS_SITE_LVL_MEMBER_NAME,

(CASE

WHEN (DH.SOURCE_DOCUMENT_TYPE_CODE = 'ISO') THEN

'Internal'

ELSE 'External'

END) ORDER_TYPE_FLAG,

CAST(FROM_TZ(CAST( DF.ACTUAL_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) SHIPPED_DATE,

CAST(FROM_TZ(CAST(DF.REQUEST_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) REQUESTED_DATE,

CAST(FROM_TZ(CAST(DF.SCHEDULE_SHIP_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) SCHEDULED_DATE,

DF.SHIPPED_QTY * (nvl(SC.PERCENT,100)/100 )* decode (ITEMSV.primary_uom_code , DF.ordered_uom, 1,inv_convert.inv_um_convert(DF.INVENTORY_ITEM_ID,0,null,DF.ordered_uom,ITEMSV.primary_uom_code,null,null ) )

VALUE_NUMBER_QUANTITY,

(( DF.SHIPPED_QTY * DF.UNIT_SELLING_PRICE) * nvl(SC.PERCENT,100)/100 )

VALUE_NUMBER_AMOUNT,

DECODE(DF.ITEM_SUB_TYPE_CODE, 'INCLUDED', 1, 2 ) IS_INCLUDED,

DF.rowid DF_ROW_ID,

DH.rowid DH_ROW_ID

FROM DOO_FULFILL_LINES_ALL DF,

DOO_HEADERS_ALL DH,

doo_sales_credits sc,

jtf_rs_resource_profiles rsp,

EGP_SYSTEM_ITEMS_VL ITEMSV,

EGP_SYSTEM_ITEMS_VL ORGITEMSV,

MSC_INSTANCE_ORGS ORG,

MSC_PARAMETERS MPARA,

HZ_PARTIES HZ,

HZ_PARTY_SITES HPS,

msc_apps_instances apps

WHERE 1=1

/* AND DF.FULFILL_LINE_ID IN

(SELECT DSID.FULFILL_LINE_ID

FROM DOO_STEP_INSTANCE_DETAILS DSID ,

DOO_PROCESS_STEP_INSTANCES DSI ,

DOO_TASK_INSTANCES DTI ,

DOO_TASK_DEFINITIONS_VL DTD ,

DOO_TASK_TYPES_VL DTT

WHERE

DSID.STEP_INSTANCE_ID = DSI.STEP_INSTANCE_ID

AND DTI.TASK_ID = DTD.TASK_ID

AND DTD.TASK_TYPE_ID = DTT.TASK_TYPE_ID

AND DTT.TASK_TYPE = 'Shipment'

)

*/

AND DH.HEADER_ID = DF.HEADER_ID

AND DH. SUBMITTED_FLAG='Y'

and (DH.SOURCE_DOCUMENT_TYPE_CODE is null or DH.SOURCE_DOCUMENT_TYPE_CODE <> 'TO')

AND DF.SHIPPED_QTY > 0

and DF.ITEM_SUB_TYPE_CODE NOT IN ('ATO-OPTION-CLASS','ATO-OPTION','OPTION-CLASS','OPTION')

and sc.salesperson_id = rsp.party_id (+)

AND DF.HEADER_ID = sc.HEADER_ID (+)

AND ITEMSV.INVENTORY_ITEM_ID = DF.INVENTORY_ITEM_ID

AND ITEMSV.ORGANIZATION_ID = ORG.SR_ORGANIZATION_ID

AND ORGITEMSV.INVENTORY_ITEM_ID(+) = DF.ORIGINAL_INVENTORY_ITEM_ID

AND ORGITEMSV.ORGANIZATION_ID(+) = ORG.SR_ORGANIZATION_ID

and MPARA.organization_code=ORG.sr_Organization_code

and MPARA.organization_id=org.organization_id

AND HPS.PARTY_SITE_ID = DF.SHIP_TO_PARTY_SITE_ID

AND HZ.PARTY_ID = HPS.PARTY_ID

and apps.instance_id (+)=DF.FULFILL_INSTANCE_ID

AND (

( ORG.sr_ORGANIZATION_ID = DF.FULFILL_ORG_ID

AND ORG.FULFILLMENT_INSTANCE_FLAG = 1

)

OR ( DF.FULFILL_ORG_ID is null and

(MPARA.modeled_supplier_id =df.supplier_id

AND MPARA.MODELED_SUPPLIER_SITE_ID = DF.SUPPLIER_SITE_ID

and ORG.drop_ship_val_org_flag is null)

or

( ORG.drop_ship_val_org_flag= 1

and DF.SUPPLIER_SITE_ID is null)

)