WIP Resource Transaction Interface

RESOURCE TRANSACTION INTERFACE
*****************************************

Interface Table :- wip_cost_txn_interface

Import Porgram :- The concurrent pgm automatically will execute when you insert data into above table. The conc program is Actual Cost Worker

PL/SQL Script to Upload Data into Interface Table
---------------------------------------------------

CREATE OR REPLACE procedure xxx_resource_txn_int_api (p_organization_id number,
p_wip_entity_id number,
p_op_seq varchar2,
p_resource_code varchar2,
p_start_time varchar2,
p_end_time varchar2,
p_employee_num varchar2,
errbuf out varchar2,
rectcode out varchar2) as
----Variable Declarations

l_wip_entity_id wip_entities.wip_entity_id%type;
l_organization_id wip_entities.organization_id%type;
l_wip_entity_name wip_entities.wip_entity_name%type;
l_entity_type wip_entities.entity_type%type;
l_primary_item_id wip_entities.primary_item_id%type;
l_organization_code org_organization_definitions.organization_code%type;

l_operation_sequence_id bom_operation_sequences.operation_sequence_id%type;
l_operation_seq_num bom_operation_sequences.operation_seq_num%type;
l_department_id bom_departments.department_id%type;
l_department_code bom_departments.department_code%type;

l_resource_seq_num bom_operation_resources.resource_seq_num%type;
l_resource_id bom_operation_resources.resource_id%type;
l_standard_rate_flag bom_operation_resources.standard_rate_flag%type;
l_usage_rate_or_amount bom_operation_resources.usage_rate_or_amount%type;
l_basis_type bom_operation_resources.basis_type%type;
l_autocharge_type bom_operation_resources.autocharge_type%type;
l_resource_code bom_resources.resource_code%type;
l_resource_type bom_resources.resource_type%type;

l_employee_id number(10);
l_employee_num varchar2(20);

l_verify_flag char(1) :='Y';
l_error_message varchar2(2500);
l_acct_period_id org_acct_periods_v.acct_period_id%type;
v_quantity number(10,2);

begin

fnd_file.put_line(fnd_file.log,'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' Resource Transaction Interface ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');


l_verify_flag := 'Y' ;


BEGIN
select wip.wip_entity_id,
wip.organization_id,
wip.wip_entity_name,
wip.entity_type,
wip.primary_item_id ,
ood.organization_code
into l_wip_entity_id,
l_organization_id,
l_wip_entity_name,
l_entity_type,
l_primary_item_id,
l_organization_code
from wip_entities wip,
org_organization_definitions ood
where wip.organization_id = ood.organization_id
and wip.wip_entity_id = p_wip_entity_id
and wip.organization_id = p_organization_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Job Name Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Job Name Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Job has some unidentified errors...';
END;


BEGIN

select operation_sequence_id,
operation_seq_num,
department_id,
department_code
into l_operation_sequence_id,
l_operation_seq_num,
l_department_id,
l_department_code
from wip_operations_v
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num = to_number(p_op_seq);


/*select bos.operation_sequence_id,
bos.operation_seq_num,
bos.department_id,
bd.department_code
into l_operation_sequence_id,
l_operation_seq_num,
l_department_id,
l_department_code
from bom_operation_sequences bos,
bom_operational_routings bor,
bom_departments bd
where bor.routing_sequence_id = bos.routing_sequence_id
and bos.department_id = bd.department_id
and bor.organization_id = bd.organization_id
and bor.organization_id = l_organization_id
and bor.assembly_item_id = l_primary_item_id
and bos.operation_seq_num = 10 ; */

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operation Sequence Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message ||' Operation Sequence Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Operation Sequence has some unidentified errors...';

END;


BEGIN

select resource_seq_num,
resource_id,
standard_rate_flag,
usage_rate_or_amount,
basis_type,
autocharge_type,
resource_code,
resource_type
INTO l_resource_seq_num,
l_resource_id,
l_standard_rate_flag,
l_usage_rate_or_amount,
l_basis_type,
l_autocharge_type,
l_resource_code,
l_resource_type
from WIP_OPERATION_RESOURCES_V
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num
and resource_code = p_resource_code ;

/*from bom_operation_resources bor,
bom_resources br
where bor.resource_id = br.resource_id
and operation_sequence_id = l_operation_sequence_id
and br.organization_id = l_organization_id
and bor.resource_seq_num = 1; */

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operation Resources Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operation Resources Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Operation Resources has some unidentified errors...';

END;


BEGIN

select acct_period_id
into l_acct_period_id
from org_acct_periods_v
where sysdate between start_date and end_date
and organization_id = l_organization_id ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Account Period Id Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Account Period Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Account Period has some unidentified errors...';

END;

BEGIN
Select person_id,employee_number
into l_employee_id, l_employee_num
from per_all_people_f
where employee_number = p_employee_num
and trunc(sysdate) between effective_start_date and nvl(effective_end_date,sysdate+1);
Exception
when others then
l_employee_id := null;
l_employee_num := null;
null;
END;


BEGIN

select (to_number(to_date(p_end_time,'DD-MON-RRRR HH:MI:SS PM') -
to_date(p_start_time,'DD-MON-RRRR HH:MI:SS PM')) * 24)
into v_quantity
from dual;


IF l_verify_flag <> 'N' then

BEGIN

INSERT INTO wip_cost_txn_interface
(last_update_date, last_updated_by, creation_date, created_by,
process_phase, process_status, transaction_type,
organization_id, organization_code, wip_entity_id,
wip_entity_name, entity_type, primary_item_id, transaction_date,
ACCT_PERIOD_ID, operation_seq_num, resource_seq_num,
department_id, department_code, resource_id, resource_code,
resource_type, usage_rate_or_amount, basis_type,
autocharge_type, standard_rate_flag, TRANSACTION_QUANTITY,
TRANSACTION_UOM, PRIMARY_QUANTITY, PRIMARY_UOM,
activity_id, activity_name, employee_id, employee_num
)
VALUES (SYSDATE, 6437, SYSDATE, 6437,
2, 1, 1,
l_organization_id, l_organization_code, l_wip_entity_id,
l_wip_entity_name, l_entity_type, l_primary_item_id, SYSDATE,
l_acct_period_id, l_operation_seq_num, l_resource_seq_num,
l_department_id, l_department_code, l_resource_id, l_resource_code,
l_resource_type, l_usage_rate_or_amount, l_basis_type,
l_autocharge_type, l_standard_rate_flag, v_quantity,
'HR', v_quantity, 'HR',
1, 'Run', l_employee_id, l_employee_num
);


fnd_file.put_line(fnd_file.log,'Data Migrated into Interface table successfully');
COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
fnd_file.put_line(fnd_file.log,'Data not Migrated successfully...'||l_error_message);
END;

else

fnd_file.put_line(fnd_file.log,l_error_message);

end if;

END LOOP;

fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');

end xxx_resource_txn_int_api ;
/