MSC_AP_BOOKINGS_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 PRD_LVL_MEMBER_ID ITEM_NAME ORIGINAL_ITEM_NAME ORG_LVL_MEMBER_ID ORGANIZATION_CODE CUSTOMER_ID CUS_LVL_MEMBER_NAME SHIP_TO_SITE_ID CUS_SITE_LVL_MEMBER_NAME ORDER_TYPE_FLAG BOOKED_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

PRD_LVL_MEMBER_ID

ITEM_NAME

ORIGINAL_ITEM_NAME

ORG_LVL_MEMBER_ID

ORGANIZATION_CODE

CUSTOMER_ID

CUS_LVL_MEMBER_NAME

SHIP_TO_SITE_ID

CUS_SITE_LVL_MEMBER_NAME

ORDER_TYPE_FLAG

BOOKED_DATE

REQUESTED_DATE

SCHEDULED_DATE

VALUE_NUMBER_QUANTITY

VALUE_NUMBER_AMOUNT

IS_INCLUDED

DF_ROW_ID

DH_ROW_ID

Query

SQL_Statement
SELECT 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, DF.INVENTORY_ITEM_ID PRD_LVL_MEMBER_ID, ITEMSV.ITEM_NUMBER ITEM_NAME, ORGITEMSV.ITEM_NUMBER ORIGINAL_ITEM_NAME, nvl( DF.FULFILL_ORG_ID ,org.sr_organization_id ) ORG_LVL_MEMBER_ID, ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE, ship_to_party_id customer_id, custHZP.party_number CUS_LVL_MEMBER_NAME, ship_to_party_site_id ship_to_site_id, custsitesHPS.PARTY_SITE_NUMBER CUS_SITE_LVL_MEMBER_NAME, decode( DH.SOURCE_DOCUMENT_TYPE_CODE,'ISO', 'Internal','External') ORDER_TYPE_FLAG, CAST(FROM_TZ(CAST(DH.ORDERED_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) BOOKED_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.ORDERED_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.ORDERED_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 custHZP, HZ_PARTY_SITES custsitesHPS, msc_apps_instances apps WHERE 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 sc.salesperson_id = rsp.party_id (+) AND DF.HEADER_ID = sc.HEADER_ID (+) and DF.CATEGORY_CODE = 'ORDER' and DF.ITEM_SUB_TYPE_CODE NOT IN ('ATO-OPTION-CLASS','ATO-OPTION','OPTION-CLASS','OPTION') 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 custHZP.party_id = DF.ship_to_party_id AND custsitesHPS.PARTY_SITE_ID = DF.ship_to_party_site_id AND apps.instance_id = ORG.sr_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

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,

DF.INVENTORY_ITEM_ID PRD_LVL_MEMBER_ID,

ITEMSV.ITEM_NUMBER ITEM_NAME,

ORGITEMSV.ITEM_NUMBER ORIGINAL_ITEM_NAME,

nvl( DF.FULFILL_ORG_ID ,org.sr_organization_id ) ORG_LVL_MEMBER_ID,

ORG.SR_ORGANIZATION_CODE ORGANIZATION_CODE,

ship_to_party_id customer_id,

custHZP.party_number CUS_LVL_MEMBER_NAME,

ship_to_party_site_id ship_to_site_id,

custsitesHPS.PARTY_SITE_NUMBER CUS_SITE_LVL_MEMBER_NAME,

decode( DH.SOURCE_DOCUMENT_TYPE_CODE,'ISO', 'Internal','External') ORDER_TYPE_FLAG,

CAST(FROM_TZ(CAST(DH.ORDERED_DATE AS TIMESTAMP), TZ_OFFSET('00:00')) AT TIME ZONE nvl(MPARA.timezone_code,'UTC') AS DATE) BOOKED_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.ORDERED_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.ORDERED_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 custHZP,

HZ_PARTY_SITES custsitesHPS,

msc_apps_instances apps

WHERE 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 sc.salesperson_id = rsp.party_id (+)

AND DF.HEADER_ID = sc.HEADER_ID (+)

and DF.CATEGORY_CODE = 'ORDER'

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

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 custHZP.party_id = DF.ship_to_party_id

AND custsitesHPS.PARTY_SITE_ID = DF.ship_to_party_site_id

AND apps.instance_id = ORG.sr_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)

)