DROP VIEW INV.

MTL_MATERIAL_TRANSACTIONS#;

/* Formatted on 10-10-2019 14:34:56 (QP5 v5.318) */

CREATE OR REPLACE FORCE EDITIONING VIEW INV.MTL_MATERIAL_TRANSACTIONS#
(
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INVENTORY_ITEM_ID,
REVISION,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_DATE,
VARIANCE_AMOUNT,
ACCT_PERIOD_ID,
TRANSACTION_REFERENCE,
REASON_ID,
DISTRIBUTION_ACCOUNT_ID,
ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT,
COST_UPDATE_ID,
COSTED_FLAG,
TRANSACTION_GROUP_ID,
INVOICED_FLAG,
ACTUAL_COST,
TRANSACTION_COST,
PRIOR_COST,
NEW_COST,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
USSGL_TRANSACTION_CODE,
QUANTITY_ADJUSTED,
EMPLOYEE_CODE,
DEPARTMENT_ID,
OPERATION_SEQ_NUM,
MASTER_SCHEDULE_UPDATE_CODE,
RECEIVING_DOCUMENT,
PICKING_LINE_ID,
TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID,
REPETITIVE_LINE_ID,
PHYSICAL_ADJUSTMENT_ID,
CYCLE_COUNT_ID,
RMA_LINE_ID,
TRANSFER_TRANSACTION_ID,
TRANSACTION_SET_ID,
RCV_TRANSACTION_ID,
MOVE_TRANSACTION_ID,
COMPLETION_TRANSACTION_ID,
SHORTAGE_PROCESS_CODE,
SOURCE_CODE,
SOURCE_LINE_ID,
VENDOR_LOT_NUMBER,
TRANSFER_ORGANIZATION_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR_ID,
SHIPMENT_NUMBER,
TRANSFER_COST,
TRANSPORTATION_DIST_ACCOUNT,
TRANSPORTATION_COST,
TRANSFER_COST_DIST_ACCOUNT,
WAYBILL_AIRBILL,
FREIGHT_CODE,
NUMBER_OF_CONTAINERS,
VALUE_CHANGE,
PERCENTAGE_CHANGE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MOVEMENT_ID,
TASK_ID,
TO_TASK_ID,
PROJECT_ID,
TO_PROJECT_ID,
SOURCE_PROJECT_ID,
PA_EXPENDITURE_ORG_ID,
SOURCE_TASK_ID,
EXPENDITURE_TYPE,
ERROR_CODE,
ERROR_EXPLANATION,
PRIOR_COSTED_QUANTITY,
TRANSFER_PRIOR_COSTED_QUANTITY,
FINAL_COMPLETION_FLAG,
PM_COST_COLLECTED,
PM_COST_COLLECTOR_GROUP_ID,
SHIPMENT_COSTED,
TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT,
COST_GROUP_ID,
TRANSFER_COST_GROUP_ID,
FLOW_SCHEDULE,
QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY,
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
MVT_STAT_STATUS,
COMMON_BOM_SEQ_ID,
COMMON_ROUTING_SEQ_ID,
ORG_COST_GROUP_ID,
COST_TYPE_ID,
PERIODIC_PRIMARY_QUANTITY,
MOVE_ORDER_LINE_ID,
TASK_GROUP_ID,
PICK_SLIP_NUMBER,
LPN_ID,
TRANSFER_LPN_ID,
PICK_STRATEGY_ID,
PICK_RULE_ID,
PUT_AWAY_STRATEGY_ID,
PUT_AWAY_RULE_ID,
CONTENT_LPN_ID,
PICK_SLIP_DATE,
COST_CATEGORY_ID,
ORGANIZATION_TYPE,
TRANSFER_ORGANIZATION_TYPE,
OWNING_ORGANIZATION_ID,
OWNING_TP_TYPE,
XFR_OWNING_ORGANIZATION_ID,
TRANSFER_OWNING_TP_TYPE,
PLANNING_ORGANIZATION_ID,
PLANNING_TP_TYPE,
XFR_PLANNING_ORGANIZATION_ID,
TRANSFER_PLANNING_TP_TYPE,
SECONDARY_UOM_CODE,
SECONDARY_TRANSACTION_QUANTITY,
TRANSACTION_GROUP_SEQ,
SHIP_TO_LOCATION_ID,
RESERVATION_ID,
TRANSACTION_MODE,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
INTRANSIT_ACCOUNT,
FOB_POINT,
PARENT_TRANSACTION_ID,
LOGICAL_TRX_TYPE_CODE,
TRX_FLOW_HEADER_ID,
LOGICAL_TRANSACTIONS_CREATED,
LOGICAL_TRANSACTION,
INTERCOMPANY_COST,
INTERCOMPANY_PRICING_OPTION,
INTERCOMPANY_CURRENCY_CODE,
ORIGINAL_TRANSACTION_TEMP_ID,
 TRANSFER_PRICE,
EXPENSE_ACCOUNT_ID,
COGS_RECOGNITION_PERCENT,
SO_ISSUE_ACCOUNT_TYPE,
OPM_COSTED_FLAG,
XML_DOCUMENT_ID,
MATERIAL_EXPENSE_ACCOUNT,
TRANSACTION_EXTRACTED,
MCC_CODE
)
AS
SELECT TRANSACTION_ID TRANSACTION_ID,
LAST_UPDATE_DATE LAST_UPDATE_DATE,
LAST_UPDATED_BY LAST_UPDATED_BY,
CREATION_DATE CREATION_DATE,
CREATED_BY CREATED_BY,
LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN,
REQUEST_ID REQUEST_ID,
PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID,
PROGRAM_ID PROGRAM_ID,
PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE,
INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
REVISION REVISION,
ORGANIZATION_ID ORGANIZATION_ID,
SUBINVENTORY_CODE SUBINVENTORY_CODE,
LOCATOR_ID LOCATOR_ID,
TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY TRANSACTION_QUANTITY,
TRANSACTION_UOM TRANSACTION_UOM,
PRIMARY_QUANTITY PRIMARY_QUANTITY,
TRANSACTION_DATE TRANSACTION_DATE,
VARIANCE_AMOUNT VARIANCE_AMOUNT,
ACCT_PERIOD_ID ACCT_PERIOD_ID,
TRANSACTION_REFERENCE TRANSACTION_REFERENCE,
REASON_ID REASON_ID,
DISTRIBUTION_ACCOUNT_ID DISTRIBUTION_ACCOUNT_ID,
ENCUMBRANCE_ACCOUNT ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT ENCUMBRANCE_AMOUNT,
COST_UPDATE_ID COST_UPDATE_ID,
COSTED_FLAG COSTED_FLAG,
TRANSACTION_GROUP_ID TRANSACTION_GROUP_ID,
INVOICED_FLAG INVOICED_FLAG,
ACTUAL_COST ACTUAL_COST,
TRANSACTION_COST TRANSACTION_COST,
PRIOR_COST PRIOR_COST,
NEW_COST NEW_COST,
CURRENCY_CODE CURRENCY_CODE,
CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE,
USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
QUANTITY_ADJUSTED QUANTITY_ADJUSTED,
EMPLOYEE_CODE EMPLOYEE_CODE,
DEPARTMENT_ID DEPARTMENT_ID,
OPERATION_SEQ_NUM OPERATION_SEQ_NUM,
MASTER_SCHEDULE_UPDATE_CODE MASTER_SCHEDULE_UPDATE_CODE,
RECEIVING_DOCUMENT RECEIVING_DOCUMENT,
PICKING_LINE_ID PICKING_LINE_ID,
TRX_SOURCE_LINE_ID TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID TRX_SOURCE_DELIVERY_ID,
REPETITIVE_LINE_ID REPETITIVE_LINE_ID,
PHYSICAL_ADJUSTMENT_ID PHYSICAL_ADJUSTMENT_ID,
CYCLE_COUNT_ID CYCLE_COUNT_ID,
RMA_LINE_ID RMA_LINE_ID,
TRANSFER_TRANSACTION_ID TRANSFER_TRANSACTION_ID,
TRANSACTION_SET_ID TRANSACTION_SET_ID,
RCV_TRANSACTION_ID RCV_TRANSACTION_ID,
MOVE_TRANSACTION_ID MOVE_TRANSACTION_ID,
COMPLETION_TRANSACTION_ID COMPLETION_TRANSACTION_ID,
SHORTAGE_PROCESS_CODE SHORTAGE_PROCESS_CODE,
SOURCE_CODE SOURCE_CODE,
SOURCE_LINE_ID SOURCE_LINE_ID,
VENDOR_LOT_NUMBER VENDOR_LOT_NUMBER,
TRANSFER_ORGANIZATION_ID TRANSFER_ORGANIZATION_ID,
TRANSFER_SUBINVENTORY TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR_ID TRANSFER_LOCATOR_ID,
SHIPMENT_NUMBER SHIPMENT_NUMBER,
TRANSFER_COST TRANSFER_COST,
TRANSPORTATION_DIST_ACCOUNT TRANSPORTATION_DIST_ACCOUNT,
TRANSPORTATION_COST TRANSPORTATION_COST,
TRANSFER_COST_DIST_ACCOUNT TRANSFER_COST_DIST_ACCOUNT,
WAYBILL_AIRBILL WAYBILL_AIRBILL,
FREIGHT_CODE FREIGHT_CODE,
NUMBER_OF_CONTAINERS NUMBER_OF_CONTAINERS,
VALUE_CHANGE VALUE_CHANGE,
PERCENTAGE_CHANGE PERCENTAGE_CHANGE,
ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
ATTRIBUTE1 ATTRIBUTE1,
ATTRIBUTE2 ATTRIBUTE2,
ATTRIBUTE3 ATTRIBUTE3,
ATTRIBUTE4 ATTRIBUTE4,
ATTRIBUTE5 ATTRIBUTE5,
ATTRIBUTE6 ATTRIBUTE6,
ATTRIBUTE7 ATTRIBUTE7,
ATTRIBUTE8 ATTRIBUTE8,
ATTRIBUTE9 ATTRIBUTE9,
ATTRIBUTE10 ATTRIBUTE10,
ATTRIBUTE11 ATTRIBUTE11,
ATTRIBUTE12 ATTRIBUTE12,
ATTRIBUTE13 ATTRIBUTE13,
ATTRIBUTE14 ATTRIBUTE14,
ATTRIBUTE15 ATTRIBUTE15,
MOVEMENT_ID MOVEMENT_ID,
TASK_ID TASK_ID,
TO_TASK_ID TO_TASK_ID,
PROJECT_ID PROJECT_ID,
TO_PROJECT_ID TO_PROJECT_ID,
SOURCE_PROJECT_ID SOURCE_PROJECT_ID,
PA_EXPENDITURE_ORG_ID PA_EXPENDITURE_ORG_ID,
SOURCE_TASK_ID SOURCE_TASK_ID,
EXPENDITURE_TYPE EXPENDITURE_TYPE,
ERROR_CODE ERROR_CODE,
ERROR_EXPLANATION ERROR_EXPLANATION,
PRIOR_COSTED_QUANTITY PRIOR_COSTED_QUANTITY,
TRANSFER_PRIOR_COSTED_QUANTITY TRANSFER_PRIOR_COSTED_QUANTITY,
FINAL_COMPLETION_FLAG FINAL_COMPLETION_FLAG,
PM_COST_COLLECTED PM_COST_COLLECTED,
PM_COST_COLLECTOR_GROUP_ID PM_COST_COLLECTOR_GROUP_ID,
SHIPMENT_COSTED SHIPMENT_COSTED,
TRANSFER_PERCENTAGE TRANSFER_PERCENTAGE,
MATERIAL_ACCOUNT MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT RESOURCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT OVERHEAD_ACCOUNT,
COST_GROUP_ID COST_GROUP_ID,
TRANSFER_COST_GROUP_ID TRANSFER_COST_GROUP_ID,
FLOW_SCHEDULE FLOW_SCHEDULE,
QA_COLLECTION_ID QA_COLLECTION_ID,
OVERCOMPLETION_TRANSACTION_QTY OVERCOMPLETION_TRANSACTION_QTY,
OVERCOMPLETION_PRIMARY_QTY OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID OVERCOMPLETION_TRANSACTION_ID,
MVT_STAT_STATUS MVT_STAT_STATUS,
COMMON_BOM_SEQ_ID COMMON_BOM_SEQ_ID,
COMMON_ROUTING_SEQ_ID COMMON_ROUTING_SEQ_ID,
ORG_COST_GROUP_ID ORG_COST_GROUP_ID,
COST_TYPE_ID COST_TYPE_ID,
PERIODIC_PRIMARY_QUANTITY PERIODIC_PRIMARY_QUANTITY,
MOVE_ORDER_LINE_ID MOVE_ORDER_LINE_ID,
TASK_GROUP_ID TASK_GROUP_ID,
PICK_SLIP_NUMBER PICK_SLIP_NUMBER,
LPN_ID LPN_ID,
TRANSFER_LPN_ID TRANSFER_LPN_ID,
PICK_STRATEGY_ID PICK_STRATEGY_ID,
PICK_RULE_ID PICK_RULE_ID,
PUT_AWAY_STRATEGY_ID PUT_AWAY_STRATEGY_ID,
PUT_AWAY_RULE_ID PUT_AWAY_RULE_ID,
CONTENT_LPN_ID CONTENT_LPN_ID,
PICK_SLIP_DATE PICK_SLIP_DATE,
COST_CATEGORY_ID COST_CATEGORY_ID,
ORGANIZATION_TYPE ORGANIZATION_TYPE,
TRANSFER_ORGANIZATION_TYPE TRANSFER_ORGANIZATION_TYPE,
OWNING_ORGANIZATION_ID OWNING_ORGANIZATION_ID,
OWNING_TP_TYPE OWNING_TP_TYPE,
XFR_OWNING_ORGANIZATION_ID XFR_OWNING_ORGANIZATION_ID,
TRANSFER_OWNING_TP_TYPE TRANSFER_OWNING_TP_TYPE,
PLANNING_ORGANIZATION_ID PLANNING_ORGANIZATION_ID,
PLANNING_TP_TYPE PLANNING_TP_TYPE,
XFR_PLANNING_ORGANIZATION_ID XFR_PLANNING_ORGANIZATION_ID,
TRANSFER_PLANNING_TP_TYPE TRANSFER_PLANNING_TP_TYPE,
SECONDARY_UOM_CODE SECONDARY_UOM_CODE,
SECONDARY_TRANSACTION_QUANTITY SECONDARY_TRANSACTION_QUANTITY,
TRANSACTION_GROUP_SEQ TRANSACTION_GROUP_SEQ,
SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
RESERVATION_ID RESERVATION_ID,
TRANSACTION_MODE TRANSACTION_MODE,
TRANSACTION_BATCH_ID TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ TRANSACTION_BATCH_SEQ,
INTRANSIT_ACCOUNT INTRANSIT_ACCOUNT,
FOB_POINT FOB_POINT,
PARENT_TRANSACTION_ID PARENT_TRANSACTION_ID,
LOGICAL_TRX_TYPE_CODE LOGICAL_TRX_TYPE_CODE,
TRX_FLOW_HEADER_ID TRX_FLOW_HEADER_ID,
 LOGICAL_TRANSACTIONS_CREATED LOGICAL_TRANSACTIONS_CREATED,
