[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_dtl_inserted NUMBER;
SELECT max(grsd.seq)
, SYSTEM.gmf_step_type
( grsd.dep_step_id, gbs.actual_step_qty, gbs.step_qty_um, 0
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, NULL, NULL
, CAST
( MULTISET
(
SELECT SYSTEM.gmf_dependency_type(a.batchstep_id, b.actual_step_qty, b.step_qty_um, NULL)
FROM gme_batch_step_dependencies a, gme_batch_steps b
WHERE a.batch_id = p_batch_id and a.dep_step_id = grsd.dep_step_id
AND a.batchstep_id = b.batchstep_id
AND a.batch_id = b.batch_id
) AS SYSTEM.gmf_dependency_tab
)
)
FROM
(
SELECT MAX(level) seq, dep_step_id, batchstep_id
FROM gme_batch_step_dependencies
START WITH batch_id = p_batch_id
AND batchstep_id NOT IN (SELECT dep_step_id FROM gme_batch_step_dependencies WHERE batch_id=p_batch_id)
CONNECT BY PRIOR dep_step_id = batchstep_id AND batch_id = PRIOR batch_id
GROUP BY dep_step_id, batchstep_id
) grsd
, gme_batch_steps gbs
WHERE gbs.batch_id = p_batch_id
AND gbs.batchstep_id = grsd.dep_step_id
GROUP BY grsd.dep_step_id, gbs.actual_step_qty, gbs.step_qty_um
UNION ALL
SELECT 0
, SYSTEM.gmf_step_type
( g.batchstep_id, g.actual_step_qty, g.step_qty_um, 0
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, NULL, NULL
, CAST
( MULTISET
(
SELECT SYSTEM.gmf_dependency_type(NULL, NULL, NULL, NULL)
FROM DUAL
) AS SYSTEM.gmf_dependency_tab
)
)
FROM
( SELECT DISTINCT/*Bug 4320765*/ gbsd.batchstep_id, gbs2.actual_step_qty, gbs2.step_qty_um
FROM gme_batch_step_dependencies gbsd
, gme_batch_steps gbs2
WHERE gbsd.batch_id = p_batch_id
AND gbs2.batch_id = p_batch_id
AND gbsd.batchstep_id NOT IN
(SELECT dep_step_id from gme_batch_step_dependencies where batch_id = p_batch_id)
AND gbsd.batchstep_id = gbs2.batchstep_id ) g
ORDER BY 1 desc;
SELECT 0
, SYSTEM.gmf_step_type
( 1, 0, NULL, 0
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, CAST
( MULTISET
( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
FROM DUAL
) AS SYSTEM.gmf_cost_tab
)
, NULL, NULL
, CAST
( MULTISET
(
SELECT SYSTEM.gmf_dependency_type(NULL, NULL, NULL, NULL)
FROM DUAL
) AS SYSTEM.gmf_dependency_tab
)
)
FROM DUAL;
SELECT 0,
SYSTEM.gmf_step_type
(gbs.batchstep_id,
gbs.actual_step_qty,
gbs.step_qty_um,
0,
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
),
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
),
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
),
NULL,
NULL,
CAST
(MULTISET (SELECT SYSTEM.gmf_dependency_type (NULL, NULL, NULL, NULL)
FROM DUAL) AS SYSTEM.gmf_dependency_tab
)
)
FROM gme_batch_steps gbs
WHERE gbs.batch_id = p_batch_id ;
SELECT batchstep_no, batchstep_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id
ORDER by batchstep_no;
SELECT SYSTEM.gmf_matl_type
(mmt.transaction_id,
hoi.org_information2,
mmt.organization_id,
mmt.inventory_item_id,
mtln.lot_number,
gmd.line_type,
NVL(mtln.primary_quantity, mmt.primary_quantity), -- B9131983 used NVL
iimb.primary_uom_code,
mmt.transaction_date,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id, 1, NULL, 0, 0),
gmd.contribute_step_qty_ind,
0,
gmd.plan_qty,
gmd.actual_qty,
gmd.dtl_um,
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
), -- Bug 7317270,
gmd.cost_alloc
)
FROM mtl_system_items_b iimb,
mtl_material_transactions mmt,
gme_batch_step_items gbsi,
gme_material_details gmd,
mtl_transaction_lot_numbers mtln,
mtl_parameters mp,
hr_organization_information hoi,
gme_transaction_pairs gtp
WHERE gbsi.batch_id = p_batch_id
AND gbsi.batchstep_id = p_batchstep_id
AND mp.organization_id = mmt.organization_id
AND hoi.organization_id = mmt.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND gbsi.material_detail_id = mmt.trx_source_line_id
AND mmt.transaction_id = mtln.transaction_id (+)
AND mmt.transaction_source_type_id = 5 /* Production */
AND mmt.transaction_quantity <> 0
AND mmt.inventory_item_id = iimb.inventory_item_id
AND mmt.organization_id = iimb.organization_id
AND mmt.transaction_date <= l_final_run_date
AND gmd.batch_id = p_batch_id
AND gmd.material_detail_id = gbsi.material_detail_id
AND mmt.transaction_id = gtp.transaction_id1 (+)
--AND NOT (mmt.inventory_item_id = transaction_row.inventory_item_id -- Bug 13386258-VC
-- AND mmt.transaction_id <> transaction_row.transaction_id
-- AND mtln.lot_number = transaction_row.lot_number
-- AND gtp.transaction_id2 IS NOT NULL )
ORDER BY mmt.transaction_date, gmd.line_type,
DECODE (gmd.line_type,1, DECODE (( ABS (DECODE (mmt.transaction_quantity, 0, 1, mmt.transaction_quantity))
/ DECODE (mmt.transaction_quantity, 0, 1, mmt.transaction_quantity)
),
1, mmt.transaction_id,
DECODE (gtp.transaction_id2,
NULL, mmt.transaction_id,
gtp.transaction_id2 + .5
)
),
mmt.transaction_id
), mtln.lot_number ; -- B9131983
SELECT SYSTEM.gmf_matl_type
( transaction_id,
org_information2,
organization_id,
inventory_item_id,
lot_number,
line_type,
primary_quantity,
primary_uom_code,
transaction_date,
lot_costed_flag,
contribute_step_qty_ind,
0,
plan_qty,
actual_qty,
dtl_um,
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
), -- Bug 7317270
cost_alloc
)
FROM (
SELECT
-- decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id,
-- mmt.transaction_id,
MIN(mmt.transaction_id) as transaction_id,
-- ) as transaction_id,
hoi.org_information2,
mmt.organization_id,
mmt.inventory_item_id,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id,
mtln.lot_number,
'DUMMY'
) as lot_number,
gmd.line_type,
SUM( NVL(mtln.primary_quantity, mmt.primary_quantity) ) as primary_quantity, -- B9131983 used NVL
iimb.primary_uom_code, --mmt.transaction_uom,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id,
Decode(mtln.transaction_date,null,mmt.transaction_date,mtln.transaction_date),
TRUNC(mmt.transaction_date)
) as transaction_date,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id, 1, NULL, 0, 0) as lot_costed_flag,
gmd.contribute_step_qty_ind,
0,
gmd.plan_qty,
gmd.actual_qty,
gmd.dtl_um, gmd.cost_alloc
FROM mtl_system_items_b iimb,
mtl_material_transactions mmt,
gme_material_details gmd,
mtl_transaction_lot_numbers mtln,
mtl_parameters mp,
hr_organization_information hoi
WHERE mmt.transaction_source_type_id = 5
AND gmd.line_type IN (-1,2)
AND mtln.transaction_quantity(+) <> 0
AND mmt.transaction_id = mtln.transaction_id(+)
AND mmt.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND mmt.organization_id = mp.organization_id
AND iimb.inventory_item_id = mmt.inventory_item_id
AND iimb.organization_id = mmt.organization_id
AND gmd.batch_id = p_batch_id
AND mmt.trx_source_line_id = gmd.material_detail_id
AND gmd.material_detail_id NOT IN
(SELECT material_detail_id FROM gme_batch_step_items
WHERE batch_id = p_batch_id)
GROUP BY
-- decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id,
-- mmt.transaction_id,
-- -999999 transaction_id) ,
hoi.org_information2,
mmt.organization_id,
mmt.inventory_item_id,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id,
mtln.lot_number,
'DUMMY'
) ,
gmd.line_type,
iimb.primary_uom_code, --mmt.transaction_uom,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id,
Decode(mtln.transaction_date,null,mmt.transaction_date,mtln.transaction_date),
TRUNC(mmt.transaction_date)
) ,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id, 1, NULL, 0, 0),
gmd.contribute_step_qty_ind,
0 ,
gmd.plan_qty,
gmd.actual_qty,
gmd.dtl_um,
gmd.cost_alloc
) ;
SELECT SYSTEM.gmf_matl_type
( mmt.transaction_id,
hoi.org_information2,
mmt.organization_id,
mmt.inventory_item_id,
mtln.lot_number,
gmd.line_type,
NVL(mtln.primary_quantity, mmt.primary_quantity), -- B9131983 used NVL
iimb.primary_uom_code, --mmt.transaction_uom,
Decode(mtln.transaction_date,null,mmt.transaction_date,mtln.transaction_date),--mtln.transaction_date,
decode(is_item_lot_costed(iimb.organization_id,iimb.inventory_item_id), iimb.inventory_item_id, 1, NULL, 0, 0),
gmd.contribute_step_qty_ind,
0,
gmd.plan_qty,
gmd.actual_qty,
gmd.dtl_um,
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
), -- Bug 7317270
gmd.cost_alloc
)
FROM mtl_system_items_b iimb,
mtl_material_transactions mmt,
gme_material_details gmd,
mtl_transaction_lot_numbers mtln,
hr_organization_information hoi
WHERE mmt.transaction_source_type_id = 5
AND gmd.line_type = 1
AND mtln.transaction_quantity(+) <> 0
AND mmt.transaction_id = mtln.transaction_id(+)
AND mmt.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information'
AND iimb.inventory_item_id = mmt.inventory_item_id
AND iimb.organization_id = mmt.organization_id
AND gmd.batch_id = p_batch_id
AND mmt.trx_source_line_id = gmd.material_detail_id
AND gmd.material_detail_id NOT IN
(SELECT material_detail_id FROM gme_batch_step_items
WHERE batch_id = p_batch_id);
SELECT SYSTEM.gmf_matl_type
( mmt.transaction_id,
l_le_id,
mmt.organization_id,
mmt.inventory_item_id,
mtln.lot_number,
gme.line_type,
NVL(mtln.primary_quantity, mmt.primary_quantity), -- B9131983 used NVL
iimb.primary_uom_code,
mmt.transaction_date,
decode(is_item_lot_costed(mmt.organization_id,iimb.inventory_item_id), iimb.inventory_item_id, 1, NULL, 0, 0),
gme.contribute_step_qty_ind,
0,
gme.plan_qty,
gme.actual_qty,
gme.dtl_um,
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
), -- Bug 7317270
gme.cost_alloc
)
FROM mtl_system_items_b iimb,
mtl_material_transactions mmt,
gme_material_details gme,
mtl_transaction_lot_numbers mtln,
gme_transaction_pairs gtp
WHERE mmt.trx_source_line_id = gme.material_detail_id
AND mmt.transaction_source_type_id = 5 /* Rajesh B8290451 */
AND mmt.transaction_source_id=gme.batch_id-- added by Francisco 23 Feb 2009
AND gme.batch_id = p_batch_id
AND mmt.transaction_quantity <> 0
AND mmt.inventory_item_id = iimb.inventory_item_id
AND mmt.organization_id = iimb.organization_id
AND mmt.transaction_date <= l_final_run_date
AND mmt.transaction_id = mtln.transaction_id (+)
AND mmt.transaction_id = gtp.transaction_id1 (+)
AND gtp.batch_id (+) = p_batch_id
--AND NOT (mmt.inventory_item_id = transaction_row.inventory_item_id -- Bug 13386258-VC
-- AND gme.line_type = transaction_row.line_type
-- AND mmt.transaction_id <> transaction_row.transaction_id
-- AND mtln.lot_number = transaction_row.lot_number
-- AND gtp.transaction_id2 IS NOT NULL )
ORDER BY mmt.transaction_date, gme.line_type,
DECODE (gme.line_type,
1, DECODE (( ABS (DECODE (mmt.transaction_quantity, 0, 1, mmt.transaction_quantity))
/ DECODE (mmt.transaction_quantity, 0, 1, mmt.transaction_quantity)
),
1, mmt.transaction_id,
DECODE (gtp.transaction_id2,
NULL, mmt.transaction_id,
gtp.transaction_id2 + .5
)
),
mmt.transaction_id
), mtln.lot_number; -- B9131983
SELECT SYSTEM.gmf_rsrc_type
( grt.poc_trans_id,
grt.organization_id,
grt.resources,
grt.resource_usage,
grt.trans_qty_um,
grt.trans_date,
0,
CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
FROM DUAL) AS SYSTEM.gmf_cost_tab
) -- Bug 7317270
)
FROM gme_resource_txns grt,
gme_batch_step_resources gbsr
WHERE gbsr.batch_id = p_batch_id
AND gbsr.batchstep_id = p_batchstep_id
AND gbsr.batchstep_resource_id = grt.line_id
AND grt.doc_type = 'PROD'
AND grt.doc_id = p_batch_id
AND grt.completed_ind = 1
AND grt.resource_usage <> 0
ORDER BY grt.trans_date;
SELECT *
FROM gmf_lot_costs glc
WHERE glc.lot_number = p_lot_number
AND glc.inventory_item_id = p_item_id
AND glc.organization_id = p_orgn_id
AND glc.cost_type_id = p_cost_type_id
AND glc.cost_date >= sysdate-60
AND glc.cost_date <= NVL(p_trans_date, glc.cost_date)
ORDER BY header_id desc
;
SELECT *
FROM gmf_lot_costs glc
WHERE glc.lot_number = p_lot_number
AND glc.inventory_item_id = p_item_id
AND glc.organization_id = p_orgn_id
AND glc.cost_type_id = p_cost_type_id
AND glc.cost_date <= NVL(p_trans_date, glc.cost_date)
ORDER BY header_id desc
;
SELECT SYSTEM.gmf_cost_type
( glcd.cost_cmpntcls_id,
glcd.cost_analysis_code,
glcd.cost_level,
glcd.component_cost,
0 -- B9131983 Burden_ind
)
FROM gmf_lot_cost_details glcd
WHERE glcd.header_id = p_header_id;
SELECT sum(cst.cmpnt_cost)
FROM cm_cmpt_dtl cst,
gmf_period_statuses gps
WHERE gps.legal_entity_id = p_le_id
AND gps.cost_type_id = p_cost_type_id
AND gps.start_date <= p_date
AND gps.end_date >= p_date
AND gps.period_id = cst.period_id
AND cst.organization_id = (SELECT NVL (cstw.cost_organization_id, invw.organization_id)
FROM cm_whse_asc cstw, mtl_parameters invw
WHERE cstw.organization_id(+) = invw.organization_id
AND invw.organization_id = p_orgn_id
AND NVL(cstw.eff_start_date(+),p_date) <= p_date -- HALUTHRA Bug 5473138/8533290
AND NVL(cstw.eff_end_date(+),p_date) >= p_date -- HALTURHA Bug 5473138/8533290
AND cstw.delete_mark (+) = 0)
AND cst.inventory_item_id = p_item_id
AND cst.delete_mark = 0
AND gps.delete_mark = 0;
SELECT SYSTEM.gmf_cost_type
(cstdtl.cost_cmpntcls_id,
cstdtl.cost_analysis_code,
cstdtl.cost_level,
cstdtl.cmpnt_cost,
0
) ,
SUM(NVL(cstdtl.cmpnt_cost,0)) OVER (PARTITION BY cost_level ) total_cost /* Groupo */
FROM
(SELECT cst.cost_cmpntcls_id,
cst.cost_analysis_code,
0 cost_level,
SUM(cst.cmpnt_cost) cmpnt_cost
FROM cm_cmpt_dtl cst
WHERE cst.cost_type_id = p_cost_type_id
AND cst.period_id = p_period_id
AND cst.organization_id =
(SELECT NVL (cstw.cost_organization_id, invw.organization_id)
FROM cm_whse_asc cstw, mtl_parameters invw
WHERE cstw.organization_id(+) = invw.organization_id
AND invw.organization_id = p_orgn_id
AND NVL(cstw.eff_start_date(+),p_date) <= p_date -- HALUTHRA Bug 5473138/8533290
AND NVL(cstw.eff_end_date(+),p_date) >= p_date -- HALUTHRA Bug 5473138/8533290
AND cstw.delete_mark (+) = 0)
AND cst.inventory_item_id = p_item_id
AND cst.delete_mark = 0
Group by cst.cost_cmpntcls_id, cst.cost_analysis_code ) cstdtl
;
SELECT SYSTEM.gmf_cost_type
(cstdtl.cost_cmpntcls_id,
cstdtl.cost_analysis_code,
cstdtl.cost_level,
cstdtl.cmpnt_cost,
0
)
FROM
(SELECT cst.cost_cmpntcls_id,
cst.cost_analysis_code,
0 cost_level,
SUM(cst.cmpnt_cost) cmpnt_cost
FROM cm_cmpt_dtl cst,
gmf_period_statuses gps
WHERE gps.legal_entity_id = p_le_id
AND gps.cost_type_id = p_cost_type_id
AND gps.start_date <= p_date
AND gps.end_date >= p_date
AND cst.cost_type_id = p_cost_type_id
AND gps.period_id = cst.period_id
AND cst.organization_id =
(SELECT NVL (cstw.cost_organization_id, invw.organization_id)
FROM cm_whse_asc cstw, mtl_parameters invw
WHERE cstw.organization_id(+) = invw.organization_id
AND invw.organization_id = p_orgn_id
AND NVL(cstw.eff_start_date(+),p_date) <= p_date -- HALUTHRA Bug 5473138/8533290
AND NVL(cstw.eff_end_date(+),p_date) >= p_date -- HALUTHRA Bug 5473138/8533290
AND cstw.delete_mark (+) = 0)
AND cst.inventory_item_id = p_item_id
AND cst.delete_mark = 0
AND gps.delete_mark = 0
Group by cst.cost_cmpntcls_id, cst.cost_analysis_code ) cstdtl
;
SELECT SYSTEM.gmf_cost_type
(gct.cost_cmpntcls_id,
gct.cost_analysis_code,
0,
gct.nominal_cost,
0)
FROM (SELECT DISTINCT gbsr.cost_cmpntcls_id, --used the distinct bug 7409599, pmarada
gbsr.cost_analysis_code,
0,
cst.nominal_cost,
0
FROM cm_rsrc_dtl cst
,gmf_period_statuses gps
,gme_batch_step_resources gbsr
WHERE gps.legal_entity_id = p_le_id
AND gps.cost_type_id = p_cost_type_id
AND gps.start_date <= p_date
AND gps.end_date >= p_date
AND cst.period_id = gps.period_id
AND cst.organization_id = p_orgn_id
AND cst.resources = p_resources
AND cst.cost_type_id = p_cost_type_id -- Bug 9686215 12.0 FP for 9646416
AND cst.delete_mark = 0
AND gps.delete_mark = 0
AND gbsr.batch_id = p_batch_id
AND gbsr.batchstep_id = p_batchstep_id
AND gbsr.resources = p_resources
) gct ;
SELECT a.mtl_cmpntcls_id,
a.mtl_analysis_code, 1
FROM cm_cmpt_mtl a
WHERE (legal_entity_id = p_le_id OR legal_entity_id IS NULL)
AND inventory_item_id = p_item_id
AND (organization_id = p_orgn_id OR organization_id IS NULL)
AND p_date BETWEEN eff_start_date AND eff_end_date
AND a.delete_mark = 0
UNION
SELECT b.mtl_cmpntcls_id, b.mtl_analysis_code, 2
FROM cm_cmpt_mtl b
WHERE (legal_entity_id = p_le_id OR legal_entity_id IS NULL)
AND (organization_id = p_orgn_id OR organization_id IS NULL)
AND p_date BETWEEN eff_start_date AND eff_end_date
AND delete_mark = 0
AND cost_category_id IN
( SELECT category_id -- cost_category_id Bug#7306720
FROM mtl_item_categories mic,
gmf_process_organizations_gt gpo
WHERE mic.inventory_item_id = p_item_id
AND mic.organization_id = gpo.organization_id
AND (mic.organization_id = p_orgn_id OR p_orgn_id IS NULL)
)
UNION
SELECT d.mtl_cmpntcls_id, d.mtl_analysis_code, 3
FROM gmf_fiscal_policies d
WHERE d.legal_entity_id = p_le_id
ORDER BY 3;
SELECT lot_burden_line_id,
resources,
cost_cmpntcls_id,
cost_analysis_code,
burden_factor,
0
FROM gmf_lot_cost_burdens
WHERE inventory_item_id = p_item_id
AND organization_id = p_orgn_id
AND lot_number = p_lot_number
AND cost_type_id = p_cost_type_id
AND delete_mark = 0
AND start_date <= p_trans_date
AND nvl(end_date, p_trans_date) >= p_trans_date
UNION
SELECT lot_burden_line_id,
resources,
cost_cmpntcls_id,
cost_analysis_code,
burden_factor, 0
FROM gmf_lot_cost_burdens
WHERE inventory_item_id = p_item_id
AND organization_id = p_orgn_id
AND lot_number IS NULL
AND cost_type_id = p_cost_type_id
AND delete_mark = 0
AND start_date <= p_trans_date
AND nvl(end_date, p_trans_date) >= p_trans_date
AND (resources, cost_cmpntcls_id, cost_analysis_code)
NOT IN
(SELECT resources, cost_cmpntcls_id, cost_analysis_code
FROM gmf_lot_cost_burdens
WHERE inventory_item_id = p_item_id
AND organization_id = p_orgn_id
AND lot_number = p_lot_number
AND cost_type_id = p_cost_type_id
AND delete_mark = 0
AND start_date <= p_trans_date
AND nvl(end_date, p_trans_date) >= p_trans_date
)
ORDER BY 3,4 ;
SELECT cst.nominal_cost
FROM cm_rsrc_dtl cst,
gmf_period_statuses gps
WHERE gps.legal_entity_id = p_le_id
AND gps.cost_type_id = p_cost_type_id
AND gps.start_date <= p_date
AND gps.end_date >= p_date
AND cst.period_id = gps.period_id
AND cst.resources = p_resources
AND cst.organization_id = p_orgn_id
AND cst.delete_mark = 0
AND gps.delete_mark = 0;
SELECT g.inventory_item_id,g.organization_id
FROM gmf_lot_costed_items_gt g
ORDER BY organization_id,inventory_item_id
;
SELECT DISTINCT gbsi.batchstep_id
FROM gme_material_details gmd, gme_batch_step_items gbsi
WHERE gmd.material_detail_id = gbsi.material_detail_id
AND gmd.batch_id = gbsi.batch_id
AND gmd.line_type = 1
AND gmd.batch_id = p_batch_id;
SELECT distinct inventory_item_id into l_inv_id FROM
(
SELECT
msi.inventory_item_id
FROM gmf_lot_costed_items lci,
mtl_system_items_b msi
WHERE lci.legal_entity_id = l_le_id
AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
AND msi.lot_control_code = 2
AND lci.inventory_item_id = msi.inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND msi.process_costing_enabled_flag = 'Y'
AND lci.cost_type_id = l_cost_type_id
AND msi.inventory_item_id = p_item_id
AND msi.organization_id = p_orgn_id
UNION
SELECT msi.inventory_item_id
FROM mtl_item_categories mic,
gmf_lot_costed_items g,
mtl_system_items_b msi
WHERE g.cost_category_id = mic.category_id
AND g.legal_entity_id = l_le_id
AND g.delete_mark = 0
AND msi.lot_control_code = 2
AND msi.organization_id = mic.organization_id
AND mic.inventory_item_id = msi.inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND msi.process_costing_enabled_flag = 'Y'
AND g.cost_type_id = l_cost_type_id
AND msi.inventory_item_id = p_item_id
AND msi.organization_id = p_orgn_id ) ;
1. Delete Lot_Costed_Items_gt table.
2. Load all the lot costed items in Lot_Costed_Items_gt table.
3. Reindex the array.
==========================================================*/
PROCEDURE ReLoad_Lot_Costed_Items_gt(p_le_id IN NUMBER,
x_return_status OUT NOCOPY NUMBER
) IS
-- l_le_id NUMBER; B 8687115 already declared global Not used.
DELETE FROM GMF_LOT_COSTED_ITEMS_GT ;
INSERT
INTO GMF_LOT_COSTED_ITEMS_GT
(
organization_id,
inventory_item_id,
primary_uom_code
)
SELECT
msi.organization_id,
msi.inventory_item_id,
msi.primary_uom_code
FROM gmf_lot_costed_items lci,
mtl_system_items_b msi,
gmf_process_organizations_gt gpo
WHERE lci.legal_entity_id = l_le_id
AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
AND gpo.organization_id = msi.organization_id
AND msi.lot_control_code = 2
AND lci.inventory_item_id = msi.inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND msi.process_costing_enabled_flag = 'Y'
AND lci.cost_type_id = l_cost_type_id
UNION
SELECT
mic.organization_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
mic.inventory_item_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
i.primary_uom_code
FROM mtl_item_categories mic,
gmf_lot_costed_items g,
mtl_system_items_b i,
gmf_process_organizations_gt gpo
WHERE g.cost_category_id = mic.category_id
AND g.legal_entity_id = l_le_id
AND g.delete_mark = 0
AND i.lot_control_code = 2
AND gpo.organization_id = i.organization_id
AND i.organization_id = mic.organization_id
AND mic.inventory_item_id = i.inventory_item_id
AND i.inventory_asset_flag = 'Y'
AND i.process_costing_enabled_flag = 'Y'
AND g.cost_type_id = l_cost_type_id;
* either written to the database or used to update an existing cost. The update will use the *
* Averaging mode. *
* History *
* LCMOPM Dev 4-Aug-2009 LCM-OPM Integration, bug 8642337 Added new merge mode V for *
* value adjustment. multiply the component cost by 1 and add to unit cost *
--**********************************************************************************************/
PROCEDURE merge_costs
( costs_table IN OUT NOCOPY l_cost_tab_type
, cost_qty IN NUMBER
, new_qty IN NUMBER
, merge_mode IN VARCHAR2
)
IS
k NUMBER;
l_burden_costs_tab.delete;
fnd_file.put_line(fnd_file.log,'Inside INSERT HEADER');
INSERT INTO gmf_lot_costs
( header_id
, inventory_item_id
, lot_number
, organization_id
, cost_type_id
, unit_cost
, cost_date
, onhand_qty
, last_trx_source_type_id
, last_trx_action_id
, last_costing_doc_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, delete_mark
, request_id
, program_application_id
, program_id
, program_update_date
, final_cost_flag
)
VALUES
(
gmf_cost_header_id_s.nextval
, p_item_id
, p_lot_number
, p_orgn_id
, p_cost_type_id
, p_unit_cost
, p_cost_date
, p_onhand_qty
, p_trx_src_type_id
, p_txn_act_id
, p_doc_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, 0
, l_request_id
, l_prog_appl_id
, l_program_id
, SYSDATE
, l_final_run_flag
)
RETURNING header_id, unit_cost, onhand_qty
INTO x_header_id, x_unit_cost, x_onhand_qty;
fnd_file.put_line(fnd_file.log,'Inside INSERT DETAIL');
INSERT INTO gmf_lot_cost_details
( header_id
, detail_id
, cost_cmpntcls_id
, cost_analysis_code
, cost_level
, component_cost
, burden_ind
, creation_date
, created_by
, last_update_date
, last_updated_by
, delete_mark
, request_id
, program_application_id
, program_id
, program_update_date
, final_cost_flag
)
VALUES
( p_header_id
, gmf_cost_detail_id_s.nextval
, p_component_class_id
, p_cost_analysis_code
, p_cost_level
, p_component_cost
, p_burden_ind
, sysdate
, l_user_id
, sysdate
, l_user_id
, 0
, l_request_id
, l_prog_appl_id
, l_program_id
, SYSDATE
, l_final_run_flag
);
INSERT INTO gmf_material_lot_cost_txns
( cost_trans_id
, cost_header_id
, cost_type_id
, cost_trans_date
, cost_trans_qty
, cost_trans_um
, total_trans_cost
, transaction_id
, new_unit_cost
, new_onhand_qty
, old_unit_cost
, old_onhand_qty
, creation_date
, created_by
, last_update_date
, last_updated_by
, request_id
, program_application_id
, program_id
, program_update_date
, final_cost_flag
, new_cost_ind
, lot_number
)
VALUES
( gmf_cost_trans_id_s.nextval
, p_header_id
, p_cost_type_id
, p_trans_date
, p_trans_qty
, p_trans_um
, p_total_cost
, decode(p_trans_id, -9, (-1*gmf_cost_trans_id_s.currval),p_trans_id)
, p_unit_cost
, p_onhand_qty
, p_old_unit_cost
, p_old_onhand_qty
, sysdate
, l_user_id
, sysdate
, l_user_id
, l_request_id
, l_prog_appl_id
, l_program_id
, SYSDATE
, l_final_run_flag
, p_new_cost_ind
, p_lot_number
);
SELECT rc.cost_component_class_id
,rc.cost_analysis_code
,nvl(rca.estimated_amount, rca.actual_amount)/mmt.transaction_quantity /* ANTHIYAG Bug#5463200 14-Aug-2006 */
,uom.uom_code
,0 lcm_flag
FROM rcv_transactions t,
po_rcv_charges rc,
po_rcv_charge_allocations rca,
mtl_units_of_measure uom,
mtl_material_transactions mmt, /* ANTHIYAG Bug#5463200 14-Aug-2006 */
po_line_locations_all pll -- AF
WHERE mmt.transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5463200 14-Aug-2006 */
AND t.transaction_id = mmt.rcv_transaction_id
AND t.shipment_header_id = rc.shipment_header_id
AND t.shipment_line_id = rca.shipment_line_id
AND rc.charge_id = rca.charge_id
AND t.unit_of_measure = uom.unit_of_measure
AND rc.include_in_acquisition_cost = 'I' /* ANTHIYAG Bug#5463200 14-Aug-2006 */
AND t.po_line_location_id = pll.line_location_id -- AF
AND NVL(pll.lcm_flag,'N') = 'N' -- AF
-- AF
UNION ALL /* Estimated LC adjustments for LCM enabled PO lines */
SELECT glat.cost_cmpntcls_id
,glat.cost_analysis_code
,(nvl(glat.new_landed_cost,0) - nvl(glat.prior_landed_cost,0)) / glat.Primary_quantity
,glat. primary_uom_code
,1 lcm_flag
FROM
gmf_lc_adj_transactions glat,
mtl_material_transactions mmt,
rcv_transactions rt ,
po_line_locations_all pll
WHERE
mmt.transaction_id = transaction_row.transaction_id
AND glat.rcv_transaction_id = mmt.rcv_transaction_id
AND rt.transaction_id = glat.rcv_transaction_id
AND (glat.lc_adjustment_flag = 0 OR glat.adjustment_num = 0)
AND glat.cost_acquisition_flag = 'I'
AND glat.component_type IN ('ITEM PRICE','CHARGE')
AND rt.po_line_location_id = pll.line_location_id
AND NVL(pll.lcm_flag,'N') = 'Y';
UPDATE gmf_lot_costs
SET onhand_qty = onhand_qty + transaction_row.trans_qty
, last_update_date = sysdate
WHERE header_id = old_cost.header_id;
SELECT *
FROM (
SELECT last_trx_source_type_id, --last_costing_doc_type prev_doc_type INVCONV sschinch,
last_trx_action_id, --last_costing_doc_id prev_doc_id INVCONV sschinch,
header_id prev_header_id,
unit_cost prev_unit_cost,
RANK () OVER (PARTITION BY glc.inventory_item_id, glc.organization_id, glc.cost_type_id, glc.lot_number
ORDER BY glc.cost_date DESC, glc.header_id DESC) lot_cost_rank
FROM gmf_lot_costs glc
WHERE glc.inventory_item_id = p_item_id
AND glc.lot_number = p_lot_number
AND glc.organization_id = p_orgn_id
AND glc.cost_type_id = p_cost_type_id
AND glc.cost_date <= p_cost_date
)
WHERE lot_cost_rank < 3
ORDER BY lot_cost_rank
;
SELECT cost_cmpntcls_id,
cost_analysis_code,
cost_level,
component_cost,
burden_ind,
cost_origin,
frozen_ind
FROM gmf_lot_cost_details
WHERE header_id = p_header_id
;
SELECT gmlct.new_cost_ind
FROM gmf_material_lot_cost_txns gmlct
WHERE gmlct.cost_header_id = p_header_id
ORDER BY cost_trans_id DESC
;
l_cost_header_id := i.prev_header_id; /* will be used to insert in material txns table */
SELECT NVL(SUM(component_cost),0)
INTO l_prev_trans_unit_cost
FROM gmf_lot_cost_details
WHERE header_id = DECODE(NVL(l_prev_prev_header_id, 0), 0, l_cost_header_id, -l_cost_header_id);
SELECT mmt.transaction_id
FROM gmf_lot_costs glc, gmf_material_lot_cost_txns gmt,
mtl_material_transactions mmt
WHERE glc.lot_number = transaction_row.lot_number
AND glc.inventory_item_id = transaction_row.inventory_item_id
AND glc.organization_id = transaction_row.orgn_id
AND glc.cost_type_id = l_cost_type_id
AND glc.cost_date <= NVL(transaction_row.trans_date, glc.cost_date)
AND gmt.cost_header_id = glc.header_id
AND mmt.transaction_id = gmt.transaction_id
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id = 31
AND mmt.transaction_source_id = transaction_row.doc_id
AND mmt.trx_source_line_id = transaction_row.line_id
ORDER By transaction_date desc ;
SELECT DECODE(NVL(txns.new_cost_ind,0), 0, txns.cost_header_id, -txns.cost_header_id), txns.new_cost_ind
FROM gmf_material_lot_cost_txns txns
WHERE txns.transaction_id = p_orig_trans_id
AND txns.cost_type_id = l_cost_type_id -- PK 9069363 added cost_type_id and order by
Order by cost_header_id desc;
UPDATE gmf_lot_costs
SET onhand_qty = onhand_qty + transaction_row.trans_qty,
last_update_date = sysdate
WHERE header_id = old_cost.header_id;
select NVL(mtln.primary_quantity, mmt.primary_quantity), -- B9131983 used NVL
lcig.primary_uom_code
from mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_lot_costed_items_gt lcig
where mmt.transaction_id = transaction_row.transfer_transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id;
/* SELECT to_number(org_information3)
INTO l_source_ou
FROM hr_organization_information
WHERE organization_id = p_source_orgn
AND org_information_context = 'Accounting Information';
SELECT to_number(org_information3)
INTO l_target_ou
FROM hr_organization_information
WHERE organization_id = p_target_orgn
AND org_information_context = 'Accounting Information';
SELECT transfer_price INTO l_unit_cost
FROM mtl_material_transactions
WHERE transaction_id = transaction_row.transaction_id;
/* SELECT mtl_cmpntcls_id, mtl_analysis_code INTO l_ccc_id, l_a_code
FROM gmf_fiscal_policies
WHERE legal_entity_id = p_target_le; */
new_cost_tab.delete;
new_cost_tab.delete;
new_cost_tab.delete;
SELECT s.base_currency_code,t.base_currency_code
INTO l_from_ccy_code, l_to_ccy_code
FROM gmf_fiscal_policies s,
gmf_fiscal_policies t
WHERE s.legal_entity_id = p_source_le
AND t.legal_entity_id = p_target_le;
we have copied prd cost before merging and updated level to 1
We also need to create negative details all the time */
IF l_burden_costs_tab.EXISTS(1) THEN -- Can be eliminated? we would not come here unless burdens exist
IF l_debug_level >= l_debug_level_high THEN
fnd_file.put_line(fnd_file.log,'Before merge prd_cost_tab is:');
/* Bug 6320304/5953977 SELECT t.po_unit_price, */
SELECT t.po_unit_price + DECODE(nvl(pda.quantity_ordered,0),0,0, (nvl(pda.nonrecoverable_tax,0)/pda.quantity_ordered)),
t.currency_code,
t.quantity,
u.uom_code,
t.source_document_code,
NVL(t.currency_conversion_rate,1),
t.transaction_id
INTO receipt_unit_cost,
receipt_ccy,
receipt_qty,
receipt_uom,
document_code,
l_exchange_rate,
l_rcv_transaction_id
FROM rcv_transactions t, mtl_units_of_measure u, mtl_material_transactions mmt -- jboppana
, po_distributions_all pda
WHERE t.source_doc_unit_of_measure = u.unit_of_measure(+)
AND t.transaction_id = mmt.rcv_transaction_id
-- AND mmt.transaction_source_id = transaction_row.doc_id
AND t.po_distribution_id = pda.po_distribution_id (+) /* Bug 6320304/5953977 */
AND mmt.transaction_id = transaction_row.transaction_id ;
SELECT
mmt.organization_id,
mmt.transfer_organization_id,
hoi1.org_information2,
hoi2.org_information2,
r.shipped_date
INTO
target_orgn_id,source_orgn_id, source_le_id, target_le_id, l_shipped_date
FROM
rcv_transactions t,
mtl_material_transactions mmt,
rcv_shipment_headers r,
rcv_shipment_lines rsl,
po_headers_all poh,
hr_organization_information hoi1,
hr_organization_information hoi2
WHERE
t.source_document_code = 'REQ'
AND t.transaction_id = mmt.rcv_transaction_id
AND mmt.transaction_id = transaction_row.transaction_id
AND mmt.organization_id = hoi2.organization_id
AND mmt.transfer_organization_id = hoi1.organization_id
AND hoi1.org_information_context = 'Accounting Information'
AND hoi2.org_information_context = 'Accounting Information'
AND t.shipment_header_id = r.shipment_header_id
AND r.receipt_source_code in ('INTERNAL ORDER')
AND t.shipment_line_id = rsl.shipment_line_id
AND t.po_header_id = poh.po_header_id (+);
SELECT
mmt.organization_id,
mmt.transfer_organization_id,
hoi1.org_information2,
hoi2.org_information2,
r.shipped_date
INTO
target_orgn_id,source_orgn_id, source_le_id, target_le_id, l_shipped_date
FROM
rcv_transactions t,
mtl_material_transactions mmt,
rcv_shipment_headers r,
rcv_shipment_lines rsl,
po_headers_all poh,
hr_organization_information hoi1,
hr_organization_information hoi2
WHERE
t.source_document_code = 'INVENTORY'
AND t.transaction_id = mmt.rcv_transaction_id
AND mmt.transaction_id = transaction_row.transaction_id
AND mmt.organization_id = hoi2.organization_id
AND mmt.transfer_organization_id = hoi1.organization_id
AND hoi1.org_information_context = 'Accounting Information'
AND hoi2.org_information_context = 'Accounting Information'
AND t.shipment_header_id = r.shipment_header_id
AND r.receipt_source_code in ('INVENTORY')
AND t.shipment_line_id = rsl.shipment_line_id
AND t.po_header_id = poh.po_header_id (+);
SELECT item_number INTO l_item_no FROM mtl_item_flexfields
WHERE inventory_item_id = p_item_id AND organization_id = p_orgn_id;
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id
AND batchstep_id NOT IN
(
SELECT batchstep_id
FROM gme_batch_step_dependencies
WHERE batch_id = p_batch_id
UNION ALL
SELECT dep_step_id
FROM gme_batch_step_dependencies
WHERE batch_id = p_batch_id
)
ORDER BY batchstep_id
;
select the default value from fiscal policy and use it in cost details */
-- Bug 13038249 Grupo viz changes starts
x_mtl_analysis_code := l_mtl_analysis_code;
/* SELECT nvl(routing_id,0) INTO l_routing
FROM gme_batch_header
WHERE batch_type = 0
AND batch_id = transaction_row.doc_id; -- Bug 13038249 */
SELECT uom_code INTO l_step_tab(1).step_qty_uom
FROM mtl_units_of_measure
WHERE uom_class = l_um_type AND base_uom_flag = 'Y';
SELECT count(*) INTO l_dep_steps
FROM gme_batch_step_dependencies
WHERE batch_id = transaction_row.doc_id;
INSERT INTO gme_batch_step_dependencies
( batch_id
, batchstep_id
, dep_step_id
, standard_delay
, dep_type
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
( transaction_row.doc_id
, step_row.batchstep_id
, l_prior_step_id
, 0
, 0
, -1
, SYSDATE
, -1
, SYSDATE
);
DELETE FROM gme_batch_step_dependencies
WHERE batch_id = transaction_row.doc_id;
SELECT MIN(dep_step_id)
INTO l_min_dep_step_id
FROM gme_batch_step_dependencies
START WITH batch_id = transaction_row.doc_id
AND batchstep_id NOT IN (SELECT dep_step_id
FROM gme_batch_step_dependencies
WHERE batch_id = transaction_row.doc_id)
CONNECT BY PRIOR dep_step_id = batchstep_id
AND batch_id = PRIOR batch_id;
** In the below insert, we will add two new records as
** batchstep_id : 30 (dep_step_id : 20)
** batchstep_id : 20 (dep_step_id : 10).
*/
FOR i IN 1..l_independent_steps_cnt
LOOP
INSERT INTO gme_batch_step_dependencies
( batch_id
, batchstep_id
, dep_step_id
, standard_delay
, dep_type
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
( transaction_row.doc_id
, l_cur_step_id
, l_independent_steps(i)
, 0
, 0
, -1
, SYSDATE
, -1
, SYSDATE
);
/* Delete only those records which were inserted above.
** Eg: We have records 10,20,30,40,50 and 30,40,50 have dependency existing.
** There will be two records in the gme_batch_step_Dependency table
** as 40 (dep_step_id : 30)
** and 50 (dep_step_id : 40)
**
** In the above insert, we have added two new records as
** 30 (dep_step_id : 20)
** 20 (dep_step_id : 10).
**Now we need to delete these two newly inserted records.
*/
FOR i IN 1 .. l_independent_steps_cnt
LOOP
DELETE FROM gme_batch_step_dependencies
WHERE batch_id = transaction_row.doc_id
AND dep_step_id = l_independent_steps(i);
SELECT COUNT (DISTINCT batchstep_id)
INTO l_count
FROM gme_material_details gmd, gme_batch_step_items gbsi
WHERE gmd.material_detail_id = gbsi.material_detail_id
AND gmd.batch_id = gbsi.batch_id
AND gmd.line_type = 1
AND gmd.batch_id = transaction_row.doc_id;
SELECT COUNT (DISTINCT mmt.inventory_item_id)
INTO l_unassociated_prds
FROM mtl_material_transactions mmt, gme_material_details gmd
WHERE mmt.transaction_source_type_id = 5
AND mmt.transaction_source_id = gmd.batch_id
AND gmd.line_type = 1
AND mmt.transaction_quantity <> 0
AND gmd.batch_id =transaction_row.doc_id
AND mmt.trx_source_line_id = gmd.material_detail_id
AND gmd.material_detail_id NOT IN (
SELECT material_detail_id
FROM gme_batch_step_items
WHERE batch_id = transaction_row.doc_id);
total_alt_cost_tab.delete;
SELECT to_char(TRUNC(l_step_tab(i).materials(j).trans_date),'J')
into k_date from dual ;
total_alt_cost_tab.delete;
select to_char(TRUNC(l_step_tab(i).materials(j).trans_date),'J')
into k_date from dual ;
new_cost_tab.delete;
cur_cost_tab.delete;
l_step_tab(i).step_costs.DELETE(l_step_tab(i).step_costs.COUNT);
new_cost_tab.delete;
old_cost_tab.delete;
cur_cost_tab.delete; /* Bug 3533452 */
l_dtl_inserted := 0; -- Bug 13038249
IF (new_cost_tab(k).component_cost <> 0 OR (l_dtl_inserted = 0 AND k = new_cost_tab.count )) THEN -- Bug 13038249 Modified condition and restored IF
create_cost_detail
( new_cost.header_id
, new_cost_tab(k).cost_cmpntcls_id
, new_cost_tab(k).cost_analysis_code
, new_cost_tab(k).cost_level
, new_cost_tab(k).component_cost/l_temp_qty -- B9131983
, 0
, l_return_status
);
l_dtl_inserted := 1; -- Bug 13038249
l_dtl_inserted := 0; -- Bug 13038249
IF ( prd_cost_tab(k).component_cost <> 0 OR (l_dtl_inserted = 0 AND k = prd_cost_tab.COUNT )) THEN -- B9131983 Bug 13038249 Modified condition and restored IF
create_cost_detail
( -new_cost.header_id
, prd_cost_tab(k).cost_cmpntcls_id
, prd_cost_tab(k).cost_analysis_code
, prd_cost_tab(k).cost_level
, prd_cost_tab(k).component_cost/l_temp_trans_qty -- B9131983
, 0
, l_return_status
);
l_dtl_inserted := 1; -- Bug 13038249
fnd_file.put_line (fnd_file.log, 'Completed inserts into tables in process_batch ');
l_dtl_inserted := 0; -- Bug 13038249
IF ( new_cost_tab(k).component_cost <> 0 OR (l_dtl_inserted = 0 AND k = new_cost_tab.count ) ) THEN -- Bug 13038249 Modified condition and restored IF
/* B9131983 If cost is ZERO or component is not available
enter the default row in cost details */
IF new_cost_tab(k).cost_cmpntcls_id = 0 THEN
new_cost_tab(k).cost_cmpntcls_id := x_mtl_cmpntcls_id;
l_dtl_inserted := 1; -- Bug 13038249
l_dtl_inserted := 0;
IF ( prd_cost_tab(k).component_cost <> 0 OR (l_dtl_inserted = 0 AND k = prd_cost_tab.COUNT )) THEN -- B9131983 Bug 13038249 Modified condition and restored IF
create_cost_detail
( -new_cost.header_id
, prd_cost_tab(k).cost_cmpntcls_id
, prd_cost_tab(k).cost_analysis_code
, prd_cost_tab(k).cost_level
, prd_cost_tab(k).component_cost/l_temp_trans_qty -- B9131983
, 0
, l_return_status
);
l_dtl_inserted := 1; -- Bug 13038249
/*UPDATE mtl_transaction_lot_numbers
SET lot_cost_ind = 1,
--request_id = l_request_id,
--program_application_id = l_prog_appl_id,
--program_id = l_program_id,
last_update_date = sysdate
WHERE transaction_id = transaction_row.transaction_id;*/
UPDATE gme_batch_header
SET actual_cost_ind = 1
WHERE batch_id = transaction_row.doc_id;
and calculate lot cost also insert a record in lot cost adjustments table
AUTHOR
Andrea 4-Aug-09, LCM-OPM Integration, bug 8642337
Parag Kanetkar Bug 10159100 Prorate quantities
HISTORY
*************************************************************************/
PROCEDURE process_lc_adjustments IS
CURSOR c_adjustments_cursor IS
SELECT
glat.cost_cmpntcls_id,
glat.cost_analysis_code,
(nvl(glat.new_landed_cost,0) - nvl(glat.prior_landed_cost,0)) * (mtln.primary_quantity /rt.primary_quantity ) adjustment_amt,
nvl(glat.new_landed_cost,0),
glat.adj_transaction_id,
glat.ship_header_id,
glat.ship_line_id,
glat.adjustment_num,
glat.organization_id,
glat.inventory_item_id,
glat.rcv_transaction_id,
glat.charge_line_type_code,
glat.component_type,
glat.component_name,
glat.transaction_date,
glat.primary_quantity,
glat.primary_uom_code,
glat.lc_adjustment_flag,
mtln.lot_number, -- Bug 13906192
0 as applied_amt, -- Bug 10159100
mtln.primary_quantity as received_qty -- Bug 13906192
FROM
gmf_lc_adj_transactions glat,
rcv_transactions rt,
mtl_material_transactions mmt, -- Bug 13906192
mtl_transaction_lot_numbers mtln -- Bug 13906192
WHERE
glat.adj_transaction_id = transaction_row.transaction_id
AND glat.rcv_transaction_id = transaction_row.doc_id
AND glat.ship_line_id = transaction_row.line_id
AND (lc_adjustment_flag = 1 OR glat.adjustment_num > 0 )
AND glat.rcv_transaction_id = rt.transaction_id
AND rt.transaction_id = mmt.rcv_transaction_id -- Bug 13906192
AND mmt.transaction_id = mtln.transaction_id -- Bug 13906192
AND glat.component_type IN ('ITEM PRICE','CHARGE')
AND mtln.lot_number = transaction_row.lot_number; -- Bug 13906192
SELECT 1 FROM gmf_lc_lot_cost_adjs
WHERE adj_transaction_id = cp_adj_transaction_id
AND lot_number = cp_lot_number ;
/* If LC lot cost adjustments exists update else insert adjustment record */
IF l_count >0 THEN
UPDATE gmf_lc_lot_cost_adjs lca SET
lca.lot_costed_flag = l_final_run_flag
, lca.last_update_date = sysdate
, lca.last_updated_by = l_user_id
, lca.last_update_login = l_user_id
, lca.program_application_id = l_prog_appl_id
, lca.program_id = l_program_id
, lca.request_id = l_request_id
WHERE
lca.adj_transaction_id = l_adjustments_cursor(i).adj_transaction_id
AND lca.lot_number = transaction_row.lot_number ;
INSERT INTO gmf_lc_lot_cost_adjs(
lc_adjustment_id, --01
adj_transaction_id, --02
adjustment_quantity, --03
costed_quantity, --04
total_quantity, --05
quantity_uom_code, --06
unit_base_price, --07
base_amount, --08
trans_amount, --09
base_adj_amount, --10
trans_adj_amount, --11
cost_type_id, --12
accounted_flag, --13
final_posting_date, --14
lot_number, --15
lot_costed_flag , --16
onhand_quantity, --17
old_cost_header_id, --18
new_cost_header_id, --19
creation_date, --20
created_by, --21
last_update_date, --22
last_updated_by, --23
last_update_login, --24
request_id, --25
program_application_id, --26
program_id, --27
program_udpate_date --28
)
VALUES(
gmf_lc_actual_adjs_s.NEXTVAL, --01
l_adjustments_cursor(i).adj_transaction_id, --02
new_cost.onhand_qty, --03
new_cost.onhand_qty, --04
new_cost.onhand_qty, --05
transaction_row.trans_um, --06
new_cost.unit_cost, --07
l_adjustments_cursor(i).adjustment_amt, --08 base_amount
l_adjustments_cursor(i).adjustment_amt, --09 trans_amount
l_adjustments_cursor(i).applied_amt, --10 base_adj_amount Bug 10159100 changed to applied_amt
l_adjustments_cursor(i).applied_amt, --11 trans_adj_amount Bug 10159100 changed to applied_amt
l_cost_type_id, --12
'N', --13
NULL, --14
transaction_row.lot_number, --15
l_final_run_flag, --16
new_cost.onhand_qty, --17
old_cost.header_id, --18
new_cost.header_id, --19
SYSDATE, --20
l_user_id, --21
SYSDATE, --22
l_user_id, --23
0, --24
l_request_id, --25
l_prog_appl_id, --26
l_program_id, --27
SYSDATE --28
);
PROCEDURE delete_lot_costs
IS
TYPE lot_cost_cursor_type IS REF CURSOR;
l_rows_to_delete PLS_INTEGER;
l_matl_rows_deleted PLS_INTEGER;
l_cdtl_rows_deleted PLS_INTEGER;
procedure_name := 'Delete Lot Costs';
l_rows_to_delete := 1000;
l_matl_rows_deleted := 0;
l_cdtl_rows_deleted := 0;
SELECT glc.header_id, glc.rowid
FROM gmf_lot_costs glc,
gmf_lot_costed_items_gt gpo
WHERE glc.organization_id = gpo.organization_id
AND glc.inventory_item_id = gpo.inventory_item_id
AND glc.cost_type_id = l_cost_type_id
AND gpo.organization_id IN (select organization_id from gmf_process_organizations_gt
where legal_entity_id = l_le_id)/* Bug 8687115 */
AND glc.final_cost_flag = 0
--AND glc.inventory_item_id = l_item_id /*jboppana*/
AND glc.lot_number = DECODE(l_lot_no, NULL, glc.lot_number, l_lot_no)
;
IF l_header_ids_tab.count <= l_rows_to_delete THEN
l_indx_to := l_header_ids_tab.count;
l_indx_to := l_rows_to_delete;
l_max_loop_cnt := ceil(l_header_ids_tab.count/l_rows_to_delete);
fnd_file.put_line(fnd_File.LOG, '#of rows to delete in cost header: ' || l_header_ids_tab.count);
DELETE FROM gmf_material_lot_cost_txns
WHERE cost_header_id in l_header_ids_tab(indx);
l_matl_rows_deleted := l_matl_rows_deleted + SQL%ROWCOUNT;
DELETE FROM gmf_lot_cost_details
WHERE abs(header_id) in l_header_ids_tab(indx);
l_cdtl_rows_deleted := l_cdtl_rows_deleted + SQL%ROWCOUNT;
l_remaining_rows := l_header_ids_tab.COUNT - (i * l_rows_to_delete);
EXIT WHEN (l_header_ids_tab.count <= l_rows_to_delete) OR
(l_remaining_rows < 0);
IF l_remaining_rows <= l_rows_to_delete THEN
l_indx_from := l_indx_to + 1;
l_indx_to := l_indx_to + l_rows_to_delete;
DELETE FROM gmf_lot_costs
WHERE rowid in l_rowids_tab(indx);
fnd_file.put_line(fnd_File.LOG, ' ' || l_matl_rows_deleted || ' rows deleted from gmf_material_lot_cost_txns.');
fnd_file.put_line(fnd_File.LOG, ' ' || l_cdtl_rows_deleted || ' rows deleted from gmf_lot_cost_details.');
fnd_file.put_line(fnd_File.LOG, ' ' || SQL%ROWCOUNT || ' rows deleted from gmf_lot_costs.');
/***** Bug 4094132 - Added the following Delete - Start *****/
-- Delete the residual transactions for which header is final costed
-- but because of reversal, one more transaction got created for the same header.
DELETE
FROM gmf_material_lot_cost_txns t
WHERE cost_type_id = l_cost_type_id
AND t.final_cost_flag = 0 -- Bug 7173679
AND EXISTS (
SELECT 1
FROM gmf_lot_costs glc,
gmf_process_organizations_gt gpo
WHERE glc.organization_id = gpo.organization_id
AND glc.header_id = t.cost_header_id
AND glc.cost_type_id = t.cost_type_id
AND glc.final_cost_flag = 1
);
l_matl_rows_deleted := l_matl_rows_deleted + SQL%ROWCOUNT;
IF l_matl_rows_deleted = 0 THEN
fnd_file.put_line(fnd_File.LOG, ' No rows found to delete.');
/***** Bug 4094132 - Added the above Delete - End *****/
IF l_debug_level >= l_debug_level_medium THEN
fnd_file.put_line(fnd_file.log,'Leaving Procedure: '||procedure_name);
END delete_lot_costs;
SELECT SYSTEM.gmf_cost_type
( lcad.cost_cmpntcls_id
, lcad.cost_analysis_code
, 0
, lcad.adjustment_cost
, 0
)
FROM gmf_lot_cost_adjustment_dtls lcad
WHERE lcad.adjustment_id = transaction_row.doc_id
AND lcad.delete_mark = 0;
select the default value from fiscal policy and use it in cost details */
SELECT mtl_analysis_code, mtl_cmpntcls_id
INTO x_mtl_analysis_code, x_mtl_cmpntcls_id
FROM GMF_FISCAL_POLICIES WHERE legal_entity_id = l_le_id ;
old_cost_tab.delete;
l_dtl_inserted := 0; -- Bug 13038249
IF ( old_cost_tab(k).component_cost <> 0 OR (l_dtl_inserted = 0 AND k = old_cost_tab.count )) THEN -- Bug 13038249 Modified condition and restored IF This is redundant though
/* B9131983 If cost is ZERO or component is not available
enter the default row in cost details */
IF old_cost_tab(k).cost_cmpntcls_id = 0 THEN
old_cost_tab(k).cost_cmpntcls_id := x_mtl_cmpntcls_id;
l_dtl_inserted := 1; -- Bug 13038249
l_dtl_inserted := 0; -- Bug 13038249
IF ( old_cost_tab(k).component_cost <> 0 OR (l_dtl_inserted = 0 AND k = old_cost_tab.COUNT )) THEN
/* B9131983 If cost is ZERO or component is not available
enter the default row in cost details */
IF old_cost_tab(k).cost_cmpntcls_id = 0 THEN
old_cost_tab(k).cost_cmpntcls_id := x_mtl_cmpntcls_id;
l_dtl_inserted := 1; -- Bug 13038249
SELECT SYSTEM.gmf_cost_type(nct.cost_cmpntcls_id,
nct.cost_analysis_code,
nct.cost_level,
sum(nct.component_cost),
nct.burden_ind)
FROM TABLE ( cast(l_cost_Table AS SYSTEM.gmf_cost_tab) ) nct
GROUP BY nct.cost_cmpntcls_id,nct.cost_analysis_code,nct.cost_level,nct.burden_ind;
SELECT SUM(nct.component_cost)
INTO x_total_cost
FROM TABLE ( CAST(l_cost_table AS SYSTEM.gmf_cost_tab) ) nct;
added inventory_asset_flag and process_costing_enabled_flag to the insert query
ANTHIYAG Bug#5279681
Modified Query to correct the Query which fetches item codes based on Category
Codes and also to add delete_mark check for the first query
Pramod Bug 13386258 - Re-org the code
==========================================================*/
PROCEDURE Load_Lot_Costed_Items_gt(p_le_id IN NUMBER,
p_orgn_id IN NUMBER,
p_item_id IN NUMBER,
p_category_id IN NUMBER,
x_return_status OUT NOCOPY NUMBER
) IS
l_from_orgn_code VARCHAR2(4) := NULL;
SELECT organization_code,
organization_id
FROM gmf_process_organizations_gt
ORDER BY organization_code;
INSERT
INTO GMF_LOT_COSTED_ITEMS_GT
(
organization_id,
inventory_item_id,
primary_uom_code
)
SELECT
msi.organization_id,
msi.inventory_item_id,
msi.primary_uom_code
FROM gmf_lot_costed_items lci,
mtl_system_items_b msi,
gmf_process_organizations_gt gpo
WHERE lci.legal_entity_id = l_le_id
AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
AND gpo.organization_id = msi.organization_id
AND msi.lot_control_code = 2
AND lci.inventory_item_id = msi.inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND msi.process_costing_enabled_flag = 'Y'
AND lci.inventory_item_id = NVL(p_item_id,lci.inventory_item_id)
AND lci.cost_type_id = l_cost_type_id
AND
(
(
p_item_id IS NULL
AND p_category_id IS NULL
)
OR
(
p_item_id IS NOT NULL
)
);
INSERT
INTO GMF_LOT_COSTED_ITEMS_GT
(
organization_id,
inventory_item_id,
primary_uom_code
)
SELECT
i.organization_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
i.inventory_item_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
i.primary_uom_code
FROM mtl_item_categories mic,
mtl_default_category_sets mdc,
mtl_category_sets mcs,
gmf_lot_costed_items g,
mtl_system_items_b i,
gmf_process_organizations_gt gpo
WHERE g.cost_category_id = mic.category_id
AND g.legal_entity_id = l_le_id
AND mdc.functional_area_id = 19
AND mdc.category_set_id = mcs.category_set_id
AND mic.category_set_id = mcs.category_set_id
AND g.delete_mark = 0
AND i.lot_control_code = 2
AND gpo.organization_id = i.organization_id
AND i.organization_id = mic.organization_id
AND mic.inventory_item_id = i.inventory_item_id
AND i.inventory_asset_flag = 'Y'
AND i.process_costing_enabled_flag = 'Y'
AND g.cost_type_id = l_cost_type_id
AND g.cost_category_id = NVL(p_category_id,g.cost_category_id)
AND
(
(
p_item_id IS NULL
AND p_category_id IS NULL
)
OR
(
p_category_id IS NOT NULL
)
)
AND not exists ( -- Exclude item/orgs from 1st insert
select 1
from GMF_LOT_COSTED_ITEMS_GT lcig
where lcig.inventory_item_id = i.inventory_item_id
and lcig.organization_id = i.organization_id
);
INSERT
INTO GMF_LOT_COSTED_ITEMS_GT
(
organization_id,
inventory_item_id,
primary_uom_code
)
SELECT
mic.organization_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
mic.inventory_item_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
i.primary_uom_code
FROM mtl_item_categories mic,
mtl_default_category_sets mdc,
mtl_category_sets mcs,
gmf_lot_costed_items g,
mtl_system_items_b i,
gmf_process_organizations_gt gpo
WHERE g.cost_category_id = mic.category_id
AND g.legal_entity_id = l_le_id
AND mdc.functional_area_id = 19
AND mdc.category_set_id = mcs.category_set_id
AND mic.category_set_id = mcs.category_set_id
AND g.delete_mark = 0
AND i.lot_control_code = 2
AND gpo.organization_id = i.organization_id
AND i.organization_id = mic.organization_id
AND mic.inventory_item_id = i.inventory_item_id
AND i.inventory_asset_flag = 'Y'
AND i.process_costing_enabled_flag = 'Y'
AND g.cost_type_id = l_cost_type_id
AND mic.inventory_item_id = p_item_id
and not exists ( -- Exclude item/orgs from 1st, 2nd inserts
select 1
from GMF_LOT_COSTED_ITEMS_GT lcig
where lcig.inventory_item_id = mic.inventory_item_id
and lcig.organization_id = mic.organization_id
);
SELECT mln.lot_number
INTO l_parent_lot_number
FROM mtl_transaction_lot_numbers mln,
mtl_material_transactions mmt
WHERE mmt.transaction_id = transaction_row.transfer_transaction_id
AND mmt.transaction_id = mln.transaction_id;
SELECT mtln.lot_number,
ABS(mtln.primary_quantity),
mtln.transaction_date
FROM mtl_transaction_lot_numbers mtln, mtl_material_transactions mmt
WHERE mmt.transfer_transaction_id = transaction_row.transfer_transaction_id
AND mmt.transaction_id <> transaction_row.transfer_transaction_id
AND mmt.transaction_id = mtln.transaction_id ;
SELECT lot_number,
transaction_date
INTO l_lot_number,
l_trans_date
FROM mtl_transaction_lot_numbers
WHERE transaction_id = transaction_row.transfer_transaction_id;
SELECT default_ovh_cmpntcls_id,
default_ovh_analysis_code,
0,
p_trp_cost,
1
FROM gmf_fiscal_policies
WHERE legal_entity_id = p_le_id ;
new_cost_tab.delete;
SELECT transaction_id
INTO l_trans_id
FROM mtl_material_transactions
WHERE transfer_transaction_id = transaction_row.transfer_transaction_id
AND transaction_action_id = 15;
SELECT cost_header_id
INTO l_header_id
FROM gmf_material_lot_cost_txns gmlc
WHERE transaction_id = l_trans_id
AND lot_number = transaction_row.lot_number
AND cost_type_id = l_cost_type_id;
SELECT * INTO new_cost
FROM gmf_lot_costs
WHERE header_id = l_header_id;
SELECT text
FROM user_source
WHERE name = 'GMF_LOT_COSTING_PUB'
AND type = 'PACKAGE BODY'
AND text like '%$Header%'
AND line < 20;
SELECT /*+ index(hoi,HR_ORGANIZATION_INFORMATIO_FK1) */
mp.organization_id,
mp.organization_code,
mp.process_enabled_flag,
ou.org_information2 le_id,
ou.org_information3 ou_id
FROM mtl_parameters mp,
hr_organization_information ou
WHERE ou.org_information_context = 'Accounting Information'
AND mp.organization_id = ou.organization_id
ORDER BY organization_id ;
SELECT period_id,
TO_CHAR( (TRUNC(start_date) + (just_a_column -1 )),'J') final_date,
TRUNC(start_date) start_date,
TRUNC(end_date) end_date
FROM gmf_period_statuses c,
(SELECT ROWNUM just_a_column
FROM dual
CONNECT BY LEVEL <= 7400 )
WHERE cost_type_id = l_default_cost_type_id
AND legal_entity_id = l_le_id
AND delete_mark = 0
AND DECODE(TO_CHAR(start_date,'DD'),1,1,1) <= just_a_column
AND (TRUNC(end_date)-trunc(start_date)+1) >= just_a_column
AND start_date >= l_trans_start_date
ORDER BY 2 ;
cost_alloc_profile := l_cost_alloc_profile; -- Bug 13038249 l_cost_alloc_profile may get updated later.
SELECT user_name INTO l_user
FROM fnd_user
WHERE user_id = l_user_id;
SELECT m1.default_lot_cost_type_id
, m1.trans_start_date
, m1.cost_mthd_code
, m2.cost_mthd_code
INTO l_default_cost_type_id,
l_trans_start_date,
l_cost_mthd_code,
l_default_cost_mthd
FROM cm_mthd_mst m1,
cm_mthd_mst m2
WHERE m1.cost_type_id = l_cost_type_id
AND m2.cost_type_id = m1.default_lot_cost_type_id;
SELECT mtl_analysis_code, mtl_cmpntcls_id, base_currency_code
INTO l_mtl_analysis_code, l_mtl_cmpntcls_id , l_base_ccy_code
FROM GMF_FISCAL_POLICIES
WHERE legal_entity_id = l_le_id ;
delete_lot_costs;
Change 2 : Added three more columns to be selected in the select clause
DECODE( NVL(gme.phantom_type,0),1,(mmt.transaction_date + (1/(2*24 * 3600)) ),
mmt.transaction_date) phantom_trans_date
gme.phantom_type phantom_type
gtp.pair_type pair_type
Change 3 : Changed the order by clause from ORDER BY 7,decode(transaction_action_id,27,-1,transaction_source_type_id)... to
ORDER BY decode(phantom_type,1,phantom_trans_date,trans_date),decode(transaction_action_id,27,-1,transaction_source_type_id)...
*/
-- PK Bug 9069363 use in line query for gme_transaction_pairs we do not want to consider pair_type 2 records since such records are created for
-- phantom batches even when there is no reversals causing duplicate selection of data.
-- Bug 13038249 Grupo viz changes starts
fnd_file.put_line(fnd_File.LOG,'Reading uncosted transactions: '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
SELECT a.doc_id,
a.transaction_source_type_id,
a.inventory_item_id,
a.line_id,
a.line_type,
a.lot_number,
a.trans_date,
a.transaction_id,
a.trans_qty,
trans_um,
orgn_id,
source,
reverse_id,
transaction_action_id,
transfer_price,
transportation_cost,
fob_point,
transfer_transaction_id,
transaction_cost,
transfer_orgn_id,
phantom_trans_date,
phantom_type,
pair_type,
oc1,
oc2,
oc3,
routing_id,
DECODE(NVL(lcb.item_id,0),0,0,1) AS burden_ind
FROM ( SELECT DISTINCT cb.organization_id, cb.inventory_item_id as item_id
FROM gmf_lot_cost_burdens cb,
gmf_process_organizations_gt gpo
WHERE cb.cost_type_id = l_cost_type_id
AND cb.organization_id = gpo.organization_id
)lcb,
-- Bug 13038249 Grupo viz changes End
(SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id as line_id,
NVL(gme.line_type,0) as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id as orgn_id,
1 source,
Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL) as reverse_id, --nvl(gtp.transaction_id2,NULL) as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0) as transfer_price,
nvl(mmt.transportation_cost,0) as transportation_cost,
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0) as transaction_cost,
mmt.transfer_organization_id as transfer_orgn_id,
DECODE( NVL(gme.phantom_type,0),1,(mmt.transaction_date + (1/(2*24 * 3600)) ),
mmt.transaction_date) phantom_trans_date,
gme.phantom_type phantom_type,
gtp.pair_type pair_type,
decode(gme.phantom_type,1,DECODE( NVL(gme.phantom_type,0),1,(mmt.transaction_date + (1/(2*24 * 3600)) ),
mmt.transaction_date),mmt.transaction_date) as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
DECODE(gme.line_type,1, DECODE((ABS(DECODE(mtln.primary_quantity, 0, 1,mtln.primary_quantity))/DECODE(mtln.primary_quantity, 0, 1, mtln.primary_quantity)),
1, mmt.transaction_id ,
DECODE(Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL), NULL, mmt.transaction_id, (Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL)+.5))),mmt.transaction_id) as oc3,
NVL(gbh.routing_id, 0) as routing_id
FROM mtl_material_transactions mmt,
gme_material_details gme,
mtl_transaction_lot_numbers mtln,
(Select pair_type, transaction_id1, transaction_id2 From gme_transaction_pairs where pair_type = 1) gtp, -- PK Bug 9069363
gmf_process_organizations_gt gpo,
gme_batch_header gbh,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.trx_source_line_id = gme.material_detail_id
AND gme.batch_id = gbh.batch_id
AND mmt.transaction_id = gtp.transaction_id1 (+)
AND mmt.transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.organization_id = NVL(l_orgn_id,mmt.organization_id)
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
AND mmt.transaction_source_type_id = 5
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
AND gme.phantom_type IN (select (decode(phantom_type,1,1,0))
from gme_material_details gme1 where mmt.trx_source_line_id = gme1.material_detail_id)
UNION ALL
SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id,
0 as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id,
2 source,
NULL as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0),
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0),
mmt.transfer_organization_id,
mmt.transaction_date as phantom_trans_date,
-1 as phantom_type,
null,
mmt.transaction_date as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
mmt.transaction_id as oc3,
0 as routing_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.organization_id = NVL(l_orgn_id,mmt.organization_id)
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
AND mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) /* ANTHIYAG Bug#5460458 11-Aug-2006 */
AND NVL(mmt.owning_tp_type,2) = 2 /* ANTHIYAG Bug#5460458 11-Aug-2006 */
AND mmt.transaction_source_type_id <> 5
AND mmt.transaction_action_id NOT IN (15,22,6,2,50,51,28,36) /* PK added subinv Xfer Bug 9616762 Ignore container pack unpack Staging Xfers */
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
UNION ALL
SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id,
0 as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id,
2 source,
NULL as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0),
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0),
mmt.transfer_organization_id,
mmt.transaction_date as phantom_trans_date,
-1 as phantom_type,
null,
mmt.transaction_date as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
mmt.transaction_id as oc3,
0 as routing_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.owning_organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.owning_tp_type = 2
AND mmt.transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.owning_organization_id = NVL(l_orgn_id,mmt.owning_organization_id)
AND mmt.transaction_source_type_id = 1
AND mmt.transaction_action_id = 6
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
UNION ALL /*sschinch INVCONV this query will pickup logical shipments and receipts */
SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id,
0 as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id,
2 source,
NULL as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0),
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0),
mmt.transfer_organization_id,
mmt.transaction_date as phantom_trans_date,
-1 as phantom_type,
null,
mmt.transaction_date as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
mmt.transaction_id as oc3,
0 as routing_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.transfer_transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.organization_id = NVL(l_orgn_id,mmt.organization_id)
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
AND mmt.transaction_source_type_id IN (8,7,13)
AND mmt.transaction_action_id IN (15,22)
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
UNION ALL
SELECT
glca.adjustment_id doc_id,
0 transaction_source_type_id,
glca.inventory_item_id,
glca.adjustment_id line_id,
0 as line_type ,
glca.lot_number ,
glca.adjustment_date trans_date ,
-9 transaction_id,
0 trans_qty,
glci.primary_uom_code trans_um,
glca.organization_id,
3 source,
NULL as reverse_id,
0 as transaction_action_id,
0 as transfer_price,
0 as transportation_cost,
0 as fob_point,
0 as transfer_transaction_id,
0 as transaction_cost,
0 as transfer_transaction_id,
glca.adjustment_date as phantom_trans_date,
-1 as phantom_type,
null,
glca.adjustment_date as oc1,
0 as oc2,
-9 as oc3,
0 as routing_id
FROM gmf_lot_cost_adjustments glca,
-- mtl_system_items_b iimb,
gmf_lot_costed_items_gt glci
WHERE glca.applied_ind = 'N'
-- AND glca.adjustment_date >= NVL(l_trans_start_date, glca.adjustment_date) changed for performance as below B14616815
AND glca.adjustment_date >= l_trans_start_date
AND glca.legal_entity_id = l_le_id
AND glca.cost_type_id = l_cost_type_id
AND glca.delete_mark = 0
-- AND iimb.inventory_item_id = glca.inventory_item_id
-- AND glca.organization_id = iimb.organization_id
-- Perf B14616815 AND glca.organization_id = NVL(l_orgn_id,glca.organization_id)
AND glca.inventory_item_id = glci.inventory_item_id
AND glca.organization_id = glci.organization_id
AND glca.adjustment_date <= l_final_run_date
-- Perf B14616815 AND glca.lot_number = nvl(p_lot_no,glca.lot_number)
AND EXISTS
(SELECT 1 FROM gmf_lot_cost_adjustment_dtls
WHERE adjustment_id = glca.adjustment_id
AND delete_mark = 0
)
--LCM-OPM Integration, Load Actual LC adjustment transactions, AF
UNION ALL
SELECT
glat.rcv_transaction_id doc_id,
50 transaction_source_type_id,
glat.inventory_item_id,
glat.ship_line_id line_id,
0 as line_type ,
mtln.lot_number, -- Bug 13906192
decode(sign(glat.transaction_date-rt.transaction_date), 1, glat.transaction_date, rt.transaction_date) trans_date,
glat.adj_transaction_id transaction_id,
glat.primary_quantity,
glat.primary_uom_code trans_um,
glat.organization_id,
4 source,
NULL as reverse_id,
0 as transaction_action_id,
0 as transfer_price,
0 as transportation_cost,
0 as fob_point,
0 as transfer_transaction_id,
0 as transaction_cost,
0 as transfer_transaction_id,
glat.transaction_date as phantom_trans_date,
-1 as phantom_type,
NULL,
decode(sign(glat.transaction_date-rt.transaction_date), 1, glat.transaction_date, rt.transaction_date) oc1,
0 as oc2,
-9 as oc3,
0 as routing_id
FROM
gmf_lc_adj_transactions glat, -- from import process of LCM
rcv_transactions rt,
mtl_material_transactions mmt, -- Bug 13906192
mtl_transaction_lot_numbers mtln, -- Bug 13906192
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt glci
WHERE glat.transaction_date >= l_trans_start_date
-- glat.transaction_date >= NVL(l_trans_start_date,glat.transaction_date) performance changed as above B14616815
-- AND glat.legal_entity_id = l_le_id -- bug 12866252
AND rt.transaction_id = mmt.rcv_transaction_id -- Bug 13906192
AND mmt.transaction_id = mtln.transaction_id -- Bug 13906192
-- Perf B14616815 AND mtln.lot_number = NVL(p_lot_no,mtln.lot_number) -- Bug 13906192
AND rt.transaction_id = glat.rcv_transaction_id
AND gpo.organization_id = glat.organization_id
-- Perf B14616815 AND glat.organization_id = NVL(l_orgn_id,glat.organization_id)
AND glci.organization_id = glat.organization_id
AND glci.inventory_item_id = glat.inventory_item_id
AND glat.transaction_date <= l_final_run_date
AND glat.event_type IN (16,17)
AND glat.component_type IN ('ITEM PRICE','CHARGE')
AND glat.cost_acquisition_flag = 'I'
AND (glat.lc_adjustment_flag = 1 OR glat.adjustment_num > 0) /* Load only actual lc adj */
AND NOT EXISTS (SELECT 1
FROM gmf_lc_lot_cost_adjs gllca
WHERE gllca.adj_transaction_id = glat.adj_transaction_id
AND gllca.cost_type_id = l_cost_type_id
AND gllca.lot_number = mtln.lot_number -- Bug 13906192
AND gllca.lot_costed_flag = 1)
-- End LCM-OPM Integration, AF
) a
WHERE a.orgn_id = lcb.organization_id (+)
AND a.inventory_item_id = lcb.item_id (+)
ORDER BY 24, 25, 5, 26, 6 ;
SELECT a.doc_id,
a.transaction_source_type_id,
a.inventory_item_id,
a.line_id,
a.line_type,
a.lot_number,
a.trans_date,
a.transaction_id,
a.trans_qty,
trans_um,
orgn_id,
source,
reverse_id,
transaction_action_id,
transfer_price,
transportation_cost,
fob_point,
transfer_transaction_id,
transaction_cost,
transfer_orgn_id,
phantom_trans_date,
phantom_type,
pair_type,
oc1,
oc2,
oc3,
routing_id,
DECODE(NVL(lcb.item_id,0),0,0,1) AS burden_ind
FROM ( SELECT DISTINCT cb.organization_id, cb.inventory_item_id as item_id
FROM gmf_lot_cost_burdens cb,
gmf_process_organizations_gt gpo
WHERE cb.cost_type_id = l_cost_type_id
AND cb.organization_id = gpo.organization_id
)lcb,
-- Bug 13038249 Grupo viz changes End
(SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id as line_id,
NVL(gme.line_type,0) as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id as orgn_id,
1 source,
Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL) as reverse_id, --nvl(gtp.transaction_id2,NULL) as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0) as transfer_price,
nvl(mmt.transportation_cost,0) as transportation_cost,
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0) as transaction_cost,
mmt.transfer_organization_id as transfer_orgn_id,
DECODE( NVL(gme.phantom_type,0),1,(mmt.transaction_date + (1/(2*24 * 3600)) ),
mmt.transaction_date) phantom_trans_date,
gme.phantom_type phantom_type,
gtp.pair_type pair_type,
decode(gme.phantom_type,1,DECODE( NVL(gme.phantom_type,0),1,(mmt.transaction_date + (1/(2*24 * 3600)) ),
mmt.transaction_date),mmt.transaction_date) as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
DECODE(gme.line_type,1, DECODE((ABS(DECODE(mtln.primary_quantity, 0, 1,mtln.primary_quantity))/DECODE(mtln.primary_quantity, 0, 1, mtln.primary_quantity)),
1, mmt.transaction_id ,
DECODE(Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL), NULL, mmt.transaction_id, (Decode(nvl(gtp.pair_type,0),1,gtp.transaction_id2,NULL)+.5))),mmt.transaction_id) as oc3,
NVL(gbh.routing_id, 0) as routing_id
FROM mtl_material_transactions mmt,
gme_material_details gme,
mtl_transaction_lot_numbers mtln,
(Select pair_type, transaction_id1, transaction_id2 From gme_transaction_pairs where pair_type = 1) gtp, -- PK Bug 9069363
gmf_process_organizations_gt gpo,
gme_batch_header gbh,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.trx_source_line_id = gme.material_detail_id
AND gme.batch_id = gbh.batch_id
AND mmt.transaction_id = gtp.transaction_id1 (+)
AND mmt.transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.organization_id = NVL(l_orgn_id,mmt.organization_id)
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
AND mmt.transaction_source_type_id = 5
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND mtln.lot_number = l_lot_no
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
AND gme.phantom_type IN (select (decode(phantom_type,1,1,0))
from gme_material_details gme1 where mmt.trx_source_line_id = gme1.material_detail_id)
UNION ALL
SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id,
0 as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id,
2 source,
NULL as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0),
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0),
mmt.transfer_organization_id,
mmt.transaction_date as phantom_trans_date,
-1 as phantom_type,
null,
mmt.transaction_date as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
mmt.transaction_id as oc3,
0 as routing_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.organization_id = NVL(l_orgn_id,mmt.organization_id)
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
AND mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) /* ANTHIYAG Bug#5460458 11-Aug-2006 */
AND NVL(mmt.owning_tp_type,2) = 2 /* ANTHIYAG Bug#5460458 11-Aug-2006 */
AND mmt.transaction_source_type_id <> 5
AND mmt.transaction_action_id NOT IN (15,22,6,2,50,51,28,36) /* PK added subinv Xfer Bug 9616762 Ignore container pack unpack Staging Xfers */
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND mtln.lot_number = l_lot_no
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
UNION ALL
SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id,
0 as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id,
2 source,
NULL as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0),
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0),
mmt.transfer_organization_id,
mmt.transaction_date as phantom_trans_date,
-1 as phantom_type,
null,
mmt.transaction_date as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
mmt.transaction_id as oc3,
0 as routing_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.owning_organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.owning_tp_type = 2
AND mmt.transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.owning_organization_id = NVL(l_orgn_id,mmt.owning_organization_id)
AND mmt.transaction_source_type_id = 1
AND mmt.transaction_action_id = 6
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND mtln.lot_number = l_lot_no
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
UNION ALL /*sschinch INVCONV this query will pickup logical shipments and receipts */
SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_line_id,
0 as line_type,
mtln.lot_number,
mmt.transaction_date as trans_date,
mmt.transaction_id as transaction_id,
mtln.primary_quantity as trans_qty,
lcig.primary_uom_code as trans_um,
mmt.organization_id,
2 source,
NULL as reverse_id,
mmt.transaction_action_id,
nvl(mmt.transfer_price,0),
nvl(mmt.transportation_cost,0),
mmt.fob_point,
mmt.transfer_transaction_id,
NVL(mmt.transaction_cost,0),
mmt.transfer_organization_id,
mmt.transaction_date as phantom_trans_date,
-1 as phantom_type,
null,
mmt.transaction_date as oc1,
decode(mmt.transaction_action_id,27,-1,mmt.transaction_source_type_id) as oc2,
mmt.transaction_id as oc3,
0 as routing_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
-- AND gpo.legal_entity_id = l_le_id -- B 8687115
-- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
AND mmt.transaction_date >= l_trans_start_date
AND mmt.transaction_date <= l_final_run_date
AND mmt.transfer_transaction_id = mtln.transaction_id
-- Perf B14616815 AND mmt.organization_id = NVL(l_orgn_id,mmt.organization_id)
AND mmt.inventory_item_id = lcig.inventory_item_id
AND mmt.organization_id = lcig.organization_id
AND mmt.transaction_source_type_id IN (8,7,13)
AND mmt.transaction_action_id IN (15,22)
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND mtln.lot_number = l_lot_no
AND NOT EXISTS (SELECT 1
FROM GMF_MATERIAL_LOT_COST_TXNS gmlct
WHERE gmlct.transaction_id = mmt.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND gmlct.cost_type_id = l_cost_type_id
AND gmlct.lot_number = mtln.lot_number
AND gmlct.final_cost_flag = 1)
UNION ALL
SELECT
glca.adjustment_id doc_id,
0 transaction_source_type_id,
glca.inventory_item_id,
glca.adjustment_id line_id,
0 as line_type ,
glca.lot_number ,
glca.adjustment_date trans_date ,
-9 transaction_id,
0 trans_qty,
glci.primary_uom_code trans_um,
glca.organization_id,
3 source,
NULL as reverse_id,
0 as transaction_action_id,
0 as transfer_price,
0 as transportation_cost,
0 as fob_point,
0 as transfer_transaction_id,
0 as transaction_cost,
0 as transfer_transaction_id,
glca.adjustment_date as phantom_trans_date,
-1 as phantom_type,
null,
glca.adjustment_date as oc1,
0 as oc2,
-9 as oc3,
0 as routing_id
FROM gmf_lot_cost_adjustments glca,
-- mtl_system_items_b iimb,
gmf_lot_costed_items_gt glci
WHERE glca.applied_ind = 'N'
-- AND glca.adjustment_date >= NVL(l_trans_start_date, glca.adjustment_date) changed for performance as below B14616815
AND glca.adjustment_date >= l_trans_start_date
AND glca.legal_entity_id = l_le_id
AND glca.cost_type_id = l_cost_type_id
AND glca.delete_mark = 0
-- AND iimb.inventory_item_id = glca.inventory_item_id
-- AND glca.organization_id = iimb.organization_id
-- Perf B14616815 AND glca.organization_id = NVL(l_orgn_id,glca.organization_id)
AND glca.inventory_item_id = glci.inventory_item_id
AND glca.organization_id = glci.organization_id
AND glca.adjustment_date <= l_final_run_date
-- Perf B14616815 AND glca.lot_number = nvl(p_lot_no,glca.lot_number)
AND glca.lot_number = l_lot_no
AND EXISTS
(SELECT 1 FROM gmf_lot_cost_adjustment_dtls
WHERE adjustment_id = glca.adjustment_id
AND delete_mark = 0
)
--LCM-OPM Integration, Load Actual LC adjustment transactions, AF
UNION ALL
SELECT
glat.rcv_transaction_id doc_id,
50 transaction_source_type_id,
glat.inventory_item_id,
glat.ship_line_id line_id,
0 as line_type ,
mtln.lot_number, -- Bug 13906192
decode(sign(glat.transaction_date-rt.transaction_date), 1, glat.transaction_date, rt.transaction_date) trans_date,
glat.adj_transaction_id transaction_id,
glat.primary_quantity,
glat.primary_uom_code trans_um,
glat.organization_id,
4 source,
NULL as reverse_id,
0 as transaction_action_id,
0 as transfer_price,
0 as transportation_cost,
0 as fob_point,
0 as transfer_transaction_id,
0 as transaction_cost,
0 as transfer_transaction_id,
glat.transaction_date as phantom_trans_date,
-1 as phantom_type,
NULL,
decode(sign(glat.transaction_date-rt.transaction_date), 1, glat.transaction_date, rt.transaction_date) oc1,
0 as oc2,
-9 as oc3,
0 as routing_id
FROM
gmf_lc_adj_transactions glat, -- from import process of LCM
rcv_transactions rt,
mtl_material_transactions mmt, -- Bug 13906192
mtl_transaction_lot_numbers mtln, -- Bug 13906192
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt glci
WHERE glat.transaction_date >= l_trans_start_date
-- glat.transaction_date >= NVL(l_trans_start_date,glat.transaction_date) performance changed as above B14616815
-- AND glat.legal_entity_id = l_le_id -- bug 12866252
AND rt.transaction_id = mmt.rcv_transaction_id -- Bug 13906192
AND mmt.transaction_id = mtln.transaction_id -- Bug 13906192
-- Perf B14616815 AND mtln.lot_number = nvl(p_lot_no,mtln.lot_number)
AND mtln.lot_number = l_lot_no
AND rt.transaction_id = glat.rcv_transaction_id
AND gpo.organization_id = glat.organization_id
-- Perf B14616815 AND glat.organization_id = NVL(l_orgn_id,glat.organization_id)
AND glci.organization_id = glat.organization_id
AND glci.inventory_item_id = glat.inventory_item_id
AND glat.transaction_date <= l_final_run_date
AND glat.event_type IN (16,17)
AND glat.component_type IN ('ITEM PRICE','CHARGE')
AND glat.cost_acquisition_flag = 'I'
AND (glat.lc_adjustment_flag = 1 OR glat.adjustment_num > 0) /* Load only actual lc adj */
AND NOT EXISTS (SELECT 1
FROM gmf_lc_lot_cost_adjs gllca
WHERE gllca.adj_transaction_id = glat.adj_transaction_id
AND gllca.cost_type_id = l_cost_type_id
AND gllca.lot_number = mtln.lot_number -- Bug 13906192
AND gllca.lot_costed_flag = 1)
-- End LCM-OPM Integration, AF
) a
WHERE a.orgn_id = lcb.organization_id (+)
AND a.inventory_item_id = lcb.item_id (+)
ORDER BY 24, 25, 5, 26, 6 ;
l_uncostable_lots_tab.delete;
l_uncostable_batches_tab.delete; -- Bug 13038249
n_uncostable_tab.delete;
l_uncostable_tab.delete;
SELECT batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id
AND NVL(ACTUAL_CMPLT_DATE,l_final_run_date) <= l_final_run_date;
IF old_cost_tab.exists(1) THEN old_cost_tab.delete; END IF;
IF new_cost_tab.exists(1) THEN new_cost_tab.delete; END IF;
IF l_burdens_tab.exists(1) THEN l_burdens_tab.delete; END IF;
IF l_acqui_cost_tab.exists(1) THEN l_acqui_cost_tab.delete; END IF;
SELECT batch_no INTO l_batch_no FROM gme_batch_header
WHERE batch_id = transaction_row.doc_id;
l_step_tab.DELETE;
SELECT transfer_organization_id,
hoi.org_information2
INTO l_source_orgn_id,
l_source_le_id
FROM mtl_material_transactions mmt,
hr_organization_information hoi
WHERE mmt.transaction_id = transaction_row.transaction_id
AND hoi.organization_id = mmt.transfer_organization_id
AND hoi.org_information_context = 'Accounting Information';
UPDATE gmf_lot_costs
SET onhand_qty = l_residual_qty
WHERE header_id = (SELECT max(header_id)
FROM gmf_lot_costs
WHERE organization_id = transaction_row.orgn_id
AND lot_number = transaction_row.lot_number
AND inventory_item_id = transaction_row.inventory_item_id)
RETURNING header_id INTO new_cost.header_id;
UPDATE gmf_material_lot_cost_txns
SET new_onhand_qty = l_residual_qty
WHERE transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
AND cost_header_id = new_cost.header_id;
UPDATE gmf_lot_cost_adjustments
SET applied_ind = 'Y',
old_cost_header_id = NVL(old_cost.header_id, new_cost.header_id),
new_cost_header_id = new_cost.header_id,
onhand_qty = new_cost.onhand_qty
WHERE adjustment_id = transaction_row.doc_id
AND inventory_item_id = transaction_row.inventory_item_id
AND lot_number = transaction_row.lot_number
AND organization_id = transaction_row.orgn_id ;
UPDATE gmf_lot_costs
SET final_cost_flag = 1
WHERE header_id = new_cost.header_id;
UPDATE gmf_lot_cost_adjustments
SET old_cost_header_id = NVL(old_cost.header_id, new_cost.header_id),
new_cost_header_id = new_cost.header_id,
onhand_qty = new_cost.onhand_qty
WHERE adjustment_id = transaction_row.doc_id;
SELECT SUBSTR(msi.concatenated_segments,1,50) INTO p_item_no
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_orgn_id ;