[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM jmf_shikyu_cur_rpt_temp;
IF p_run = 'RUN_BEFORECOSTUPDATE'
THEN
cuar_get_unreceived_po(p_cost_type_id => p_cost_type_id
,p_org_id => p_org_id
,p_inv_org_name_from => p_inv_org_name_from
,p_inv_org_name_to => p_inv_org_name_to
,p_currency_cnv_type => p_currency_cnv_type
,p_currency_cnv_date => l_currency_cnv_date
,p_func_currency_code => l_func_currency_code
);
SELECT 'UnReceived'
,haotl.NAME
--updated to fix project_number related issue start
--,pa.segment1
,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)) segment1
--updated to fix project_number related issue end
,tasks.task_number
,ven.vendor_name
,pv.vendor_site_code
,h.segment1
,l.line_num
,sub.osa_item_id
,jmf_shikyu_rpt_util.get_item_number(l.org_id
,l.item_id)
,mtl.description
,sub.osa_item_price
,sub.currency
,loc.quantity - loc.quantity_received unreceived_qty
,l.unit_meas_lookup_code
-- ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.org_id
,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.ship_to_organization_id
,sub.osa_item_id
,1) frozend_cost
-- ,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.org_id
,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(loc.ship_to_organization_id
,sub.osa_item_id
,lp_cost_type_id)
,decode(sub.currency,lp_func_currency_code,sub.osa_item_price,jmf_shikyu_rpt_util.convert_amount(sub.currency
,lp_func_currency_code
,decode(lp_currency_cnv_date,null,sysdate,lp_currency_cnv_date)
,decode(lp_currency_cnv_type,null,h.rate_type,lp_currency_cnv_type)
,sub.osa_item_price))
,jmf_shikyu_rpt_cur_pvt.get_uom_primary_qty(l.item_id
,loc.ship_to_organization_id
,2
,1
-- ,loc.unit_meas_lookup_code)
,l.unit_meas_lookup_code) --UOM exchange rate
,pra.release_num --Added to display release number
/* FROM po_headers_all h
,po_lines_all l
,po_line_locations_all loc
,jmf_subcontract_orders sub
,mtl_system_items_vl mtl
,po_vendor_sites_all pv
,hr_all_organization_units_tl haotl
,pa_projects_all pa
,pa_tasks tasks
,po_vendors ven
WHERE \*h.type_lookup_code IN ('STANDARD') AND *\
h.po_header_id = sub.subcontract_po_header_id
AND l.po_line_id = sub.subcontract_po_line_id
AND sub.project_id = pa.project_id(+)
AND sub.task_id = tasks.task_id(+)
AND loc.line_location_id = sub.subcontract_po_shipment_id
AND pv.vendor_site_id(+) = h.vendor_site_id
AND mtl.inventory_item_id = l.item_id
AND haotl.organization_id(+) = loc.ship_to_organization_id
AND haotl.LANGUAGE = userenv('LANG')
AND h.vendor_id = ven.vendor_id
AND loc.ship_to_organization_id = mtl.organization_id
-- AND loc.org_id = mtl.organization_id
AND h.org_id = lp_org_id
AND haotl.NAME >= nvl(lp_inv_org_name_from
,haotl.NAME)
AND haotl.NAME <= nvl(lp_inv_org_name_to
,haotl.NAME);*/
INSERT INTO jmf_shikyu_cur_rpt_temp
(SOURCE
,inventory_org_name
,project_num
,task_num
,vendor_name
,vendor_site_code
,order_num
,line_num
,item_id
,item_name
,item_desc
,unit_price
,currency
,quantity
,uom_code
,unit_cost_frozen
,unit_cost_plan
,func_unit_price
,primary_qty
,func_currency_code
,order_line_id)--Added to display release number(When unreceived means release_num)
VALUES
(l_source
,l_inventory_org_name
,l_project_num
,l_task_num
,l_vendor_name
,l_vendor_site_code
,l_order_num
,l_line_num
,l_item_id
,l_item_name
,l_item_desc
,l_unit_price
,l_currency
,l_quantity
,l_uom_code
,l_unit_cost_frozen *l_qty_rate
,l_unit_cost_plan *l_qty_rate
,l_func_unit_price
,l_qty_rate
,l_func_currency_code
,l_order_line_id);--Added to display release number(When unreceived means release_num)
SELECT 'UnShipped'
,haotl.NAME
--updated to fix project_number related issue start
--,pa.segment1
,NVL((SELECT DISTINCT segment1 AS project_number
FROM pa_projects_all
WHERE pa_projects_all.project_id(+) = sol.project_id),
(SELECT DISTINCT project_number
FROM pjm_seiban_numbers
WHERE pjm_seiban_numbers.project_id(+) = sol.project_id)) segment1
--updated to fix project_number related issue end
,tasks.task_number
,soh.order_number
,sol.line_number
,sol.inventory_item_id
,jmf_shikyu_rpt_util.get_item_number(sol.org_id
,sol.inventory_item_id) item_num
,mtl.description
,sol.unit_selling_price
,soh.transactional_curr_code
-- Updated to fix bug 5462851 start
-- To get unshipped Quantity
-- ,sol.ordered_quantity - nvl(sol.shipped_quantity,0) unshipped_qty
,repo.allocated_quantity unshipped_quantity
-- Updated to fix bug 5462851 end
,sol.pricing_quantity_uom
,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(sol.ship_from_org_id
,sol.inventory_item_id
,1) frozend_cost
,jmf_shikyu_rpt_cur_pvt.cuar_get_item_cost(sol.ship_from_org_id
,sol.inventory_item_id
,lp_cost_type_id) planned_cost
,decode(soh.transactional_curr_code,lp_func_currency_code
, sol.unit_selling_price,jmf_shikyu_rpt_util.convert_amount(soh.transactional_curr_code
,lp_func_currency_code
/* ,lp_currency_cnv_date
,lp_currency_cnv_type*/
,decode(lp_currency_cnv_date,null,sysdate,lp_currency_cnv_date)
,decode(lp_currency_cnv_type,null,soh.CONVERSION_TYPE_CODE,lp_currency_cnv_type)
,sol.unit_selling_price)) convert_amount
,jmf_shikyu_rpt_cur_pvt.get_uom_primary_qty_from_code(sol.inventory_item_id
,sol.ship_from_org_id
,2
,1
,sol.pricing_quantity_uom) exchange_UOM-- returns qty in primary UOM for 1 UOM in document
,sol.line_id
FROM oe_order_headers_all soh
,oe_order_lines_all sol
,hr_all_organization_units_tl haotl
--updated to fix project_number related issue start
--,pa_projects_all pa
--updated to fix project_number related issue end
,pa_tasks tasks
,jmf_shikyu_replenishments repo
,mtl_system_items_vl mtl
--updated to fix project_number related issue start
--WHERE pa.project_id(+) = sol.project_id
-- AND tasks.task_id(+) = sol.task_id
WHERE tasks.task_id(+) = sol.task_id
--updated to fix project_number related issue end
AND haotl.organization_id = sol.ship_from_org_id
AND haotl.LANGUAGE = userenv('LANG')
AND mtl.inventory_item_id = sol.inventory_item_id
-- AND mtl.organization_id = sol.org_id
AND mtl.organization_id = sol.ship_from_org_id
-- Added to fix bug 5462851 start
AND repo.allocated_quantity > 0
AND sol.shipped_quantity IS NULL
-- Added to fix bug 5462851 end
AND repo.replenishment_so_header_id = soh.header_id
AND repo.replenishment_so_line_id = sol.line_id
AND soh.flow_status_code NOT IN ('ENTERED'
,'CANCELLED'
,'CLOSED')
AND sol.org_id = lp_org_id
AND haotl.NAME >= nvl(lp_inv_org_name_from
,haotl.NAME)
AND haotl.NAME <= nvl(lp_inv_org_name_to
,haotl.NAME)
/* 12.1 Buy/Sell Subcontracting changes */
/* Cost update analysis report is applicable only for Chargeable Sucbontracting */
AND nvl(JMF_SHIKYU_GRP. GET_SUBCONTRACTING_TYPE(repo.oem_organization_id, repo.tp_organization_id),
NULL) = 'C' ;
INSERT INTO jmf_shikyu_cur_rpt_temp
(SOURCE
,inventory_org_name
,project_num
,task_num
,vendor_name
,vendor_site_code
,order_num
,line_num
,item_id
,item_name
,item_desc
,unit_price
,currency
,quantity
,uom_code
,unit_cost_frozen
,unit_cost_plan
,func_unit_price
,primary_qty
,func_currency_code
,order_line_id)
VALUES
(l_source
,l_inventory_org_name
,l_project_num
,l_task_num
,l_vendor_name
,l_vendor_site_code
,l_order_num
,l_line_num
,l_item_id
,l_item_name
,l_item_desc
,l_unit_price
,l_currency
,l_quantity
,l_uom_code
,l_unit_cost_frozen * l_qty_rate --convert into UOM in document
,l_unit_cost_plan * l_qty_rate --convert into UOM in document
,l_func_unit_price
,l_qty_rate -- exchange UOM Rate
,l_func_currency_code
,l_order_line_id);
SELECT rcv.transaction_id
,oel.line_id
,oel.reference_line_id
,oel.org_id
,oeh.order_number
,oel.line_number
,oel.ordered_quantity
,oel.shipped_quantity
,oel.ship_from_org_id
,haotl.NAME
,rcv.creation_date
,rcv.transaction_id
FROM rcv_transactions rcv
,oe_order_lines_all oel
,oe_order_headers_all oeh
,hr_all_organization_units_tl haotl
WHERE oel.org_id = lp_org_id
AND rcv.transaction_type = 'DELIVER'
AND oel.line_id = rcv.oe_order_line_id
AND rcv.organization_id = oel.ship_from_org_id
AND oel.header_id = oeh.header_id
AND haotl.organization_id(+) = oel.ship_from_org_id
AND haotl.LANGUAGE = userenv('LANG')
AND haotl.NAME >= nvl(lp_inv_org_name_from
,haotl.NAME)
AND haotl.NAME <= nvl(lp_inv_org_name_to
,haotl.NAME)
/* 12.1 Buy/Sell Subcontracting changes */
/* Cost update analysis report is applicable only for Chargeable Sucbontracting */
AND nvl(JMF_SHIKYU_GRP. GET_SUBCONTRACTING_TYPE(oel.ship_from_org_id, oel.ship_to_org_id),
NULL) = 'C' ;
SELECT pa.segment1
,tasks.task_number
,soh.order_number
,sol.line_number
,sol.inventory_item_id
,jmf_shikyu_rpt_util.get_item_number(sol.org_id
,sol.inventory_item_id) item_num
,mtl.description
,sol.shipped_quantity
,sol.flow_status_code
,sol.ship_from_org_id
,sol.actual_shipment_date
INTO l_project_num
,l_task_num
,l_order_num
,l_line_num
,l_item_id
,l_item_name
,l_item_desc
,l_shipped_quantity
,l_flow_status_code
,l_om_ship_from_org_id
,l_actual_shipment_date
FROM oe_order_headers_all soh
,oe_order_lines_all sol
,pa_projects_all pa
,pa_tasks tasks
,jmf_shikyu_replenishments repo
,mtl_system_items_vl mtl
WHERE pa.project_id(+) = sol.project_id
AND tasks.task_id(+) = sol.task_id
AND mtl.inventory_item_id = sol.inventory_item_id
AND mtl.organization_id = l_ship_from_org_id
AND repo. replenishment_so_header_id = soh.header_id
AND repo. replenishment_so_line_id = sol.line_id
AND sol.line_id = l_reference_line_id
AND soh.header_id =sol.header_id;
SELECT DISTINCT actual_cost
INTO l_unit_cost_frozen
FROM mtl_material_transactions
WHERE trx_source_line_id = l_reference_line_id
AND source_code = 'ORDER ENTRY'
AND inventory_item_id = l_item_id
AND organization_id= l_om_ship_from_org_id
AND transaction_date = l_actual_shipment_date ;
SELECT DISTINCT actual_cost
INTO l_unit_cost_plan
FROM mtl_material_transactions
WHERE trx_source_line_id =l_rcv_line_id
AND source_code = 'RCV'
AND rcv_transaction_id = l_rcv_transaction_id ;
INSERT INTO jmf_shikyu_cur_rpt_temp
(SOURCE
,inventory_org_name
,project_num
,task_num
,vendor_name
,vendor_site_code
,order_num
,line_num
,item_id
,item_name
,item_desc
,quantity
,uom_code
,unit_cost_frozen
,unit_cost_plan
,func_unit_price
,primary_qty
,order_line_id
,func_currency_code)
VALUES
(l_source
,l_inventory_org_name
,l_project_num
,l_task_num
,NULL
,NULL
,l_order_num
,l_line_num
,l_item_id
,l_item_name
,l_item_desc
,l_cur_rcv_shipped_quantity
,NULL
,l_unit_cost_frozen
,l_unit_cost_plan
,NULL
,l_shipped_quantity
,l_reference_line_id
,l_func_currency_code);
SELECT 1
INTO l_number
FROM ORG_ACCT_PERIODS
WHERE organization_id= p_org_id
AND trunc(p_date,'dd') >= period_start_date
AND trunc(p_date,'dd') <= schedule_close_date
AND trunc(sysdate,'dd') >= period_start_date
AND trunc(sysdate,'dd') <= schedule_close_date ;
SELECT item_cost
INTO l_item_cost
FROM cst_item_costs cost
WHERE cost.cost_type_id = p_cst_type_id
AND cost.inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT primary_unit_of_measure
INTO l_primary_uom
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT mtl_units_of_measure.unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE mtl_units_of_measure.uom_code = p_from_unit;