[Home] [Help]
259: lv_icode varchar2(3);
260: v_dblink_a2m VARCHAR2(128);
261:
262: cursor org IS
263: select /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */ organization_id org_id,
264: DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
265: p_worker_num, MSC_UTIL.SYS_YES,
266: MSC_UTIL.SYS_NO) yes_flag
267: from msc_instance_orgs
263: select /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */ organization_id org_id,
264: DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
265: p_worker_num, MSC_UTIL.SYS_YES,
266: MSC_UTIL.SYS_NO) yes_flag
267: from msc_instance_orgs
268: where sr_instance_id= MSC_CL_PULL.v_instance_id
269: and enabled_flag= 1
270: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group))
271: order by
278: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
279:
280: select count(mio.organization_id)
281: into lv_org_count
282: from msc_instance_orgs mio,
283: msc_coll_parameters mcp
284: where mio.sr_instance_id= MSC_CL_PULL.v_instance_id
285: and mcp.instance_id = MSC_CL_PULL.v_instance_id
286: and mio.enabled_flag= 1
290: if (lv_org_count > v_maxnum_of_orgstr) then
291: lv_in_org_str := ' IN (select a.organization_id '
292: || ' from '
293: || ' ( select organization_id ,rownum seqnum '
294: || ' from msc_instance_orgs '
295: || ' where sr_instance_id= ' || MSC_CL_PULL.v_instance_id
296: || ' and enabled_flag= 1 '
297: || ' and (('''
298: || MSC_CL_PULL.v_org_group ||''' = '''
853: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
854: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
855: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
856: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
857: ||' From msc_instance_orgs '
858: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
859: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
860:
861: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1559: where st_item.SR_INVENTORY_ITEM_ID in ( -1000,-1001,-1002,-1003,-1004) and
1560: st_item.sr_instance_id = MSC_CL_PULL.v_instance_id and
1561: st_item.organization_id in
1562: ( select x.organization_id
1563: FROM msc_instance_orgs x
1564: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1565: and x.enabled_flag= 1
1566: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1567: );
1590: ATP_FLAG,
1591: eam_item_type,
1592: ITEM_NAME,
1593: ORGANIZATION_CODE)
1594: SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1595: x.ORGANIZATION_ID ,
1596: -1000,
1597: MSC_CL_PULL.v_instance_id,
1598: 1, --LOT_CONTROL_CODE 1 no control, 2 full control
1613: 2, --ATP_FLAG
1614: 1, --eam_item_type
1615: lv_item_name ,
1616: org.ORGANIZATION_CODE
1617: FROM msc_instance_orgs x,
1618: MTL_PARAMETERS org
1619: WHERE sr_instance_id= MSC_CL_PULL.v_instance_id
1620: and enabled_flag= 1
1621: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1646: MRP_CALCULATE_ATP_FLAG,
1647: ATP_FLAG,
1648: ITEM_NAME,
1649: ORGANIZATION_CODE)
1650: SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1651: x.ORGANIZATION_ID ,
1652: -1001,
1653: MSC_CL_PULL.v_instance_id,
1654: 1, --LOT_CONTROL_CODE
1668: 2, --CALCULATE_ATP
1669: 2, --ATP_FLAG
1670: lv_item_name ,
1671: org.ORGANIZATION_CODE
1672: FROM msc_instance_orgs x,
1673: MTL_PARAMETERS org
1674: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1675: and x.enabled_flag= 1
1676: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1722: ||' 2, '
1723: ||' 2, '
1724: ||' ''CMRO UMP'' , '
1725: ||' :V_ICODE||org.ORGANIZATION_CODE'
1726: ||' FROM msc_instance_orgs x, '
1727: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1728: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1729: ||' and x.enabled_flag= 1 '
1730: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1778: ||' 2, '
1779: ||' 2, '
1780: ||' ''EAM Forecast'' , '
1781: ||' :V_ICODE||org.ORGANIZATION_CODE'
1782: ||' FROM msc_instance_orgs x, '
1783: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1784: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1785: ||' and x.enabled_flag= 1 '
1786: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1833: ||' 2, '
1834: ||' 2, '
1835: ||' ''Maintenance'' , '
1836: ||' :V_ICODE||org.ORGANIZATION_CODE'
1837: ||' FROM msc_instance_orgs x, '
1838: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1839: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1840: ||' and x.enabled_flag= 1 '
1841: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1913: st_item_category.SR_CATEGORY_ID in (-5001) and
1914: st_item_category.sr_instance_id = MSC_CL_PULL.v_instance_id and
1915: st_item_category.organization_id in
1916: ( select x.organization_id
1917: FROM msc_instance_orgs x
1918: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1919: and x.enabled_flag= 1
1920: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1921: );
1950: ||'START_DATE_ACTIVE, '
1951: ||'END_DATE_ACTIVE, '
1952: ||'DELETED_FLAG, '
1953: ||'SR_INSTANCE_ID)'
1954: ||' SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1)*/ '
1955: ||' :lv_item_id ,'
1956: || 'x.ORGANIZATION_ID ,'
1957: || lv_category_set_id ||','
1958: || lv_category_id ||','
1964: || 'null,'
1965: || 'null,'
1966: || lv_deleted_flag ||','
1967: || MSC_CL_PULL.v_instance_id
1968: || ' FROM msc_instance_orgs x,'
1969: || lv_table_name ||' org '
1970: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1971: ||' and x.enabled_flag= 1'
1972: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID'
1984: and SR_CATEGORY_SET_ID = lv_category_set_id
1985: and SR_CATEGORY_ID = lv_category_id
1986: and organization_id in
1987: ( select x.organization_id
1988: FROM msc_instance_orgs x
1989: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1990: and x.enabled_flag= 1
1991: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1992: );