LOGICAL_TRANSACTION LOGICAL_TRANSACTION,
INTERCOMPANY_COST INTERCOMPANY_COST,
INTERCOMPANY_PRICING_OPTION INTERCOMPANY_PRICING_OPTION,
INTERCOMPANY_CURRENCY_CODE INTERCOMPANY_CURRENCY_CODE,
ORIGINAL_TRANSACTION_TEMP_ID ORIGINAL_TRANSACTION_TEMP_ID,
TRANSFER_PRICE TRANSFER_PRICE,
EXPENSE_ACCOUNT_ID EXPENSE_ACCOUNT_ID,
COGS_RECOGNITION_PERCENT COGS_RECOGNITION_PERCENT,
SO_ISSUE_ACCOUNT_TYPE SO_ISSUE_ACCOUNT_TYPE,
OPM_COSTED_FLAG OPM_COSTED_FLAG,
XML_DOCUMENT_ID XML_DOCUMENT_ID,
MATERIAL_EXPENSE_ACCOUNT MATERIAL_EXPENSE_ACCOUNT,
TRANSACTION_EXTRACTED TRANSACTION_EXTRACTED,
MCC_CODE MCC_CODE
FROM "INV"."MTL_MATERIAL_TRANSACTIONS";

CREATE OR REPLACE TRIGGER APPS.CLL_F040_COST_UPDATE

after insert on "INV"."MTL_MATERIAL_TRANSACTIONS#"
for each row
DECLARE
l_country_code VARCHAR2(2) := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
l_primary_cost_method NUMBER;
l_value_change NUMBER;
l_provision_combination_id NUMBER;
l_stock NUMBER;
l_mtl_tran_head_id NUMBER;
l_result BOOLEAN;
l_req_id NUMBER;

CURSOR c1 IS
SELECT po_line_location_id,
quantity transaction_quantity,
unit_of_measure transaction_uom,
primary_unit_of_measure original_uom,
primary_unit_of_measure primary_uom
FROM rcv_transactions
WHERE transaction_id = :NEW.rcv_transaction_id;
rec_c1 c1%ROWTYPE;

BEGIN

/* $Header: CLLCITCU.sql 120.0 2007/08/27 17:39:01 gmeni noship $ */

IF l_country_code = 'CL' AND :NEW.transaction_type_id = 80 AND

:NEW.transaction_source_name = 'CLLCI PROV GASTOS' THEN
DELETE FROM mtl_transactions_interface
WHERE transaction_header_id = :NEW.transaction_set_id;
END IF;

BEGIN
SELECT primary_cost_method
INTO l_primary_cost_method
FROM mtl_parameters
WHERE organization_id = :NEW.organization_id;
END;

IF l_country_code = 'CL'
 AND ((:NEW.transaction_action_id = 27 AND :NEW.transaction_source_type_id =
1) OR
(:NEW.transaction_action_id = 1 AND :NEW.transaction_source_type_id =
1))
AND l_primary_cost_method = 2
THEN

OPEN c1;
FETCH c1 INTO rec_c1;

BEGIN
SELECT provision_combination_id
INTO l_provision_combination_id
FROM CLL_F040_axi
WHERE organization_id = :NEW.organization_id;
EXCEPTION
WHEN no_data_found THEN
l_provision_combination_id := -1;
END;

IF l_provision_combination_id <> -1 THEN

BEGIN
SELECT NVL(CLL_F040_inv_pkg.f_prov_value_change(:NEW.inventory_item_id,

rec_c1.po_line_location_id,

rec_c1.transaction_quantity,
rec_c1.transaction_uom,
rec_c1.original_uom,
rec_c1.primary_uom),0)
INTO l_value_change
FROM DUAL;
END;

IF l_value_change <> 0 THEN

BEGIN
SELECT NVL(CLL_F040_inv_pkg.f_existencias(:NEW.inventory_item_id,
:NEW.organization_id),0)
INTO l_stock
FROM DUAL;
END;

SELECT mtl_material_transactions_s.nextval
INTO l_mtl_tran_head_id
FROM DUAL;

