[Home] [Help]
169: 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))),
170: msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
171: ) --reciept_date
172: from msc_plan_organizations_v ov,
173: msc_system_items i,
174: msc_supplies sup,
175: msc_plan_buckets mpb
176: where ov.plan_id = p_plan_id and
177: ov.planned_organization = NVL(p_org_id, ov.planned_organization) and
180: i.organization_id = ov.planned_organization and
181: i.sr_instance_id = ov.sr_instance_id and
182: NVL(i.base_item_id, i.inventory_item_id)
183: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
184: from msc_system_items i1
185: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
186: and i1.organization_id = i.organization_id
187: and i1.plan_id = i.plan_id
188: and i1.sr_instance_id = i.sr_instance_id) and
269: 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))),
270: msc_calendar.DATE_OFFSET(sup.organization_id,sup.sr_instance_id, 1,sup.firm_date, - nvl(i.postprocessing_lead_time,0))
271: ) --reciept_date
272: from msc_plan_organizations_v ov,
273: msc_system_items i,
274: msc_supplies sup,
275: msc_trading_partners t1,
276: msc_plan_buckets mpb
277: where ov.plan_id = p_plan_id and
281: i.organization_id = ov.planned_organization and
282: i.sr_instance_id = ov.sr_instance_id and
283: NVL(i.base_item_id, i.inventory_item_id)
284: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
285: from msc_system_items i1
286: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
287: and i1.organization_id = i.organization_id
288: and i1.plan_id = i.plan_id
289: and i1.sr_instance_id = i.sr_instance_id) and
412: -- order_number => order number for intransit shipment, NULL for Expected inbound shipment
413: sup.order_number order_number, -- use only for intransit_shipment_def and intransit_receipt_def
414: NULL line_number -- dummy type not to be used
415: from msc_plan_organizations_v ov,
416: msc_system_items i,
417: msc_supplies sup,
418: msc_trading_partners t1,
419: msc_plan_buckets mpb
420: where ov.plan_id = p_plan_id and
426: /* Check for defective item type*/
427: sup.item_type_value = 2 and -- for bad goods only
428: NVL(i.base_item_id, i.inventory_item_id)
429: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
430: from msc_system_items i1
431: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
432: and i1.organization_id = i.organization_id
433: and i1.plan_id = i.plan_id
434: and i1.sr_instance_id = i.sr_instance_id) and
517: -- Order_number =>
518: decode(instr(dem.order_number,'.'), 0 , dem.order_number, substr(dem.order_number,1,instr(dem.order_number,'.')-1 )),--
519: 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 ))) --
520: from msc_plan_organizations_v ov,
521: msc_system_items i,
522: msc_demands dem,
523: msc_trading_partners t1,
524: msc_plan_buckets mpb
525: where ov.plan_id = p_plan_id and
534: /* Check for defective item type*/
535: dem.item_type_value = 2 and -- for bad goods only
536: NVL(i.base_item_id, i.inventory_item_id)
537: IN (select nvl(i1.base_item_id,i1.inventory_item_id)
538: from msc_system_items i1
539: where i1.inventory_item_id = nvl(p_item_id, i.inventory_item_id )
540: and i1.organization_id = i.organization_id
541: and i1.plan_id = i.plan_id
542: and i1.sr_instance_id = i.sr_instance_id) and
1624: msi.uom_code
1625: into t_supp_item_name(j),
1626: t_supp_item_desc(j),
1627: t_tp_uom(j)
1628: from msc_system_items msi,
1629: msc_trading_partners part,
1630: msc_trading_partner_maps map
1631: where msi.plan_id = -1 and
1632: msi.inventory_item_id = t_item_id(j) and
1676: IF (t_supp_item_desc(j) is null and t_supp_item_name(j) is not null ) THEN
1677: begin
1678: select description
1679: into t_supp_item_desc(j)
1680: from msc_system_items
1681: where plan_id = -1 and
1682: inventory_item_id = t_item_id(j) and
1683: organization_id = t_org_id(j) and
1684: sr_instance_id = t_sr_instance_id(j);
5124: cs.company_site_id = sd.publisher_site_id and rownum=1) and
5125: sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
5126: sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
5127: exists (select nvl(i.base_item_id,i.inventory_item_id)
5128: from msc_system_items i,
5129: msc_plan_organizations o
5130: where o.plan_id = p_plan_id and
5131: i.plan_id = o.plan_id and
5132: O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID) AND
5170: rownum=1) and
5171: sd.supplier_id = nvl(l_supplier_id, sd.supplier_id) and
5172: sd.supplier_site_id = nvl(l_supplier_site_id, sd.supplier_site_id) and
5173: exists (select nvl(i.base_item_id,i.inventory_item_id)
5174: from msc_system_items i,
5175: msc_plan_organizations o
5176: where o.plan_id = p_plan_id and
5177: i.plan_id = o.plan_id and
5178: i.organization_id = nvl(p_org_id,