VIEW: APPS.MSC_PO_REQUISITIONS_V

Source

View Text - Preformatted

SELECT mpr.source_line_id, mpr.item_id, msi.ITEM_NAME, mpr.quantity, mpr.need_by_date, mpr.deliver_to_location_id, mtp_deliver_sites.LOCATION LOCATION_CODE, mtp_deliver.partner_name DELIVER_TO_PARTNER_NAME, mpr.deliver_to_requestor_id, nvl(pln.PLANNER_CODE,'-1') REQUESTOR_CODE, mpr.authorization_status, decode(mpr.authorization_status,'APPROVED',1,0) authorization_status_code, mpr.uom_code, mpr.charge_account_id, mpr.item_revision, mpr.destination_organization_id, substr(mtp_dest.organization_code,instr(mtp_dest.organization_code,':') + 1) DEST_ORG_CODE, mtp_deliver.partner_name DEST_PARTNER_NAME, mpr.source_organization_id, decode(mpr.source_organization_id, NULL, NULL,MSC_ST_UTIL.ret_org_code(mpr.SR_INSTANCE_ID ,mpr.source_organization_id)) SOURCE_ORG_CODE, mpr.suggested_vendor_id, MSC_ST_UTIL.ret_partner_name(mpr.SR_INSTANCE_ID, mpr.suggested_vendor_id, 1)PARTNER_NAME, mpr.suggested_vendor_site, null VENDOR_SITE_ADDRESS, mpr.project_id, decode(mpr.PROJECT_ID, NULL, NULL, MSC_ST_UTIL.ret_project_number(mpr.project_id,mpr.SR_INSTANCE_ID, mpr.destination_organization_id,-1)) PROJECT_NUMBER, mpr.task_id, decode(mpr.TASK_ID, NULL, NULL, MSC_ST_UTIL.ret_task_number(mpr.project_id, mpr.task_id, mpr.SR_INSTANCE_ID, mpr.destination_organization_id, -1)) TASK_NUMBER, mpr.SR_INSTANCE_ID, mai.INSTANCE_CODE FROM MSC_PO_REQUISITIONS_INTERFACE mpr, MSC_APPS_INSTANCES mai, MSC_TRADING_PARTNERS mtp_dest, MSC_TRADING_PARTNERS mtp_deliver, MSC_SYSTEM_ITEMS msi, MSC_TRADING_PARTNER_SITES mtp_deliver_sites, MSC_PLANNERS pln WHERE mtp_dest.sr_tp_id = mpr.destination_organization_id and mtp_dest.partner_type = 3 and mtp_dest.sr_instance_id = mpr.sr_instance_id and mtp_deliver_sites.sr_tp_site_id(+) = mpr.deliver_to_location_id and mtp_deliver_sites.sr_instance_id(+) = mpr.sr_instance_id and mtp_deliver.partner_id(+) = mtp_deliver_sites.partner_id and msi.sr_inventory_item_id = mpr.item_id and msi.plan_id = -1 and msi.organization_id = mpr.destination_organization_id and msi.sr_instance_id = mpr.sr_instance_id and pln.employee_id(+)=mpr.deliver_to_requestor_id and pln.sr_instance_id(+)=mpr.sr_instance_id and pln.organization_id(+)=mpr.destination_organization_id and mai.instance_id = mpr.sr_instance_id

View Text - HTML Formatted

SELECT MPR.SOURCE_LINE_ID
, MPR.ITEM_ID
, MSI.ITEM_NAME
, MPR.QUANTITY
, MPR.NEED_BY_DATE
, MPR.DELIVER_TO_LOCATION_ID
, MTP_DELIVER_SITES.LOCATION LOCATION_CODE
, MTP_DELIVER.PARTNER_NAME DELIVER_TO_PARTNER_NAME
, MPR.DELIVER_TO_REQUESTOR_ID
, NVL(PLN.PLANNER_CODE
, '-1') REQUESTOR_CODE
, MPR.AUTHORIZATION_STATUS
, DECODE(MPR.AUTHORIZATION_STATUS
, 'APPROVED'
, 1
, 0) AUTHORIZATION_STATUS_CODE
, MPR.UOM_CODE
, MPR.CHARGE_ACCOUNT_ID
, MPR.ITEM_REVISION
, MPR.DESTINATION_ORGANIZATION_ID
, SUBSTR(MTP_DEST.ORGANIZATION_CODE
, INSTR(MTP_DEST.ORGANIZATION_CODE
, ':') + 1) DEST_ORG_CODE
, MTP_DELIVER.PARTNER_NAME DEST_PARTNER_NAME
, MPR.SOURCE_ORGANIZATION_ID
, DECODE(MPR.SOURCE_ORGANIZATION_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_ORG_CODE(MPR.SR_INSTANCE_ID
, MPR.SOURCE_ORGANIZATION_ID)) SOURCE_ORG_CODE
, MPR.SUGGESTED_VENDOR_ID
, MSC_ST_UTIL.RET_PARTNER_NAME(MPR.SR_INSTANCE_ID
, MPR.SUGGESTED_VENDOR_ID
, 1)PARTNER_NAME
, MPR.SUGGESTED_VENDOR_SITE
, NULL VENDOR_SITE_ADDRESS
, MPR.PROJECT_ID
, DECODE(MPR.PROJECT_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_PROJECT_NUMBER(MPR.PROJECT_ID
, MPR.SR_INSTANCE_ID
, MPR.DESTINATION_ORGANIZATION_ID
, -1)) PROJECT_NUMBER
, MPR.TASK_ID
, DECODE(MPR.TASK_ID
, NULL
, NULL
, MSC_ST_UTIL.RET_TASK_NUMBER(MPR.PROJECT_ID
, MPR.TASK_ID
, MPR.SR_INSTANCE_ID
, MPR.DESTINATION_ORGANIZATION_ID
, -1)) TASK_NUMBER
, MPR.SR_INSTANCE_ID
, MAI.INSTANCE_CODE
FROM MSC_PO_REQUISITIONS_INTERFACE MPR
, MSC_APPS_INSTANCES MAI
, MSC_TRADING_PARTNERS MTP_DEST
, MSC_TRADING_PARTNERS MTP_DELIVER
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNER_SITES MTP_DELIVER_SITES
, MSC_PLANNERS PLN
WHERE MTP_DEST.SR_TP_ID = MPR.DESTINATION_ORGANIZATION_ID
AND MTP_DEST.PARTNER_TYPE = 3
AND MTP_DEST.SR_INSTANCE_ID = MPR.SR_INSTANCE_ID
AND MTP_DELIVER_SITES.SR_TP_SITE_ID(+) = MPR.DELIVER_TO_LOCATION_ID
AND MTP_DELIVER_SITES.SR_INSTANCE_ID(+) = MPR.SR_INSTANCE_ID
AND MTP_DELIVER.PARTNER_ID(+) = MTP_DELIVER_SITES.PARTNER_ID
AND MSI.SR_INVENTORY_ITEM_ID = MPR.ITEM_ID
AND MSI.PLAN_ID = -1
AND MSI.ORGANIZATION_ID = MPR.DESTINATION_ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = MPR.SR_INSTANCE_ID
AND PLN.EMPLOYEE_ID(+)=MPR.DELIVER_TO_REQUESTOR_ID
AND PLN.SR_INSTANCE_ID(+)=MPR.SR_INSTANCE_ID
AND PLN.ORGANIZATION_ID(+)=MPR.DESTINATION_ORGANIZATION_ID
AND MAI.INSTANCE_ID = MPR.SR_INSTANCE_ID