[Home] [Help]
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
36: and md.delivery_id in
37: (select mdl.delivery_id
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
68: and mda.plan_id = mdd.plan_id
69: and mda.delivery_detail_id = mdd.delivery_detail_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
87: and mda.plan_id = mdd.plan_id
88: and mda.delivery_detail_id = mdd.delivery_detail_id
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
103: and mdd.delivery_detail_id = mda.delivery_detail_id
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
434: and mda.delivery_detail_id = mdd.delivery_detail_id
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
447: and mda.plan_id = mdd.plan_id
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
465: and mda.delivery_detail_id = mdd.delivery_detail_id
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
479: and mdd.delivery_detail_id = mda.delivery_detail_id
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
1666: and md.delivery_id = mda.delivery_id
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
1685: where md.plan_id = mda.plan_id
1694: and md.plan_id = l_plan_id;
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
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
1732: and md.delivery_id = mda.delivery_id
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
1751: and md.delivery_id in
1752: (select mdl.delivery_id
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
2985: and md.delivery_id = mda.delivery_id
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
3004: where md.plan_id = mda.plan_id
3013: and md.plan_id = l_plan_id;
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
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
3051: and md.delivery_id = mda.delivery_id
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
3069: and md.delivery_id in
3070: (select mdl.delivery_id
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
4302: and md.delivery_id in
4303: (select mdl.delivery_id
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
4338: and mda.plan_id = mdd.plan_id
4339: and mda.delivery_detail_id = mdd.delivery_detail_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
4360: and mda.plan_id = mdd.plan_id
4361: and mda.delivery_detail_id = mdd.delivery_detail_id
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
4377: and md.delivery_id in
4378: (select mdl.delivery_id
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
5036: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5037: and md.delivery_id in
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
5074: and mda.plan_id = mdd.plan_id
5075: and mda.delivery_detail_id = mdd.delivery_detail_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
5094: and mda.plan_id = mdd.plan_id
5095: and mda.delivery_detail_id = mdd.delivery_detail_id
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
5115: and mda.plan_id = mdd.plan_id
5116: and mda.delivery_detail_id = mdd.delivery_detail_id