[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Function : Validate LCM adjustments before inserting in
gmf_lc_adj_transactions table
Pre-reqs : None
Parameters :
IN : p_le_id IN NUMBER
OUT : p_adjustment_row IN OUT transaction_type
p_validation_status IN OUT VARCHAR2
p_return_status OUT VARCHAR2
DESCRIPTION
Validate LC adjustments before insert into OPM tables
AUTHOR
LCM-OPM dev 04-Aug-2009, LCM-OPM Integration, bug 8889977
Prasadmarada 15-oct-2009 added code for prorate return to vendor, correction
transactions, bug 8933738, 8925152
HISTORY
*************************************************************************/
PROCEDURE Validate_Adjustments(p_le_id IN NUMBER,
p_adjustment_row IN OUT NOCOPY transaction_type,
p_validation_status IN OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Get the lcm flag and item id
CURSOR check_lcm_flag IS
SELECT NVL(pll.lcm_flag,'N'), NVL(pl.item_id,0)
FROM
po_line_locations_all pll,
po_lines_all pl,
rcv_transactions rt
WHERE
pll.line_location_id = rt.po_line_location_id
AND pl.po_line_id = rt.po_line_id
AND pl.po_line_id = pll.po_line_id
AND rt.transaction_id = p_adjustment_row.rcv_transaction_id;
SELECT 1
FROM cm_cmpt_mst
WHERE cost_cmpntcls_id = cp_cost_cmpntcls_id;
SELECT 1
FROM CM_ALYS_MST
WHERE cost_analysis_code = cp_cost_analysis_code;
SELECT ccm.mtl_cmpntcls_id,
ccm.mtl_analysis_code,
1
FROM cm_cmpt_mtl ccm
WHERE ccm.legal_entity_id = NVL(cp_le_id,ccm.legal_entity_id)
AND ccm.inventory_item_id = NVL(cp_inventory_item_id,ccm.inventory_item_id)
AND ccm.organization_id = NVL(cp_organization_id,ccm.organization_id)
AND cp_date BETWEEN ccm.eff_start_date AND ccm.eff_end_date
AND ccm.delete_mark = 0
UNION
SELECT cm.mtl_cmpntcls_id,
cm.mtl_analysis_code,
2
FROM cm_cmpt_mtl cm
WHERE cm.legal_entity_id = NVL(cp_le_id, cm.legal_entity_id)
AND cm.organization_id = NVL(cp_organization_id, cm.organization_id)
AND cp_date BETWEEN cm.eff_start_date AND cm.eff_end_date
AND cm.delete_mark = 0
AND cm.cost_category_id IN
( SELECT category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id = NVL(cp_inventory_item_id, mic.inventory_item_id)
AND mic.organization_id = NVL(cp_organization_id, mic.organization_id)
)
UNION
SELECT gfp.mtl_cmpntcls_id,
gfp.mtl_analysis_code,
3
FROM gmf_fiscal_policies gfp
WHERE gfp.legal_entity_id = cp_le_id
AND gfp.delete_mark = 0
ORDER BY 3;
SELECT asset_inventory
FROM mtl_secondary_inventories subinv,
mtl_material_transactions mmt,
rcv_transactions rt
WHERE subinv.secondary_inventory_name = mmt.subinventory_code
AND subinv.organization_id = mmt.organization_id
AND rt.transaction_id = mmt.rcv_transaction_id
AND rt.transaction_id = cp_rcv_transaction_id;
SELECT gl.currency_code
FROM gl_sets_of_books gl,
financials_system_parameters fsp
WHERE gl.set_of_books_id = fsp.set_of_books_id
AND org_id = cp_org_id;
SELECT NVL(SUM(landed_cost),0) landed_cost
FROM gmf_lc_adj_headers_v glah
WHERE glah.parent_ship_line_id = p_parent_ship_line_id
AND glah.ship_header_id = p_ship_header_id
AND glah.ship_line_group_id = p_ship_line_group_id
AND glah.component_type = p_component_type
AND glah.component_name = p_component_name
AND glah.inventory_item_id = p_inventory_item_id
AND glah.rcv_transaction_id = p_rcv_transaction_id
AND glah.adjustment_num = p_previous_adj_num;
SELECT parent_transaction_id, primary_quantity, destination_type_code
FROM rcv_transactions
WHERE transaction_id = cp_transaction_id;
SELECT MAX(adjustment_num)
INTO l_previous_adj_num
FROM inl_allocations ia
WHERE ia.ship_header_id = p_adjustment_row.ship_header_id
AND ia.ship_line_id = p_adjustment_row.ship_line_id
AND ia.adjustment_num < p_adjustment_row.adjustment_num;
SELECT ABS(DECODE(p_adjustment_row.currency_code,rt.currency_code,
NVL(rt.po_unit_price,0) * NVL(rt.source_doc_quantity,0) ,
NVL(rt.po_unit_price,0) * NVL(rt.source_doc_quantity,0) * rt.currency_conversion_rate))
INTO p_adjustment_row.prior_landed_cost
FROM rcv_transactions rt
WHERE rt.transaction_id = p_adjustment_row.rcv_transaction_id;
fnd_file.put_line(fnd_file.log,'New landed cost is less than zero. Then skipping to insert in transactions table');
fnd_file.put_line(fnd_file.log,'Prior landed cost amount is less than zero. Then skipping to insert in transactions table');
fnd_file.put_line(fnd_file.log,'Adjustment amount is zero. Then skipping to insert in transactions table');
fnd_file.put_line(fnd_file.log,'Invalid Rcv Transaction. Then skipping to insert in transactions table');
fnd_file.put_line(fnd_file.log,'PO Shipment is not LCM Enabled. then skipping to insert in transactions table');
fnd_file.put_line(fnd_file.log,'Not matched Item. then skipping to insert in transactions table');
Insert Landed cost adjustment transaction in gmf_lc_adj_transactions
table
AUTHOR
LCM-OPM dev 04-Aug-2009, LCM-OPM Integration, bug
HISTORY
*************************************************************************/
PROCEDURE Create_AdjTrxLines(p_le_id IN NUMBER,
p_ledger_id IN NUMBER,
p_adjustment_row IN transaction_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_proc_name VARCHAR2(40) := 'Process_Lc_Adjustments';
INSERT INTO gmf_lc_adj_transactions
(adj_transaction_id, -- 01
rcv_transaction_id, -- 02
event_type, -- 03
event_source, -- 04
event_source_id, -- 05
ledger_id, -- 06
org_id, -- 07
inventory_item_id, -- 08
organization_id, -- 09
legal_entity_id, -- 10
parent_ship_line_id, -- 11
ship_header_id, -- 12
ship_line_group_id, -- 13
ship_line_id, -- 14
adjustment_num, -- 15
parent_table_name, -- 16
parent_table_id, -- 17
prior_landed_cost, -- 18
new_landed_cost, -- 19
charge_line_type_id, -- 20
charge_line_type_code, -- 21
cost_acquisition_flag, -- 22
component_type, -- 23
component_name, -- 24
cost_cmpntcls_id, -- 25
cost_analysis_code, -- 26
lc_adjustment_flag, -- 27
transaction_date, -- 28
transaction_quantity, -- 29
transaction_uom_code, -- 30
primary_quantity, -- 31
primary_uom_code, -- 32
currency_code, -- 33
/*currency_conversion_type, -- 34
currency_conversion_rate, -- 35
currency_conversion_date, -- 36
*/
lc_ship_num, -- 37
lc_ship_line_num, -- 38
lc_var_account_id, -- 39
lc_absorption_account_id, -- 40
accounted_flag, -- 41
final_posting_date, -- 42
creation_date, -- 43
created_by, -- 44
last_update_date, -- 45
last_updated_by, -- 46
last_update_login, -- 47
request_id, -- 48
program_application_id, -- 49
program_id, -- 50
program_udpate_date) -- 51
VALUES
(gmf_lc_adj_transactions_s.NEXTVAL, -- 01
p_adjustment_row.rcv_transaction_id, -- 02
p_adjustment_row.event_type, -- 03
p_adjustment_row.event_source, -- 04
p_adjustment_row.event_source_id, -- 05
p_ledger_id, -- 06
p_adjustment_row.org_id, -- 07
p_adjustment_row.inventory_item_id, -- 08
p_adjustment_row.organization_id, -- 09
p_le_id, -- 10
p_adjustment_row.parent_ship_line_id, -- 11
p_adjustment_row.ship_header_id, -- 12
p_adjustment_row.ship_line_group_id, -- 13
p_adjustment_row.ship_line_id, -- 14
p_adjustment_row.adjustment_num, -- 15
p_adjustment_row.parent_table_name, -- 16
p_adjustment_row.parent_table_id, -- 17
p_adjustment_row.prior_landed_cost, -- 18
p_adjustment_row.new_landed_cost, -- 19
p_adjustment_row.charge_line_type_id, -- 20
p_adjustment_row.charge_line_type_code, -- 21
p_adjustment_row.cost_acquisition_flag, -- 22
p_adjustment_row.component_type, -- 23
p_adjustment_row.component_name, -- 24
p_adjustment_row.cost_cmpntcls_id, -- 25
p_adjustment_row.cost_analysis_code, -- 26
p_adjustment_row.lc_adjustment_flag, -- 27
p_adjustment_row.transaction_date, -- 28
p_adjustment_row.transaction_quantity, -- 29
p_adjustment_row.transaction_uom_code, -- 30
p_adjustment_row.primary_quantity, -- 31
p_adjustment_row.primary_uom_code, -- 32
p_adjustment_row.currency_code, -- 33
/*
p_adjustment_row.currency_conversion_type, -- 34
p_adjustment_row.currency_conversion_rate, -- 35
p_adjustment_row.currency_conversion_date, -- 36
*/
p_adjustment_row.lc_ship_num, -- 37
p_adjustment_row.lc_ship_line_num, -- 38
p_adjustment_row.lc_var_account_id, -- 39
p_adjustment_row.lc_absorption_account_id , -- 40
'N', -- 41
NULL, -- 42
SYSDATE, -- 43
fnd_global.user_id, -- 44
SYSDATE, -- 45
fnd_global.user_id, -- 46
0, -- 47
fnd_global.conc_request_id, -- 48
fnd_global.conc_program_id, -- 49
fnd_global.prog_appl_id, -- 50
SYSDATE -- 51
);
fnd_file.put_line(fnd_file.log,'Failed to insert into adjsutment transactions table in '||l_proc_name);
SELECT glah.parent_ship_line_id,
glah.adjustment_num,
glah.ship_header_id,
glah.org_id,
glah.ship_line_group_id,
glah.ship_line_id,
glah.organization_id,
glah.inventory_item_id,
NVL(glah.prior_landed_cost,0) AS prior_landed_cost,
NVL(glah.landed_cost,0) AS new_landed_cost,
glah.allocation_percent,
glah.charge_line_type_id,
glah.charge_line_type_code,
glah.cost_acquisition_flag,
glah.component_type,
glah.component_name,
glah.parent_table_name,
glah.parent_table_id,
glah.cost_cmpntcls_id,
glah.cost_analysis_code,
glah.transaction_date,
NVL(glah.transaction_quantity,0) AS transaction_quantity,
glah.transaction_uom_code,
NVL(glah.primary_quantity,0) AS primary_quantity,
glah.primary_uom_code,
glah.lc_adjustment_flag,
glah.rcv_transaction_id,
glah.rcv_transaction_type,
glah.lc_ship_num lc_ship_num,
glah.lc_ship_line_num lc_ship_line_num,
mp.lcm_enabled_flag,
mp.lcm_var_account lc_var_account_id,
rp.lcm_account_id lc_absorption_account_id
FROM
gmf_lc_adj_headers_v glah,
org_organization_definitions ood,
mtl_parameters mp,
rcv_parameters rp,
mtl_item_flexfields mif
WHERE
mp.organization_id = glah.organization_id
AND ood.legal_entity = cp_le_id
AND mp.process_enabled_flag = 'Y'
AND mp.lcm_enabled_flag = 'Y'
AND ood.organization_id = glah.organization_id
AND mp.organization_id = glah.organization_id
AND rp.organization_id = glah.organization_id
AND mp.organization_code >= NVL(cp_from_org_cd, mp.organization_code)
AND mp.organization_code <= NVL(cp_to_org_cd, mp.organization_code)
AND glah.inventory_item_id = mif.inventory_item_id
AND glah.organization_id = mif.organization_id
AND mif.item_number >= NVL(cp_from_item, mif.item_number)
AND mif.item_number <= NVL(cp_to_item, mif.item_number)
AND TRUNC(glah.transaction_date) >= TRUNC(NVL(cp_start_dt, glah.transaction_date))
AND TRUNC(glah.transaction_date) <= TRUNC(NVL(cp_end_dt, glah.transaction_date))
AND glah.component_type IN ('ITEM PRICE','CHARGE')
-- AND NVL(glah.new_landed_cost,0) - NVL(glah.prior_landed_cost,0) <> 0
AND NOT EXISTS (SELECT 1 FROM gmf_lc_adj_transactions lat
WHERE lat.adjustment_num = glah.adjustment_num
AND lat.ship_header_id = glah.ship_header_id
AND lat.ship_line_id = glah.ship_line_id
AND lat.rcv_transaction_id = glah.rcv_transaction_id
AND lat.component_type = glah.component_type
AND DECODE(lat.component_type,'CHARGE', lat.charge_line_type_code, 'ITEM PRICE', lat.component_name, lat.component_name) =
DECODE(glah.component_type,'CHARGE', glah.charge_line_type_code, 'ITEM PRICE', glah.component_name, glah.component_name )
AND lat.legal_entity_id = cp_le_id)
ORDER BY glah.rcv_transaction_id, glah.adjustment_num;
SELECT legal_entity_name
FROM gmf_legal_entities
WHERE legal_entity_id = p_le_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = cp_organization_id ;
SELECT item_number
FROM mtl_item_flexfields
WHERE inventory_item_id = cp_inventory_item_id
AND rownum < 2;
SELECT 1
FROM fnd_product_installations
WHERE application_id = 555 -- GMF product
AND patch_level = 'R12.GMF.B.1' -- This indicates that installed GMF product level is 12.1.1
;
SELECT primary_ledger_id
INTO l_ledger_id
FROM gmf_legal_entities
WHERE legal_entity_id = p_le_id;
SELECT max(m.adj_group_date) adj_group_date
INTO l_adjustment_row.transaction_date
FROM inl_matches m
WHERE m.ship_header_id = adjustments_row.ship_header_id
AND m.adjustment_num = adjustments_row.adjustment_num;