[Home] [Help]
27:
28: cursor cur_plan_orders_myfac (l_plan_id in number
29: , l_myfac_id in number) is
30: select count(distinct mdd.source_header_number)
31: from mst_delivery_details mdd
32: , mst_deliveries md
33: , mst_delivery_assignments mda
34: where md.plan_id = mda.plan_id
35: and md.delivery_id = mda.delivery_id
59: , l_cust_supp_id in number) is
60: select count(distinct dd.source_header_number)
61: from (
62: select mdd.source_header_number
63: from mst_delivery_details mdd
64: , mst_deliveries md
65: , mst_delivery_assignments mda
66: where md.plan_id = mda.plan_id
67: and md.delivery_id = mda.delivery_id
78: and ( mdl.pick_up_stop_id = mts.stop_id
79: or mdl.drop_off_stop_id = mts.stop_id))
80: union all
81: select mdd.source_header_number
82: from mst_delivery_details mdd
83: , mst_deliveries md
84: , mst_delivery_assignments mda
85: where md.plan_id = mda.plan_id
86: and md.delivery_id = mda.delivery_id
94:
95: cursor cur_plan_orders_carr (l_plan_id in number
96: , l_carrier_id in number) is
97: select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
98: from mst_delivery_details mdd
99: , mst_delivery_assignments mda
100: , mst_deliveries md
101: where mdd.plan_id = l_plan_id
102: and mdd.plan_id = mda.plan_id
425: l_plan_value number;
426:
427: cursor cur_plan_value (l_plan_id in number) is
428: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
429: from mst_delivery_details mdd
430: , mst_delivery_assignments mda
431: , mst_deliveries md
432: where mdd.plan_id = l_plan_id
433: and mda.plan_id = mdd.plan_id
438:
439: cursor cur_plan_value_myfac (l_plan_id in number
440: , l_myfac_id in number) is
441: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
442: from mst_delivery_details mdd
443: , mst_delivery_assignments mda
444: , mst_deliveries md
445: , fte_location_parameters flp
446: where mdd.plan_id = l_plan_id
456: cursor cur_plan_value_c_s (l_plan_id in number
457: , l_c_s_ident in number
458: , l_cust_supp_id in number) is
459: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
460: from mst_delivery_details mdd
461: , mst_delivery_assignments mda
462: , mst_deliveries md
463: where mdd.plan_id = l_plan_id
464: and mda.plan_id = mdd.plan_id
470:
471: cursor cur_plan_value_carr (l_plan_id in number
472: , l_carrier_id in number) is
473: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
474: from mst_delivery_details mdd
475: , mst_delivery_assignments mda
476: , mst_deliveries md
477: where mdd.plan_id = l_plan_id
478: and mdd.plan_id = mda.plan_id
1657: cursor cur_orders_orig (l_plan_id in number
1658: , l_orig_state in varchar2) is
1659: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
1660: select count(distinct mdd.source_header_number)
1661: from mst_delivery_details mdd
1662: , mst_delivery_assignments mda
1663: , mst_deliveries md
1664: , wsh_locations wl
1665: where md.plan_id = mda.plan_id
1676: , l_orig_state in varchar2) is
1677: /*
1678: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
1679: select count(distinct mdd.source_header_number)
1680: from mst_delivery_details mdd
1681: , mst_delivery_assignments mda
1682: , mst_deliveries md
1683: , fte_location_parameters flp
1684: , wsh_locations wl
1695: */
1696: --Bug_Fix for 3696518 - II
1697: select count(distinct mdd.source_header_number)
1698: from mst_delivery_assignments mda
1699: , mst_delivery_details mdd
1700: where mdd.plan_id = l_plan_id
1701: and mdd.plan_id = mda.plan_id
1702: and mdd.delivery_detail_id = mda.delivery_detail_id
1703: and mda.delivery_id in (select mdl.delivery_id
1723: , l_cust_supp_id in number
1724: , l_orig_state in varchar2) is
1725: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
1726: select count(distinct mdd.source_header_number)
1727: from mst_delivery_details mdd
1728: , mst_delivery_assignments mda
1729: , mst_deliveries md
1730: , wsh_locations wl
1731: where md.plan_id = mda.plan_id
1742: cursor cur_orders_orig_carr (l_plan_id in number
1743: , l_carrier_id in number
1744: , l_orig_state in varchar2) is
1745: select count(distinct mdd.source_header_number)
1746: from mst_delivery_details mdd
1747: , mst_deliveries md
1748: , mst_delivery_assignments mda
1749: where md.plan_id = mda.plan_id
1750: and md.delivery_id = mda.delivery_id
2976: cursor cur_orders_dest (l_plan_id in number
2977: , l_destination_state in varchar2) is
2978: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
2979: select count(distinct mdd.source_header_number)
2980: from mst_delivery_details mdd
2981: , mst_delivery_assignments mda
2982: , mst_deliveries md
2983: , wsh_locations wl
2984: where md.plan_id = mda.plan_id
2995: , l_destination_state in varchar2) is
2996: /*
2997: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
2998: select count(distinct mdd.source_header_number)
2999: from mst_delivery_details mdd
3000: , mst_delivery_assignments mda
3001: , mst_deliveries md
3002: , fte_location_parameters flp
3003: , wsh_locations wl
3014: */
3015: --Bug_Fix for 3696518 - II
3016: select count(distinct mdd.source_header_number)
3017: from mst_delivery_assignments mda
3018: , mst_delivery_details mdd
3019: where mdd.plan_id = l_plan_id
3020: and mdd.plan_id = mda.plan_id
3021: and mdd.delivery_detail_id = mda.delivery_detail_id
3022: and mda.delivery_id in (select mdl.delivery_id
3042: , l_cust_supp_id in number
3043: , l_destination_state in varchar2) is
3044: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
3045: select count(distinct mdd.source_header_number)
3046: from mst_delivery_details mdd
3047: , mst_delivery_assignments mda
3048: , mst_deliveries md
3049: , wsh_locations wl
3050: where md.plan_id = mda.plan_id
3060: cursor cur_orders_dest_carr (l_plan_id in number
3061: , l_carrier_id in number
3062: , l_destination_state in varchar2) is
3063: select count(distinct mdd.source_header_number)
3064: from mst_delivery_details mdd
3065: , mst_deliveries md
3066: , mst_delivery_assignments mda
3067: where md.plan_id = mda.plan_id
3068: and md.delivery_id = mda.delivery_id
4293:
4294: cursor cur_plan_orders_myfac (l_plan_id in number
4295: , l_myfac_id in number) is
4296: select count(distinct mdd.source_header_number)
4297: from mst_delivery_details mdd
4298: , mst_deliveries md
4299: , mst_delivery_assignments mda
4300: where md.plan_id = mda.plan_id
4301: and md.delivery_id = mda.delivery_id
4329: , l_cust_supp_id in number) is
4330: select count(distinct dd.source_header_number)
4331: from (
4332: select mdd.source_header_number
4333: from mst_delivery_details mdd
4334: , mst_deliveries md
4335: , mst_delivery_assignments mda
4336: where md.plan_id = mda.plan_id
4337: and md.delivery_id = mda.delivery_id
4351: and flp.location_id = mts.stop_location_id
4352: and flp.facility_id = l_myfac_id)
4353: union all
4354: select mdd.source_header_number
4355: from mst_delivery_details mdd
4356: , mst_deliveries md
4357: , mst_delivery_assignments mda
4358: where md.plan_id = mda.plan_id
4359: and md.delivery_id = mda.delivery_id
4368: cursor cur_plan_orders_carr (l_plan_id in number
4369: , l_myfac_id in number
4370: , l_carrier_id in number) is
4371: select count(distinct mdd.source_header_number)
4372: from mst_delivery_details mdd
4373: , mst_deliveries md
4374: , mst_delivery_assignments mda
4375: where md.plan_id = mda.plan_id
4376: and md.delivery_id = mda.delivery_id
5027: , l_c_s_ident in number
5028: , l_cust_supp_id in number
5029: , l_myfac_id in number) is
5030: select count(distinct mdd.source_header_number)
5031: from mst_delivery_details mdd
5032: , mst_deliveries md
5033: , mst_delivery_assignments mda
5034: where md.plan_id = mda.plan_id
5035: and md.delivery_id = mda.delivery_id
5065: , l_cust_supp_id in number) is
5066: select count(distinct dd.source_header_number)
5067: from (
5068: select mdd.source_header_number
5069: from mst_delivery_details mdd
5070: , mst_deliveries md
5071: , mst_delivery_assignments mda
5072: where md.plan_id = mda.plan_id
5073: and md.delivery_id = mda.delivery_id
5085: and ( mdl.pick_up_stop_id = mts.stop_id
5086: or mdl.drop_off_stop_id = mts.stop_id))
5087: union all
5088: select mdd.source_header_number
5089: from mst_delivery_details mdd
5090: , mst_deliveries md
5091: , mst_delivery_assignments mda
5092: where md.plan_id = mda.plan_id
5093: and md.delivery_id = mda.delivery_id
5106: , l_carrier_id in number) is
5107: select count(distinct dd.source_header_number)
5108: from (
5109: select mdd.source_header_number source_header_number
5110: from mst_delivery_details mdd
5111: , mst_deliveries md
5112: , mst_delivery_assignments mda
5113: where md.plan_id = mda.plan_id
5114: and md.delivery_id = mda.delivery_id
6441: and pov.vendor_id = hzr.subject_id
6442: and hzr.object_id = hz.party_id
6443: and hzr.relationship_type = 'POS_VENDOR_PARTY'
6444: and pov.vendor_id in (select mdd.supplier_id
6445: from mst_delivery_details mdd
6446: where mdd.plan_id = mp.plan_id)
6447: and decode ( p_report_for, 4, pov.vendor_id , p_report_for_id ) = p_report_for_id
6448: );
6449:
6465: , l_source_header_number in number) is
6466: select distinct mc.segment2
6467: from mtl_category_sets mcs
6468: , mtl_categories mc
6469: , mst_delivery_details mdd
6470: where mcs.structure_id = mc.structure_id
6471: and UPPER(mcs.category_set_name) = 'WSH_COMMODITY_CODE' --'FREIGHT CLASS'
6472: and mc.category_id = mdd.commodity_code_cat_id --mdd.freight_class_cat_id
6473: and mdd.plan_id = l_plan_id