OBJECT_VERSION_IDNUMBER(15,0)Required
Sequences NAME INCREMENT BY CYCLE CACHE ORDER MIN VALUE MAX VALUE IM_AP_STAGE_DETAIL_ERROR_SEQ 1 Y 5000 N 1000 9999999999 IM_AP_STAGE_DETAIL_SEQ 1 Y 5000 Y 1000 9999999999 IM_AP_STAGE_HEAD_ERROR_SEQ 1 Y 5000 N 1000 9999999999 IM_AP_STAGE_HEAD_SEQ 1 Y 5000 N 1000 9999999999 IM_AP_STG_DTL_LINE_GROUP_SEQ 1 Y 20 Y 1000 9999999999 IM_BATCH_PROCESS_SEQ 1 Y 20 N 1 999999999999999 IM_BDI_AP_STG_DETL_OUT_SEQ 1 N 10000 N 1 9999999999 IM_BDI_AP_STG_HEAD_OUT_SEQ 1 N 10000 N 1 9999999999 IM_BDI_FIN_STAGE_OUT_SEQ 1 N 10000 N 1 9999999999 IM_CN_DETAIL_MATCH_HIS_SEQ 1 N 5000 N 1 9999999999 IM_CN_MANUAL_GROUPS_SEQ 1 N 5000 N 1 9999999999 IM_CN_SUMMARY_MATCH_HIS_SEQ 1 N 5000 N 1 9999999999 IM_DETAIL_MATCH_CN_HIST_SEQ 1 N 5000 N 1 9999999999 IM_DETAIL_MATCH_CN_WS_SEQ 1 N 5000 N 1 9999999999 IM_DETAIL_MATCH_HISTORY_SEQ 1 N 5000 N 1 9999999999 IM_DETAIL_MATCH_WS_SEQ 1 Y 100 N 1 9999999999 IM_DISCREPANCY_LIST_RCPT_SEQ 1 N 5000 N 1 9999999999 IM_DISCREPANCY_LIST_WS_SEQ 1 N 5000 N 1 9999999999 NAME INCREMENT BY CYCLE CACHE ORDER MIN VALUE MAX VALUE IM_DOC_BDI_TRANSMISSION_NO_SEQ 1 Y 0 N 1 9999999999 IM_DOC_DETAIL_COMMENTS_SEQ 1 N 5000 N 1 9999999999 IM_DOC_DETAIL_REASON_CODES_SEQ 1 N 5000 N 1 99999999999999999999 IM_DOC_HEAD_SEQ 1 N 5000 N 1 9999999999 IM_DOC_MATCH_ATTRIB_SEQ 1 Y 5000 N 1 999999999999999 IM_DOC_SEQ_GROUP_ID_SEQ 1 Y 10 N 1 9999999999 IM_DOC_SEQ_HEAD_SEQ 1 Y 10 N 1 9999999999 IM_DOC_SEQ_TRAN_SEQ 1 Y 10 N 1 9999999999 IM_DYN_SEG_CLASS_SEQ 1 Y 100 N 1 9999999999 IM_DYN_SEG_DEPT_SEQ 1 Y 100 N 1 9999999999 IM_DYN_SEG_LOC_SEQ 1 Y 100 N 1 9999999999 IM_EDOC_SEQ 1 N 5000 N 1 9999999999 IM_FDG_DOC_DTL_ID_SEQ 1 Y 5000 N 1 99999999999999999999 IM_FDG_DOC_DTL_PACK_COMP_ID_SEQ 1 Y 5000 N 1 99999999999999999999 IM_FDG_DOC_ETT_ID_SEQ 1 Y 1000 N 1 99999999999999999999 IM_FDG_DOC_EXT_ID_SEQ 1 Y 5000 N 1 99999999999999999999 IM_FDG_DOC_HEAD_ID_SEQ 1 Y 1000 N 1 99999999999999999999 IM_FDG_DOC_NON_MERCH_ID_SEQ 1 Y 1000 N 1 99999999999999999999 IM_FDG_DOC_REF_ID_SEQ 1 Y 1000 N 1 99999999999999999999 NAME INCREMENT BY CYCLE CACHE ORDER MIN VALUE MAX VALUE IM_FDG_DOC_TAX_ID_SEQ 1 Y 5000 N 1 99999999999999999999 IM_FDG_DOC_TEXT_ID_SEQ 1 Y 1000 N 1 99999999999999999999 IM_FDG_PROCESS_ID_SEQ 1 Y 10 N 1 99999999999999999999 IM_FINANCIALS_STAGE_ERROR_SEQ 1000 N 5000 N 1 9999999999 IM_FINANCIALS_STAGE_SEQ 1 Y 5000 Y 1000 9999999999 IM_GL_CROSS_REF_SEQ 1 Y 100 N 1 9999999999 IM_GROUP_ID_SEQ 1 Y 100 N 1 9999999999 IM_INJECT_DOC_ALLOWANCE_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_ALLW_TAX_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_ATTACHMENT_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_DETAIL_SEQ 1 Y 50000 N 1 99999999999999 IM_INJECT_DOC_DETAIL_TAX_SEQ 1 Y 50000 N 1 999999999999999 IM_INJECT_DOC_DTL_ALLW_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_DTL_ALLW_TAX_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_ERROR_ID_SEQ 1 Y 5000 N 1 9999999999999999999999999999 IM_INJECT_DOC_MATCH_ATTRIB_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_NONMERCH_TAX_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_NON_MERCH_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_DOC_SEQ 1 Y 5000 N 1 99999999999999 NAME INCREMENT BY CYCLE CACHE ORDER MIN VALUE MAX VALUE IM_INJECT_DOC_TAX_SEQ 1 Y 5000 N 1 999999999999999 IM_INJECT_SEQ 1 Y 5000 N 1 99999999999999 IM_INJECT_TAX_DISCREPANCY_SEQ 1 Y 20 N 1 999999999999999 IM_INJ_DOC_NON_CRIT_ER_ID_SEQ 1 Y 5000 N 1 9999999999999999999999999999 IM_INVC_ITEM_VIEW_RCPT_SEQ 1 Y 1000 N 1 9999999999 IM_INVC_ITEM_VIEW_WS_ID_SEQ 1 Y 1000 N 1 9999999999 IM_INVOICE_DTL_ALLW_SEQ 1 Y 5000 N 1 999999999999999 IM_MANUAL_GROUPS_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_GROUP_CN_CNR_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_GROUP_CN_CN_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_GROUP_CN_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_GROUP_SEQ 1 Y 5000 N 1 9999999999 IM_MATCH_INQ_DETAIL_WS_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_INVC_GROUP_SEQ 1 Y 5000 N 1 9999999999 IM_MATCH_KEY_ID_SEQ 1 Y 100 N 1 9999999999 IM_MATCH_POOL_RESULT_ID_SEQ 1 N 5000 N 1 99999999999 IM_MATCH_RCPT_GROUP_SEQ 1 Y 5000 N 1 9999999999 IM_MATCH_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_STGY_DETAIL_SEQ 1 N 5000 N 1 9999999999 NAME INCREMENT BY CYCLE CACHE ORDER MIN VALUE MAX VALUE IM_MATCH_STGY_DTL_SEQ 1 Y 100 N 1 9999999999 IM_MATCH_STGY_HEAD_SEQ 1 N 5000 N 1 9999999999 IM_MATCH_STGY_SEQ 1 Y 100 N 1 9999999999 IM_PAYLOAD_ID_SEQ 1 Y 100 N 1 9999999999 IM_POSTING_DOC_AMOUNTS_SEQ 1 N 5000 N 0 999999999999999 IM_POSTING_DOC_ERRORS_ID_SEQ 1 Y 5000 N 1 9999999999999999999999999999 IM_POSTING_DOC_SEQ 1 Y 5000 N 1 9999999999 IM_PROCESS_SEQ 1 Y 100 N 1 9999999999 IM_PRORATE_WORKSPACE_SEQ 1 Y 5000 N 1 9999999999 IM_RCPT_ITEM_POST_INV_STG_SEQ 1 Y 20 N 1 999999999999999 IM_REASON_TRAN_CODE_MAP_SEQ 1 Y 100 N 1 9999999999 IM_RECEIPT_ITEM_POSTING_SEQ 1 N 10000 N 1 9999999999 IM_SEC_GRP_RC_SEQ 1 Y 100 N 1 9999999999 IM_SUMMARY_MATCH_HISTORY_SEQ 1 N 5000 N 1 9999999999 IM_SUM_MATCH_CN_HIST_SEQ 1 N 5000 N 1 9999999999 IM_SUPPLIER_GROUPS_SEQ 1 N 5000 N 1 9999999999 IM_TASK_ID_SEQ 1 Y 5000 N 1 9999999999 IM_TAX_DISCREPANCY_SEQ 1 Y 20 N 1 999999999999999 IM_TAX_DISC_HISTORY_SEQ 1 Y 20 N 1 999999999999999 NAME INCREMENT BY CYCLE CACHE ORDER MIN VALUE MAX VALUE IM_TOLERANCE_DETAIL_SEQ 1 Y 5000 N 1 9999999999 IM_TOLERANCE_HEAD_SEQ 1 Y 5000 N 1 9999999999 IM_TOLERANCE_LEVEL_MAP_SEQ 1 Y 5000 N 1 9999999999 IM_TRAN_CODE_SEQ 1 Y 100 N 1 9999999999 IM_TRAN_ID_SEQ 1 Y 100 N 1 9999999999 IM_TRIAL_ID_SEQ 1 Y 10 N 1 9999999999 IM_UNMTCH_ID_SEQ 1 Y 100 N 1 9999999999 IM_VALID_ACCOUNTS_ID_SEQ 1 Y 5000 N 1 9999999999999999999999999999 IM_WORKSPACE_ID_SEQ 1 Y 10 N 1 9999999999 Views VIEW NAME VIEW TEXT IM_11 SELECT "ITEM","ITEM_MASTER_L90099" FROM (SELECT ITEM,VARCHAR2_1 ITEM_MASTER_L90099 from ITEM_MASTER_CFA_EXT where group_id =9999) V_IM_FINANCIALS_STAGE SELECT IM.SEQ_NO, IM.TRAN_CODE, IM.DEBIT_CREDIT_IND, IM.DOC_ID, IM.PARENT_ID, NVL(IM.DOC_DATE,SYSDATE) DOC_DATE, IM.RECEIPT_ID, IM.RECEIPT_DATE, IM.VENDOR_TYPE, IM.VENDOR, IM.ORDER_NO, IM.CURRENCY_CODE, IM.AMOUNT, IM.VAT_CODE, IM.VAT_RATE, IM.BEST_TERMS, IM.BEST_TERMS_DATE, IM.MANUALLY_PAID_IND, IM.PRE_PAID_IND, IM.CREATE_ID, IM.CREATE_DATETIME , IM.SEGMENT_1, IM.SEGMENT_2, IM.SEGMENT_3, IM.SEGMENT_4, IM.SEGMENT_5, IM.SEGMENT_6, IM.SEGMENT_7, IM.SEGMENT_8, IM.SEGMENT_9, IM.SEGMENT_10, IM.DEAL_ID, IM.LOCAL_CURRENCY, IM.INCOME_LOCAL_CURRENCY, IM.EXT_DOC_ID, FIF.SET_OF_BOOKS_ID, 'Retail Invoices' USER_JE_SOURCE_NAME, DECODE(NVL(IM.PRE_PAID_IND, 'N'), 'Y', 'Prepayments', DECODE(NVL(IM.MANUALLY_PAID_IND, 'N'), 'Y','Manual Payments', 'Writeoffs')) USER_JE_CATEGORY_NAME, DECODE(DEBIT_CREDIT_IND, 'DEBIT', AMOUNT, NULL) ENTERED_DR , DECODE(DEBIT_CREDIT_IND, 'CREDIT', AMOUNT, NULL) ENTERED_CR, 'A' ACTUAL_FLAG, 'NEW' STATUS, IM.EXCHANGE_RATE, IM.EXCHANGE_RATE_TYPE FROM IM_FINANCIALS_STAGE IM, FIF_GL_SETUP FIF WHERE IM.SET_OF_BOOKS_ID = FIF.SET_OF_BOOKS_ID V_IM_GL_CROSS_REF WITH GL_CROSS_REF AS (SELECT SET_OF_BOOKS_ID, ACCOUNT_TYPE, ACCOUNT_CODE, TAX_CODE, SEGMENT_NO, SEGMENT_VALUE FROM IM_GL_CROSS_REF UNPIVOT INCLUDE NULLS (SEGMENT_VALUE FOR SEGMENT_NO IN (SEGMENT1 AS 1, SEGMENT2 AS 2, SEGMENT3 AS 3, SEGMENT4 AS 4, SEGMENT5 AS 5, SEGMENT6 AS 6, SEGMENT7 AS 7, SEGMENT8 AS 8, SEGMENT9 AS 9, SEGMENT10 AS 10, SEGMENT11 AS 11, SEGMENT12 AS 12, SEGMENT13 AS 13, SEGMENT14 AS 14, SEGMENT15 AS 15, SEGMENT16 AS 16, SEGMENT17 AS 17, SEGMENT18 AS 18, SEGMENT19 AS 19, SEGMENT20 AS 20))), GL_OPTIONS AS (SELECT SET_OF_BOOKS_ID, MAX(SEGMENT_NO) MAX_SEGMENT_NO FROM IM_GL_OPTIONS GROUP BY SET_OF_BOOKS_ID) SELECT GCR.SET_OF_BOOKS_ID, GCR.ACCOUNT_TYPE, GCR.ACCOUNT_CODE, GCR.TAX_CODE, GCR.SEGMENT_NO, GCR.SEGMENT_VALUE FROM GL_CROSS_REF GCR, GL_OPTIONS GO WHERE GCR.SET_OF_BOOKS_ID = GO.SET_OF_BOOKS_ID AND GCR.SEGMENT_NO <= GO.MAX_SEGMENT_NO V_IM_ITEM_LOV_VIEW SELECT IM.ITEM_DESC ITEM_DESC, IM.ITEM ITEM, ITS.VPN VPN_DESC , SP.SUPPLIER_PARENT SUPPLIER FROM ITEM_SUPPLIER ITS, ITEM_MASTER IM , SUPS SP WHERE ( ITS.SUPPLIER = VIEW NAME VIEW TEXT SP.SUPPLIER OR ( EXISTS (SELECT 'X' FROM IM_SUPPLIER_GROUP_MEMBERS L1, IM_SUPPLIER_GROUP_MEMBERS L2 WHERE ITS.SUPPLIER = L2.SUPPLIER AND L1.GROUP_ID = L2.GROUP_ID ) AND EXISTS (SELECT 'X' FROM IM_SUPPLIER_GROUP_MEMBERS L1, IM_SUPPLIER_GROUP_MEMBERS L2, IM_SUPPLIER_OPTIONS OPT , SUPS SS WHERE ITS.SUPPLIER = SS.SUPPLIER AND SS.SUPPLIER_PARENT = L2.SUPPLIER AND L1.GROUP_ID = L2.GROUP_ID AND OPT.SUPPLIER =L1.SUPPLIER ) ) ) AND ITS.ITEM = IM.ITEM AND IM.TRAN_LEVEL = IM.ITEM_LEVEL AND IM.STATUS = 'A' V_IM_ITEM_SUPP_COUNTRY_LOC SELECT iscl.unit_cost / NVL (pi.pack_qty, 1) AS unit_cost, iscl.item, iscl.supplier, iscl.origin_country_id, iscl.loc FROM item_supp_country_loc iscl, item_master im, packitem pi WHERE im.item = iscl.item AND iscl.item = pi.pack_no AND im.catch_weight_type = '2' UNION ALL SELECT (iscl.unit_cost / NVL (iscd.net_weight, 1) * isc.supp_pack_size ) unit_cost, iscl.item, iscl.supplier, iscl.origin_country_id, iscl.loc FROM item_supp_country_loc iscl, item_supp_country_dim iscd, item_supp_country isc, item_master im WHERE im.item = iscl.item AND iscl.item = iscd.item AND iscl.supplier = iscd.supplier AND iscl.origin_country_id = iscd.origin_country AND iscd.dim_object = 'CA' AND iscl.item = isc.item AND iscl.supplier = isc.supplier AND iscl.origin_country_id = isc.origin_country_id AND im.catch_weight_type = '4' UNION ALL SELECT iscl.unit_cost, iscl.item, iscl.supplier, iscl.origin_country_id, iscl.loc FROM item_supp_country_loc iscl, item_master im, item_supp_country isc, sups s WHERE im.item = iscl.item AND ( im.catch_weight_type = '3' OR im.catch_weight_type = '1' OR im.catch_weight_type IS NULL ) AND iscl.item = isc.item AND iscl.supplier = isc.supplier AND iscl.origin_country_id = isc.origin_country_id AND isc.supplier = s.supplier AND s.sup_qty_level <> 'CA' UNION ALL SELECT ((iscl.unit_cost) * isc.supp_pack_size) unit_cost, iscl.item, iscl.supplier, iscl.origin_country_id, iscl.loc FROM item_supp_country_loc iscl, item_master im, item_supp_country isc, sups s WHERE im.item = iscl.item AND ( im.catch_weight_type = '3' OR im.catch_weight_type = '1' OR im.catch_weight_type IS NULL ) AND iscl.item = isc.item AND iscl.supplier = isc.supplier AND iscl.origin_country_id = isc.origin_country_id AND isc.supplier = s.supplier AND s.sup_qty_level = 'CA' V_IM_LOCATION_LOV_VIEW select s_tl.store_name location_name, s.store location, 'Store' location_type, s.currency_code from v_store s, v_store_tl s_tl where s.store = s_tl.store union select w_tl.wh_name location_name, w.wh location, 'Warehouse' location_type, w.currency_code from v_wh w, v_wh_tl w_tl where w.wh = w.physical_wh and w.wh = w_tl.wh V_IM_ORDER_LOCATION select distinct oh.order_no, case when oh.import_id is not null and oh.import_type is not null then oh.import_id else ol.location end location, case when oh.import_id is not null and oh.import_type is not null and exists (select 'x' from store st where st.store = oh.import_id) then 'S' when oh.import_id is not null and oh.import_type is not null and exists (select 'x' from wh wh VIEW NAME VIEW TEXT where wh.wh = oh.import_id) then 'W' else ol.loc_type end loc_type from ordhead oh, ordloc ol where oh.status IN ('A', 'C') and oh.order_no = ol.order_no V_IM_ORDLOC select unique oh.order_no, ol.item, case when oh.import_id is not null and oh.import_type is not null then oh.import_id else ol.location end location, case when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from store st where st.store = oh.import_id) then 'S' when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from wh wh where wh.wh = oh.import_id) then 'W' else ol.loc_type end loc_type, (ol.qty_ordered * pi.pack_qty) qty_ordered, (ol.unit_cost / nvl(pi.pack_qty, 1)) unit_cost, (ol.unit_cost_init / nvl(pi.pack_qty, 1)) unit_cost_init, ol.cost_source cost_source from ordloc ol, item_master im, packitem pi, ordhead oh, ordsku os, sups s where im.item = ol.item and ol.item = pi.pack_no and oh.status in ('A', 'C') and oh.order_no = ol.order_no and os.order_no = ol.order_no and os.item = ol.item and oh.supplier = s.supplier and im.catch_weight_type = '2' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and s.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and s.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) = 0))) union all select oh.order_no, ol.item, case when oh.import_id is not null and oh.import_type is not null then oh.import_id else ol.location end location, case when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from store st where st.store = oh.import_id) then 'S' when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from wh wh where wh.wh = oh.import_id) then 'W' else ol.loc_type end loc_type, (ol.qty_ordered * iscd.net_weight / nvl(isc.supp_pack_size, 1) ) qty_ordered, (ol.unit_cost / nvl(iscd.net_weight, 1) * isc.supp_pack_size ) unit_cost, (ol.unit_cost_init / nvl(iscd.net_weight, 1) * isc.supp_pack_size ) unit_cost_init, ol.cost_source cost_source from ordloc ol, item_supp_country_dim iscd, item_supp_country isc, ordhead oh, ordsku os, sups s, item_master im where im.item = ol.item and im.item = iscd.item and im.item = isc.item and ol.order_no = oh.order_no and oh.status in ('A', 'C') and oh.supplier = isc.supplier and os.item = isc.item and os.origin_country_id = isc.origin_country_id and os.order_no = oh.order_no and oh.supplier = s.supplier VIEW NAME VIEW TEXT and iscd.supplier = oh.supplier and iscd.origin_country = os.origin_country_id and iscd.dim_object = 'CA' and im.catch_weight_type = '4' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and s.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and s.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) = 0))) union all select oh.order_no, ol.item, case when oh.import_id is not null and oh.import_type is not null then oh.import_id else ol.location end location, case when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from store st where st.store = oh.import_id) then 'S' when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from wh wh where wh.wh = oh.import_id) then 'W' else ol.loc_type end loc_type, ol.qty_ordered qty_ordered, ol.unit_cost unit_cost, ol.unit_cost_init unit_cost_init, ol.cost_source cost_source from ordloc ol, item_master im, ordhead oh, ordsku os, sups s where im.item = ol.item and ( im.catch_weight_type = '3' or im.catch_weight_type = '1' or im.catch_weight_type is null) and ol.order_no = oh.order_no and oh.status in ('A', 'C') and os.item = ol.item and os.order_no = oh.order_no and oh.supplier = s.supplier and s.sup_qty_level <> 'CA' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and s.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and s.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) = 0))) union all select oh.order_no, ol.item, case when oh.import_id is not null and oh.import_type is not null then oh.import_id else ol.location end location, case when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from store st where st.store = oh.import_id) then 'S' when oh.import_id is not null and oh.import_type is not null and exists (select 'X' from wh wh where wh.wh = oh.import_id) then 'W' else ol.loc_type end loc_type, (ol.qty_ordered) / os.supp_pack_size qty_ordered, (ol.unit_cost) * os.supp_pack_size unit_cost, VIEW NAME VIEW TEXT (ol.unit_cost_init) * os.supp_pack_size unit_cost_init, ol.cost_source cost_source from ordloc ol, item_master im, ordhead oh, ordsku os, sups s where im.item = ol.item and ( im.catch_weight_type = '3' or im.catch_weight_type = '1' or im.catch_weight_type is null ) and ol.order_no = oh.order_no and oh.status in ('A', 'C') and oh.order_no = os.order_no and ol.item = os.item and oh.supplier = s.supplier and sup_qty_level = 'CA' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and s.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and s.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = ol.location and nvl(v_iscl.purchase_type, 0) = 0))) V_IM_ORDLOC_STORES_PHYS_WH SELECT ol.order_no ORDER_NO, physical_wh LOCATION, 'W' LOC_TYPE, ol.item ITEM, SUM(ol.qty_ordered) TOTAL_QTY_ORDERED, ol.unit_cost UNIT_COST, COUNT(ol.location) COUNTER FROM V_IM_ordloc ol, wh w WHERE ol.location = w.wh GROUP BY order_no, physical_wh, item, unit_cost UNION SELECT ol.order_no ORDER_NO, STORE LOCATION, 'S' LOC_TYPE, ol.item ITEM, ol.qty_ordered TOTAL_QTY_ORDERED, ol.unit_cost UNIT_COST, COUNT(ol.location) COUNTER FROM V_im_ordloc ol, STORE s WHERE ol.location = s.STORE GROUP BY order_no, STORE, item, qty_ordered, unit_cost V_IM_SHIPSKU with locs as (select store p_loc, store loc, 'S' loc_type from store union all select physical_wh p_loc, wh loc, 'W' loc_type from wh where wh <> physical_wh) select /*+ INDEX(os, PK_ORDSKU) INDEX(oh, PK_ORDHEAD) */ ss.shipment, ss.seq_no, ss.item, ss.status_code, ss.weight_received qty_received, (ss.unit_cost / nvl(pi.pack_qty, 1)) unit_cost, ss.qty_matched, ss.weight_received_uom weight_received_uom, ss.carton, case when nvl(ss.qty_matched,0) = 0 then 'U' when ss.qty_matched = ss.qty_received then 'M' else 'P' end as invc_match_status from shipsku ss, item_supp_country isc, shipment s, ordhead oh, ordsku os, packitem pi, item_master im, sups where im.item = ss.item and ss.item = pi.pack_no and im.catch_weight_type = '2' and isc.item = ss.item and s.shipment = ss.shipment and s.order_no = oh.order_no and oh.supplier = isc.supplier and oh.supplier = sups.supplier and oh.order_no = os.order_no and os.item = ss.item and os.origin_country_id = isc.origin_country_id and ss.weight_received is NOT NULL and ( ss.weight_received_uom is NULL or ss.weight_received_uom = isc.cost_uom) and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = VIEW NAME VIEW TEXT 'N' and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) = 0))) union all select /*+ INDEX(os, PK_ORDSKU) INDEX(oh, PK_ORDHEAD) */ ss.shipment, ss.seq_no, ss.item, ss.status_code, case when convert.operator = 'M' then nvl(ss.weight_received, 0) * convert.factor else nvl(ss.weight_received, 0) / convert.factor end as qty_received, (ss.unit_cost / nvl(pi.pack_qty, 1)) unit_cost, ss.qty_matched, ss.weight_received_uom, ss.carton, case when nvl(ss.qty_matched,0) = 0 then 'U' when ss.qty_matched = ss.qty_received then 'M' else 'P' end as invc_match_status from shipsku ss, item_supp_country isc, shipment s, ordhead oh, ordsku os, uom_conversion convert, packitem pi, item_master im, sups where im.item = ss.item and ss.item = pi.pack_no and im.catch_weight_type = '2' and isc.item = ss.item and s.shipment = ss.shipment and s.order_no = oh.order_no and oh.supplier = isc.supplier and oh.supplier = sups.supplier and oh.order_no = os.order_no and os.item = ss.item and os.origin_country_id = isc.origin_country_id and ss.weight_received is NOT NULL and ss.weight_received_uom is NOT NULL and ss.weight_received_uom <> isc.cost_uom and convert.from_uom = ss.weight_received_uom and convert.to_uom = isc.cost_uom and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) = 0))) union all select /*+ INDEX(os, PK_ORDSKU) INDEX(oh, PK_ORDHEAD) */ ss.shipment, ss.seq_no, ss.item, ss.status_code, ss.weight_received as qty_received, (ss.unit_cost / nvl(iscd.net_weight, 1) * isc.supp_pack_size) as unit_cost, ss.qty_matched, ss.weight_received_uom, ss.carton, case when VIEW NAME VIEW TEXT nvl(ss.qty_matched,0) = 0 then 'U' when ss.qty_matched = ss.qty_received then 'M' else 'P' end as invc_match_status from shipment s, shipsku ss, item_supp_country_dim iscd, item_supp_country isc, ordhead oh, ordsku os, item_master im, sups where im.item = ss.item and im.catch_weight_type = '4' and ss.item = iscd.item and ss.item = isc.item and s.shipment = ss.shipment and s.order_no = oh.order_no and oh.supplier = isc.supplier and oh.supplier = sups.supplier and os.item = isc.item and os.origin_country_id = isc.origin_country_id and os.order_no = oh.order_no and iscd.supplier = oh.supplier and iscd.origin_country = os.origin_country_id and iscd.dim_object = 'CA' and ss.weight_received is NOT NULL and ( ss.weight_received_uom is NULL or ss.weight_received_uom = isc.cost_uom) and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) = 0))) union all select /*+ INDEX(os, PK_ORDSKU) INDEX(oh, PK_ORDHEAD) */ ss.shipment, ss.seq_no, ss.item, ss.status_code, case when convert.operator = 'M' then nvl(ss.weight_received, 0) * convert.factor else nvl(ss.weight_received, 0) / convert.factor end as qty_received, (ss.unit_cost / nvl(iscd.net_weight, 1) * isc.supp_pack_size) as unit_cost, ss.qty_matched, ss.weight_received_uom, ss.carton, case when nvl(ss.qty_matched,0) = 0 then 'U' when ss.qty_matched = ss.qty_received then 'M' else 'P' end as invc_match_status from shipment s, shipsku ss, item_supp_country_dim iscd, item_supp_country isc, ordhead oh, ordsku os, uom_conversion convert, item_master im, sups where im.item = ss.item and im.item = iscd.item and im.item = isc.item and s.order_no = oh.order_no and oh.supplier = isc.supplier and os.item = isc.item and os.origin_country_id = isc.origin_country_id and os.order_no = oh.order_no and iscd.supplier = oh.supplier and oh.supplier = sups.supplier and iscd.origin_country = os.origin_country_id and iscd.dim_object = 'CA' and convert.from_uom = ss.weight_received_uom and im.catch_weight_type = '4' and ss.weight_received is NOT NULL and ss.weight_received_uom is NOT NULL and ss.weight_received_uom <> isc.cost_uom and convert.from_uom = ss.weight_received_uom and convert.to_uom = isc.cost_uom and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type VIEW NAME VIEW TEXT and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) = 0))) union all select /*+ INDEX(os, PK_ORDSKU) INDEX(oh, PK_ORDHEAD) */ ss.shipment, ss.seq_no, ss.item, ss.status_code, ss.qty_received, ss.unit_cost unit_cost, ss.qty_matched, ss.weight_received_uom, ss.carton, case when nvl(ss.qty_matched,0) = 0 then 'U' when ss.qty_matched = ss.qty_received then 'M' else 'P' end as invc_match_status from shipsku ss, item_master im, shipment s, ordhead oh, ordsku os, sups where im.item = ss.item and ( im.catch_weight_type = '3' or im.catch_weight_type = '1' or im.catch_weight_type is NULL) and ss.shipment = s.shipment and s.order_no = oh.order_no and oh.order_no = os.order_no and os.item = ss.item and ss.qty_received is NOT NULL and oh.supplier = sups.supplier and sups.sup_qty_level <> 'CA' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) = 0))) union all select /*+ INDEX(os, PK_ORDSKU) INDEX(oh, PK_ORDHEAD) */ ss.shipment, ss.seq_no, ss.item, ss.status_code, ((ss.qty_received) / os.supp_pack_size) qty_received, ((ss.unit_cost) * os.supp_pack_size) unit_cost, ((ss.qty_matched) / os.supp_pack_size) qty_matched, ss.weight_received_uom, ss.carton, case when nvl(ss.qty_matched,0) = 0 then 'U' when ss.qty_matched = ss.qty_received then 'M' else 'P' end as invc_match_status from shipsku ss, item_master im, shipment s, ordhead oh, ordsku os, sups where im.item = ss.item and ( im.catch_weight_type = '3' or im.catch_weight_type = '1' or im.catch_weight_type is NULL) and ss.shipment = s.shipment and s.order_no = oh.order_no and oh.order_no = os.order_no and os.item = ss.item and ss.qty_received is NOT NULL and oh.supplier = sups.supplier and sups.sup_qty_level = 'CA' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, VIEW NAME VIEW TEXT 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) in (1, 2))) or (( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from locs l, v_item_supp_country_loc v_iscl where s.bill_to_loc = l.p_loc and s.bill_to_loc_type = l.loc_type and v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = l.loc and v_iscl.loc_type = l.loc_type and nvl(v_iscl.purchase_type,0) = 0))) V_IM_STAGE_PURGED_SHIPSKUS select unique sps.shipment, sps.seq_no, sps.item, case when sps.weight_received_uom is null then nvl(sps.weight_received, 0) when sps.weight_received_uom = isc.cost_uom then nvl(sps.weight_received, 0) when convert.operator = 'M' then nvl(sps.weight_received, 0) * convert.factor else nvl(sps.weight_received, 0) / convert.factor end as qty_received, sps.weight_received_uom weight_received_uom, (sps.unit_cost / pi.pack_qty) unit_cost from stage_purged_shipskus sps, packitem pi, item_master im, item_supp_country isc, stage_purged_shipments s, ordhead oh, ordsku os, sups, uom_conversion convert where im.item = sps.item and sps.item = pi.pack_no and im.catch_weight_type = '2' and convert.from_uom = sps.weight_received_uom and isc.item = sps.item and s.shipment = sps.shipment and s.order_no = oh.order_no and oh.supplier = isc.supplier and oh.order_no = os.order_no and oh.supplier = sups.supplier and os.item = sps.item and os.origin_country_id = isc.origin_country_id and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) in (1, 2))) or ( ( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) = 0))) UNION ALL select unique sps.shipment, sps.seq_no, sps.item, case when sps.weight_received_uom is null then nvl(sps.weight_received, 0) when sps.weight_received_uom = isc.cost_uom then nvl(sps.weight_received, 0) when convert.operator = 'M' then nvl(sps.weight_received, 0) * convert.factor else nvl(sps.weight_received, 0) / VIEW NAME VIEW TEXT convert.factor end as qty_received, sps.weight_received_uom weight_received_uom, (sps.unit_cost / iscd.net_weight * isc.supp_pack_size) unit_cost from stage_purged_shipskus sps, item_supp_country_dim iscd, item_supp_country isc, ordhead oh, ordsku os, sups, stage_purged_shipments s, uom_conversion convert, item_master im where im.item = sps.item and im.item = iscd.item and im.item = isc.item and s.order_no = oh.order_no and oh.supplier = isc.supplier and oh.supplier = sups.supplier and os.item = isc.item and os.origin_country_id = isc.origin_country_id and os.order_no = oh.order_no and iscd.supplier = oh.supplier and iscd.origin_country = os.origin_country_id and iscd.dim_object = 'CA' and im.catch_weight_type = '4' and convert.from_uom = sps.weight_received_uom and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) in (1, 2))) or ( ( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) = 0))) UNION ALL select sps.shipment, sps.seq_no, sps.item, (sps.qty_received) qty_received, sps.weight_received_uom weight_received_uom, (sps.unit_cost) unit_cost from stage_purged_shipskus sps, item_master im, stage_purged_shipments s, ordhead oh, ordsku os, sups where im.item = sps.item and ( im.catch_weight_type = '3' or im.catch_weight_type = '1' or im.catch_weight_type is null) and sps.shipment = s.shipment and s.order_no = oh.order_no and oh.order_no = os.order_no and os.item = sps.item and oh.supplier = sups.supplier and sups.sup_qty_level <> 'CA' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) in (1, 2))) or ( ( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) = 0))) UNION ALL select sps.shipment, sps.seq_no, sps.item, ((nvl(sps.qty_received, 0)) / os.supp_pack_size) qty_received, sps.weight_received_uom weight_received_uom, ((sps.unit_cost) * os.supp_pack_size) unit_cost from stage_purged_shipskus sps, item_master im, stage_purged_shipments s, ordhead oh, ordsku os, sups where im.item = sps.item and (im.catch_weight_type = '3' or im.catch_weight_type = '1' or im.catch_weight_type is null) and VIEW NAME VIEW TEXT sps.shipment = s.shipment and s.order_no = oh.order_no and oh.order_no = os.order_no and os.item = sps.item and oh.supplier = sups.supplier and sups.sup_qty_level = 'CA' and ( oh.orig_ind = 10 or ( oh.orig_ind in (4, 9, 11, 12) and sups.gen_consign_invoice = 'N' and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) in (1, 2))) or ( ( oh.orig_ind not in (4, 9, 11, 12) or ( oh.orig_ind in (4, 9, 11) and sups.gen_consign_invoice = 'Y')) and exists (select 'X' from v_item_supp_country_loc v_iscl where v_iscl.item = os.item and v_iscl.supplier = oh.supplier and v_iscl.origin_country_id = os.origin_country_id and v_iscl.loc = s.to_loc and nvl(v_iscl.purchase_type,0) = 0))) V_IM_SUPP_PARENT_ATTRIB_EXPL select NVL(supp_grp.supplier, supp_options.supplier) supplier, NVL(supp_grp.ap_reviewer, supp_options.ap_reviewer) ap_reviewer, NVL(supp_grp.match_key, supp_options.match_key) match_key, NVL(supp_grp.match_total_qty_ind, supp_options.match_total_qty_ind) match_total_qty_ind, NVL(supp_grp.total_qty_required_ind, supp_options.total_qty_required_ind) total_qty_required_ind, NVL(supp_grp.match_stgy_id, supp_options.match_stgy_id) match_stgy_id, NVL(supp_grp.sku_comp_percent, supp_options.sku_comp_percent) sku_comp_percent, NVL(supp_grp.qty_disc_day_before_rte, supp_options.qty_disc_day_before_rte) qty_disc_day_before_rte, NVL(supp_grp.online_parent_match_ind, supp_options.online_parent_match_ind) online_parent_match_ind, NVL(supp_grp.data_source, supp_options.data_source) data_source, NVL(supp_grp.group_id, supp_options.group_id) group_id, NVL(supp_grp.group_desc, supp_options.group_desc) group_desc, NVL(supp_grp.send_debit_memo, supp_options.send_debit_memo) send_debit_memo, NVL(supp_grp.manually_paid_ind, supp_options.manually_paid_ind) manually_paid_ind, NVL(supp_grp.use_invoice_terms_ind, supp_options.use_invoice_terms_ind) use_invoice_terms_ind, NVL(supp_grp.rog_date_allowed_ind, supp_options.rog_date_allowed_ind) rog_date_allowed_ind, NVL(supp_grp.close_open_shipment_days, supp_options.close_open_shipment_days) close_open_shipment_days, NVL(supp_grp.hold_invoices, supp_options.hold_invoices) hold_invoices, NVL(supp_grp.match_master_po, supp_options.match_master_po) match_master_po from ( select sups.supplier, isg.ap_reviewer, isg.match_key, isg.match_total_qty_ind, isg.total_qty_required_ind, isg.match_stgy_id, isg.sku_comp_percent, isg.qty_disc_day_before_rte, isg.online_parent_match_ind, 'IM_SUPPLIER_GROUPS' data_source, isg.group_id, isg.group_desc, NULL send_debit_memo, NULL manually_paid_ind, NULL use_invoice_terms_ind, NULL rog_date_allowed_ind, NULL close_open_shipment_days, NULL hold_invoices, isg.match_master_po from im_supplier_group_members isgm, im_supplier_groups isg, sups where isgm.group_id = isg.group_id and isgm.supplier = sups.supplier and sups.supplier_parent is NULL) supp_grp, ( select sups.supplier, iso.ap_reviewer, iso.match_key, iso.match_total_qty_ind, iso.total_qty_required_ind, iso.match_stgy_id, iso.sku_comp_percent, iso.qty_disc_day_before_rte, VIEW NAME VIEW TEXT iso.online_parent_match_ind, 'IM_SUPPLIER_OPTIONS - SUPPLIER PARENT' data_source, ISO.SEND_DEBIT_MEMO, iso.manually_paid_ind, iso.use_invoice_terms_ind, iso.rog_date_allowed_ind, iso.close_open_shipment_days, iso.hold_invoices, NULL group_id, NULL group_desc, iso.match_master_po from im_supplier_options iso, sups where iso.supplier = sups.supplier and sups.supplier_parent is NULL) supp_options where supp_options.supplier = supp_grp.supplier (+) V_IM_SUPP_SITE_ATTRIB_EXPL select sups.supplier, NVL(gs.ap_reviewer, NVL(iso_s.ap_reviewer, iso_p.ap_reviewer)) ap_reviewer, NVL(gs.match_key, NVL(iso_p.match_key, iso_s.match_key)) match_key, case when NVL(gs.match_key, NVL(iso_p.match_key, iso_s.match_key)) in ('POL', 'PO', 'SSL') then NVL(gs.match_total_qty_ind, NVL(iso_s.match_total_qty_ind, iso_p.match_total_qty_ind)) else NVL(gs.match_total_qty_ind, NVL(iso_p.match_total_qty_ind, iso_s.match_total_qty_ind)) end match_total_qty_ind, NVL(gs.total_qty_required_ind, NVL(iso_p.total_qty_required_ind, iso_s.total_qty_required_ind)) total_qty_required_ind, NVL(gs.match_stgy_id,NVL(iso_p.match_stgy_id,iso_s.match_stgy_id)) match_stgy_id, NVL(gs.sku_comp_percent, NVL(iso_p.sku_comp_percent, iso_s.sku_comp_percent)) sku_comp_percent, NVL(gs.qty_disc_day_before_rte, NVL(iso_p.qty_disc_day_before_rte, iso_s.qty_disc_day_before_rte)) qty_disc_day_before_rte, NVL(gs.online_parent_match_ind, iso_p.online_parent_match_ind) online_parent_match_ind, DECODE(gs.supplier, NULL, DECODE(iso_s.supplier, NULL, 'IM_SUPPLIER_OPTIONS - SUPPLIER PARENT', 'IM_SUPPLIER_OPTIONS - SUPPLIER SITE'), 'IM_SUPPLIER_GROUPS') data_source, gs.group_id, gs.group_desc, NVL(iso_s.send_debit_memo, iso_p.send_debit_memo) send_debit_memo, NVL(iso_s.manually_paid_ind, iso_p.manually_paid_ind) manually_paid_ind, NVL(iso_s.use_invoice_terms_ind, iso_p.use_invoice_terms_ind) use_invoice_terms_ind, NVL(iso_s.rog_date_allowed_ind, iso_p.rog_date_allowed_ind) rog_date_allowed_ind, NVL(iso_s.close_open_shipment_days, iso_p.close_open_shipment_days) close_open_shipment_days, NVL(iso_s.hold_invoices, iso_p.hold_invoices) hold_invoices, NVL(gs.automatch_across_multi_po_ind, NVL(iso_p.automatch_across_multi_po_ind, iso_s.automatch_across_multi_po_ind)) automatch_across_multi_po_ind, NVL(gs.match_master_po, NVL(iso_p.match_master_po, iso_s.match_master_po)) match_master_po from sups, (select isgm.supplier, isg.group_id, isg.group_desc, isg.ap_reviewer, isg.match_key, isg.match_total_qty_ind, isg.total_qty_required_ind, isg.match_stgy_id, isg.sku_comp_percent, isg.qty_disc_day_before_rte, isg.online_parent_match_ind, isg.automatch_across_multi_po_ind, isg.match_master_po from im_supplier_groups isg, im_supplier_group_members isgm where isg.group_id = isgm.group_id) gs, im_supplier_options iso_s, im_supplier_options iso_p where iso_p.supplier(+) = sups.supplier_parent and iso_s.supplier(+) = sups.supplier and gs.supplier(+) = sups.supplier_parent and sups.supplier_parent is not NULL and NVL(gs.ap_reviewer, NVL(iso_s.ap_reviewer, iso_p.ap_reviewer)) is not NULL CONNECT WITH US Call +1.800.Oracle1 or visit oracle.com Outside North America, find your local office at oracle.com/contact blogs.oracle.com facebook.com/oracle twitter.com/oracle notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. 0120
