VIEW: APPS.CST_BIS_MARGIN_ANALYSIS_V

Source

View Text - Preformatted

SELECT NVL(MCV.concatenated_segments, 'UNASSIGNED') , MSIV.concatenated_segments , MSI.primary_uom_code , CMB.legal_entity_name , NVL(temp.OPERATING_UNIT_NAME, 'UNASSIGNED') , temp.order_number , temp.parent_line_number , temp.gl_date , NVL(SL.meaning, 'UNASSIGNED') , NVL(hp.party_name, 'UNASSIGNED') , NVL(RSA.name, 'UNASSIGNED') , NVL(AL.meaning, 'UNASSIGNED') , NVL(RT.name, 'UNASSIGNED') , NVL(temp.AREA_NAME, 'UNASSIGNED') , NVL(temp.COUNTRY_NAME, 'UNASSIGNED') , NVL(temp.REGION_NAME, 'UNASSIGNED') , NVL(temp.PERIOD_NAME_YEAR, 'UNASSIGNED') , NVL(temp.PERIOD_NAME_QTR, 'UNASSIGNED') , NVL(temp.PERIOD_NAME_MONTH, 'UNASSIGNED') , temp.PERIOD_NUM_YEAR , temp.PERIOD_NUM_QTR , temp.PERIOD_NUM_MONTH , decode( temp.PERIOD_NUM_QTR, NULL,'UNASSIGNED', 'Quarter ' || temp.PERIOD_NUM_QTR) , decode(temp.PERIOD_NUM_MONTH, 'UNASSIGNED', 'Month ' || temp.PERIOD_NUM_MONTH) , temp.PERIOD_SEQ_QTR , temp.PERIOD_SEQ_MONTH , NVL(temp.invoice_quantity,0) , NVL(temp.ship_quantity,0) , NVL(temp.invoiced_amount,0) , NVL(temp.cogs_amount,0) FROM mtl_categories_kfv MCV , mtl_parameters MP , mtl_system_items MSI , mtl_system_items_kfv MSIV , so_lookups SL , ra_territories RT , ar_lookups AL , ra_salesreps_all RSA, hz_cust_accounts HCA , hz_parties HP, cst_bis_margin_build CMB , cst_bis_margin_summary TEMP WHERE temp.parent_organization_id = MP.organization_id AND MSI.inventory_item_id = temp.parent_inventory_item_id AND MSI.organization_id = MP.master_organization_id AND SL.lookup_type (+) = 'SALES_CHANNEL' AND SL.lookup_code (+) = temp.sales_channel_code AND RT.territory_id (+) = temp.territory_id AND AL.lookup_type (+) = 'CUSTOMER CLASS' AND AL.lookup_code (+) = temp.customer_class_code AND RSA.salesrep_id (+) = temp.primary_salesrep_id AND RSA.org_id (+) = temp.org_id AND HCA.party_id = HP.party_id(+) AND HCA.cust_account_id(+) = temp.customer_id AND CMB.legal_entity_id = temp.legal_entity_id AND MSI.inventory_item_id = MSIV.inventory_item_id AND MSI.organization_id = MSIV.organization_id AND temp.OE_ITEM_CATEGORY_ID= MCV.category_id (+)

View Text - HTML Formatted

SELECT NVL(MCV.CONCATENATED_SEGMENTS
, 'UNASSIGNED')
, MSIV.CONCATENATED_SEGMENTS
, MSI.PRIMARY_UOM_CODE
, CMB.LEGAL_ENTITY_NAME
, NVL(TEMP.OPERATING_UNIT_NAME
, 'UNASSIGNED')
, TEMP.ORDER_NUMBER
, TEMP.PARENT_LINE_NUMBER
, TEMP.GL_DATE
, NVL(SL.MEANING
, 'UNASSIGNED')
, NVL(HP.PARTY_NAME
, 'UNASSIGNED')
, NVL(RSA.NAME
, 'UNASSIGNED')
, NVL(AL.MEANING
, 'UNASSIGNED')
, NVL(RT.NAME
, 'UNASSIGNED')
, NVL(TEMP.AREA_NAME
, 'UNASSIGNED')
, NVL(TEMP.COUNTRY_NAME
, 'UNASSIGNED')
, NVL(TEMP.REGION_NAME
, 'UNASSIGNED')
, NVL(TEMP.PERIOD_NAME_YEAR
, 'UNASSIGNED')
, NVL(TEMP.PERIOD_NAME_QTR
, 'UNASSIGNED')
, NVL(TEMP.PERIOD_NAME_MONTH
, 'UNASSIGNED')
, TEMP.PERIOD_NUM_YEAR
, TEMP.PERIOD_NUM_QTR
, TEMP.PERIOD_NUM_MONTH
, DECODE( TEMP.PERIOD_NUM_QTR
, NULL
, 'UNASSIGNED'
, 'QUARTER ' || TEMP.PERIOD_NUM_QTR)
, DECODE(TEMP.PERIOD_NUM_MONTH
, 'UNASSIGNED'
, 'MONTH ' || TEMP.PERIOD_NUM_MONTH)
, TEMP.PERIOD_SEQ_QTR
, TEMP.PERIOD_SEQ_MONTH
, NVL(TEMP.INVOICE_QUANTITY
, 0)
, NVL(TEMP.SHIP_QUANTITY
, 0)
, NVL(TEMP.INVOICED_AMOUNT
, 0)
, NVL(TEMP.COGS_AMOUNT
, 0)
FROM MTL_CATEGORIES_KFV MCV
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_KFV MSIV
, SO_LOOKUPS SL
, RA_TERRITORIES RT
, AR_LOOKUPS AL
, RA_SALESREPS_ALL RSA
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, CST_BIS_MARGIN_BUILD CMB
, CST_BIS_MARGIN_SUMMARY TEMP
WHERE TEMP.PARENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = TEMP.PARENT_INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND SL.LOOKUP_TYPE (+) = 'SALES_CHANNEL'
AND SL.LOOKUP_CODE (+) = TEMP.SALES_CHANNEL_CODE
AND RT.TERRITORY_ID (+) = TEMP.TERRITORY_ID
AND AL.LOOKUP_TYPE (+) = 'CUSTOMER CLASS'
AND AL.LOOKUP_CODE (+) = TEMP.CUSTOMER_CLASS_CODE
AND RSA.SALESREP_ID (+) = TEMP.PRIMARY_SALESREP_ID
AND RSA.ORG_ID (+) = TEMP.ORG_ID
AND HCA.PARTY_ID = HP.PARTY_ID(+)
AND HCA.CUST_ACCOUNT_ID(+) = TEMP.CUSTOMER_ID
AND CMB.LEGAL_ENTITY_ID = TEMP.LEGAL_ENTITY_ID
AND MSI.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND TEMP.OE_ITEM_CATEGORY_ID= MCV.CATEGORY_ID (+)