VIEW: APPS.MSC_MATERIAL_FLOW_DETAILS_V

Source

View Text - Preformatted

SELECT msa.segment_start_date ,msa.segment_end_date ,msc_get_name.get_mfd_order_number ( ms.order_type , ms.order_number, ms.transaction_id ,ms.sr_instance_id, ms.plan_id , ms.disposition_id) ,msa.producer_op_seq_id ,msa.producer_op_seq_num ,msa.producer_res_seq_num ,msc_get_name.get_res_and_dept_details (msa.plan_id,msa.producer_sr_instance_id,msa.from_res_transaction_id, 'DC') ,to_number(msc_get_name.get_res_and_dept_details (msa.plan_id,msa.producer_sr_instance_id,msa.from_res_transaction_id ,'TI') ) ,to_number(msc_get_name.get_res_and_dept_details (msa.plan_id,msa.producer_sr_instance_id,msa.from_res_transaction_id ,'RI') ) ,msc_get_name.get_res_and_dept_details (msa.plan_id,msa.producer_sr_instance_id,msa.from_res_transaction_id,'RC') ,msa.segment_quantity ,msa.minimum_transfer_qty ,msa.consumer_start_date ,msa.consumer_end_date ,msc_get_name.get_mfd_order_number (ms1.order_type , ms1.order_number , ms1.transaction_id, ms1.sr_instance_id, ms1.plan_id , ms1.disposition_id ) ,msa.consumer_op_seq_id ,msa.consumer_op_seq_num ,msa.consumer_res_seq_num ,msc_get_name.get_res_and_dept_details (msa.plan_id,msa.consumer_sr_instance_id,msa.to_res_transaction_id, 'DC') ,to_number(msc_get_name.get_res_and_dept_details (msa.plan_id,msa.consumer_sr_instance_id,msa.to_res_transaction_id, 'TI' ) ) ,to_number(msc_get_name.get_res_and_dept_details (msa.plan_id,msa.consumer_sr_instance_id,msa.to_res_transaction_id,'RI') ) ,msc_get_name.get_res_and_dept_details (msa.plan_id,msa.consumer_sr_instance_id,msa.to_res_transaction_id , 'RC') ,msa.segment_quantity ,msc_get_name.lookup_meaning('MSC_DEPENDENCY_TYPE',nvl(msa.dependency_type,1)) ,nvl(msa.dependency_type,1) ,msa.minimum_time_offset ,msa.maximum_time_offset ,msa.actual_time_offset ,msa.producer_trans_id ,msa.consumer_trans_id ,msa.producer_sr_instance_id ,msa.consumer_sr_instance_id ,msa.plan_id ,msc_get_name.org_code(ms.organization_id,ms.sr_instance_id) ,msc_get_name.org_code(ms1.organization_id,ms1.sr_instance_id) ,msc_get_name.item_name(ms.inventory_item_id,NULL,NULL,NULL) ,msc_get_name.item_name(ms1.inventory_item_id,NULL,NULL,NULL) ,msc_get_name.item_desc(ms.inventory_item_id, ms.organization_id,ms.plan_id,ms.sr_instance_id) ,msc_get_name.item_desc(ms1.inventory_item_id,ms1.organization_id,ms1.plan_id,ms1.sr_instance_id) from msc_segment_allocations msa, msc_supplies ms, msc_supplies ms1 WHERE ms.transaction_id = msa.producer_trans_id and ms.plan_id = msa.plan_id and ms.sr_instance_id = msa.producer_sr_instance_id and ms1.transaction_id = msa.consumer_trans_id and ms1.plan_id = msa.plan_id and ms1.sr_instance_id = msa.consumer_sr_instance_id

View Text - HTML Formatted

