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,
order_source --Added by Kirankumar D for 4284 Help Desk Ticket
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,
oos.name order_source --Added by Kirankumar for 4284 Help Desk Ticket
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,
oe_order_sources oos --Added by Kirankumar D for 4284 Help Desk Ticket
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 ooh.order_source_id=oos.order_source_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,
oos.name )
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
order_source
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,
order_source --Added by Kirankumar D for 4284 Help Desk Ticket
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,
oos.name order_source --Added by Kirankumar for 4284 Help Desk Ticket
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,
oe_order_sources oos --Added by Kirankumar D for 4284 Help Desk Ticket
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 ooh.order_source_id=oos.order_source_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,
oos.name )
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
order_source
No comments:
Post a Comment