[Home] [Help]
172: decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
173: msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
174: ) --reciept_date
175: from msc_plan_organizations_v ov,
176: msc_system_items i,
177: msc_supplies sup,
178: msc_plan_buckets mpb
179: where ov.plan_id = p_plan_id and
180: ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
183: i.organization_id = ov.planned_organization and
184: i.sr_instance_id = ov.sr_instance_id and
185: NVL(i.base_item_id, i.inventory_item_id)
186: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
187: from msc_system_items i1
188: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
189: and i1.organization_id = i.organization_id
190: and i1.plan_id = i.plan_id
191: and i1.sr_instance_id = i.sr_instance_id) and
272: decode(sup.firm_date,null,nvl(sup.new_dock_date,msc_calendar.DATE_OFFSET(sup.organization_id, sup.sr_instance_id, 1, sup.new_schedule_date, - nvl(i.postprocessing_lead_time,0))),
273: msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
274: ) --reciept_date
275: from msc_plan_organizations_v ov,
276: msc_system_items i,
277: msc_supplies sup,
278: msc_trading_partners t1,
279: msc_plan_buckets mpb
280: where ov.plan_id = p_plan_id and
284: i.organization_id = ov.planned_organization and
285: i.sr_instance_id = ov.sr_instance_id and
286: NVL(i.base_item_id, i.inventory_item_id)
287: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
288: from msc_system_items i1
289: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
290: and i1.organization_id = i.organization_id
291: and i1.plan_id = i.plan_id
292: and i1.sr_instance_id = i.sr_instance_id) and
415: -- order_number => order number for intransit shipment, NULL for Expected inbound shipment
416: sup.order_number order_number, -- use only for intransit_shipment_def and intransit_receipt_def
417: NULL line_number -- dummy type not to be used
418: from msc_plan_organizations_v ov,
419: msc_system_items i,
420: msc_supplies sup,
421: msc_trading_partners t1,
422: msc_plan_buckets mpb
423: where ov.plan_id = p_plan_id and
429: /* Check for defective item type*/
430: sup.item_type_value = 2 and -- for bad goods only
431: NVL(i.base_item_id, i.inventory_item_id)
432: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
433: from msc_system_items i1
434: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
435: and i1.organization_id = i.organization_id
436: and i1.plan_id = i.plan_id
437: and i1.sr_instance_id = i.sr_instance_id) and
520: -- Order_number =>
521: decode(instr(dem.order_number,'.'), 0 , dem.order_number, substr(dem.order_number,1,instr(dem.order_number,'.')-1 )),--
522: TRIM(decode(instr(dem.order_number,'(',-1,1),0,null,substr(dem.order_number,instr(dem.order_number,'(',-1,1) + 1,instr(dem.order_number,')',-1,1) - instr(dem.order_number,'(',-1,1) -1 ))) --
523: from msc_plan_organizations_v ov,
524: msc_system_items i,
525: msc_demands dem,
526: msc_trading_partners t1,
527: msc_plan_buckets mpb
528: where ov.plan_id = p_plan_id and
537: /* Check for defective item type*/
538: dem.item_type_value = 2 and -- for bad goods only
539: NVL(i.base_item_id, i.inventory_item_id)
540: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
541: from msc_system_items i1
542: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
543: and i1.organization_id = i.organization_id
544: and i1.plan_id = i.plan_id
545: and i1.sr_instance_id = i.sr_instance_id) and
1641: msi.uom_code
1642: into t_supp_item_name(j),
1643: t_supp_item_desc(j),
1644: t_tp_uom(j)
1645: from msc_system_items msi,
1646: msc_trading_partners part,
1647: msc_trading_partner_maps map
1648: where msi.plan_id = -1 and
1649: msi.inventory_item_id = t_item_id(j) and
1693: IF (t_supp_item_desc(j) is null and t_supp_item_name(j) is not null ) THEN
1694: begin
1695: select description
1696: into t_supp_item_desc(j)
1697: from msc_system_items
1698: where plan_id = -1 and
1699: inventory_item_id = t_item_id(j) and
1700: organization_id = t_org_id(j) and
1701: sr_instance_id = t_sr_instance_id(j);
5141: cs.company_site_id = sd.publisher_site_id and rownum=1) and
5142: sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
5143: sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
5144: exists (select nvl(i.base_item_id,i.inventory_item_id)
5145: from msc_system_items i,
5146: msc_plan_organizations o
5147: where o.plan_id = p_plan_id and
5148: i.plan_id = o.plan_id and
5149: O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID) AND
5187: rownum=1) and
5188: sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
5189: sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
5190: exists (select nvl(i.base_item_id,i.inventory_item_id)
5191: from msc_system_items i,
5192: msc_plan_organizations o
5193: where o.plan_id = p_plan_id and
5194: i.plan_id = o.plan_id and
5195: i.organization_id = nvl(p_org_id,