BEGIN
INSERT INTO mtl_transactions_interface
(transaction_header_id,
inventory_item_id,
cost_group_id,
organization_id,
source_code,
source_line_id,
source_header_id,
transaction_mode,
transaction_quantity,
primary_quantity,
 transaction_uom,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_date,
acct_period_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
value_change,
distribution_account_id,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
process_flag,
lock_flag,
validation_required,
transaction_source_name,
attribute1)
VALUES
(l_mtl_tran_head_id,
:NEW.inventory_item_id,
:NEW.cost_group_id,
:NEW.organization_id,
'Act. Costo CLLCI',
1,
1,
3, --- Transaction Mode
0,
0,
:NEW.transaction_uom,
80,
24,
13,
:NEW.transaction_date,
:NEW.acct_period_id,
SYSDATE,
:NEW.last_updated_by,
SYSDATE,
:NEW.created_by,
:NEW.last_update_login,

DECODE(:NEW.transaction_action_id,27,l_value_change,1,l_value_change*-1),
l_provision_combination_id,
l_provision_combination_id,
l_provision_combination_id,
l_provision_combination_id,
l_provision_combination_id,
l_provision_combination_id,
1, --- process flag
2, --- lOCK fLAG
1,
'CLLCI PROV GASTOS',
l_stock);
 END;

