[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
mmt.primary_quantity,
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,
NULL,
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
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
);
SELECT SYSTEM.gmf_matl_type
( mmt.transaction_id,
hoi.org_information2,
mmt.organization_id,
mmt.inventory_item_id,
mtln.lot_number,
gmd.line_type,
mmt.transaction_quantity,
mmt.transaction_uom,
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,
NULL ,
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);
SELECT SYSTEM.gmf_matl_type
( mmt.transaction_id,
hoi.org_information2,
mmt.organization_id,
mmt.inventory_item_id,
mtln.lot_number,
gmd.line_type,
mmt.transaction_quantity,
mmt.transaction_uom,
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,
NULL ,
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,
mmt.primary_quantity,
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,
NULL ,
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 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
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
);
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,
NULL
)
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 <= 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
)
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
AND NVL(cstw.eff_end_date,p_date) >= p_date
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
(cst.cost_cmpntcls_id,
cst.cost_analysis_code,
cst.cost_level,
cst.cmpnt_cost,
0
)
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
AND NVL(cstw.eff_end_date,p_date) >= p_date
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
(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.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;
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
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 */
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 */
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 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;
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 mtln.primary_quantity,
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;
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;
/* 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)
INTO receipt_unit_cost,
receipt_ccy,
receipt_qty,
receipt_uom,
document_code,
l_exchange_rate
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 nvl(routing_id,0) INTO l_routing
FROM gme_batch_header
WHERE batch_type = 0
AND batch_id = transaction_row.doc_id;
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);
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 */
fnd_file.put_line (fnd_file.log, 'Completed inserts into tables in process_batch ');
/*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;
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 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 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
AND t.final_cost_flag = 0);
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;
old_cost_tab.delete;
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
==========================================================*/
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;
SELECT mp.organization_code
INTO l_from_orgn_code
FROM mtl_parameters mp
WHERE mp.organization_id = p_orgn_id;
INSERT
INTO GMF_PROCESS_ORGANIZATIONS_GT
(
organization_id,
organization_code,
base_currency_code,
std_uom,
legal_entity_id,
operating_unit_id
)
SELECT mp.organization_id, mp.organization_code, gfp.base_currency_code,
NULL, gfp.legal_entity_id, ood.operating_unit
FROM mtl_parameters mp,
gmf_fiscal_policies gfp,
org_organization_definitions ood
WHERE mp.process_enabled_flag = 'Y'
AND gfp.legal_entity_id = ood.legal_entity
AND mp.organization_id = ood.organization_id;
UPDATE gmf_process_organizations_gt gpo
SET std_uom = (SELECT u.uom_code
FROM mtl_units_of_measure u,
gmd_parameters_hdr h,
gmd_parameters_dtl d
WHERE u.base_uom_flag = 'Y'
AND gpo.organization_id = h.organization_id
AND h.parameter_id = d.parameter_id
AND d.parameter_name = 'FM_YIELD_TYPE'
AND d.parameter_value = u.uom_class)
WHERE gpo.std_uom IS NULL;
UPDATE gmf_process_organizations_gt gpo
SET std_uom = (SELECT u.uom_code
FROM mtl_units_of_measure u,
gmd_parameters_hdr h,
gmd_parameters_dtl d
WHERE u.base_uom_flag = 'Y'
AND h.organization_id IS NULL
AND h.parameter_id = d.parameter_id
AND d.parameter_name = 'FM_YIELD_TYPE'
AND d.parameter_value = u.uom_class)
WHERE gpo.std_uom IS NULL;
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 = p_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
)
)
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
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
)
) ;
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 batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id
AND ACTUAL_CMPLT_DATE <= l_final_run_date;
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 base_currency_code
INTO l_base_ccy_code
FROM gmf_fiscal_policies
WHERE legal_entity_id = l_le_id;
SELECT mic.category_id
INTO l_cost_category_id
FROM mtl_default_category_sets mdc,
mtl_category_sets mcs,
mtl_item_categories mic,
mtl_categories mc
WHERE mic.inventory_item_id = l_item_id
AND mic.organization_id = l_orgn_id
AND mic.category_id = mc.category_id
AND mcs.structure_id = mc.structure_id
AND mdc.functional_area_id = 19
AND mcs.category_set_id = mic.category_set_id
AND mcs.category_set_id = mdc.category_set_id;
delete_lot_costs;
SELECT * from
(SELECT
mmt.transaction_source_id as doc_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.trx_source_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,
1 source,
nvl(gtp.transaction_id2,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
FROM mtl_material_transactions mmt,
gme_material_details gme,
mtl_transaction_lot_numbers mtln,
gme_transaction_pairs gtp,
gmf_process_organizations_gt gpo,
gmf_lot_costed_items_gt lcig
WHERE
gpo.organization_id = mmt.organization_id
AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
AND mmt.transaction_date <= l_final_run_date
AND mmt.trx_source_line_id = gme.material_detail_id
AND mmt.transaction_id = gtp.transaction_id1 (+)
AND mmt.transaction_id = mtln.transaction_id
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
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
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 mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
AND mmt.transaction_date <= l_final_run_date
AND mmt.transaction_id = mtln.transaction_id
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) /* PK added subinv Xfer */
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
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 mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
AND mmt.transaction_date <= l_final_run_date
AND mmt.owning_tp_type = 2
AND mmt.transaction_id = mtln.transaction_id
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
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
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 mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
AND mmt.transaction_date <= l_final_run_date
AND mmt.transfer_transaction_id = mtln.transaction_id
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)
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,
iimb.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
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)
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
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
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
)
)
--ORDER BY 7,2,5 /*Bug 7215069 - Changed ordering for Receipt into Stores*/
ORDER BY 7,decode(transaction_action_id,27,-1,transaction_source_type_id),5
,DECODE(line_type,1, DECODE((ABS(DECODE(trans_qty, 0, 1,trans_qty))/DECODE(trans_qty, 0, 1, trans_qty)),
1, transaction_id ,
DECODE(reverse_id, NULL, transaction_id, reverse_id+.5)),transaction_id)
;
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 decode(mp.process_enabled_flag,'N',1,0)
INTO l_flg_ind
FROM mtl_parameters mp
WHERE mp.organization_id = transaction_row.transfer_orgn_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';
SELECT decode(mp.process_enabled_flag,'N',1,0)
INTO l_flg_ind
FROM mtl_parameters mp
WHERE mp.organization_id = transaction_row.transfer_orgn_id;
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)
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 mtl_material_lot_numbers
SET lot_costed_ind = 1
WHERE transaction_id = transaction_row.transaction_id
AND lot_number = transaction_row.lot_number;
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;
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;