CREATE OR REPLACE VIEW XXFTC_OPEN_ORDER_LINES_FC_V1
(line_id, inventory_item_id, order_type, order_number,
account_number, customer_name, ship_to, bill_to, deliver_to, customer_po,
order_status, ship_method, salesrep, item, description, ordered_date, request_date, quantity, selling_price,
released_status, amount, line_number, shipment_number, list_price,
shipping_method, freight_term, unit_weight, unit_cost, reserved_quantity,
ordered_weight, reserved_weight, warehouse, available_qty, ship_to_add1,
ship_to_add2, ship_to_city, bill_to_add1, bill_to_add2, bill_to_city,
del_to_add1, del_to_add2, del_to_city, allocation_allowed, allocation_status)
AS
SELECT line_id, -- Added Bvengana 26/07/10
inventory_item_id, -- Added Bvengana 26/07/10
order_type,
order_number,
account_number,
party_name,
ship_to,
bill_to,
del_to,
cust_po_number,
flow_status_code,
ship_method_meaning,
NAME,
segment1,
description,
TO_CHAR (ordered_date, 'MM/DD/YYYY'),
request_date,
--SUM(ordered_quantity),
-- Commented by Bvengana 20/08/10 (EDTS : 26949) To fix duplicate qty
issue
ordered_quantity, -- Added by
Bvengana 20/08/10 (EDTS : 26949) To fix duplicate qty issue
unit_selling_price,
delivery_line_status,
SUM (selling_price),
line_number,
shipment_number,
unit_list_price,
freight_carrier_code,
freight_terms_code,
unit_weight,
item_cost,
SUM (reservation_quantity),
SUM (tot_ordered_weight),
SUM (tot_reserved_weight),
organization_code,
available,
ship_to_add1,
ship_to_add2,
ship_to_city,
bill_to_add1,
bill_to_add2,
bill_to_city,
del_to_add1,
del_to_add2,
del_to_city,
allocation_allowed,
allocation_status
FROM ( SELECT ool.line_id, --
Added Bvengana 26/07/10
ool.inventory_item_id, -- Added Bvengana 26/07/10
ott.NAME order_type,
ooh.order_number,
hca.account_number,
hp.party_name,
hcsu.LOCATION ship_to,
hcsu1.LOCATION bill_to,
hcsu2.LOCATION del_to,
ooh.cust_po_number,
ooh.flow_status_code,
wcs.ship_method_meaning,
sr.NAME,
msi.segment1,
msi.description,
TRUNC (ooh.ordered_date) ordered_date,
TRUNC (ool.request_date) request_date,
ool.ordered_quantity,
ool.unit_selling_price,
NVL (DECODE
(wdd.released_status,
'R',
'AB',
'S',
'AB',
'Y',
'AB',
'C',
'Closed',
'D',
'Cancelled',
'B',
'AB'), 'AB')
delivery_line_status,
(ool.ordered_quantity * ool.unit_selling_price)
selling_price,
ool.line_number,
ool.shipment_number,
ool.unit_list_price,
NVL (ooh.freight_carrier_code, ' ')
freight_carrier_code,
NVL (ooh.freight_terms_code, ' ') freight_terms_code,
NVL (msi.unit_weight, 0) unit_weight,
cst.item_cost,
(NVL (mr.reservation_quantity, 0)) reservation_quantity,
(ool.ordered_quantity * NVL (msi.unit_weight, 0))
tot_ordered_weight,
(NVL (mr.reservation_quantity, 0)
* NVL (msi.unit_weight, 0))
tot_reserved_weight,
mp.organization_code,
( (SELECT NVL (SUM (moq.transaction_quantity), 0)
FROM mtl_onhand_quantities moq,
mtl_secondary_inventories
msi
WHERE msi.secondary_inventory_name =
moq.subinventory_code
AND msi.organization_id =
moq.organization_id
AND msi.reservable_type = 1
AND ool.ship_from_org_id =
moq.organization_id
AND ool.inventory_item_id =
moq.inventory_item_id)
- (SELECT NVL (SUM (mr.reservation_quantity), 0)
FROM mtl_reservations mr
WHERE mr.supply_source_type_id = 13
AND mr.organization_id =
ool.ship_from_org_id
AND mr.inventory_item_id =
ool.inventory_item_id))
available,
hl.address1 ship_to_add1,
hl.address2 ship_to_add2,
hl.city ship_to_city,
hl1.address1 bill_to_add1,
hl1.address2 bill_to_add2,
hl1.city bill_to_city,
hl2.address1 del_to_add1,
hl2.address2 del_to_add2,
hl2.city del_to_city,
NVL (
(SELECT 'No'
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM wsh_delivery_details wdd1
WHERE wdd1.source_code = 'OE'
AND wdd1.source_header_id =
ool.header_id
AND wdd1.released_status IN
('S', 'Y'))),
'Yes'
)
allocation_allowed,
DECODE (
( SELECT
ool.ordered_quantity
- SUM (NVL
(mr.reservation_quantity, 0))
FROM mtl_reservations mr
WHERE mr.supply_source_type_id(+) = 13
AND mr.organization_id(+) =
ool.ship_from_org_id
AND mr.demand_source_line_id(+) =
ool.line_id
GROUP BY ool.ordered_quantity),
0,
'Reserved',
'Backordered'
)
allocation_status
FROM jtf_rs_salesreps sr,
hz_cust_accounts hca,
hz_cust_site_uses_all hcsu,
hz_parties hp,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
hz_cust_site_uses_all hcsu1,
hz_cust_acct_sites_all hcas1,
hz_party_sites hps1,
hz_locations hl1,
hz_cust_site_uses_all hcsu2,
hz_cust_acct_sites_all hcas2,
hz_party_sites hps2,
hz_locations hl2,
oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott,
wsh_delivery_details wdd,
mtl_system_items_b msi,
wsh_carrier_services wcs,
cst_item_costs cst,
mtl_reservations mr,
mtl_parameters mp
WHERE sr.org_id = ooh.org_id
AND sr.salesrep_id =
ooh.salesrep_id
AND hl.location_id = hps.location_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.party_id = hp.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcsu.site_use_id = ool.ship_to_org_id
AND hl1.location_id = hps1.location_id
AND hps1.party_site_id = hcas1.party_site_id
AND hcas1.cust_acct_site_id = hcsu1.cust_acct_site_id
AND hcsu1.site_use_id = ool.invoice_to_org_id
AND hl2.location_id(+) = hps2.location_id
AND hps2.party_site_id(+) = hcas2.party_site_id
AND hcas2.cust_acct_site_id(+) =
hcsu2.cust_acct_site_id
AND hcsu2.site_use_id(+) = ool.deliver_to_org_id
AND ool.header_id = ooh.header_id
AND ool.line_category_code = 'ORDER'
AND ool.flow_status_code IN
('ENTERED', 'BOOKED', 'AWAITING_SHIPPING')
AND msi.organization_id = 102
AND msi.inventory_item_id = ool.inventory_item_id
AND ott.transaction_type_id = ooh.order_type_id
AND wdd.source_line_id(+) = ool.line_id
AND wdd.source_code(+) = 'OE'
AND wcs.ship_method_code(+) = ooh.shipping_method_code
AND cst.organization_id = ool.ship_from_org_id
AND msi.inventory_item_id = cst.inventory_item_id
AND cst.cost_type_id = 2
AND mr.demand_source_line_id(+) = ool.line_id
AND mr.inventory_item_id(+) = ool.inventory_item_id
AND mr.supply_source_type_id(+) = 13
AND mp.organization_id = ool.ship_from_org_id
--AND
wdd.released_status NOT IN ('D') -- Added by bvengana 21/01/2011 (
avioding "Cancelled" records)
AND NVL(wdd.released_status,'N') NOT IN ('D') -- Added by bvengana 25/02/2011 ( To display Entered
Records.)
GROUP BY ool.line_id,
ool.inventory_item_id,
ool.header_id,
ott.NAME,
ooh.order_number,
hca.account_number,
hp.party_name,
hl.address1,
hl1.address1,
hl2.address1,
ooh.cust_po_number,
ooh.flow_status_code,
wcs.ship_method_meaning,
sr.NAME,
msi.segment1,
msi.description,
TRUNC (ooh.ordered_date),
TRUNC (ool.request_date),
ool.ordered_quantity,
ool.unit_selling_price,
NVL (DECODE (wdd.released_status,
'R',
'AB',
'S',
'AB',
'Y',
'AB',
'C',
'Closed',
'D',
'Cancelled',
'B',
'AB'), 'AB'),
(ool.ordered_quantity * ool.unit_selling_price),
ool.line_number,
ool.shipment_number,
ool.unit_list_price,
NVL (ooh.freight_carrier_code, ' '),
NVL (ooh.freight_terms_code, ' '),
NVL (msi.unit_weight, 0),
cst.item_cost,
mp.organization_code,
ool.ship_from_org_id,
hcsu.LOCATION,
hcsu1.LOCATION,
hcsu2.LOCATION,
hl.address2,
hl.city,
hl1.address2,
hl1.city,
hl2.address2,
hl2.city,
mr.reservation_quantity)
GROUP BY line_id, -- Added Bvengana 26/07/10
inventory_item_id,
-- Added Bvengana 26/07/10
order_type,
order_number,
account_number,
party_name,
ship_to,
bill_to,
del_to,
cust_po_number,
flow_status_code,
ship_method_meaning,
NAME,
segment1,
description,
ordered_date,
request_date,
unit_selling_price,
delivery_line_status,
--selling_price,
line_number,
shipment_number,
unit_list_price,
freight_carrier_code,
freight_terms_code,
unit_weight,
item_cost, --reservation_quantity,
--tot_ordered_weight,
--tot_reserved_weight,
organization_code,
available,
ship_to_add1,
ship_to_add2,
ship_to_city,
bill_to_add1,
bill_to_add2,
bill_to_city,
del_to_add1,
del_to_add2,
del_to_city,
allocation_allowed,
allocation_status,
ordered_quantity -- Added by
Bvengana 20/08/10 (EDTS : 26949) To fix duplicate qty issue;
No comments:
Post a Comment