l_result := FND_REQUEST.SET_MODE(TRUE);
l_req_id:=FND_REQUEST.SUBMIT_REQUEST('INV','INCTCW','CLLCI PROV
GASTOS',to_char(sysdate),FALSE,l_mtl_tran_head_id,1,'','');

BEGIN
UPDATE CLL_F040_exp_prov_deta
SET cost_update_flag = 'Y'
WHERE line_location_id = rec_c1.po_line_location_id;
END;

END IF;

END IF;

CLOSE c1;

END IF;

END;
/

CREATE OR REPLACE TRIGGER APPS.cll_f255_mtl_mat_trans_t1

AFTER INSERT OR UPDATE OR DELETE ON "INV"."MTL_MATERIAL_TRANSACTIONS#"
FOR EACH ROW
DECLARE
--
-- l_module_name CONSTANT VARCHAR2(100) := 'CLL_F255_PAY_ALL_PAYROLLS_F_T1';
l_module_name CONSTANT VARCHAR2(100) := 'CLL_F255_MTL_MAT_TRANS_T1'; --BUG
15941741
l_key VARCHAR2(240);
l_transaction_type VARCHAR2(10);
l_profile VARCHAR2(1):=
NVL(FND_PROFILE.VALUE('CLL_F255_ENABLE_BES'),'N');
l_event_name VARCHAR2(240) := 'oracle.apps.cll.mtl_material_transactions';
l_count NUMBER;
BEGIN
/* $Header: CLLBETMT.sql 120.2 2012/11/30 18:58:34 mallonso noship $ */
IF l_profile = 'Y' THEN
--
IF cll_f255_business_events_pkg.exist_subscription( l_event_name ) = 'N' THEN
RETURN;
END IF;
--
IF INSERTING THEN
l_transaction_type := 'INSERT';
ELSIF UPDATING THEN
l_transaction_type := 'UPDATE';
ELSIF DELETING THEN
l_transaction_type := 'DELETE';
END IF;
--
l_key := TO_CHAR(NVL(:new.transaction_id,:old.transaction_id))||'-'||
TO_CHAR(SYSTIMESTAMP, 'DD-MM-YYYY HH24:MM:SS:FF');
--
cll_f255_business_events_pkg.raise( p_event_name => l_event_name,
p_event_key => l_key,
 p_parameter_name1 => 'TABLE_NAME',
p_parameter_value1 =>
'MTL_MATERIAL_TRANSACTIONS',
p_parameter_name2 => 'TRANSACTION_TYPE',
p_parameter_value2 => l_transaction_type,
p_parameter_name3 => 'TRANSACTION_ID',
p_parameter_value3 =>
NVL(:new.transaction_id, :old.transaction_id),
p_parameter_name4 => 'ORGANIZATION_ID',
p_parameter_value4 =>
NVL(:new.organization_id, :old.organization_id)
);
--
END IF;
EXCEPTION
WHEN others THEN
NULL;
END;
/

CREATE OR REPLACE TRIGGER APPS.JAI_INV_MMT_ARIUD_T1

AFTER INSERT OR UPDATE OR DELETE
ON "INV"."MTL_MATERIAL_TRANSACTIONS#" FOR EACH ROW
DECLARE
t_old_rec MTL_MATERIAL_TRANSACTIONS%rowtype ;
t_new_rec MTL_MATERIAL_TRANSACTIONS%rowtype ;
lv_return_message VARCHAR2(2000);
lv_return_code VARCHAR2(100) ;
le_error EXCEPTION ;
lv_action VARCHAR2(20) ;

/* Bug 5413264. Added by Lakshmi Gopalsami

| Removed the reference to org_organization_definitions
| Removed cursor Fetch_Book_Id_Cur
*/

v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%TYPE;

/*
|| Here initialising the pr_new record type in the inline procedure
||
*/
/* Bug 5413264. Added by Lakshmi Gopalsami */
l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
v_currency_code gl_sets_of_books.currency_code%type;

PROCEDURE populate_new IS
BEGIN

t_new_rec.TRANSACTION_ID := :new.TRANSACTION_ID
;
t_new_rec.LAST_UPDATE_DATE := :new.LAST_UPDATE_DATE
;
t_new_rec.LAST_UPDATED_BY := :new.LAST_UPDATED_BY
;
t_new_rec.CREATION_DATE := :new.CREATION_DATE
;
t_new_rec.CREATED_BY := :new.CREATED_BY
;
t_new_rec.LAST_UPDATE_LOGIN := :new.LAST_UPDATE_LOGIN
;
t_new_rec.REQUEST_ID := :new.REQUEST_ID
;
t_new_rec.PROGRAM_APPLICATION_ID :=
:new.PROGRAM_APPLICATION_ID ;
t_new_rec.PROGRAM_ID := :new.PROGRAM_ID
;
t_new_rec.PROGRAM_UPDATE_DATE := :new.PROGRAM_UPDATE_DATE
;
t_new_rec.INVENTORY_ITEM_ID := :new.INVENTORY_ITEM_ID
;
t_new_rec.REVISION := :new.REVISION
;
t_new_rec.ORGANIZATION_ID := :new.ORGANIZATION_ID
;
t_new_rec.SUBINVENTORY_CODE := :new.SUBINVENTORY_CODE
;
t_new_rec.LOCATOR_ID := :new.LOCATOR_ID
;
t_new_rec.TRANSACTION_TYPE_ID := :new.TRANSACTION_TYPE_ID
;
t_new_rec.TRANSACTION_ACTION_ID :=
:new.TRANSACTION_ACTION_ID ;
t_new_rec.TRANSACTION_SOURCE_TYPE_ID :=
:new.TRANSACTION_SOURCE_TYPE_ID ;
t_new_rec.TRANSACTION_SOURCE_ID :=
:new.TRANSACTION_SOURCE_ID ;
t_new_rec.TRANSACTION_SOURCE_NAME :=
:new.TRANSACTION_SOURCE_NAME ;
t_new_rec.TRANSACTION_QUANTITY := :new.TRANSACTION_QUANTITY
;
t_new_rec.TRANSACTION_UOM := :new.TRANSACTION_UOM
;
t_new_rec.PRIMARY_QUANTITY := :new.PRIMARY_QUANTITY
;
t_new_rec.TRANSACTION_DATE := :new.TRANSACTION_DATE
;
t_new_rec.VARIANCE_AMOUNT := :new.VARIANCE_AMOUNT
;
t_new_rec.ACCT_PERIOD_ID := :new.ACCT_PERIOD_ID
;
t_new_rec.TRANSACTION_REFERENCE :=
:new.TRANSACTION_REFERENCE ;
t_new_rec.REASON_ID := :new.REASON_ID
;
t_new_rec.DISTRIBUTION_ACCOUNT_ID :=
:new.DISTRIBUTION_ACCOUNT_ID ;
t_new_rec.ENCUMBRANCE_ACCOUNT := :new.ENCUMBRANCE_ACCOUNT
;
t_new_rec.ENCUMBRANCE_AMOUNT := :new.ENCUMBRANCE_AMOUNT
;
t_new_rec.COST_UPDATE_ID := :new.COST_UPDATE_ID
;
t_new_rec.COSTED_FLAG := :new.COSTED_FLAG
;
 t_new_rec.INVOICED_FLAG := :new.INVOICED_FLAG
;
t_new_rec.ACTUAL_COST := :new.ACTUAL_COST
;
t_new_rec.TRANSACTION_COST := :new.TRANSACTION_COST
;
t_new_rec.PRIOR_COST := :new.PRIOR_COST
;
t_new_rec.NEW_COST := :new.NEW_COST
;
t_new_rec.CURRENCY_CODE := :new.CURRENCY_CODE
;
t_new_rec.CURRENCY_CONVERSION_RATE :=
:new.CURRENCY_CONVERSION_RATE ;
t_new_rec.CURRENCY_CONVERSION_TYPE :=
:new.CURRENCY_CONVERSION_TYPE ;
t_new_rec.CURRENCY_CONVERSION_DATE :=
:new.CURRENCY_CONVERSION_DATE ;
t_new_rec.USSGL_TRANSACTION_CODE :=
:new.USSGL_TRANSACTION_CODE ;
t_new_rec.QUANTITY_ADJUSTED := :new.QUANTITY_ADJUSTED
;
t_new_rec.EMPLOYEE_CODE := :new.EMPLOYEE_CODE
;
t_new_rec.DEPARTMENT_ID := :new.DEPARTMENT_ID
;
t_new_rec.OPERATION_SEQ_NUM := :new.OPERATION_SEQ_NUM
;
t_new_rec.MASTER_SCHEDULE_UPDATE_CODE :=
:new.MASTER_SCHEDULE_UPDATE_CODE ;
t_new_rec.RECEIVING_DOCUMENT := :new.RECEIVING_DOCUMENT
;
t_new_rec.PICKING_LINE_ID := :new.PICKING_LINE_ID
;
t_new_rec.TRX_SOURCE_LINE_ID := :new.TRX_SOURCE_LINE_ID
;
t_new_rec.TRX_SOURCE_DELIVERY_ID :=
:new.TRX_SOURCE_DELIVERY_ID ;
t_new_rec.REPETITIVE_LINE_ID := :new.REPETITIVE_LINE_ID
;
t_new_rec.PHYSICAL_ADJUSTMENT_ID :=
:new.PHYSICAL_ADJUSTMENT_ID ;
t_new_rec.CYCLE_COUNT_ID := :new.CYCLE_COUNT_ID
;
t_new_rec.RMA_LINE_ID := :new.RMA_LINE_ID
;
t_new_rec.TRANSFER_TRANSACTION_ID :=
:new.TRANSFER_TRANSACTION_ID ;
t_new_rec.TRANSACTION_SET_ID := :new.TRANSACTION_SET_ID
;
t_new_rec.RCV_TRANSACTION_ID := :new.RCV_TRANSACTION_ID
;
t_new_rec.MOVE_TRANSACTION_ID := :new.MOVE_TRANSACTION_ID
;
t_new_rec.COMPLETION_TRANSACTION_ID :=
:new.COMPLETION_TRANSACTION_ID ;
t_new_rec.SOURCE_CODE := :new.SOURCE_CODE
;
t_new_rec.SOURCE_LINE_ID := :new.SOURCE_LINE_ID
;
t_new_rec.VENDOR_LOT_NUMBER := :new.VENDOR_LOT_NUMBER
;
t_new_rec.TRANSFER_ORGANIZATION_ID :=
:new.TRANSFER_ORGANIZATION_ID ;
t_new_rec.TRANSFER_SUBINVENTORY :=
:new.TRANSFER_SUBINVENTORY ;
t_new_rec.TRANSFER_LOCATOR_ID := :new.TRANSFER_LOCATOR_ID
;
t_new_rec.SHIPMENT_NUMBER := :new.SHIPMENT_NUMBER
;
t_new_rec.TRANSFER_COST := :new.TRANSFER_COST
;
t_new_rec.TRANSPORTATION_DIST_ACCOUNT :=
:new.TRANSPORTATION_DIST_ACCOUNT ;
t_new_rec.TRANSPORTATION_COST := :new.TRANSPORTATION_COST
;
t_new_rec.TRANSFER_COST_DIST_ACCOUNT :=
:new.TRANSFER_COST_DIST_ACCOUNT ;
t_new_rec.WAYBILL_AIRBILL := :new.WAYBILL_AIRBILL
;
t_new_rec.FREIGHT_CODE := :new.FREIGHT_CODE
;
t_new_rec.NUMBER_OF_CONTAINERS := :new.NUMBER_OF_CONTAINERS
;
t_new_rec.VALUE_CHANGE := :new.VALUE_CHANGE
;
t_new_rec.PERCENTAGE_CHANGE := :new.PERCENTAGE_CHANGE
;
t_new_rec.ATTRIBUTE_CATEGORY := :new.ATTRIBUTE_CATEGORY
;
t_new_rec.ATTRIBUTE1 := :new.ATTRIBUTE1
;
t_new_rec.ATTRIBUTE2 := :new.ATTRIBUTE2
;
t_new_rec.ATTRIBUTE3 := :new.ATTRIBUTE3
;
t_new_rec.ATTRIBUTE4 := :new.ATTRIBUTE4
;
t_new_rec.ATTRIBUTE5 := :new.ATTRIBUTE5
;
t_new_rec.ATTRIBUTE6 := :new.ATTRIBUTE6
;
t_new_rec.ATTRIBUTE7 := :new.ATTRIBUTE7
;
t_new_rec.ATTRIBUTE8 := :new.ATTRIBUTE8
;
t_new_rec.ATTRIBUTE9 := :new.ATTRIBUTE9
;
t_new_rec.ATTRIBUTE10 := :new.ATTRIBUTE10
;
t_new_rec.ATTRIBUTE11 := :new.ATTRIBUTE11
;
t_new_rec.ATTRIBUTE12 := :new.ATTRIBUTE12
;
t_new_rec.ATTRIBUTE13 := :new.ATTRIBUTE13
;
t_new_rec.ATTRIBUTE14 := :new.ATTRIBUTE14
;
 t_new_rec.ATTRIBUTE15 := :new.ATTRIBUTE15
;
t_new_rec.MOVEMENT_ID := :new.MOVEMENT_ID
;
t_new_rec.TRANSACTION_GROUP_ID := :new.TRANSACTION_GROUP_ID
;
t_new_rec.TASK_ID := :new.TASK_ID
;
t_new_rec.TO_TASK_ID := :new.TO_TASK_ID
;
t_new_rec.PROJECT_ID := :new.PROJECT_ID
;
t_new_rec.TO_PROJECT_ID := :new.TO_PROJECT_ID
;
t_new_rec.SOURCE_PROJECT_ID := :new.SOURCE_PROJECT_ID
;
t_new_rec.PA_EXPENDITURE_ORG_ID :=
:new.PA_EXPENDITURE_ORG_ID ;
t_new_rec.SOURCE_TASK_ID := :new.SOURCE_TASK_ID
;
t_new_rec.EXPENDITURE_TYPE := :new.EXPENDITURE_TYPE
;
t_new_rec.ERROR_CODE := :new.ERROR_CODE
;
t_new_rec.ERROR_EXPLANATION := :new.ERROR_EXPLANATION
;
t_new_rec.PRIOR_COSTED_QUANTITY :=
:new.PRIOR_COSTED_QUANTITY ;
t_new_rec.FINAL_COMPLETION_FLAG :=
:new.FINAL_COMPLETION_FLAG ;
t_new_rec.PM_COST_COLLECTED := :new.PM_COST_COLLECTED
;
t_new_rec.PM_COST_COLLECTOR_GROUP_ID :=
:new.PM_COST_COLLECTOR_GROUP_ID ;
t_new_rec.SHIPMENT_COSTED := :new.SHIPMENT_COSTED
;
t_new_rec.TRANSFER_PERCENTAGE := :new.TRANSFER_PERCENTAGE
;
t_new_rec.MATERIAL_ACCOUNT := :new.MATERIAL_ACCOUNT
;
t_new_rec.MATERIAL_OVERHEAD_ACCOUNT :=
:new.MATERIAL_OVERHEAD_ACCOUNT ;
t_new_rec.RESOURCE_ACCOUNT := :new.RESOURCE_ACCOUNT
;
t_new_rec.OUTSIDE_PROCESSING_ACCOUNT :=
:new.OUTSIDE_PROCESSING_ACCOUNT ;
t_new_rec.OVERHEAD_ACCOUNT := :new.OVERHEAD_ACCOUNT
;
t_new_rec.COST_GROUP_ID := :new.COST_GROUP_ID
;
t_new_rec.TRANSFER_COST_GROUP_ID :=
:new.TRANSFER_COST_GROUP_ID ;
t_new_rec.FLOW_SCHEDULE := :new.FLOW_SCHEDULE
;
t_new_rec.TRANSFER_PRIOR_COSTED_QUANTITY :=
:new.TRANSFER_PRIOR_COSTED_QUANTITY ;
t_new_rec.SHORTAGE_PROCESS_CODE :=
:new.SHORTAGE_PROCESS_CODE ;
t_new_rec.QA_COLLECTION_ID := :new.QA_COLLECTION_ID
;
t_new_rec.OVERCOMPLETION_TRANSACTION_QTY :=
:new.OVERCOMPLETION_TRANSACTION_QTY ;
t_new_rec.OVERCOMPLETION_PRIMARY_QTY :=
:new.OVERCOMPLETION_PRIMARY_QTY ;
t_new_rec.OVERCOMPLETION_TRANSACTION_ID :=
:new.OVERCOMPLETION_TRANSACTION_ID ;
t_new_rec.MVT_STAT_STATUS := :new.MVT_STAT_STATUS
;
t_new_rec.COMMON_BOM_SEQ_ID := :new.COMMON_BOM_SEQ_ID
;
t_new_rec.COMMON_ROUTING_SEQ_ID :=
:new.COMMON_ROUTING_SEQ_ID ;
t_new_rec.ORG_COST_GROUP_ID := :new.ORG_COST_GROUP_ID
;
t_new_rec.COST_TYPE_ID := :new.COST_TYPE_ID
;
t_new_rec.PERIODIC_PRIMARY_QUANTITY :=
:new.PERIODIC_PRIMARY_QUANTITY ;
t_new_rec.MOVE_ORDER_LINE_ID := :new.MOVE_ORDER_LINE_ID
;
t_new_rec.TASK_GROUP_ID := :new.TASK_GROUP_ID
;
t_new_rec.PICK_SLIP_NUMBER := :new.PICK_SLIP_NUMBER
;
t_new_rec.LPN_ID := :new.LPN_ID
;
t_new_rec.TRANSFER_LPN_ID := :new.TRANSFER_LPN_ID
;
t_new_rec.PICK_STRATEGY_ID := :new.PICK_STRATEGY_ID
;
t_new_rec.PICK_RULE_ID := :new.PICK_RULE_ID
;
t_new_rec.PUT_AWAY_STRATEGY_ID := :new.PUT_AWAY_STRATEGY_ID
;
t_new_rec.PUT_AWAY_RULE_ID := :new.PUT_AWAY_RULE_ID
;
t_new_rec.CONTENT_LPN_ID := :new.CONTENT_LPN_ID
;
t_new_rec.PICK_SLIP_DATE := :new.PICK_SLIP_DATE
;
t_new_rec.COST_CATEGORY_ID := :new.COST_CATEGORY_ID
;
t_new_rec.ORGANIZATION_TYPE := :new.ORGANIZATION_TYPE
;
t_new_rec.TRANSFER_ORGANIZATION_TYPE :=
:new.TRANSFER_ORGANIZATION_TYPE ;
t_new_rec.OWNING_ORGANIZATION_ID :=
:new.OWNING_ORGANIZATION_ID ;
t_new_rec.OWNING_TP_TYPE := :new.OWNING_TP_TYPE
;
t_new_rec.XFR_OWNING_ORGANIZATION_ID :=
:new.XFR_OWNING_ORGANIZATION_ID ;
t_new_rec.TRANSFER_OWNING_TP_TYPE :=
:new.TRANSFER_OWNING_TP_TYPE ;
t_new_rec.PLANNING_ORGANIZATION_ID :=
:new.PLANNING_ORGANIZATION_ID ;
t_new_rec.PLANNING_TP_TYPE := :new.PLANNING_TP_TYPE
;
 t_new_rec.XFR_PLANNING_ORGANIZATION_ID :=
:new.XFR_PLANNING_ORGANIZATION_ID ;
t_new_rec.TRANSFER_PLANNING_TP_TYPE :=
:new.TRANSFER_PLANNING_TP_TYPE ;
t_new_rec.SECONDARY_UOM_CODE := :new.SECONDARY_UOM_CODE
;
t_new_rec.SECONDARY_TRANSACTION_QUANTITY :=
:new.SECONDARY_TRANSACTION_QUANTITY ;
t_new_rec.SHIP_TO_LOCATION_ID := :new.SHIP_TO_LOCATION_ID
;
t_new_rec.TRANSACTION_MODE := :new.TRANSACTION_MODE
;
t_new_rec.TRANSACTION_BATCH_ID := :new.TRANSACTION_BATCH_ID
;
t_new_rec.TRANSACTION_BATCH_SEQ :=
:new.TRANSACTION_BATCH_SEQ ;
t_new_rec.INTRANSIT_ACCOUNT := :new.INTRANSIT_ACCOUNT
;
t_new_rec.FOB_POINT := :new.FOB_POINT
;
t_new_rec.PARENT_TRANSACTION_ID :=
:new.PARENT_TRANSACTION_ID ;
t_new_rec.LOGICAL_TRX_TYPE_CODE :=
:new.LOGICAL_TRX_TYPE_CODE ;
t_new_rec.TRX_FLOW_HEADER_ID := :new.TRX_FLOW_HEADER_ID
;
t_new_rec.LOGICAL_TRANSACTIONS_CREATED :=
:new.LOGICAL_TRANSACTIONS_CREATED ;
t_new_rec.LOGICAL_TRANSACTION := :new.LOGICAL_TRANSACTION
;
t_new_rec.INTERCOMPANY_COST := :new.INTERCOMPANY_COST
;
t_new_rec.INTERCOMPANY_PRICING_OPTION :=
:new.INTERCOMPANY_PRICING_OPTION ;
t_new_rec.RESERVATION_ID := :new.RESERVATION_ID
;
t_new_rec.INTERCOMPANY_CURRENCY_CODE :=
:new.INTERCOMPANY_CURRENCY_CODE ;
t_new_rec.ORIGINAL_TRANSACTION_TEMP_ID :=
:new.ORIGINAL_TRANSACTION_TEMP_ID ;
t_new_rec.TRANSFER_PRICE := :new.TRANSFER_PRICE
;
t_new_rec.EXPENSE_ACCOUNT_ID := :new.EXPENSE_ACCOUNT_ID
;
t_new_rec.COGS_RECOGNITION_PERCENT :=
:new.COGS_RECOGNITION_PERCENT ;
t_new_rec.SO_ISSUE_ACCOUNT_TYPE :=
:new.SO_ISSUE_ACCOUNT_TYPE ;
t_new_rec.OPM_COSTED_FLAG := :new.OPM_COSTED_FLAG
;
END populate_new ;

PROCEDURE populate_old IS
BEGIN
t_old_rec.TRANSACTION_ID := :old.TRANSACTION_ID
;
t_old_rec.LAST_UPDATE_DATE := :old.LAST_UPDATE_DATE
;
t_old_rec.LAST_UPDATED_BY := :old.LAST_UPDATED_BY
;
t_old_rec.CREATION_DATE := :old.CREATION_DATE
;
t_old_rec.CREATED_BY := :old.CREATED_BY
;
t_old_rec.LAST_UPDATE_LOGIN := :old.LAST_UPDATE_LOGIN
;
t_old_rec.REQUEST_ID := :old.REQUEST_ID
;
t_old_rec.PROGRAM_APPLICATION_ID :=
:old.PROGRAM_APPLICATION_ID ;
t_old_rec.PROGRAM_ID := :old.PROGRAM_ID
;
t_old_rec.PROGRAM_UPDATE_DATE := :old.PROGRAM_UPDATE_DATE
;
t_old_rec.INVENTORY_ITEM_ID := :old.INVENTORY_ITEM_ID
;
t_old_rec.REVISION := :old.REVISION
;
t_old_rec.ORGANIZATION_ID := :old.ORGANIZATION_ID
;
t_old_rec.SUBINVENTORY_CODE := :old.SUBINVENTORY_CODE
;
t_old_rec.LOCATOR_ID := :old.LOCATOR_ID
;
t_old_rec.TRANSACTION_TYPE_ID := :old.TRANSACTION_TYPE_ID
;
t_old_rec.TRANSACTION_ACTION_ID :=
:old.TRANSACTION_ACTION_ID ;
t_old_rec.TRANSACTION_SOURCE_TYPE_ID :=
:old.TRANSACTION_SOURCE_TYPE_ID ;
t_old_rec.TRANSACTION_SOURCE_ID :=
:old.TRANSACTION_SOURCE_ID ;
t_old_rec.TRANSACTION_SOURCE_NAME :=
:old.TRANSACTION_SOURCE_NAME ;
t_old_rec.TRANSACTION_QUANTITY := :old.TRANSACTION_QUANTITY
;
t_old_rec.TRANSACTION_UOM := :old.TRANSACTION_UOM
;
t_old_rec.PRIMARY_QUANTITY := :old.PRIMARY_QUANTITY
;
t_old_rec.TRANSACTION_DATE := :old.TRANSACTION_DATE
;
t_old_rec.VARIANCE_AMOUNT := :old.VARIANCE_AMOUNT
;
t_old_rec.ACCT_PERIOD_ID := :old.ACCT_PERIOD_ID
;
t_old_rec.TRANSACTION_REFERENCE :=
:old.TRANSACTION_REFERENCE ;
t_old_rec.REASON_ID := :old.REASON_ID
;
t_old_rec.DISTRIBUTION_ACCOUNT_ID :=
:old.DISTRIBUTION_ACCOUNT_ID ;
t_old_rec.ENCUMBRANCE_ACCOUNT := :old.ENCUMBRANCE_ACCOUNT
;
t_old_rec.ENCUMBRANCE_AMOUNT := :old.ENCUMBRANCE_AMOUNT
;
t_old_rec.COST_UPDATE_ID := :old.COST_UPDATE_ID
;
 t_old_rec.COSTED_FLAG := :old.COSTED_FLAG
;
t_old_rec.INVOICED_FLAG := :old.INVOICED_FLAG
;
t_old_rec.ACTUAL_COST := :old.ACTUAL_COST
;
t_old_rec.TRANSACTION_COST := :old.TRANSACTION_COST
;
t_old_rec.PRIOR_COST := :old.PRIOR_COST
;
t_old_rec.NEW_COST := :old.NEW_COST
;
t_old_rec.CURRENCY_CODE := :old.CURRENCY_CODE
;
t_old_rec.CURRENCY_CONVERSION_RATE :=
:old.CURRENCY_CONVERSION_RATE ;
t_old_rec.CURRENCY_CONVERSION_TYPE :=
:old.CURRENCY_CONVERSION_TYPE ;
t_old_rec.CURRENCY_CONVERSION_DATE :=
:old.CURRENCY_CONVERSION_DATE ;
t_old_rec.USSGL_TRANSACTION_CODE :=
:old.USSGL_TRANSACTION_CODE ;
t_old_rec.QUANTITY_ADJUSTED := :old.QUANTITY_ADJUSTED
;
t_old_rec.EMPLOYEE_CODE := :old.EMPLOYEE_CODE
;
t_old_rec.DEPARTMENT_ID := :old.DEPARTMENT_ID
;
t_old_rec.OPERATION_SEQ_NUM := :old.OPERATION_SEQ_NUM
;
t_old_rec.MASTER_SCHEDULE_UPDATE_CODE :=
:old.MASTER_SCHEDULE_UPDATE_CODE ;
t_old_rec.RECEIVING_DOCUMENT := :old.RECEIVING_DOCUMENT
;
t_old_rec.PICKING_LINE_ID := :old.PICKING_LINE_ID
;
t_old_rec.TRX_SOURCE_LINE_ID := :old.TRX_SOURCE_LINE_ID
;
t_old_rec.TRX_SOURCE_DELIVERY_ID :=
:old.TRX_SOURCE_DELIVERY_ID ;
t_old_rec.REPETITIVE_LINE_ID := :old.REPETITIVE_LINE_ID
;
t_old_rec.PHYSICAL_ADJUSTMENT_ID :=
:old.PHYSICAL_ADJUSTMENT_ID ;
t_old_rec.CYCLE_COUNT_ID := :old.CYCLE_COUNT_ID
;
t_old_rec.RMA_LINE_ID := :old.RMA_LINE_ID
;
t_old_rec.TRANSFER_TRANSACTION_ID :=
:old.TRANSFER_TRANSACTION_ID ;
t_old_rec.TRANSACTION_SET_ID := :old.TRANSACTION_SET_ID
;
t_old_rec.RCV_TRANSACTION_ID := :old.RCV_TRANSACTION_ID
;
t_old_rec.MOVE_TRANSACTION_ID := :old.MOVE_TRANSACTION_ID
;
t_old_rec.COMPLETION_TRANSACTION_ID :=
:old.COMPLETION_TRANSACTION_ID ;
t_old_rec.SOURCE_CODE := :old.SOURCE_CODE
;
t_old_rec.SOURCE_LINE_ID := :old.SOURCE_LINE_ID
;
t_old_rec.VENDOR_LOT_NUMBER := :old.VENDOR_LOT_NUMBER
;
t_old_rec.TRANSFER_ORGANIZATION_ID :=
:old.TRANSFER_ORGANIZATION_ID ;
t_old_rec.TRANSFER_SUBINVENTORY :=
:old.TRANSFER_SUBINVENTORY ;
t_old_rec.TRANSFER_LOCATOR_ID := :old.TRANSFER_LOCATOR_ID
;
t_old_rec.SHIPMENT_NUMBER := :old.SHIPMENT_NUMBER
;
t_old_rec.TRANSFER_COST := :old.TRANSFER_COST
;
t_old_rec.TRANSPORTATION_DIST_ACCOUNT :=
:old.TRANSPORTATION_DIST_ACCOUNT ;
t_old_rec.TRANSPORTATION_COST := :old.TRANSPORTATION_COST
;
t_old_rec.TRANSFER_COST_DIST_ACCOUNT :=
:old.TRANSFER_COST_DIST_ACCOUNT ;
t_old_rec.WAYBILL_AIRBILL := :old.WAYBILL_AIRBILL
;
t_old_rec.FREIGHT_CODE := :old.FREIGHT_CODE
;
t_old_rec.NUMBER_OF_CONTAINERS := :old.NUMBER_OF_CONTAINERS
;
t_old_rec.VALUE_CHANGE := :old.VALUE_CHANGE
;
t_old_rec.PERCENTAGE_CHANGE := :old.PERCENTAGE_CHANGE
;
t_old_rec.ATTRIBUTE_CATEGORY := :old.ATTRIBUTE_CATEGORY
;
t_old_rec.ATTRIBUTE1 := :old.ATTRIBUTE1
;
t_old_rec.ATTRIBUTE2 := :old.ATTRIBUTE2
;
t_old_rec.ATTRIBUTE3 := :old.ATTRIBUTE3
;
t_old_rec.ATTRIBUTE4 := :old.ATTRIBUTE4
;
t_old_rec.ATTRIBUTE5 := :old.ATTRIBUTE5
;
t_old_rec.ATTRIBUTE6 := :old.ATTRIBUTE6
;
t_old_rec.ATTRIBUTE7 := :old.ATTRIBUTE7
;
t_old_rec.ATTRIBUTE8 := :old.ATTRIBUTE8
;
t_old_rec.ATTRIBUTE9 := :old.ATTRIBUTE9
;
t_old_rec.ATTRIBUTE10 := :old.ATTRIBUTE10
;
t_old_rec.ATTRIBUTE11 := :old.ATTRIBUTE11
;
t_old_rec.ATTRIBUTE12 := :old.ATTRIBUTE12
;
t_old_rec.ATTRIBUTE13 := :old.ATTRIBUTE13
;
 t_old_rec.ATTRIBUTE14 := :old.ATTRIBUTE14
;
t_old_rec.ATTRIBUTE15 := :old.ATTRIBUTE15
;
t_old_rec.MOVEMENT_ID := :old.MOVEMENT_ID
;
t_old_rec.TRANSACTION_GROUP_ID := :old.TRANSACTION_GROUP_ID
;
t_old_rec.TASK_ID := :old.TASK_ID
;
t_old_rec.TO_TASK_ID := :old.TO_TASK_ID
;
t_old_rec.PROJECT_ID := :old.PROJECT_ID
;
t_old_rec.TO_PROJECT_ID := :old.TO_PROJECT_ID
;
t_old_rec.SOURCE_PROJECT_ID := :old.SOURCE_PROJECT_ID
;
t_old_rec.PA_EXPENDITURE_ORG_ID :=
:old.PA_EXPENDITURE_ORG_ID ;
t_old_rec.SOURCE_TASK_ID := :old.SOURCE_TASK_ID
;
t_old_rec.EXPENDITURE_TYPE := :old.EXPENDITURE_TYPE
;
t_old_rec.ERROR_CODE := :old.ERROR_CODE
;
t_old_rec.ERROR_EXPLANATION := :old.ERROR_EXPLANATION
;
t_old_rec.PRIOR_COSTED_QUANTITY :=
:old.PRIOR_COSTED_QUANTITY ;
t_old_rec.FINAL_COMPLETION_FLAG :=
:old.FINAL_COMPLETION_FLAG ;
t_old_rec.PM_COST_COLLECTED := :old.PM_COST_COLLECTED
;
t_old_rec.PM_COST_COLLECTOR_GROUP_ID :=
:old.PM_COST_COLLECTOR_GROUP_ID ;
t_old_rec.SHIPMENT_COSTED := :old.SHIPMENT_COSTED
;
t_old_rec.TRANSFER_PERCENTAGE := :old.TRANSFER_PERCENTAGE
;
t_old_rec.MATERIAL_ACCOUNT := :old.MATERIAL_ACCOUNT
;
t_old_rec.MATERIAL_OVERHEAD_ACCOUNT :=
:old.MATERIAL_OVERHEAD_ACCOUNT ;
t_old_rec.RESOURCE_ACCOUNT := :old.RESOURCE_ACCOUNT
;
t_old_rec.OUTSIDE_PROCESSING_ACCOUNT :=
:old.OUTSIDE_PROCESSING_ACCOUNT ;
t_old_rec.OVERHEAD_ACCOUNT := :old.OVERHEAD_ACCOUNT
;
t_old_rec.COST_GROUP_ID := :old.COST_GROUP_ID
;
t_old_rec.TRANSFER_COST_GROUP_ID :=
:old.TRANSFER_COST_GROUP_ID ;
t_old_rec.FLOW_SCHEDULE := :old.FLOW_SCHEDULE
;
t_old_rec.TRANSFER_PRIOR_COSTED_QUANTITY :=
:old.TRANSFER_PRIOR_COSTED_QUANTITY ;
t_old_rec.SHORTAGE_PROCESS_CODE :=
:old.SHORTAGE_PROCESS_CODE ;
t_old_rec.QA_COLLECTION_ID := :old.QA_COLLECTION_ID
;
t_old_rec.OVERCOMPLETION_TRANSACTION_QTY :=
:old.OVERCOMPLETION_TRANSACTION_QTY ;
t_old_rec.OVERCOMPLETION_PRIMARY_QTY :=
:old.OVERCOMPLETION_PRIMARY_QTY ;
t_old_rec.OVERCOMPLETION_TRANSACTION_ID :=
:old.OVERCOMPLETION_TRANSACTION_ID ;
t_old_rec.MVT_STAT_STATUS := :old.MVT_STAT_STATUS
;
t_old_rec.COMMON_BOM_SEQ_ID := :old.COMMON_BOM_SEQ_ID
;
t_old_rec.COMMON_ROUTING_SEQ_ID :=
:old.COMMON_ROUTING_SEQ_ID ;
t_old_rec.ORG_COST_GROUP_ID := :old.ORG_COST_GROUP_ID
;
t_old_rec.COST_TYPE_ID := :old.COST_TYPE_ID
;
t_old_rec.PERIODIC_PRIMARY_QUANTITY :=
:old.PERIODIC_PRIMARY_QUANTITY ;
t_old_rec.MOVE_ORDER_LINE_ID := :old.MOVE_ORDER_LINE_ID
;
t_old_rec.TASK_GROUP_ID := :old.TASK_GROUP_ID
;
t_old_rec.PICK_SLIP_NUMBER := :old.PICK_SLIP_NUMBER
;
t_old_rec.LPN_ID := :old.LPN_ID
;
t_old_rec.TRANSFER_LPN_ID := :old.TRANSFER_LPN_ID
;
t_old_rec.PICK_STRATEGY_ID := :old.PICK_STRATEGY_ID
;
t_old_rec.PICK_RULE_ID := :old.PICK_RULE_ID
;
t_old_rec.PUT_AWAY_STRATEGY_ID := :old.PUT_AWAY_STRATEGY_ID
;
t_old_rec.PUT_AWAY_RULE_ID := :old.PUT_AWAY_RULE_ID
;
t_old_rec.CONTENT_LPN_ID := :old.CONTENT_LPN_ID
;
t_old_rec.PICK_SLIP_DATE := :old.PICK_SLIP_DATE
;
t_old_rec.COST_CATEGORY_ID := :old.COST_CATEGORY_ID
;
t_old_rec.ORGANIZATION_TYPE := :old.ORGANIZATION_TYPE
;
t_old_rec.TRANSFER_ORGANIZATION_TYPE :=
:old.TRANSFER_ORGANIZATION_TYPE ;
t_old_rec.OWNING_ORGANIZATION_ID :=
:old.OWNING_ORGANIZATION_ID ;
t_old_rec.OWNING_TP_TYPE := :old.OWNING_TP_TYPE
;
t_old_rec.XFR_OWNING_ORGANIZATION_ID :=
:old.XFR_OWNING_ORGANIZATION_ID ;
t_old_rec.TRANSFER_OWNING_TP_TYPE :=
:old.TRANSFER_OWNING_TP_TYPE ;
t_old_rec.PLANNING_ORGANIZATION_ID :=
:old.PLANNING_ORGANIZATION_ID ;
 t_old_rec.PLANNING_TP_TYPE := :old.PLANNING_TP_TYPE
;
t_old_rec.XFR_PLANNING_ORGANIZATION_ID :=
:old.XFR_PLANNING_ORGANIZATION_ID ;
t_old_rec.TRANSFER_PLANNING_TP_TYPE :=
:old.TRANSFER_PLANNING_TP_TYPE ;
t_old_rec.SECONDARY_UOM_CODE := :old.SECONDARY_UOM_CODE
;
t_old_rec.SECONDARY_TRANSACTION_QUANTITY :=
:old.SECONDARY_TRANSACTION_QUANTITY ;
t_old_rec.SHIP_TO_LOCATION_ID := :old.SHIP_TO_LOCATION_ID
;
t_old_rec.TRANSACTION_MODE := :old.TRANSACTION_MODE
;
t_old_rec.TRANSACTION_BATCH_ID := :old.TRANSACTION_BATCH_ID
;
t_old_rec.TRANSACTION_BATCH_SEQ :=
:old.TRANSACTION_BATCH_SEQ ;
t_old_rec.INTRANSIT_ACCOUNT := :old.INTRANSIT_ACCOUNT
;
t_old_rec.FOB_POINT := :old.FOB_POINT
;
t_old_rec.PARENT_TRANSACTION_ID :=
:old.PARENT_TRANSACTION_ID ;
t_old_rec.LOGICAL_TRX_TYPE_CODE :=
:old.LOGICAL_TRX_TYPE_CODE ;
t_old_rec.TRX_FLOW_HEADER_ID := :old.TRX_FLOW_HEADER_ID
;
t_old_rec.LOGICAL_TRANSACTIONS_CREATED :=
:old.LOGICAL_TRANSACTIONS_CREATED ;
t_old_rec.LOGICAL_TRANSACTION := :old.LOGICAL_TRANSACTION
;
t_old_rec.INTERCOMPANY_COST := :old.INTERCOMPANY_COST
;
t_old_rec.INTERCOMPANY_PRICING_OPTION :=
:old.INTERCOMPANY_PRICING_OPTION ;
t_old_rec.RESERVATION_ID := :old.RESERVATION_ID
;
t_old_rec.INTERCOMPANY_CURRENCY_CODE :=
:old.INTERCOMPANY_CURRENCY_CODE ;
t_old_rec.ORIGINAL_TRANSACTION_TEMP_ID :=
:old.ORIGINAL_TRANSACTION_TEMP_ID ;
t_old_rec.TRANSFER_PRICE := :old.TRANSFER_PRICE
;
t_old_rec.EXPENSE_ACCOUNT_ID := :old.EXPENSE_ACCOUNT_ID
;
t_old_rec.COGS_RECOGNITION_PERCENT :=
:old.COGS_RECOGNITION_PERCENT ;
t_old_rec.SO_ISSUE_ACCOUNT_TYPE :=
:old.SO_ISSUE_ACCOUNT_TYPE ;
t_old_rec.OPM_COSTED_FLAG := :old.OPM_COSTED_FLAG
;
END populate_old ;

BEGIN

/*
|| assign the new values depending upon the triggering event.
*/
 IF UPDATING OR INSERTING THEN
populate_new;
END IF;

/*
|| assign the old values depending upon the triggering event.
*/

IF UPDATING OR DELETING THEN

populate_old;
END IF;

/* Bug 5413264. Added by Lakshmi Gopalsami

Removed cursor Fetch_Book_Id_Cur
fetching from org_organization_definitions
and implemented the same using cache.
*/

l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
(p_org_id => :new.organization_id );

v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
v_currency_code := l_func_curr_det.currency_code;

IF v_currency_code <> 'INR' THEN

RETURN ;
END IF;

/* Bug 5413264. Added by Lakshmi Gopalsami

| commented the following call as the check
| can be done using the variable retrieved using caching logic.
| make a call to the INR check package.

IF jai_cmn_utils_pkg.check_jai_exists(P_CALLING_OBJECT => 'JAI_INV_MMT_ARIUD_T1',

p_set_of_books_id => v_gl_set_of_bks_id ) = FALSE THEN
RETURN;
END IF;
*/

/*
|| check for action in trigger and pass the same to the procedure
*/
IF INSERTING THEN
lv_action := jai_constants.inserting ;
ELSIF UPDATING THEN
lv_action := jai_constants.updating ;
ELSIF DELETING THEN
lv_action := jai_constants.deleting ;
END IF ;

IF INSERTING THEN

JAI_INV_MMT_TRIGGER_PKG.ARI_T1 (
pr_old => t_old_rec ,
pr_new => t_new_rec ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
 );

IF lv_return_code <> jai_constants.successful then

RAISE le_error;
END IF;

END IF ;

EXCEPTION

WHEN le_error THEN

app_exception.raise_exception (
EXCEPTION_TYPE => 'APP' ,
EXCEPTION_CODE => -20110 ,
EXCEPTION_TEXT => lv_return_message
);

WHEN OTHERS THEN

app_exception.raise_exception (
EXCEPTION_TYPE => 'APP',
EXCEPTION_CODE => -20110 ,
EXCEPTION_TEXT => 'Encountered the error in
trigger JAI_INV_MMT_ARIUD_T1' || substr(sqlerrm,1,1900)
);

END JAI_INV_MMT_ARIUD_T1 ;
/

CREATE OR REPLACE TRIGGER APPS.JA_AU_LOCAL_ACCOUNTING

after update of costed_flag
ON "INV"."MTL_MATERIAL_TRANSACTIONS#" for each row
WHEN (old.transaction_type_id in (50,54,61,62) and
old.transaction_source_type_id = 8 and
new.costed_flag is null and
((sys_context('JG','JGZZ_COUNTRY_CODE') in ('AU'))
OR (to_char(new.ORGANIZATION_ID) <>
nvl(sys_context('JG','JGZZ_ORG_ID'),'XX'))))
Declare
l_country_code VARCHAR2(5);
BEGIN

IF (to_char(:new.organization_id) <> nvl(sys_context('JG','JGZZ_ORG_ID'),'XX'))

THEN

l_country_code := FND_PROFILE.value('JGZZ_COUNTRY_CODE');

JG_CONTEXT.name_value('JGZZ_COUNTRY_CODE',l_country_code);

JG_CONTEXT.name_value('JGZZ_ORG_ID',to_char(:new.organization_id));

END IF;

IF (sys_context('JG','JGZZ_COUNTRY_CODE') = 'AU') THEN

ja_au_costproc_pkg.ja_au_local_account(:old.organization_id,
:old.subinventory_code,
 :old.inventory_item_id,
:old.transaction_id);
END IF;

END;
/

CREATE OR REPLACE TRIGGER APPS.XXINV_MTL_MATERIAL_TRANS_ARI

-- ****************************************************************************
-- Copyright (c) 2004 Emerson Electric Company
-- This program contains proprietary and confidential information.
-- All rights reserved except as may be permitted by prior
-- written consent.
--
--
-- File Name: XXINV/12.0.0/admin/sql/XXINV_MTL_MATERIAL_TRANS_ARI.trg
-- PVCS Spec: BETSY:A180.A-TRG;6
--
-- Attribute Label: Kintana Pkg# 226023
--
-- Description:
-- add XXINV_MTL_MATERIAL_TRANS_ARI.trg
--
--
--
-- **********************************************************************
--
-- project : EMRPALIM
--
-- H/W platform : Sun UltraSparc
-- O/S : SOLARIS
--
-- application :
--
-- script : XXINV_MTL_MATERIAL_TRANS_ARI.trg
--
-- created by : Infosys
--
-- creation date : 03-OCT-2008
--
-- description :
--
-- parameters : NA
--
-- inputs : NA
--
-- outputs : NA
--
-- platform - os : Solaris
-- platform - h/w : Sun UltraSparc
--
-- database : Oracle 9.2.0.6.0
-- apps. Release : Oracle Applications 11.5.10
--
-- development and maintenance history
--
-- Date Author Description
-- -------------- ------
-----------------------------------------
-- 25-Jul-2009 Infosys Created
-- 14-Jun-2013 Wipro Technologies Mobrey (ex RTR) project updates
-- 20-DEC-2013 Wipro Technologies Added if condition by Mobrey team not
to insert
-- Null Unit Id values
-- 18-May-2016 INFOSYS Modified for R12 OLP changes
-- ****************************************************************************
AFTER INSERT ON --inv.mtl_material_transactions --commented by R12 upgrade
OLP changes
"INV"."MTL_MATERIAL_TRANSACTIONS#" --added by
R12 upgrade OLP changes
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
--Cursor to Fetch all the serial numbers for new transaction_id
CURSOR cur_ser_numbers IS
SELECT serial_number
FROM mtl_unit_transactions mut
WHERE transaction_id = :NEW.transaction_id;

/* LOCAL VARIABLES*/
v_chr_org_name hr_operating_units.NAME%TYPE :=
fnd_profile.VALUE('ORG_NAME');
v_chr_nolabel VARCHAR2(1);
v_num_ou_cnt NUMBER;
v_chr_transaction_type mtl_transaction_types.transaction_type_name%TYPE;
v_bol_retval BOOLEAN;
v_num_reqid NUMBER;
v_chr_comments VARCHAR2(1000);
v_dte_sysdate DATE := SYSDATE;
v_num_unit_id NUMBER;
v_chr_ou_URD VARCHAR2 (1000);
v_chr_plan_name VARCHAR2 (1000);
v_Chr_Serial_number VARCHAR2 (1000);
v_chr_coo VARCHAR2 (1000);
v_num_line_id NUMBER;
v_num_header_id NUMBER;
v_chr_org_id NUMBER :=fnd_profile.VALUE('ORG_ID');
v_picking_line_id NUMBER;
BEGIN

--RESETING LOCAL VARIABLES

v_num_ou_cnt := 0;

--VERIFY THE OU
BEGIN
SELECT COUNT(1)
INTO v_num_ou_cnt
FROM fnd_lookup_values
WHERE LANGUAGE = userenv('LANG')
AND lookup_type = 'EMR LABEL PRINT LOOKUP LQD'
AND meaning = v_chr_org_name
AND nvl(start_date_active, v_dte_sysdate) <= v_dte_sysdate --ADDED FROM
HERE.
AND nvl(end_date_active, v_dte_sysdate) >= v_dte_sysdate
AND enabled_flag = 'Y';

IF v_num_ou_cnt = 0 THEN
 v_chr_nolabel := 'Y';
ELSIF v_num_ou_cnt > 0 THEN
v_chr_nolabel := 'N';
END IF;
EXCEPTION
WHEN no_data_found THEN
v_chr_nolabel := 'Y';
WHEN OTHERS THEN
v_chr_nolabel := 'Y';
END;
--
IF v_chr_nolabel = 'N' THEN
-- fetching transaction_type
BEGIN
SELECT mtt.transaction_type_name
INTO v_chr_transaction_type
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_id = :NEW.transaction_type_id;
EXCEPTION
WHEN no_data_found THEN
v_chr_transaction_type := NULL;
WHEN OTHERS THEN
NULL;
END;
--
IF v_chr_transaction_type = 'WIP Assy Completion' AND
v_chr_org_name = 'LQD_USA_OU' THEN
v_bol_retval := fnd_request.set_mode(db_trigger => TRUE);
v_bol_retval := fnd_request.set_options(implicit => 'ERROR');
v_num_reqid := fnd_request.submit_request('XXINV',

'XXINV_LABEL_SHELF_LIFE_LQD',
NULL,
NULL,
FALSE,
:NEW.transaction_id);
--
IF v_num_reqid = 0 THEN
raise_application_error(-20160, fnd_message.get);
END IF;
END IF;
END IF; -- IF v_chr_nolabel = 'N' THEN
--
-- Mobrey (ex RTR) change start
-- fetching ORG in-scope for trigger to replicate WA functionality in Oracle
BEGIN
SELECT 'Y', description
INTO v_chr_ou_URD, v_chr_plan_name
FROM fnd_lookup_values flv,
hr_operating_units hou
WHERE LANGUAGE = USERENV ('LANG')
AND lookup_type = 'EMR NO WADV URD TRG ORGS RMT'
AND flv.meaning=hou.name
AND hou.organization_id = v_chr_org_id
AND NVL (start_date_active, v_dte_sysdate) <= v_dte_sysdate
AND NVL (end_date_active, v_dte_sysdate) >= v_dte_sysdate
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
 v_chr_ou_URD := 'N';
WHEN OTHERS THEN
v_chr_ou_URD := 'N';
END;
--
IF v_chr_ou_URD = 'Y' THEN
-- fetching transaction_type
BEGIN
SELECT mtt.transaction_type_name
INTO v_chr_transaction_type
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_id = :NEW.transaction_type_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_chr_transaction_type := NULL;
WHEN OTHERS
THEN
NULL;
END;
--
IF (v_chr_transaction_type = 'WIP Assy Completion') THEN
FOR cur_ser_numbers_rec IN cur_ser_numbers LOOP
-- fetch COO from item category
BEGIN
SELECT mcb.segment10
INTO v_chr_coo
FROM mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_b mcb
WHERE mcs.category_set_name = 'EMR COUNTRY OF ORIGIN'
AND mcb.category_id = mic.category_id
AND mic.category_set_id = mcs.category_set_id
AND mic.inventory_item_id = :NEW.inventory_item_id
AND mic.organization_id = :NEW.organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_chr_coo := NULL;
WHEN OTHERS THEN
v_chr_coo := NULL;
END;
--
BEGIN
SELECT unit_id
INTO v_num_unit_id
FROM xxurd_so_unit xsu, wip_entities we
WHERE we.wip_entity_id = :NEW.TRANSACTION_SOURCE_ID
AND xsu.wip_order_number = we.wip_entity_name
AND xsu.serial_number = cur_ser_numbers_rec.serial_number;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_num_unit_id := NULL;
WHEN OTHERS
THEN
v_num_unit_id := NULL;
END;
--
BEGIN
 IF (v_num_unit_id IS NOT NULL) THEN
INSERT INTO XXADC_ADC_URD_INTERFACE (UNIT,
PROCESS_TYPE,
ID_TYPE,
CREATED_BY,
CREATION_DATE,
COUNTRY_OF_ORIGIN,
PROCESS_FLAG)
VALUES (v_num_unit_id,
1,
'N',
fnd_global.user_id,
SYSDATE,
v_chr_coo,
'1');
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--
END LOOP;
--
ELSIF ( v_chr_transaction_type = 'Sales order issue'
AND :NEW.source_code = 'ORDER ENTRY') THEN
--
FOR cur_ser_numbers_rec IN cur_ser_numbers LOOP
--
BEGIN
SELECT unit_id
INTO v_num_unit_id
FROM xxurd_so_unit xsu, oe_order_lines ol
WHERE 1=1
AND xsu.header_id = ol.header_id
AND xsu.line_id = NVL(ol.top_model_line_id, ol.line_id)
AND ol.line_id = :NEW.source_line_id
--AND xsu.serial_number = cur_ser_numbers_rec.serial_number;
AND NVL(xsu.serial_number,
cur_ser_numbers_rec.serial_number) = cur_ser_numbers_rec.serial_number -- this is
to handle serial number generation at ship (for MMI scenario)
AND ROWNUM < 2
AND xsu.unit_id NOT IN (SELECT unit
FROM xxadc_adc_urd_interface i
WHERE 1=1
AND i.unit = xsu.unit_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_num_unit_id := NULL;
WHEN OTHERS
THEN
v_num_unit_id := NULL;
END;
--get the picking line id (for URD status closure in downstream
process)
BEGIN
SELECT wdd.delivery_detail_id
INTO v_picking_line_id
 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda,
wsh_new_deliveries wsh
WHERE 1=1
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wsh.delivery_id
AND wdd.source_line_id = :NEW.source_line_id;
EXCEPTION
WHEN OTHERS THEN
v_picking_line_id := NULL;
END;
--update the picking line id (for daily schd job to CLOSE the
status of URD units)
BEGIN
IF (v_num_unit_id IS NOT NULL) THEN
UPDATE xxurd_so_unit
SET picking_line_id = v_picking_line_id,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id
WHERE 1=1
AND unit_id = v_num_unit_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
BEGIN -- fetch COO from item category
SELECT mcb.segment10
INTO v_chr_coo
FROM mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_b mcb
WHERE mcs.category_set_name = 'EMR COUNTRY OF ORIGIN'
AND mcb.category_id = mic.category_id
AND mic.category_set_id = mcs.category_set_id
AND mic.inventory_item_id = :NEW.inventory_item_id
AND mic.organization_id = :NEW.organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_chr_coo := NULL;
WHEN OTHERS THEN
v_chr_coo := NULL;
END;
--
BEGIN
IF (v_num_unit_id IS NOT NULL) THEN
INSERT INTO XXADC_ADC_URD_INTERFACE (UNIT,
PROCESS_TYPE,
ID_TYPE,
CREATED_BY,
CREATION_DATE,
COUNTRY_OF_ORIGIN,
PROCESS_FLAG)
VALUES (v_num_unit_id,
3,
'N',
fnd_global.user_id,
SYSDATE,
v_chr_coo,
 '1');
END IF; -- for IF (v_num_unit_id IS NOT NULL) THEN
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END IF; -- IF (v_chr_transaction_type = 'WIP Assy Completion') THEN
END IF; -- IF v_chr_ou_URD = 'Y' THEN
-- Mobrey (ex RTR) change end
EXCEPTION
WHEN OTHERS THEN
v_chr_comments := 'Exception in the trigger xxinv_mtl_material_trans_ari. '
||SQLERRM;
END;
/

CREATE OR REPLACE SYNONYM APPS.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM APPSREAD.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM EMRWF.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM EPMDELPHI_IU.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM HOTSOS.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM NOETIXCONNECT_RO.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM NOETIX_GLOBAL.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM NOETIX_GLOBAL_ARCHIVE.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM OBT_IF.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM WADVSYS_IU.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM XXISV.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

CREATE OR REPLACE SYNONYM XXPSLIR.MTL_MATERIAL_TRANSACTIONS FOR

INV.MTL_MATERIAL_TRANSACTIONS#;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE, DEBUG ON

INV.MTL_MATERIAL_TRANSACTIONS# TO APPS WITH GRANT OPTION;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO APPSREAD;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO CHRIS_HUNT;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO CLAUDIA_GHEORGHITA;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO DAWN_WALLACE;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO "EMANUELA.DOBRILA";

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO EMRALPHA_IU;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO EMRQRY;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO EMRQRY_BUTTONS;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO EMRQRY_SMALL;

GRANT DELETE, INSERT, UPDATE ON INV.MTL_MATERIAL_TRANSACTIONS# TO EMRSUP;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO EMRSUP_BI;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO EPMDELPHI_IU;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO HOTSOS;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO "NJOUD.ALMAHBOOB";

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO NOETIXCONNECT_RO;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO NOETIX_GLOBAL WITH GRANT OPTION;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO NOETIX_GLOBAL_ARCHIVE WITH GRANT

OPTION;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO NOETIX_OBTAA WITH GRANT OPTION;

GRANT DELETE, INSERT, SELECT, UPDATE ON INV.MTL_MATERIAL_TRANSACTIONS# TO OBT_IF;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO SYSSWWSC_IU;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO VEERA_GANAPABATTULA;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO VLVSDW_IU;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO WADVSYS_IU;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON INV.MTL_MATERIAL_TRANSACTIONS#

TO XXABSL WITH GRANT OPTION;
GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO XXDMCBI_IU;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO XXISV;

GRANT SELECT ON INV.MTL_MATERIAL_TRANSACTIONS# TO XXPSLIR;