[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
INTO x_organization_name
FROM hr_all_organization_units_tl haoutl
WHERE haoutl.organization_id = p_organization_id
AND haoutl.LANGUAGE = USERENV('LANG');
SELECT gl_ledgers.currency_code
INTO l_functional_currency
FROM gl_ledgers gl_ledgers
WHERE gl_ledgers.ledger_id =
/* (SELECT DISTINCT xllv.ledger_id
FROM xle_le_ou_ledger_v xllv
WHERE xllv.operating_unit_id = p_ou_id
);*/
(select set_of_books_id from hr_operating_units
where organization_id = p_ou_id);
DELETE FROM JMF_SHIKYU_CFR_MID_TEMP;
DELETE FROM JMF_SHIKYU_CFR_RPT_TEMP;
SELECT mp.organization_id
,mp.organization_code
,haoutl.NAME
FROM mtl_parameters mp
,hr_organization_information hoi
,hr_all_organization_units haou
,HR_ALL_ORGANIZATION_UNITS_TL haoutl
WHERE mp.organization_id = hoi.organization_id
AND haou.organization_id = hoi.organization_id
AND haou.organization_id = haoutl.organization_id
AND NVL(mp.trading_partner_org_flag,'N') = 'N'
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information3 = lp_ou_id
AND haoutl.NAME >= NVL(lp_oem_inv_org_name_from
,haoutl.NAME)
AND haoutl.NAME <= NVL(lp_oem_inv_org_name_to
,haoutl.NAME)
AND haoutl.LANGUAGE = USERENV('LANG');
SELECT mip.to_organization_id --tp_org_id
,mp.organization_code --tp_org_code
,pv.vendor_id --supplier_id
,pvs.vendor_site_id --supplier_site_id
FROM mtl_interorg_parameters mip
,po_vendors pv
,po_vendor_sites_all pvs
,hr_organization_information hoi
,mtl_parameters mp
WHERE mip.from_organization_id = lp_oem_inv_org_id
--AND mip.shikyu_enabled_flag = 'Y'
AND mip.subcontracting_type in ('B','C') -- 12.1 Buy/Sell Subcontracting Changes
AND mp.trading_partner_org_flag = 'Y' --hide for test as there is not data for this column!!!
AND hoi.org_information_context = 'Customer/Supplier Association' --to identify the flexfield
AND hoi.org_information3 = pv.vendor_id --(Application : Human Resources,Descriptive Flexfield Segment Title: Org Developer DF.)
AND hoi.org_information4 = pvs.vendor_site_id
AND mip.to_organization_id = hoi.organization_id
AND mip.to_organization_id = mp.organization_id
AND ((pv.vendor_name IS NULL) OR
((pv.vendor_name >= NVL(lp_supplier_name_from
,pv.vendor_name)) AND
(pv.vendor_name <= NVL(lp_supplier_name_to
,pv.vendor_name))))
AND ((pvs.vendor_site_code IS NULL) OR
(pvs.vendor_site_code >=
NVL(lp_supplier_site_code_from
,pvs.vendor_site_code)) AND
(pvs.vendor_site_code <=
NVL(lp_supplier_site_code_to
,pvs.vendor_site_code)));
INSERT INTO jmf_shikyu_cfr_mid_temp
(row_type --onhand row type
,oem_inv_org_id --oem_inv_org_id
,supplier_id --supplier_id
,site_id --site_id
,tp_inv_org_id --tp_inv_org_id
,item_id --item_id
,primary_unconsumed_quantity --onhand primary uom quantity
,project_id --project_id
,task_id --task_id
)
SELECT p_onhand_row_type
,l_oem_inv_org_id
,l_supplier_id
,l_supplier_site_id
,onhand.organization_id
,onhand.inventory_item_id
,SUM(onhand.transaction_quantity) primary_uom_qty
,onhand.project_id
,onhand.task_id
FROM MTL_ONHAND_QUANTITIES onhand
,MTL_SYSTEM_ITEMS_B_KFV item_f -- the latest view for the item flexfield
WHERE onhand.organization_id = l_tp_inv_org_id
AND onhand.organization_id = item_f.organization_id
AND onhand.inventory_item_id = item_f.inventory_item_id
AND item_f.subcontracting_component IS NOT NULL --= 'Y'
/* AND item_f.concatenated_segments >=
NVL(p_item_number_from, item_f.concatenated_segments)
AND item_f.concatenated_segments <=
NVL(p_item_number_to, item_f.concatenated_segments)*/
AND (p_item_number_from IS NULL
OR item_f.concatenated_segments >= p_item_number_from)
AND (p_item_number_to IS NULL
OR item_f.concatenated_segments <= p_item_number_to)
GROUP BY onhand.organization_id
,onhand.inventory_item_id
,onhand.project_id
,onhand.task_id;
SELECT supplier_id
,site_id
,oem_inv_org_id
,tp_inv_org_id
,item_id
,project_id
,task_id
,primary_unconsumed_quantity
FROM jmf_shikyu_cfr_mid_temp
WHERE row_type = p_onhand_row_type;
UPDATE jmf_shikyu_cfr_mid_temp
SET primary_unallocated_quantity = l_onhand_quantity
WHERE row_type = p_onhand_row_type
AND supplier_id = l_supplier_id
AND site_id = l_supplier_site_id
AND oem_inv_org_id = l_oem_inv_org_id
AND tp_inv_org_id = l_tp_inv_org_id
AND item_id = l_item_id
AND ((project_id IS NULL) OR (project_id = l_project_id))
AND ((task_id IS NULL) OR (task_id = l_task_id));
SELECT rt.transaction_id
-- Updated to fix potential issue of operations between null numbers
-- ,rt.primary_quantity -
,NVL(rt.primary_quantity,0) -
NVL((SELECT SUM(NVL(jscmt_rcv.primary_unallocated_quantity
,0) + NVL(jscmt_rcv.primary_unconsumed_quantity
,0))
FROM jmf_shikyu_cfr_mid_temp jscmt_rcv
WHERE jscmt_rcv.row_type = 40
AND jscmt_rcv.shikyu_id = rt.transaction_id)
,0)
--Added to fix bug 5509464 start
,pha.vendor_id --p_supplier_id
,pha.vendor_site_id --p_supplier_site_id
--Added to fix bug 5509464 end
FROM jmf_shikyu_cfr_mid_temp mid
,po_line_locations_all pll
,rcv_transactions rt
--Added to fix bug 5509464 start
,po_headers_all pha
--Added to fix bug 5509464 end
WHERE mid.row_type = p_rep_po_unalloc_row_type
AND NVL(mid.get_rcv_flag
,'N') <> CFR_REP_PO_GET_RCV_FLAG --if the rep_po line have done get rcv process, the get_rcv_flag will be set to 1
AND mid.shikyu_id = pll.line_location_id
AND rt.transaction_type = 'RECEIVE'
AND pll.line_location_id = rt.po_line_location_id
--Added to fix bug 5509464 start
AND pha.po_header_id=pll.po_header_id
--Added to fix bug 5509464 end
ORDER BY rt.transaction_date;
INSERT INTO jmf_shikyu_cfr_mid_temp
(row_type
,shikyu_id
,uom
,primary_uom
,primary_unallocated_quantity
,supplier_id
,site_id
,oem_inv_org_id
,tp_inv_org_id
,item_id)
SELECT p_rep_po_unalloc_row_type
,plla.line_location_id
,uom_tl.uom_code
,JMF_SHIKYU_RPT_UTIL.get_item_primary_uom_code(p_tp_inv_org_id
,p_item_id)
,JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(p_tp_inv_org_id
,pla.item_id
,uom_tl.uom_code
-- Updated to fix potential issue of operations between null numbers
-- ,plla.quantity_received) -
,NVL(plla.quantity_received,0)) -
(SELECT SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.tp_organization_id
,jsr.shikyu_component_id
-- fix bug 5702139
-- ,uom_tl_s.uom_code
,decode(jsa.allocated_quantity
,null
,uom_tl_s.uom_code
,jsa.uom)
--Updated to fix bug 5509464 start
--,jsa.allocated_quantity)) pll_allocated
,nvl(jsa.allocated_quantity,0))) pll_allocated
--Updated to fix bug 5509464 end
FROM jmf_shikyu_allocations jsa
,jmf_shikyu_replenishments jsr
,MTL_UNITS_OF_MEASURE_TL uom_tl_s
--Updated to fix bug 5509464 start
--WHERE jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
-- AND jsa.shikyu_component_id = jsr.shikyu_component_id
WHERE jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
--Updated to fix bug 5509464 end
AND jsr.replenishment_po_shipment_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND uom_tl_s.LANGUAGE = USERENV('LANG')
-- fix bug 5702139
-- AND pla.unit_meas_lookup_code = uom_tl_s.unit_of_measure
AND po_uom_s.get_primary_uom(pla.item_id,p_tp_inv_org_id,null) = uom_tl_s.unit_of_measure -- primary UOM
AND jsr.shikyu_component_id = p_item_id) residual_unallocated_pri
,pha.vendor_id --p_supplier_id
,pha.vendor_site_id --p_supplier_site_id
,p_oem_inv_org_id
,p_tp_inv_org_id
,p_item_id
FROM po_headers_all pha
,po_lines_all pla
,po_line_locations_all plla
,MTL_UNITS_OF_MEASURE_TL uom_tl
,HR_ORGANIZATION_INFORMATION hoi -- Add this table to get information of oem
WHERE pla.po_header_id = pha.po_header_id
AND plla.po_line_id = pla.po_line_id
AND pla.unit_meas_lookup_code = uom_tl.unit_of_measure
AND uom_tl.LANGUAGE = USERENV('LANG')
AND pha.org_id = p_ou_id
AND hoi.ORGANIZATION_ID = p_oem_inv_org_id
AND hoi.org_information_context = 'Customer/Supplier Association' --to identify the flexfield
AND ((pha.vendor_id = hoi.org_information3) OR -- when org_information_context is set to be 'Customer/Supplier Association',this identify the supplier_id of org
(pha.vendor_id IS NULL AND hoi.org_information3 IS NULL))
AND ((pha.vendor_site_id = hoi.org_information4) OR -- when org_information_context is set to be 'Customer/Supplier Association',this identify the supplier_site_id of org
(pha.vendor_site_id IS NULL AND hoi.org_information4 IS NULL))
AND pla.item_id = p_item_id
AND plla.line_location_id NOT IN
(SELECT jscmt.shikyu_id
FROM jmf_shikyu_cfr_mid_temp jscmt
WHERE jscmt.row_type = p_rep_po_unalloc_row_type
AND jscmt.item_id = p_item_id
AND jscmt.oem_inv_org_id = p_oem_inv_org_id
AND jscmt.tp_inv_org_id = p_tp_inv_org_id
AND jscmt.supplier_id = p_supplier_id
AND jscmt.site_id = p_supplier_site_id)
--updated to fix bug 5232863 start
-- AND JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(pha.org_id
AND JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(p_tp_inv_org_id
,pla.item_id
,uom_tl.uom_code
-- Updated to fix potential issue of operations between null numbers
-- ,plla.quantity_received) >
,NVL(plla.quantity_received,0)) >
-- (SELECT SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.tp_organization_id
(SELECT SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(p_tp_inv_org_id
--updated to fix bug 5232863 end
,jsr.shikyu_component_id
-- fix bug 5702139
-- ,uom_tl_s.uom_code
,decode(jsa.allocated_quantity
,null
,uom_tl_s.uom_code
,jsa.uom)
--Updated to fix bug 5509464 start
--,jsa.allocated_quantity)) pll_allocated
,nvl(jsa.allocated_quantity,0))) pll_allocated
--Updated to fix bug 5509464 end
FROM jmf_shikyu_allocations jsa
,jmf_shikyu_replenishments jsr
,MTL_UNITS_OF_MEASURE_TL uom_tl_s
--Updated to fix bug 5509464 start
--WHERE jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
-- AND jsa.shikyu_component_id = jsr.shikyu_component_id
WHERE jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
--Updated to fix bug 5509464 end
AND jsr.replenishment_po_shipment_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
-- fix bug 5702139
-- AND pla.unit_meas_lookup_code = uom_tl_s.unit_of_measure
AND po_uom_s.get_primary_uom(pla.item_id,p_tp_inv_org_id,null) = uom_tl_s.unit_of_measure -- primary UOM
AND jsr.shikyu_component_id = p_item_id);
UPDATE jmf_shikyu_cfr_mid_temp
SET quantity = NVL(primary_unallocated_quantity,0) *
JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(primary_uom
,uom
,item_id)
WHERE row_type = p_rep_po_unalloc_row_type
AND quantity IS NULL --only for those do not get the primary uom
AND supplier_id = p_supplier_id
AND site_id = p_supplier_site_id
AND oem_inv_org_id = p_oem_inv_org_id
AND tp_inv_org_id = p_tp_inv_org_id
AND item_id = p_item_id;
SELECT cfr_mid.primary_unallocated_quantity
INTO l_rep_po_unallocated_pri
FROM jmf_shikyu_cfr_mid_temp cfr_mid
,po_line_locations_all poloc
,rcv_transactions rcv
WHERE rcv.transaction_type = 'RECEIVE'
AND poloc.line_location_id = rcv.po_line_location_id
AND cfr_mid.shikyu_id = poloc.line_location_id
AND rcv.transaction_id = P_rcv_transaction_id
AND cfr_mid.supplier_id = p_supplier_id
AND cfr_mid.site_id = p_supplier_site_id
AND cfr_mid.oem_inv_org_id = p_oem_inv_org_id
AND cfr_mid.tp_inv_org_id = p_tp_inv_org_id
AND cfr_mid.item_id = p_item_id
AND cfr_mid.row_type = p_rep_po_unalloc_row_type
AND rownum = 1;
UPDATE jmf_shikyu_cfr_mid_temp
-- Updated to fix potential issue of operations between null numbers
--SET quantity = quantity -
-- (quantity *
SET quantity = NVL(quantity,0) -
(NVL(quantity,0) *
p_new_rep_po_unallocated_pri /
primary_unallocated_quantity) --residual_unallocated for the UOM
-- Updated to fix potential issue of operations between null numbers
-- ,primary_unallocated_quantity = primary_unallocated_quantity -
,primary_unallocated_quantity = NVL(primary_unallocated_quantity,0) -
p_new_rep_po_unallocated_pri --residual_unallocated for the primary UOM
WHERE row_type = p_rep_po_unalloc_row_type
AND supplier_id = p_supplier_id
AND site_id = p_supplier_site_id
AND oem_inv_org_id = p_oem_inv_org_id
AND tp_inv_org_id = p_tp_inv_org_id
AND item_id = p_item_id
AND shikyu_id =
(SELECT rcv.po_line_location_id
FROM rcv_transactions rcv
WHERE rcv.transaction_id = p_rcv_transaction_id
AND rcv.transaction_type = 'RECEIVE');
SELECT COUNT(*)
INTO l_jmf_cfr_mid_temp_rcv_rows
FROM jmf_shikyu_cfr_mid_temp
WHERE row_type = p_rcv_row_type
AND shikyu_id = p_rcv_transaction_id;
UPDATE jmf_shikyu_cfr_mid_temp
-- Updated to fix potential issue of operations between null numbers
-- SET primary_unallocated_quantity = primary_unallocated_quantity +
SET primary_unallocated_quantity = NVL(primary_unallocated_quantity,0) +
p_rcv_unallocated_pri
WHERE row_type = p_rcv_row_type
AND shikyu_id = p_rcv_transaction_id;
INSERT INTO jmf_shikyu_cfr_mid_temp
(row_type
,shikyu_id
,primary_unallocated_quantity
,oem_inv_org_id
,tp_inv_org_id
,item_id
,supplier_id
,site_id)
VALUES
(p_rcv_row_type
,p_rcv_transaction_id
,p_rcv_unallocated_pri
,p_oem_inv_org_id
,p_tp_inv_org_id
,p_item_id
,p_supplier_id
,p_supplier_site_id);
SELECT rt.transaction_id
,rt.primary_quantity -
NVL((SELECT SUM(NVL(mid_s.primary_unallocated_quantity
,0) + NVL(mid_s.primary_unconsumed_quantity
,0))
FROM jmf_shikyu_cfr_mid_temp mid_s
WHERE mid_s.row_type = p_rcv_transaction_row_type
AND mid_s.shikyu_id = rt.transaction_id)
,0)
FROM jmf_shikyu_allocations alloc
,jmf_shikyu_replenishments jsr
,jmf_shikyu_cfr_mid_temp mid
,rcv_transactions rt
WHERE mid.row_type = p_sub_po_unconsumed_row_type
AND NVL(mid.get_rep_flag
,'N') <> CFR_REP_PO_GET_RCV_FLAG --if the rep_po line have done get rcv process, the get_rcv_flag will be set to 1
AND mid.shikyu_id = alloc.subcontract_po_shipment_id
AND mid.item_id = alloc.shikyu_component_id
AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
AND jsr.replenishment_po_shipment_id = rt.po_line_location_id
AND rt.transaction_type = 'RECEIVE'
AND jsr.oem_organization_id = p_oem_inv_org_id
AND jsr.tp_organization_id = p_tp_inv_org_id
AND jsr.tp_supplier_id = p_supplier_id
AND jsr.tp_supplier_site_id = p_supplier_site_id
AND jsr.shikyu_component_id = p_item_id
ORDER BY rt.transaction_date;
INSERT INTO jmf_shikyu_cfr_mid_temp
(row_type
,shikyu_id
,uom
,primary_uom
,primary_unconsumed_quantity
,supplier_id
,site_id
,oem_inv_org_id
,tp_inv_org_id
,item_id)
SELECT p_sub_po_unconsumed_row_type
,sub_po.subcontract_po_shipment_id
,comp.uom
,JMF_SHIKYU_RPT_UTIL.get_item_primary_uom_code(sub_po.tp_organization_id
,comp.shikyu_component_id)
,((SELECT NVL(SUM(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(sub_po.tp_organization_id
,jsa_a.shikyu_component_id
,jsa_a.uom
,NVL(jsa_a.allocated_quantity
,0)))
,0)
FROM jmf_shikyu_allocations jsa_a
WHERE jsa_a.subcontract_po_shipment_id =
comp.subcontract_po_shipment_id
AND jsa_a.shikyu_component_id = comp.shikyu_component_id) -
wip_req.quantity_issued
) primary_possible_unconsumed
,ph.vendor_id
,ph.vendor_site_id
,sub_po.oem_organization_id
,sub_po.tp_organization_id
,comp.shikyu_component_id
FROM po_line_locations_all pll
,jmf_subcontract_orders sub_po
,po_headers_all ph
,jmf_shikyu_components comp
,wip_requirement_operations wip_req
WHERE pll.line_location_id = sub_po.subcontract_po_shipment_id
AND pll.quantity > pll.quantity_received --this can be ignore if allow the allocated qty larger than ordered qty
AND sub_po.oem_organization_id = p_oem_inv_org_id
AND sub_po.tp_organization_id = p_tp_inv_org_id
AND pll.po_header_id = ph.po_header_id
AND ((ph.org_id IS NULL) OR (ph.org_id = p_ou_id))
AND ph.vendor_id = p_supplier_id
AND ph.vendor_site_id = p_supplier_site_id
AND sub_po.wip_entity_id = wip_req.wip_entity_id
AND sub_po.subcontract_po_shipment_id =
comp.subcontract_po_shipment_id
AND comp.shikyu_component_id = wip_req.inventory_item_id
AND comp.shikyu_component_id = p_item_id
AND sub_po.tp_organization_id = wip_req.organization_id
AND wip_req.repetitive_schedule_id IS NULL
AND wip_req.operation_seq_num = 1
--This may cause issue when same components are found in different tp organization,the onhand component are found
-- secondly,will be lost in them mid_temp table.That means in the report will lose some datas which should be displayed.
-- updated to fix this potensial issue.
/* AND NOT ((comp.subcontract_po_shipment_id IN
(SELECT jscmt_s.shikyu_id
FROM jmf_shikyu_cfr_mid_temp jscmt_s
WHERE jscmt_s.row_type = p_sub_po_unconsumed_row_type)) AND
(comp.shikyu_component_id IN
(SELECT jscmt_i.item_id
FROM jmf_shikyu_cfr_mid_temp jscmt_i
WHERE jscmt_i.row_type = p_sub_po_unconsumed_row_type)))*/
AND NOT (comp.subcontract_po_shipment_id IN
(SELECT jscmt_s.shikyu_id
FROM jmf_shikyu_cfr_mid_temp jscmt_s
WHERE jscmt_s.row_type = p_sub_po_unconsumed_row_type
AND jscmt_s.item_id = p_item_id))
;
UPDATE jmf_shikyu_cfr_mid_temp
SET quantity = NVL(primary_unconsumed_quantity,0) *
JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(primary_uom
,uom
,item_id)
WHERE row_type = p_sub_po_unconsumed_row_type
AND quantity IS NULL --only for those do not get the quantity of uom
AND supplier_id = p_supplier_id
AND site_id = p_supplier_site_id
AND oem_inv_org_id = p_oem_inv_org_id
AND tp_inv_org_id = p_tp_inv_org_id
AND item_id = p_item_id;
INSERT INTO jmf_shikyu_cfr_mid_temp
(row_type
,shikyu_id
,uom
,primary_uom
,primary_unconsumed_quantity
,supplier_id
,site_id
,oem_inv_org_id
,tp_inv_org_id
,item_id)
SELECT p_rep_po_unconsumed_row_type
,jsr.replenishment_po_shipment_id
,alloc.uom
,JMF_SHIKYU_RPT_UTIL.get_item_primary_uom_code(jsr.oem_organization_id
,alloc.shikyu_component_id)
,JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.oem_organization_id
,alloc.shikyu_component_id
,alloc.uom
,alloc.allocated_quantity) -
NVL(wro.quantity_issued
,0)
,jsr.tp_supplier_id
,jsr.tp_supplier_site_id
,jsr.oem_organization_id
,jsr.tp_organization_id
,alloc.shikyu_component_id
FROM jmf_shikyu_allocations alloc
,jmf_shikyu_replenishments jsr
,jmf_shikyu_cfr_mid_temp mid
,jmf_subcontract_orders jso
,wip_requirement_operations wro
WHERE mid.row_type = p_sub_po_unconsumed_row_type
AND NVL(mid.get_rep_flag
,'N') <> CFR_SUB_PO_GET_REP_FLAG
AND mid.shikyu_id = alloc.subcontract_po_shipment_id
AND mid.item_id = alloc.shikyu_component_id
AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
AND jsr.oem_organization_id = p_oem_inv_org_id
AND jsr.tp_organization_id = p_tp_inv_org_id
AND jsr.tp_supplier_id = p_supplier_id
AND jsr.tp_supplier_site_id = p_supplier_site_id
AND alloc.shikyu_component_id = p_item_id
AND alloc.subcontract_po_shipment_id =
jso.subcontract_po_shipment_id
AND jso.wip_entity_id = wro.wip_entity_id(+)
AND jso.tp_organization_id = wro.organization_id(+)
AND ((wro.operation_seq_num IS NULL) OR
(wro.operation_seq_num = 1))
AND wro.repetitive_schedule_id IS NULL
AND alloc.shikyu_component_id = wro.inventory_item_id
--This may cause issue when same components are found in different tp organization,the onhand component are found
-- secondly,will be lost in them mid_temp table.That means in the report will lose some datas which should be displayed.
-- updated to fix this potensial issue.
/* AND NOT
((jsr.replenishment_po_shipment_id IN
(SELECT jscmt_s.shikyu_id
FROM jmf_shikyu_cfr_mid_temp jscmt_s
WHERE jscmt_s.row_type = p_rep_po_unconsumed_row_type)) AND
(alloc.shikyu_component_id IN
(SELECT jscmt_i.item_id
FROM jmf_shikyu_cfr_mid_temp jscmt_i
WHERE jscmt_i.row_type = p_rep_po_unconsumed_row_type)));
(SELECT jscmt_s.shikyu_id
FROM jmf_shikyu_cfr_mid_temp jscmt_s
WHERE jscmt_s.row_type = p_rep_po_unconsumed_row_type
AND jscmt_s.item_id = p_item_id));
UPDATE jmf_shikyu_cfr_mid_temp
SET quantity = NVL(primary_unconsumed_quantity,0) *
JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(primary_uom
,uom
,item_id)
WHERE row_type = p_rep_po_unconsumed_row_type
AND quantity IS NULL --only for those do not get the quantity of uom
AND supplier_id = p_supplier_id
AND site_id = p_supplier_site_id
AND oem_inv_org_id = p_oem_inv_org_id
AND tp_inv_org_id = p_tp_inv_org_id
AND item_id = p_item_id;
SELECT SUM(NVL(cfr_mid.primary_unconsumed_quantity
,0))
INTO l_sub_po_residual_pri
FROM jmf_shikyu_cfr_mid_temp cfr_mid
,rcv_transactions rcv
,jmf_shikyu_allocations alloc
,jmf_shikyu_replenishments jsr
WHERE rcv.transaction_id = P_rcv_transaction_id
AND rcv.transaction_type = 'RECEIVE'
AND cfr_mid.row_type = p_sub_po_unconsumed_row_type
AND cfr_mid.supplier_id = p_supplier_id
AND cfr_mid.site_id = p_supplier_site_id
AND cfr_mid.oem_inv_org_id = p_oem_inv_org_id
AND cfr_mid.tp_inv_org_id = p_tp_inv_org_id
AND cfr_mid.item_id = p_item_id
AND cfr_mid.shikyu_id = alloc.subcontract_po_shipment_id
AND cfr_mid.item_id = alloc.shikyu_component_id
AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
AND jsr.replenishment_po_shipment_id = rcv.po_line_location_id;
SELECT cfr_mid.shikyu_id
,cfr_mid.primary_unconsumed_quantity
FROM jmf_shikyu_cfr_mid_temp cfr_mid
,rcv_transactions rcv
,jmf_shikyu_allocations alloc
,jmf_shikyu_replenishments jsr
WHERE rcv.transaction_id = p_rcv_transaction_id
AND rcv.transaction_type = 'RECEIVE'
AND cfr_mid.row_type = p_sub_po_unconsumed_row_type
AND cfr_mid.supplier_id = p_supplier_id
AND cfr_mid.site_id = p_supplier_site_id
AND cfr_mid.oem_inv_org_id = p_oem_inv_org_id
AND cfr_mid.tp_inv_org_id = p_tp_inv_org_id
AND cfr_mid.item_id = p_item_id
AND cfr_mid.shikyu_id = alloc.subcontract_po_shipment_id
AND cfr_mid.item_id = alloc.shikyu_component_id
AND alloc.replenishment_so_line_id = jsr.replenishment_so_line_id
AND jsr.replenishment_po_shipment_id = rcv.po_line_location_id
AND cfr_mid.primary_unconsumed_quantity > 0
ORDER BY cfr_mid.shikyu_id DESC;
UPDATE jmf_shikyu_cfr_mid_temp
-- Updated to fix potential issue of operations between null numbers
-- SET quantity = quantity -
-- (quantity * l_cur_sub_po_consumed_pri /
SET quantity = NVL(quantity,0) -
( NVL(quantity,0) * l_cur_sub_po_consumed_pri /
primary_unconsumed_quantity) --possible unconsumed for UOM
,primary_unconsumed_quantity = NVL(primary_unconsumed_quantity,0) -
l_cur_sub_po_consumed_pri --possible unconsumed for primary UOM
WHERE row_type = p_sub_po_unconsumed_row_type
AND shikyu_id = l_sub_po_id
AND oem_inv_org_id = p_oem_inv_org_id
AND tp_inv_org_id = p_tp_inv_org_id
AND item_id = p_item_id;
SELECT COUNT(*)
INTO l_jmf_cfr_mid_temp_rcv_rows
FROM jmf_shikyu_cfr_mid_temp
WHERE row_type = p_rcv_row_type
AND shikyu_id = p_rcv_transaction_id;
UPDATE jmf_shikyu_cfr_mid_temp
-- Updated to fix potential issue of operations between null numbers
-- SET primary_unconsumed_quantity = primary_unconsumed_quantity +
SET primary_unconsumed_quantity = NVL(primary_unconsumed_quantity,0) +
p_rcv_unconsumed_pri
WHERE row_type = p_rcv_row_type
AND shikyu_id = p_rcv_transaction_id;
INSERT INTO jmf_shikyu_cfr_mid_temp
(row_type
,shikyu_id
,primary_unconsumed_quantity
,oem_inv_org_id
,tp_inv_org_id
,item_id
,supplier_id
,site_id)
VALUES
(p_rcv_row_type
,p_rcv_transaction_id
,p_rcv_unconsumed_pri
,p_oem_inv_org_id
,p_tp_inv_org_id
,p_item_id
,p_supplier_id
,p_supplier_site_id);
UPDATE jmf_shikyu_cfr_mid_temp jscmt
SET jscmt.uom = (SELECT rt.unit_of_measure
FROM rcv_transactions rt
WHERE jscmt.row_type = CFR_TMP_RCV_ROW
AND rt.transaction_id = jscmt.shikyu_id)
,jscmt.primary_uom = (SELECT rt.primary_unit_of_measure
FROM rcv_transactions rt
WHERE jscmt.row_type = CFR_TMP_RCV_ROW
AND rt.transaction_id = jscmt.shikyu_id)
WHERE jscmt.row_type = CFR_TMP_RCV_ROW;
UPDATE jmf_shikyu_cfr_mid_temp
SET quantity = (NVL(primary_unallocated_quantity
,0) + NVL(primary_unconsumed_quantity
,0)) *
JMF_SHIKYU_RPT_UTIL.po_uom_convert_p(JMF_SHIKYU_RPT_UTIL.uom_to_code(primary_uom)
,JMF_SHIKYU_RPT_UTIL.uom_to_code(uom)
,item_id)
WHERE row_type = CFR_TMP_RCV_ROW;
INSERT INTO jmf_shikyu_cfr_rpt_temp
(rpt_mode
,TRANSACTION_ID
,RPT_DATA_TYPE
,oem_inv_org_id
,oem_inv_org_code
,supplier_id
,supplier_name
,site_id
,site_code
,site_address
,contact_id
,contact_name
,tp_inv_org_id
,tp_inv_org_code
,item_id
,item_number
,item_description
,currency_code
,functional_currency
,shikyu_price
,item_cost --the cost based on UOM after conversion from Pri_Uom
,value1 --the item qty in Pri Uom
,value2 --the item price in Pri UOM and Pri Currency
,value3 --the item cost in Pri UOM and Pri Currency, from cst_item_costs table
,project_id
,project_num
,task_id
,task_num
,uom
,ESTIMATED_QTY
,primary_uom
,REP_SO_HEADER_ID
,REP_SO_NUMBER
,REP_SO_VERSION_NUMBER
,REP_SO_LINE_ID
,REP_SO_LINE
,SHIPPED_DATE
,EXPECTED_RCV_DATE --the Rep PO need by date
,REP_PO_HEADER_ID
,REP_PO_NUMBER
,REP_PO_REVISION_NUM
,REP_PO_RELEASE_ID
,REP_PO_RELEASE_NUM
,REP_PO_LINE_ID
,REP_PO_LINE
,REP_PO_LINE_LOCATION_ID
,REP_PO_SHIPMENT
,REP_PO_DISTRIBUTION_ID
,SUBPO_HEADER_ID
,SUBPO_NUMBER
,SUBPO_LINE_ID
,SUBPO_LINE_NUM
,SUBPO_RELEASE_ID
,SUBPO_RELEASE_NUM
,SUBPO_SHIPMENT_ID
,SUBPO_SHIPMENT_NUM)
SELECT p_rpt_mode rpt_mode
,cfr_mid.shikyu_id TRANSACTION_ID
,CFR_CRUDE_DATA RPT_DATA_TYPE
,cfr_mid.oem_inv_org_id oem_inv_org_id
,mp_oem.organization_code oem_org_code
,cfr_mid.supplier_id supplier_id
,pv.vendor_name supplier_name
,cfr_mid.site_id site_id
,pvs.vendor_site_code site_code
,pvs.address_line1 || ',' || pvs.address_line2 || ',' ||
pvs.address_line3 site_address
,ph.vendor_contact_id contact_id
,pvc.prefix || ' ' || pvc.first_name || ',' || pvc.middle_name || ',' ||
pvc.middle_name || ',' || pvc.last_name contact_name
,cfr_mid.tp_inv_org_id tp_inv_org_id
,mp_tp.organization_code tp_inv_org_code
,cfr_mid.item_id item_id
,JMF_SHIKYU_RPT_UTIL.get_item_number(cfr_mid.tp_inv_org_id
,cfr_mid.item_id) item_number
,item_v.description item_description
,ooha.transactional_curr_code currency_code --or jmf_shikyu_components.Currency ,not ph.currency_code
,p_functional_currency functional_currency
,oola.unit_selling_price po_unit_price --not pl.unit_price
,JMF_SHIKYU_RPT_UTIL.convert_amount(ooha.transactional_curr_code
,p_functional_currency
--Amy update for fixing currency conversing issue start
--when paramter p_currency_conversion_date and p_currency_conversion_type are not specified,
-- use sysdate and conversion_type in so as default value.
--,p_currency_conversion_date
--,p_currency_conversion_type
,decode(p_currency_conversion_date,null,sysdate,p_currency_conversion_date)
,decode(p_currency_conversion_type,null,ooha.CONVERSION_TYPE_CODE,p_currency_conversion_type)
--Amy update for fixing currency conversing issue end
,NVL(cic.item_cost
,0) *
PO_UOM_S.po_uom_convert_p(cfr_mid.primary_uom
,cfr_mid.uom
,cfr_mid.item_id)) item_cost --the cost based on UOM after conversion from Pri_Uom
,(cfr_mid.quantity *
PO_UOM_S.po_uom_convert_p(cfr_mid.uom
,cfr_mid.primary_uom
,cfr_mid.item_id)) value1 --item qty in Primary
,JMF_SHIKYU_RPT_UTIL.convert_amount(ooha.transactional_curr_code
,p_functional_currency
--Amy update for fixing currency conversing issue start
--when paramter p_currency_conversion_date and p_currency_conversion_type are not specified,
-- use sysdate and conversion_type in so as default value.
--,p_currency_conversion_date
--,p_currency_conversion_type
,decode(p_currency_conversion_date,null,sysdate,p_currency_conversion_date)
,decode(p_currency_conversion_type,null,ooha.CONVERSION_TYPE_CODE,p_currency_conversion_type)
--Amy update for fixing currency conversing issue end
,oola.unit_selling_price *
PO_UOM_S.po_uom_convert_p(cfr_mid.uom
,cfr_mid.primary_uom
,cfr_mid.item_id)) value2 --the item price in Pri UOM and Pri Currency
,NVL(cic.item_cost
,0) value3 --Standard_Item_Cost in Pri UOM and Pri Currency
--updated to fix project_id related issue start
--,rcv.project_id project_id
,sub.project_id project_id
--updated to fix project_id related issue start
--updated to fix project_number related issue start
--,prj.segment1 project_number
,NVL((SELECT DISTINCT segment1 AS project_number
FROM pa_projects_all
WHERE pa_projects_all.project_id(+) = sub.project_id),
(SELECT DISTINCT project_number
FROM pjm_seiban_numbers
WHERE pjm_seiban_numbers.project_id(+) = sub.project_id)) project_number
--updated to fix project_number related issue end
--updated to fix project_id related issue start
--,rcv.task_id task_id
,sub.task_id task_id
--updated to fix project_id related issue start
,task.task_number task_number
,cfr_mid.uom --should jmf_shikyu_components.uom,need rcv.unit_of_measure to conversion?
,cfr_mid.quantity --the SHIKYU component quantity find in rcv for unallocated + unconsumed
,cfr_mid.primary_uom --rcv.primary_unit_of_measure
,oola.header_id RepSO_header_id
,ooha.order_number REP_SO_NUMBER
,ooha.version_number RepSO_Version_number
,oola.line_id RepSO_line_id
,oola.line_number REP_SO_LINE
,oola.actual_shipment_date SHIPPED_DATE
,poloc.need_by_date --,EXPECTED_RCV_DATE
,rcv.po_header_id RepPO_header_id
,ph.segment1 REP_PO_NUMBER
,rcv.po_revision_num RepPO_Revision_num
,rcv.po_release_id RepPO_Release_id
,pra.release_num REP_PO_RELEASE
,rcv.po_line_id RepPO_Line_id
,pl.line_num REP_PO_LINE
,rcv.po_line_location_id RepPO_line_location_id
,poloc.shipment_num REP_PO_SHIPMENT
,rcv.po_distribution_id RepPO_distribution_id
,pha_s.po_header_id SubPO_header_id
,pha_s.segment1 SubPO_Number
,pla_s.po_line_id SubPO_line_id
,pla_s.line_num SubPO_Line_num
,pra_s.po_release_id SubPO_release_id
,pra_s.release_num SubPO_release_Num
,plla_s.line_location_id SubPO_shipment_id
,plla_s.shipment_num SubPO_shipment_num
FROM jmf_shikyu_cfr_mid_temp cfr_mid
,jmf_subcontract_orders sub
,po_line_locations_all poloc
,rcv_transactions rcv
,mtl_parameters mp_oem
,mtl_parameters mp_tp
,po_vendors pv
,po_vendor_sites_all pvs
,po_headers_all ph
,po_vendor_contacts pvc
,mtl_system_items_vl item_v
,po_lines_all pl
,pa_projects_all prj
,pa_tasks task
,po_releases_all pra
,jmf_shikyu_replenishments jsr
,oe_order_lines_all oola
,oe_order_headers_all ooha
,jmf_shikyu_allocations jsa
,po_line_locations_all plla_s
,po_headers_all pha_s
,po_lines_all pla_s
,po_releases_all pra_s
,cst_item_costs cic
WHERE cfr_mid.row_type = CFR_TMP_RCV_ROW
AND cfr_mid.oem_inv_org_id = mp_oem.organization_id
AND cfr_mid.tp_inv_org_id = mp_tp.organization_id
AND cfr_mid.supplier_id = pv.vendor_id(+)
AND cfr_mid.site_id = pvs.vendor_site_id(+)
AND cfr_mid.shikyu_id = rcv.transaction_id
AND rcv.transaction_type = 'RECEIVE'
AND rcv.po_header_id = ph.po_header_id
AND ph.vendor_contact_id = pvc.vendor_contact_id(+)
AND cfr_mid.tp_inv_org_id = item_v.organization_id
AND cfr_mid.item_id = item_v.inventory_item_id
AND rcv.po_line_location_id = poloc.line_location_id
AND pl.po_line_id = poloc.po_line_id
AND rcv.project_id = prj.project_id(+)
AND rcv.task_id = task.task_id(+)
AND poloc.po_release_id = pra.po_release_id(+)
AND poloc.line_location_id = jsr.replenishment_po_shipment_id
AND jsr.replenishment_so_line_id = oola.line_id
AND oola.header_id = ooha.header_id
--Updated to fix bug 5509464 start
/* AND jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
AND jsa.subcontract_po_shipment_id = plla_s.line_location_id
AND sub.subcontract_po_shipment_id = plla_s.line_location_id
AND plla_s.po_header_id = pha_s.po_header_id
AND plla_s.po_line_id = pla_s.po_line_id*/
AND jsa.replenishment_so_line_id(+) = jsr.replenishment_so_line_id
AND jsa.subcontract_po_shipment_id = plla_s.line_location_id(+)
AND plla_s.line_location_id = sub.subcontract_po_shipment_id(+)
AND plla_s.po_header_id = pha_s.po_header_id(+)
AND plla_s.po_line_id = pla_s.po_line_id(+)
--Updated to fix bug 5509464 end
AND plla_s.po_release_id = pra_s.po_release_id(+)
AND oola.inventory_item_id = cic.inventory_item_id(+)
AND oola.ship_from_org_id = cic.organization_id(+);
text="should insert the onhand items that without SubContracting Order."
*/
COMMIT; -- for debug on UT ?????
INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
(rpt_mode
,RPT_DATA_TYPE
,oem_inv_org_id
,supplier_id
,site_id
,contact_id
,tp_inv_org_id
,item_id
,shikyu_price
,currency_code
,uom
,project_id
--added to fix project_number related issue start
,project_num
--added to fix project_number related issue end
,task_id
--added to fix project_number related issue start
,task_num
--added to fix project_number related issue end
,primary_uom
,ESTIMATED_QTY)
--value SUM(temp.estimated_qty) are supposed to onhand_quantity at item/price level,but got incorrect quantity.
--got incorrect onhand quantity due to use inapposite group.
--updated select statement to add transaction_id into group by to get correct onhand quantity.
/*
TODO: owner="amy" category="Fix" priority="2 - Severe Loss of Service" created="2006-6-22"
text="--updated for fix bug 5231233 Begin"
*/
/* SELECT p_rpt_mode RPT_MODE --temp.rpt_mode
,CFR_EXT_COMPONENT RPT_DATA_TYPE
,temp.oem_inv_org_id shikyu_oem_inv_org_id
,temp.supplier_id shikyu_supplier_id
,temp.site_id shikyu_site_id
,temp.contact_id
,temp.tp_inv_org_id shikyu_tp_inv_org_id
,temp.item_id shikyu_item_id
,temp.shikyu_price shikyu_price
,temp.currency_code shikyu_currency
,temp.uom shikyu_uom
,temp.project_id
,temp.task_id
,temp.primary_uom shikyu_primary_uom
,SUM(temp.estimated_qty) shikyu_estimated_qty
FROM JMF_SHIKYU_CFR_RPT_TEMP temp
WHERE temp.rpt_DATA_TYPE = CFR_CRUDE_DATA
GROUP BY temp.rpt_mode
,temp.oem_inv_org_id
,temp.supplier_id
,temp.site_id
,temp.contact_id
,temp.tp_inv_org_id
,temp.item_id
,temp.shikyu_price
,temp.currency_code
,temp.uom
,temp.project_id
,temp.task_id
,temp.primary_uom;
SELECT p_rpt_mode RPT_MODE --temp.rpt_mode
,CFR_EXT_COMPONENT RPT_DATA_TYPE
,temp.oem_inv_org_id shikyu_oem_inv_org_id
,temp.supplier_id shikyu_supplier_id
,temp.site_id shikyu_site_id
,temp.contact_id
,temp.tp_inv_org_id shikyu_tp_inv_org_id
,temp.item_id shikyu_item_id
,temp.shikyu_price shikyu_price
,temp.currency_code shikyu_currency
,temp.uom shikyu_uom
,temp.project_id
--added to fix project_number related issue start
,temp.project_num
--added to fix project_number related issue end
,temp.task_id
--added to fix project_number related issue start
,temp.task_num
--added to fix project_number related issue end
,temp.primary_uom shikyu_primary_uom
,SUM(temp.estimated_qty) shikyu_estimated_qty
FROM (SELECT rpt_temp.rpt_mode rpt_mode
,rpt_temp.oem_inv_org_id oem_inv_org_id
,rpt_temp.supplier_id supplier_id
,rpt_temp.site_id site_id
,rpt_temp.contact_id contact_id
,rpt_temp.tp_inv_org_id tp_inv_org_id
,rpt_temp.item_id item_id
,rpt_temp.shikyu_price shikyu_price
,rpt_temp.currency_code currency_code
,rpt_temp.uom uom
,rpt_temp.project_id project_id
--added to fix project_number related issue start
,rpt_temp.project_num project_num
--added to fix project_number related issue end
,rpt_temp.task_id task_id
--added to fix project_number related issue start
,rpt_temp.task_num task_num
--added to fix project_number related issue end
,rpt_temp.primary_uom primary_uom
,rpt_temp.estimated_qty
,rpt_temp.transaction_id transaction_id
FROM JMF_SHIKYU_CFR_RPT_TEMP rpt_temp
WHERE rpt_temp.rpt_DATA_TYPE = CFR_CRUDE_DATA
GROUP BY rpt_temp.rpt_mode
,rpt_temp.oem_inv_org_id
,rpt_temp.supplier_id
,rpt_temp.site_id
,rpt_temp.contact_id
,rpt_temp.tp_inv_org_id
,rpt_temp.item_id
,rpt_temp.shikyu_price
,rpt_temp.currency_code
,rpt_temp.uom
,rpt_temp.project_id
--added to fix project_number related issue start
,rpt_temp.project_num
--added to fix project_number related issue end
,rpt_temp.task_id
--added to fix project_number related issue start
,rpt_temp.task_num
--added to fix project_number related issue end
,rpt_temp.primary_uom
,rpt_temp.estimated_qty
,rpt_temp.transaction_id) temp
GROUP BY temp.rpt_mode
,temp.oem_inv_org_id
,temp.supplier_id
,temp.site_id
,temp.contact_id
,temp.tp_inv_org_id
,temp.item_id
,temp.shikyu_price
,temp.currency_code
,temp.uom
,temp.project_id
--added to fix project_number related issue start
,temp.project_num
--added to fix project_number related issue end
,temp.task_id
--added to fix project_number related issue start
,temp.task_num
--added to fix project_number related issue end
,temp.primary_uom;
text="--updated for fix bug 5231233 Begin"
*/
-- Deleted Update to fix bug 5665445 for incorrect onhand Qty in Secondary UOM case
/* UPDATE JMF_SHIKYU_CFR_RPT_TEMP jscrt
SET jscrt.estimated_qty = PO_UOM_S.po_uom_convert_p(jscrt.primary_uom
,jscrt.uom
,jscrt.item_id) *jscrt.ESTIMATED_QTY,
(SELECT jscmt.primary_unconsumed_quantity
FROM jmf_shikyu_cfr_mid_temp jscmt
WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
AND jscmt.tp_inv_org_id =
jscrt.tp_inv_org_id
AND jscmt.item_id = jscrt.item_id),
jscrt.value1 = (SELECT jscmt.primary_unconsumed_quantity
FROM jmf_shikyu_cfr_mid_temp jscmt
WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
AND jscmt.tp_inv_org_id =
jscrt.tp_inv_org_id
AND jscmt.item_id = jscrt.item_id)
jscrt.value1 = jscrt.ESTIMATED_QTY
--updated for fix bug 5231233 End
WHERE jscrt.rpt_data_type = CFR_EXT_COMPONENT; --should not CFR_INT_COMPONENT;
UPDATE JMF_SHIKYU_CFR_RPT_TEMP temp
SET temp.item_number = (SELECT msibk.concatenated_segments
FROM MTL_SYSTEM_ITEMS_B_KFV msibk
WHERE temp.tp_inv_org_id =
msibk.organization_id
AND temp.item_id =
msibk.inventory_item_id)
,temp.item_description = (SELECT msibk.description
FROM MTL_SYSTEM_ITEMS_B_KFV msibk
WHERE temp.tp_inv_org_id =
msibk.organization_id
AND temp.item_id =
msibk.inventory_item_id)
,temp.replenishment_type = (SELECT flv.meaning
FROM fnd_lookup_values flv
,MTL_SYSTEM_ITEMS_B_KFV msibk
WHERE flv.LANGUAGE = USERENV('LANG')
AND flv.lookup_type =
'JMF_SHK_ITEM_REPLEN_TYPE'
AND msibk.subcontracting_component =
flv.lookup_code
AND temp.tp_inv_org_id =
msibk.organization_id
AND temp.item_id =
msibk.inventory_item_id)
WHERE temp.rpt_DATA_TYPE = CFR_EXT_COMPONENT;
INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
(RPT_MODE
,RPT_DATA_TYPE
,oem_inv_org_id
,supplier_id
,site_id
,tp_inv_org_id
,item_id
,shikyu_price
,currency_code
,uom
,project_id
--added to fix project_number related issue start
,project_num
--added to fix project_number related issue end
,task_id
--added to fix project_number related issue start
,task_num
--added to fix project_number related issue end
,primary_uom
,subpo_header_id
,subpo_number
,subpo_line_num
,subpo_release_num
,subpo_shipment_num
,OSA_ITEM_ID
,OSA_ITEM_NUMBER
,OSA_ITEM_DESCRIPTION
,REQUESTED_COMP_QTY
,ISSUED_COMP_QTY)
SELECT DISTINCT p_rpt_mode RPT_MODE --temp.rpt_mode
,CFR_EXT_SUBCONTRACT_PO RPT_DATA_TYPE
,cfr_mid_item_group.oem_inv_org_id oem_inv_org_id
,cfr_mid_item_group.supplier_id supplier_id
,cfr_mid_item_group.site_id site_id
,cfr_mid_item_group.tp_inv_org_id tp_inv_org_id
,cfr_mid_item_group.item_id item_id
,oola.unit_selling_price po_unit_price --not pl.unit_price
,ooha.transactional_curr_code currency_code --or jmf_shikyu_components.Currency ,not ph.currency_code
,cfr_mid_item_group.uom --should jmf_shikyu_components.uom,need rcv.unit_of_measure to conversion?
,jso.project_id project_id
--Added to fix project_number related issue start
,NVL((SELECT DISTINCT segment1 AS project_number
FROM pa_projects_all
WHERE pa_projects_all.project_id(+) = jso.project_id),
(SELECT DISTINCT project_number
FROM pjm_seiban_numbers
WHERE pjm_seiban_numbers.project_id(+) = jso.project_id)) project_number
--Added to fix project_number related issue end
,jso.task_id task_id
--Added to get task number start
,task.task_number task_number
--Added to get task number end
,cfr_mid_item_group.primary_uom --rcv.primary_unit_of_measure
,pha_s.po_header_id SubPO_header_id
,pha_s.segment1 SubPO_Number
,pla_s.line_num SubPO_Line_num
,pra_s.release_num SubPO_release_Num
,plla_s.shipment_num SubPO_shipment_NUm
,jso.osa_item_id
,msibk.concatenated_segments
,msibk.description
,wro.required_quantity
,wro.quantity_issued
FROM jmf_subcontract_orders jso
,jmf_shikyu_components jsc
,(select DISTINCT cfr_mid_temp.oem_inv_org_id oem_inv_org_id
,cfr_mid_temp.supplier_id supplier_id
,cfr_mid_temp.site_id site_id
,cfr_mid_temp.tp_inv_org_id tp_inv_org_id
,cfr_mid_temp.item_id item_id
,cfr_mid_temp.uom
,cfr_mid_temp.primary_uom
from jmf_shikyu_cfr_mid_temp cfr_mid_temp
where cfr_mid_temp.row_type = CFR_TMP_RCV_ROW) cfr_mid_item_group
,jmf_shikyu_replenishments jsr
,jmf_shikyu_allocations jsa
,oe_order_lines_all oola
,oe_order_headers_all ooha
,po_line_locations_all plla_s
,po_headers_all pha_s
,po_lines_all pla_s
,po_releases_all pra_s
,wip_requirement_operations wro
,MTL_SYSTEM_ITEMS_B_KFV msibk
,rcv_transactions rt
--Added to get task start
,pa_tasks task
--Added to get task end
WHERE jsc.SHIKYU_COMPONENT_ID = cfr_mid_item_group.item_id
AND jsc.OEM_ORGANIZATION_ID = cfr_mid_item_group.oem_inv_org_id
AND jsc.UOM = JMF_SHIKYU_RPT_UTIL.uom_to_code(cfr_mid_item_group.uom)
AND jsc.PRIMARY_UOM = JMF_SHIKYU_RPT_UTIL.uom_to_code(cfr_mid_item_group.primary_uom)
AND jso.TP_ORGANIZATION_ID = cfr_mid_item_group.tp_inv_org_id
AND jso.OEM_ORGANIZATION_ID = cfr_mid_item_group.oem_inv_org_id
AND jso.SUBCONTRACT_PO_SHIPMENT_ID = jsc.SUBCONTRACT_PO_SHIPMENT_ID
AND jsc.SUBCONTRACT_PO_SHIPMENT_ID = jsa.SUBCONTRACT_PO_SHIPMENT_ID
AND jsc.SHIKYU_COMPONENT_ID = jsa.SHIKYU_COMPONENT_ID
AND jsa.REPLENISHMENT_SO_LINE_ID = jsr.REPLENISHMENT_SO_LINE_ID
AND jsr.REPLENISHMENT_SO_LINE_ID = oola.LINE_ID
AND oola.HEADER_ID = ooha.HEADER_ID
AND plla_s.LINE_LOCATION_ID = jsa.SUBCONTRACT_PO_SHIPMENT_ID
AND pla_s.PO_LINE_ID=plla_s.PO_LINE_ID
AND pla_s.PO_HEADER_ID=plla_s.PO_HEADER_ID
AND pha_s.PO_HEADER_ID=pla_s.PO_HEADER_ID
AND plla_s.po_release_id = pra_s.po_release_id(+)
AND jso.osa_item_id = msibk.inventory_item_id
AND jso.tp_organization_id = wro.organization_id
AND jso.wip_entity_id = wro.wip_entity_id
AND jso.interlock_status = 'C' --added to fix bug 5415777
AND wro.operation_seq_num = 1
AND wro.repetitive_schedule_id IS NULL
AND cfr_mid_item_group.item_id = wro.inventory_item_id
AND plla_s.QUANTITY_RECEIVED>0
and plla_s.LINE_LOCATION_ID = rt.PO_LINE_LOCATION_ID
and rt.transaction_date < sysdate+1--period to date
and rt.transaction_date >= sysdate-p_days_received --period from date
--Added to get task start
AND jso.task_id = task.task_id(+)
--Added to get task end
;
INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
(rpt_mode
,rpt_data_type
,Oem_Inv_Org_Id
,Tp_Inv_Org_Id
,Supplier_Id
,Site_Id
,rep_so_header_id
,rep_so_number
,rep_so_line_id
,rep_so_line
,rep_po_header_id
,rep_po_number
,rep_po_line_id
,rep_po_line
,rep_po_line_location_id
,rep_po_shipment
,rep_po_release_id
,rep_po_release_num
,item_id
,item_number
,estimated_qty
,uom
,shipped_date
,expected_rcv_date)
SELECT p_rpt_mode rpt_mode
,CFR_EXT_UN_RCV rpt_data_type
,jsr.oem_organization_id -- = oola.ship_from_org_id
,jsr.tp_organization_id -- = plla.ship_to_organization_id
,hoi.org_information3 -- the tp org 's supplier = SubPO supplier
,hoi.org_information4 -- the tp org 's supplier site = SubPO site
,ooha.header_id rep_so_header_id
,ooha.order_number rep_so_number
,oola.line_id rep_so_line_id
,oola.line_number rep_so_line
,pha.po_header_id rep_po_header_id
,pha.segment1 rep_po_number
,pla.po_line_id rep_po_line_id
,pla.line_num rep_po_line
,plla.line_location_id rep_po_line_location_id
,plla.shipment_num rep_po_shipment
,pra.po_release_id rep_po_release_id
,pra.release_num rep_po_release_num
,oola.inventory_item_id item_id --jsr.shikyu_component_id
,oola.ordered_item item_number
,oola.shipped_quantity estimated_qty
,oola.order_quantity_uom uom
,oola.actual_shipment_date shipped_date
,NVL(plla.need_by_date
,plla.promised_date) expected_rcv_date
FROM oe_order_lines_all oola
,oe_order_headers_all ooha
,po_line_locations_all plla
,po_lines_all pla
,po_releases_all pra
,po_headers_all pha
,jmf_shikyu_replenishments jsr
,hr_organization_information hoi
-- Amy added to fix bug 5583680 start
,hr_all_organization_units_tl oem_haoutl
,hr_organization_information tp_hoi
,po_vendors pv
,po_vendor_sites_all pvs
-- Amy added to fix bug 5583680 end
WHERE oola.header_id = ooha.header_id
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
AND plla.po_release_id = pra.po_release_id(+)
AND oola.line_id = jsr.replenishment_so_line_id
AND plla.line_location_id = jsr.replenishment_po_shipment_id
AND jsr.tp_organization_id = hoi.organization_id
AND hoi.org_information_context = 'Customer/Supplier Association'
-- Amy updated to fix bug 5583680 start
/*AND (SELECT COUNT(*)
FROM JMF_SHIKYU_CFR_RPT_TEMP jscrt
WHERE jscrt.rpt_data_type = CFR_EXT_COMPONENT
AND jscrt.oem_inv_org_id = jsr.oem_organization_id
AND jscrt.tp_inv_org_id = jsr.tp_organization_id) > 0
AND JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(oola.sold_from_org_id
,oola.inventory_item_id
,oola.order_quantity_uom
,NVL(oola.shipped_quantity
,0)) >
JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(plla.ship_to_organization_id
,pla.item_id
,JMF_SHIKYU_RPT_UTIL.uom_to_code(pla.unit_meas_lookup_code)
,NVL(plla.quantity_received
,0));*/
-- Amy updated to fix bug 5583680 end
COMMIT; -- for debug on UT ?????
INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
(rpt_mode
,rpt_data_type
,Oem_Inv_Org_Id
,Tp_Inv_Org_Id
,Supplier_Id
,Site_Id
,rep_so_header_id
,rep_so_number
,rep_so_line_id
,rep_so_line
,rep_po_header_id
,rep_po_number
,rep_po_line_id
,rep_po_line
,rep_po_line_location_id
,rep_po_shipment
,rep_po_release_id
,rep_po_release_num
,item_id
,item_number
,estimated_qty
,uom
,shipped_date
,expected_rcv_date)
SELECT p_rpt_mode rpt_mode
,CFR_EXT_RCV_IN_DAYS rpt_data_type
,jsr.oem_organization_id -- = oola.ship_from_org_id
,jsr.tp_organization_id -- = plla.ship_to_organization_id
,hoi.org_information3 -- the tp org 's supplier = SubPO supplier
,hoi.org_information4 -- the tp org 's supplier site = SubPO site
,ooha.header_id rep_so_header_id
,ooha.order_number rep_so_number
,oola.line_id rep_so_line_id
,oola.line_number rep_so_line
,pha.po_header_id rep_po_header_id
,pha.segment1 rep_po_number
,pla.po_line_id rep_po_line_id
,pla.line_num rep_po_line
,plla.line_location_id rep_po_line_location_id
,plla.shipment_num rep_po_shipment
,pra.po_release_id rep_po_release_id
,pra.release_num rep_po_release_num
,oola.inventory_item_id item_id --jsr.shikyu_component_id
,oola.ordered_item item_number
,oola.shipped_quantity estimated_qty
,oola.order_quantity_uom uom
,oola.actual_shipment_date shipped_date
,NVL(plla.need_by_date
,plla.promised_date) expected_rcv_date
FROM oe_order_lines_all oola
,oe_order_headers_all ooha
,po_line_locations_all plla
,po_lines_all pla
,po_releases_all pra
,po_headers_all pha
,jmf_shikyu_replenishments jsr
,hr_organization_information hoi
WHERE oola.header_id = ooha.header_id
AND plla.po_line_id = pla.po_line_id
AND plla.po_header_id = pha.po_header_id
AND plla.po_release_id = pra.po_release_id(+)
AND oola.line_id = jsr.replenishment_so_line_id
AND plla.line_location_id = jsr.replenishment_po_shipment_id
AND jsr.tp_organization_id = hoi.organization_id
AND hoi.org_information_context = 'Customer/Supplier Association'
AND (SELECT COUNT(*)
FROM JMF_SHIKYU_CFR_RPT_TEMP jscrt
WHERE jscrt.rpt_data_type = CFR_EXT_COMPONENT
AND jscrt.oem_inv_org_id = jsr.oem_organization_id
AND jscrt.tp_inv_org_id = jsr.tp_organization_id) > 0
AND (SYSDATE - NVL(plla.need_by_date
,plla.promised_date)) <= p_days_received
-- Bug 5583680: Fixed data issue for the Received Replenishments in
-- Past xx Days section
AND NVL(oola.shipped_quantity,0) > 0
AND NVL(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(jsr.oem_organization_id
,oola.inventory_item_id
,oola.order_quantity_uom
,NVL(oola.shipped_quantity
,0)),0) =
NVL(JMF_SHIKYU_RPT_UTIL.get_item_primary_quantity(plla.ship_to_organization_id
,pla.item_id
,JMF_SHIKYU_RPT_UTIL.uom_to_code(pla.unit_meas_lookup_code)
,NVL(plla.quantity_received
,0)),0);
INSERT INTO jmf_shikyu_cfr_rpt_temp
(rpt_mode
,rpt_data_type
,oem_inv_org_id
,oem_inv_org_code
,oem_inv_org_name
,oem_inv_org_address
,supplier_id
,supplier_name
,site_id
,site_code
,site_address
,tp_inv_org_id
,tp_inv_org_code
,project_id
,project_num
,task_id
,task_num
,item_id
,item_number
,item_description
,estimated_qty
,primary_uom
,shikyu_price
,currency_code
,uom
,item_cost
,functional_currency
,value1 --Qty in Primary UOM
,value2 --SHIKYU Price in Pri UOM and Pri Currency
,value3) --SHIKYU Cost in Pri UOM and Pri Currency
--value SUM(temp.estimated_qty) are supposed to onhand_quantity at item/price level,but got incorrect quantity.
--got incorrect onhand quantity due to use inapposite group.
--updated select statement to add transaction_id into group by to get correct onhand quantity.
/*
TODO: owner="amy" category="Fix" priority="2 - Severe Loss of Service" created="2006-6-22"
text="--updated for fix bug 5231233 Begin"
*/
/* SELECT p_rpt_mode rpt_mode
,CFR_INT_COMPONENT rpt_data_type
,jscrt.oem_inv_org_id Iss_oem_inv_org_id
,jscrt.oem_inv_org_code Iss_oem_inv_org_code
,haou.name Iss_oem_inv_org_name
,jscrt.oem_inv_org_address Iss_oem_inv_org_address
,jscrt.supplier_id Iss_supplier_id
,jscrt.supplier_name Iss_supplier_name
,jscrt.site_id Iss_site_id
,jscrt.site_code Iss_site_code
,jscrt.site_address Iss_site_address
,jscrt.tp_inv_org_id Iss_tp_inv_org_id
,jscrt.tp_inv_org_code Iss_tp_inv_org_code
,jscrt.project_id Iss_project_id
,jscrt.project_num Iss_project_num
,jscrt.task_id Iss_task_id
,jscrt.task_num Iss_task_num
,jscrt.item_id Iss_item_id
,jscrt.item_number Iss_item_number
,jscrt.item_description Iss_item_description
,SUM(jscrt.estimated_qty) Iss_estimated_qty_Sum
,jscrt.primary_uom Iss_primary_uom
,jscrt.shikyu_price Iss_shikyu_price
,jscrt.currency_code Iss_currency_code
,jscrt.uom Iss_uom
,jscrt.item_cost Iss_item_cost
,jscrt.functional_currency Iss_functional_currency
,SUM(jscrt.value1) Iss_estimated_qty_Sum_Pri
,jscrt.value2 Iss_SHIKYU_Price_PriU
,jscrt.value3 Iss_SHIKYU_Cost_PriU
FROM jmf_shikyu_cfr_rpt_temp jscrt
,HR_ALL_ORGANIZATION_UNITS haou
WHERE jscrt.oem_inv_org_id = haou.organization_id
AND jscrt.rpt_data_type = CFR_CRUDE_DATA
GROUP BY jscrt.oem_inv_org_id
,jscrt.oem_inv_org_code
,haou.name
,jscrt.oem_inv_org_address
,jscrt.supplier_id
,jscrt.supplier_name
,jscrt.site_id
,jscrt.site_code
,jscrt.site_address
,jscrt.tp_inv_org_id
,jscrt.tp_inv_org_code
,jscrt.project_id
,jscrt.project_num
,jscrt.task_id
,jscrt.task_num
,jscrt.item_id
,jscrt.item_number
,jscrt.item_description
,jscrt.primary_uom
,jscrt.shikyu_price
,jscrt.currency_code
,jscrt.uom
,jscrt.item_cost
,jscrt.functional_currency
,jscrt.value2
,jscrt.value3
;
SELECT rpt_temp.rpt_mode
,rpt_temp.rpt_data_type
,rpt_temp.oem_inv_org_id Iss_oem_inv_org_id
,rpt_temp.oem_inv_org_code Iss_oem_inv_org_code
,rpt_temp.oem_inv_org_name Iss_oem_inv_org_name
,rpt_temp.oem_inv_org_address Iss_oem_inv_org_address
,rpt_temp.supplier_id Iss_supplier_id
,rpt_temp.supplier_name Iss_supplier_name
,rpt_temp.site_id Iss_site_id
,rpt_temp.site_code Iss_site_code
,rpt_temp.site_address Iss_site_address
,rpt_temp.tp_inv_org_id Iss_tp_inv_org_id
,rpt_temp.tp_inv_org_code Iss_tp_inv_org_code
,rpt_temp.project_id Iss_project_id
,rpt_temp.project_num Iss_project_num
,rpt_temp.task_id Iss_task_id
,rpt_temp.task_num Iss_task_num
,rpt_temp.item_id Iss_item_id
,rpt_temp.item_number Iss_item_number
,rpt_temp.item_description Iss_item_description
,SUM(rpt_temp.estimated_qty) Iss_estimated_qty_Sum
,rpt_temp.primary_uom Iss_primary_uom
,rpt_temp.shikyu_price Iss_shikyu_price
,rpt_temp.currency_code Iss_currency_code
,rpt_temp.uom Iss_uom
,rpt_temp.item_cost Iss_item_cost
,rpt_temp.functional_currency Iss_functional_currency
,SUM(rpt_temp.value1) Iss_estimated_qty_Sum_Pri
,rpt_temp.value2 Iss_SHIKYU_Price_PriU
,rpt_temp.value3 Iss_SHIKYU_Cost_PriU
FROM (
SELECT p_rpt_mode rpt_mode
,CFR_INT_COMPONENT rpt_data_type
,jscrt.oem_inv_org_id oem_inv_org_id
,jscrt.oem_inv_org_code oem_inv_org_code
,haou.name oem_inv_org_name
,jscrt.oem_inv_org_address oem_inv_org_address
,jscrt.supplier_id supplier_id
,jscrt.supplier_name supplier_name
,jscrt.site_id site_id
,jscrt.site_code site_code
,jscrt.site_address site_address
,jscrt.tp_inv_org_id tp_inv_org_id
,jscrt.tp_inv_org_code tp_inv_org_code
,jscrt.project_id project_id
,jscrt.project_num project_num
,jscrt.task_id task_id
,jscrt.task_num task_num
,jscrt.item_id item_id
,jscrt.item_number item_number
,jscrt.item_description item_description
,jscrt.estimated_qty estimated_qty
,jscrt.primary_uom primary_uom
,jscrt.shikyu_price shikyu_price
,jscrt.currency_code currency_code
,jscrt.uom uom
,jscrt.item_cost item_cost
,jscrt.functional_currency functional_currency
,jscrt.value1 value1
,jscrt.value2 value2
,jscrt.value3 value3
,jscrt.transaction_id transaction_id
FROM jmf_shikyu_cfr_rpt_temp jscrt
,HR_ALL_ORGANIZATION_UNITS_TL haou
WHERE jscrt.oem_inv_org_id = haou.organization_id
AND jscrt.rpt_data_type = CFR_CRUDE_DATA
AND haou.language = USERENV('LANG')
GROUP BY jscrt.rpt_mode
,jscrt.oem_inv_org_id
,jscrt.oem_inv_org_code
,haou.name
,jscrt.oem_inv_org_address
,jscrt.supplier_id
,jscrt.supplier_name
,jscrt.site_id
,jscrt.site_code
,jscrt.site_address
,jscrt.tp_inv_org_id
,jscrt.tp_inv_org_code
,jscrt.project_id
,jscrt.project_num
,jscrt.task_id
,jscrt.task_num
,jscrt.item_id
,jscrt.item_number
,jscrt.item_description
,jscrt.primary_uom
,jscrt.shikyu_price
,jscrt.currency_code
,jscrt.uom
,jscrt.item_cost
,jscrt.functional_currency
,jscrt.value2
,jscrt.value3
,jscrt.transaction_id
,jscrt.estimated_qty
,jscrt.value1
) rpt_temp
GROUP BY rpt_temp.rpt_mode
,rpt_temp.rpt_data_type
,rpt_temp.oem_inv_org_id
,rpt_temp.oem_inv_org_code
,rpt_temp.oem_inv_org_name
,rpt_temp.oem_inv_org_address
,rpt_temp.supplier_id
,rpt_temp.supplier_name
,rpt_temp.site_id
,rpt_temp.site_code
,rpt_temp.site_address
,rpt_temp.tp_inv_org_id
,rpt_temp.tp_inv_org_code
,rpt_temp.project_id
,rpt_temp.project_num
,rpt_temp.task_id
,rpt_temp.task_num
,rpt_temp.item_id
,rpt_temp.item_number
,rpt_temp.item_description
,rpt_temp.primary_uom
,rpt_temp.shikyu_price
,rpt_temp.currency_code
,rpt_temp.uom
,rpt_temp.item_cost
,rpt_temp.functional_currency
,rpt_temp.value2
,rpt_temp.value3;
text="--updated for fix bug 5231233 Begin Internal"
*/
-- Deleted Update to fix bug 5665445 for incorrect onhand Qty in Secondary UOM case
/* UPDATE JMF_SHIKYU_CFR_RPT_TEMP jscrt
SET jscrt.estimated_qty = PO_UOM_S.po_uom_convert_p(jscrt.primary_uom
,jscrt.uom
,jscrt.item_id) *jscrt.ESTIMATED_QTY,
(SELECT jscmt.primary_unconsumed_quantity
FROM jmf_shikyu_cfr_mid_temp jscmt
WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
AND jscmt.tp_inv_org_id =
jscrt.tp_inv_org_id
AND jscmt.item_id = jscrt.item_id),
jscrt.value1 = (SELECT jscmt.primary_unconsumed_quantity
FROM jmf_shikyu_cfr_mid_temp jscmt
WHERE jscmt.row_type = CFR_TMP_ONHAND_ROW --10
AND jscmt.tp_inv_org_id =
jscrt.tp_inv_org_id
AND jscmt.item_id = jscrt.item_id)
jscrt.value1 = jscrt.ESTIMATED_QTY
--updated for fix bug 5231233 End Internal
WHERE jscrt.rpt_data_type = CFR_INT_COMPONENT;
INSERT INTO JMF_SHIKYU_CFR_RPT_TEMP
(RPT_MODE
,RPT_DATA_TYPE
,oem_inv_org_id
,supplier_id
,site_id
,tp_inv_org_id
,item_id
,shikyu_price
,currency_code
,uom
,project_id
,task_id
,primary_uom
,subpo_header_id
,subpo_number
,subpo_line_num
,subpo_release_num
,subpo_shipment_num
,OSA_ITEM_ID
,OSA_ITEM_NUMBER
,OSA_ITEM_DESCRIPTION
,REQUESTED_COMP_QTY
,ISSUED_COMP_QTY)
SELECT DISTINCT p_rpt_mode RPT_MODE --temp.rpt_mode
,CFR_EXT_SUBPO_AFT_ONHAND RPT_DATA_TYPE
,temp.oem_inv_org_id shikyu_oem_inv_org_id
,temp.supplier_id shikyu_supplier_id
,temp.site_id shikyu_site_id
,temp.tp_inv_org_id shikyu_tp_inv_org_id
,temp.item_id shikyu_item_id
,temp.shikyu_price shikyu_price
,temp.currency_code shikyu_currency
,temp.uom shikyu_uom
,temp.project_id
,temp.task_id
,temp.primary_uom shikyu_primary_uom
,temp.subpo_header_id
,temp.subpo_number
,temp.subpo_line_num
,temp.subpo_release_num
,temp.subpo_shipment_num
,jso.osa_item_id
,msibk.concatenated_segments
,msibk.description
,wro.required_quantity
,wro.quantity_issued
FROM JMF_SHIKYU_CFR_RPT_TEMP temp
,jmf_subcontract_orders jso
,wip_requirement_operations wro
,MTL_SYSTEM_ITEMS_B_KFV msibk
WHERE temp.rpt_DATA_TYPE = CFR_CRUDE_DATA
AND temp.subpo_shipment_id = jso.subcontract_po_shipment_id
AND jso.oem_organization_id = msibk.organization_id
AND jso.osa_item_id = msibk.inventory_item_id
AND jso.tp_organization_id = wro.organization_id
AND jso.wip_entity_id = wro.wip_entity_id
AND wro.operation_seq_num = 1
AND wro.repetitive_schedule_id IS NULL
AND temp.item_id = wro.inventory_item_id;
SELECT row_type
,shikyu_id
,tp_inv_org_id
,item_id
,uom
,quantity
,primary_uom
,primary_unallocated_quantity
,primary_unconsumed_quantity
,project_id
,task_id
,oem_inv_org_id
,supplier_id
,site_id
,ou_id
,get_rcv_flag
,get_rep_flag
FROM jmf_shikyu_cfr_mid_temp
WHERE (lp_row_type IS NULL)
OR (row_type = lp_row_type)
ORDER BY row_type
,shikyu_id
,item_id;
SELECT rpt_mode
,rpt_data_type
,oem_inv_org_id
,oem_inv_org_code
,oem_inv_org_name
,oem_inv_org_address
,supplier_id
,supplier_name
,site_id
,site_code
,site_address
,tp_inv_org_id
,tp_inv_org_code
,project_id
,project_num
,task_id
,task_num
,item_id
,item_number
,item_description
,estimated_qty
,primary_uom
,shikyu_price
,currency_code
,uom
,item_cost
,functional_currency
,value1 --Qty in Primary UOM
,value2 --SHIKYU Price in Pri UOM and Pri Currency
,value3
FROM jmf_shikyu_cfr_rpt_temp
WHERE (lrpt_data_type IS NULL)
OR (rpt_data_type = lrpt_data_type)
ORDER BY rpt_mode
,rpt_data_type
,oem_inv_org_id
,supplier_id
,site_id;