SELECT MSA.SEGMENT_START_DATE
, MSA.SEGMENT_END_DATE
, MSC_GET_NAME.GET_MFD_ORDER_NUMBER ( MS.ORDER_TYPE
, MS.ORDER_NUMBER
, MS.TRANSACTION_ID
, MS.SR_INSTANCE_ID
, MS.PLAN_ID
, MS.DISPOSITION_ID)
, MSA.PRODUCER_OP_SEQ_ID
, MSA.PRODUCER_OP_SEQ_NUM
, MSA.PRODUCER_RES_SEQ_NUM
, MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.PRODUCER_SR_INSTANCE_ID
, MSA.FROM_RES_TRANSACTION_ID
, 'DC')
, TO_NUMBER(MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.PRODUCER_SR_INSTANCE_ID
, MSA.FROM_RES_TRANSACTION_ID
, 'TI') )
, TO_NUMBER(MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.PRODUCER_SR_INSTANCE_ID
, MSA.FROM_RES_TRANSACTION_ID
, 'RI') )
, MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.PRODUCER_SR_INSTANCE_ID
, MSA.FROM_RES_TRANSACTION_ID
, 'RC')
, MSA.SEGMENT_QUANTITY
, MSA.MINIMUM_TRANSFER_QTY
, MSA.CONSUMER_START_DATE
, MSA.CONSUMER_END_DATE
, MSC_GET_NAME.GET_MFD_ORDER_NUMBER (MS1.ORDER_TYPE
, MS1.ORDER_NUMBER
, MS1.TRANSACTION_ID
, MS1.SR_INSTANCE_ID
, MS1.PLAN_ID
, MS1.DISPOSITION_ID )
, MSA.CONSUMER_OP_SEQ_ID
, MSA.CONSUMER_OP_SEQ_NUM
, MSA.CONSUMER_RES_SEQ_NUM
, MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.CONSUMER_SR_INSTANCE_ID
, MSA.TO_RES_TRANSACTION_ID
, 'DC')
, TO_NUMBER(MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.CONSUMER_SR_INSTANCE_ID
, MSA.TO_RES_TRANSACTION_ID
, 'TI' ) )
, TO_NUMBER(MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.CONSUMER_SR_INSTANCE_ID
, MSA.TO_RES_TRANSACTION_ID
, 'RI') )
, MSC_GET_NAME.GET_RES_AND_DEPT_DETAILS (MSA.PLAN_ID
, MSA.CONSUMER_SR_INSTANCE_ID
, MSA.TO_RES_TRANSACTION_ID
, 'RC')
, MSA.SEGMENT_QUANTITY
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DEPENDENCY_TYPE'
, NVL(MSA.DEPENDENCY_TYPE
, 1))
, NVL(MSA.DEPENDENCY_TYPE
, 1)
, MSA.MINIMUM_TIME_OFFSET
, MSA.MAXIMUM_TIME_OFFSET
, MSA.ACTUAL_TIME_OFFSET
, MSA.PRODUCER_TRANS_ID
, MSA.CONSUMER_TRANS_ID
, MSA.PRODUCER_SR_INSTANCE_ID
, MSA.CONSUMER_SR_INSTANCE_ID
, MSA.PLAN_ID
, MSC_GET_NAME.ORG_CODE(MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID)
, MSC_GET_NAME.ORG_CODE(MS1.ORGANIZATION_ID
, MS1.SR_INSTANCE_ID)
, MSC_GET_NAME.ITEM_NAME(MS.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, MSC_GET_NAME.ITEM_NAME(MS1.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, MSC_GET_NAME.ITEM_DESC(MS.INVENTORY_ITEM_ID
, MS.ORGANIZATION_ID
, MS.PLAN_ID
, MS.SR_INSTANCE_ID)
, MSC_GET_NAME.ITEM_DESC(MS1.INVENTORY_ITEM_ID
, MS1.ORGANIZATION_ID
, MS1.PLAN_ID
, MS1.SR_INSTANCE_ID)
FROM MSC_SEGMENT_ALLOCATIONS MSA
, MSC_SUPPLIES MS
, MSC_SUPPLIES MS1
WHERE MS.TRANSACTION_ID = MSA.PRODUCER_TRANS_ID
AND MS.PLAN_ID = MSA.PLAN_ID
AND MS.SR_INSTANCE_ID = MSA.PRODUCER_SR_INSTANCE_ID
AND MS1.TRANSACTION_ID = MSA.CONSUMER_TRANS_ID
AND MS1.PLAN_ID = MSA.PLAN_ID
AND MS1.SR_INSTANCE_ID = MSA.CONSUMER_SR_INSTANCE_ID